id,summary,reporter,owner,description,type,status,priority,milestone,component,version,severity,resolution,keywords,cc
4212,OGR Spatialite driver does not utilise spatial index,jratike80,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.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);
}}}
 
",defect,closed,normal,1.9.0,OGR_SF,unspecified,normal,fixed,,