OGR Spatialite driver does not utilise spatial index

[Jukka Rahkonen]

OGR Spatialite driver is made in belief that the MBRIntersects() function would automatically utilise the spatial index in the Spatialite database. This is not the case as Alesandro Furieri wrote:

"SpatiaLite isn't PostGIS: you *must* explicitly write your SQL Queries in such a way to access the corresponding Spatial Index table as appropriate.

Please note well: in SQLite/SpatiaLite the R*Tree Spatial Index simply is another table between many others. The SQL engine has absolutely no idea that a strict correlation exists between the "geometry" table and the corresponding R*Tree. So you are explicitly required to define an explicit sub-query in order to inquiry the R*Tree."

Test database is available at ​http://188.64.1.61/tiedostot/berlin_2011_08_25.zip

Plain MBRIntersects does not use spatial index and is is slow

SELECT geometry, osm_id, highway, ref, name, tunnel 
FROM osm_line 
WHERE highway IS NOT NULL AND 
  MBRIntersects(geometry, 
    BuildMBR(1487400, 6894200, 1487500, 6894300));

This query is using spatial index table idx_osm_line_GEOMETRY and it is much faster. Difference with bigger databases and small bounding boxes is huge.

SELECT geometry, osm_id, highway,ref, name, tunnel 
FROM osm_line 
WHERE highway IS NOT NULL AND ROWID IN (
  SELECT pkid FROM idx_osm_line_GEOMETRY
  WHERE xmax > 1487400 AND xmin < 1487500 
    AND ymax > 6894200 AND ymin < 6894300);

[Even Rouault]

r23008 /trunk/gdal/ogr/ogrsf_frmts/sqlite/ (ogr_sqlite.h ogrsqlitetablelayer.cpp): Spatialite: speed-up spatial filter on table layers by using spatial index table instead of MBRIntersects() function (#4212)

Before :

$ time ogrinfo berlin.sqlite osm_polygon -spat 1489000 6899000 1490000 6900000 -al -so
real	0m0.619s
user	0m0.580s
sys	0m0.040s

After :

$ time ogrinfo berlin.sqlite osm_polygon -spat 1489000 6899000 1490000 6900000 -al -so
real	0m0.053s
user	0m0.020s
sys	0m0.040s

[Even Rouault]

r23015 /trunk/gdal/ogr/ogrsf_frmts/sqlite/ (4 files): Spatialite: fallback to MBRIntersects() test if there's no spatial index but spatialite is loaded (#4212)