7

I'm working with GeoPackage files built with QGIS3. I tried to execute some spatial queries but it was really slow, so I'd like to speed it up with indexes.

After some readings I tried

create virtual table SpatialIndex using VirtualSpatialIndex(); select * from geosirene where ROWID in ( select ROWID from SpatialIndex where f_table_name = 'geosirene' and f_geometry_column = 'geom' and search_frame = BuildMbr(3807953,2282592,3841940,2314762,3035) )

I tested several versions but the 'select rowid from SpatialIndex' always returns an empty set. Every table has its rtree_* . What's wrong ?

Also, I need to use geometries from other table as search_frame , is it possible ?

select b.* from buildings as b, poi_merged as p where intersects(b.geom,p.geom) and b.ROWID in ( select ROWID from SpatialIndex where f_table_name = 'buildings' and f_geometry_column = 'geom' and search_frame = p.geom) )
Improve this question

1 Answer 1

Reset to default
5

The "search_frame" shortcut is only used by the SpatiaLite library. GeoPackage standard does not define such a shortcut but you must use subquery or join in the query. Query that is utilizing r-tree index with sub-query:

 SELECT * FROM the_table WHERE fid IN (SELECT id FROM the_rtree WHERE xmin <= bbox_xmax AND xmax >= bbox_xmin AND ymin <= bboy_ymay AND ymay >= bboy_ymin);

Query that is utilizing r-tree index with JOIN:

 SELECT * FROM the_table t JOIN the_rtree r ON t.fid = r.id WHERE r.xmin <= bbox_xmax AND r.xmax >= bbox_xmin AND r.ymin <= bboy_ymax AND r.ymax >= bboy_ymin;

This blog post deals with the differences between these two methods http://erouault.blogspot.fi/2017/03/dealing-with-huge-vector-geopackage.html.

Improve this answer
5
  • Ok, does it mean that it's useless to load_module('mod_spatialite') when I start to query on the GPKG with python, or is it still necessary for usage of spatial operators ? Is there a better way to do so ? Commented Apr 24, 2018 at 20:08
  • You need spatialite for the spatial operators. In that case it may be that you can use the spatial index of gpkg transparently through the "virtual gpkg" system. Try to find documentation from the spatialite site. If may be a challenge. Commented Apr 24, 2018 at 20:49
  • Wiki page about virtual gpkg is at gaia-gis.it/fossil/libspatialite/wiki?name=4.2.0+functions#8. I don't know if it is possible to make r-tree queries with select ROWID from SpatialIndex where f_table_name= for virtual gpkg tables. Commented Apr 25, 2018 at 6:12
  • when I select AutoGPKGStart(), it doesn't create virtual tables...I'll find another trick ; thanks anyway ! Commented Apr 25, 2018 at 7:41
  • Spatialite-gui is good reference implementation. Commented Apr 25, 2018 at 8:10

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.