OGR Spatialite driver does not utilise spatial index
|Reported by:||Jukka Rahkonen||Owned by:||warmerdam|
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.8.131.52/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);