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- _______________________________________________ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev