[gdal-dev] Re: OGR Spatialite driver and spatial index

[Even Rouault]

Le lundi 29 août 2011 12:19:26, Jukka Rahkonen a écrit :
> Even Rouault <even.rouault <at> mines-paris.org> writes:
> > 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.
> 
> Ticket [GDAL] #4212 done with sample requests which should show the speed
> difference and a download link to a test database which is created with
> ogr2ogr v. 1.8.1.

Improvement commited in r23008.

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

And for comparison, the same for a postgis instance :
$ time ogrinfo pg:dbname=autotest osm_polygon -spat 1489000 6899000 1490000 
6900000 -al -so
real	0m0.168s
user	0m0.050s
sys	0m0.000s

However, there is no gain to expect from the above change for a request such 
as :
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

Indeed, when you specify -sql, the driver (and to my knowlegde, this is true 
for all other OGR drivers) makes no attempt to merge the spatial filter, so the 
spatial filter is evaluated on OGR side, which will be rather slow. So, you 
have 2 possibilities, either use the "-where highway is not null" on a layer 
so that OGR can merge the attribute and spatial filters, or incorporate at hand 
the spatial filter inside the SQL query specified with -sql.

> 
> -Jukka Rahkonen-
> 
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/gdal-dev