[gdal-dev] OGR Spatialite driver and spatial index

[Even Rouault]

Selon Jukka Rahkonen <jukka.rahkonen at mmmtike.fi>:

> Hi,
>
>
> 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))"

The MBRIntersects() stuff is exactly what the OGR driver does, when you specify
-spat, so it is not surprising that they have the same runtime.

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

Hum, the OGR driver was indeed written a bit too naively, assuming that
MBRIntersects() would be similar to the && operator in PostGIS. Apparently not.
Strange that this hasn't been reported before, but I assume you can only see the
difference with big amount of data, like your OSM database (and the
MBRIntersects() test must be faster than evaluating the spatial filter on OGR
side, so that's perhaps why it wasn't seen at implementation time). Anyway,
would you mind opening a Trac ticket about this ? Changing the request to use
the spatial index table should not be too difficult.

Thanks,

Even