Opened 6 years ago

Closed 6 years ago

Last modified 6 years ago

#4212 closed defect (fixed)

OGR Spatialite driver does not utilise spatial index

Reported by: Jukka Rahkonen Owned by: warmerdam
Priority: normal Milestone: 1.9.0
Component: OGR_SF Version: unspecified
Severity: normal Keywords:
Cc:

Description

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);

Change History (2)

comment:1 Changed 6 years ago by Even Rouault

Milestone: 1.9.0
Resolution: fixed
Status: newclosed

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

comment:2 Changed 6 years ago by 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)

Note: See TracTickets for help on using tickets.