Conflict between spatial and dbf indexes

[regodon]

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!

[warmerdam]

Francisco,

Can you supply the shapefile in question?

[regodon]

Attached shapefile.

[warmerdam]

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.

[chaitanya]

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.

[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.

[Even Rouault]

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)