Opened 9 years ago

Closed 9 years ago

#3722 closed defect (fixed)

Conflict between spatial and dbf indexes

Reported by: regodon Owned by: chaitanya
Priority: normal Milestone:
Component: OGR_SF Version: 1.7.2
Severity: normal Keywords:
Cc: warmerdam

Description

It seems there is a problem quering shpefiles with both espatial (qix) and dbf (idm-ind) indexes.

  • Query on DBF field using idm/ind index:
# ogrinfo -al -q -sql "SELECT ID from testdata WHERE ID='0000'" testdata.shp |grep POINT |wc -l
38390

Takes about 9 seconds and returns 383904 points.

  • Query on DBF using idm/ind index limited to a window:
# ogrinfo -al -q -spat 0 35 30 60 -sql "SELECT ID from testdata WHERE ID='0000'" testdata.shp |grep POINT |wc -l
281047

Takes about 19 seconds and return 281047 points. It is much slower than without spatial limitations. Lets create an spatial index:

# ogrinfo -al -q -sql "CREATE SPATIAL INDEX ON testdata" testdata.shp
INFO: Open of `testdata.shp'
      using driver `ESRI Shapefile' successful.
  • Lets query again:
# ogrinfo -al -q -spat 0 35 30 60 -sql "SELECT ID from testdata WHERE ID='0000'" testdata.shp |grep POINT |wc -l
1

It returns instantly, but only first feature is returned instead of 281047!

Attachments (1)

testdata.tar.bz2 (324.7 KB) - added by regodon 9 years ago.

Download all attachments as: .zip

Change History (8)

comment:1 Changed 9 years ago by warmerdam

Component: defaultOGR_SF
Status: newassigned

Francisco,

Can you supply the shapefile in question?

Changed 9 years ago by regodon

Attachment: testdata.tar.bz2 added

comment:2 Changed 9 years ago by regodon

Attached shapefile.

comment:3 Changed 9 years ago by regodon

Version: unspecified1.7.2

comment:4 Changed 9 years ago by warmerdam

Cc: warmerdam added
Owner: changed from warmerdam to chaitanya
Status: assignednew

Chaitanya,

Could you attempt to reproduce this problem, and make a preliminary investigation of what might be causing it? You can bounce it back to me if it gets quite involved. You might want to try in 1.7 and trunk since trunk is changing a lot.

comment:5 Changed 9 years ago by chaitanya

Status: newassigned

Francisco,

I did not find any problem with the data. Also, GDAL's shapefile driver doesn't consider dbf file's indexes.

The shapefile you provided has only 104721 features. Is this the same file? If not, can you check if this is giving you problems? If you still find the problem, be include the dbf's index files this time.

comment:6 Changed 9 years ago by regodon

Hi,

it's a smaller shapefile because Trac doesn't allow to upload files bigger than 1MB.

You can reproduce the problem this way:

[regodon@calima p]$ ls -la
total 3936
drwxr-xr-x  2 regodon regodon    4096 2010-08-20 15:11 ./
drwx------ 34 regodon regodon   28672 2010-08-20 15:11 ../
-rw-r--r--  1 regodon regodon  209507 2010-08-17 15:44 testdata.dbf
-rw-r--r--  1 regodon regodon 2932288 2010-08-17 15:44 testdata.shp
-rw-r--r--  1 regodon regodon  837868 2010-08-17 15:44 testdata.shx
[regodon@calima p]$ ogrinfo -al -q -sql "SELECT ID from testdata WHERE ID='0000'" testdata.shp |grep POINT |wc -l
97807
[regodon@calima p]$ ogrinfo -al -q -spat 0 35 30 60 -sql "SELECT ID from testdata WHERE ID='0000'" testdata.shp |grep POINT |wc -l
94052
[regodon@calima p]$ ogrinfo -sql "CREATE INDEX ON testdata USING ID" testdata.shp
INFO: Open of `testdata.shp'
      using driver `ESRI Shapefile' successful.
[regodon@calima p]$ ogrinfo -al -q -spat 0 35 30 60 -sql "SELECT ID from testdata WHERE ID='0000'" testdata.shp |grep POINT |wc -l
94052
[regodon@calima p]$ ogrinfo -sql "CREATE SPATIAL INDEX ON testdata" testdata.shp
INFO: Open of `testdata.shp'
      using driver `ESRI Shapefile' successful.
[regodon@calima p]$ ogrinfo -al -q -spat 0 35 30 60 -sql "SELECT ID from testdata WHERE ID='0000'" testdata.shp |grep POINT |wc -l
1

After creation of spatial index, only one feature is retrieved.

comment:7 Changed 9 years ago by Even Rouault

Resolution: fixed
Status: assignedclosed

Tamas did fix that partially in trunk for the IN case (r20101). Was left the general case...

Fixed in trunk (r20484), branches/1.7 (r20485) and branches/1.6 (r20486)

Note: See TracTickets for help on using tickets.