[gdal-dev] OGR Spatialite driver and spatial index

[Jukka Rahkonen]

Hi,

Does the OGR SQLite/Spatialite driver really utilise the spatial
index if such exists in the Spatialite database? The documentation
suggests so http://gdal.org/ogr/drv_sqlite.html.  However, I haven't
been able to see any difference in my speed tests with ogr yet.

I made a set of tests with ogrinfo against a Spatialite database
with and without spatial index. These two tests took the same 30 
seconds to run both with and without spatial index. 

ogrinfo OSM_Finland.sqlite -sql "select geometry, osm_id ,highway,ref, 
name, tunnel from osm_line where highway is not null" 
-spat  389116 6677305 389579 6677661

ogrinfo OSM_Finland.sqlite -sql "select geometry, osm_id ,highway,ref, 
name, tunnel from osm_line where highway is not null 
AND MBRIntersects(geometry, BuildMBR(389116,6677305,389579,6677661))"

This one takes only 2 seconds if spatial index table is available.

ogrinfo OSM_Finland.sqlite -sql "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 > 389116 
AND xmin < 389579 AND ymax > 6677305 AND ymin < 6677661)" 

The Spatialite man Alessandro Furieri wrote this information:

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

Is the OGR Spatialite driver clever enough for making a conclusion 
"If exists spatial index table 'idx_table_GEOMETRY' for layer 'table' 
do use if when building bounding box filter"?

I found a blog posting about this thing and the author tells how 
he could make SharpMap to render blisteringly fast from Spatialite 
http://epsg27700.blogspot.com/2009/08/adventures-with-spatialite.html

I would not call my OpenStreetMap rendering from Spatialite with 
Mapserver 6.0 (MS4W 3.0.3) blistering at the moment. The highway 
layer takes more than 5 minutes to render even when zoomed very 
close. The corresponding SQL query which is utilising the 
idx_osm_line_GEOMETRY takes less than a second to run directly 
through Spatialite-GUI so there is absolutely something to improve.

-Jukka Rahkonen-