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

1 Answer 1

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.

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 ?
    – vidlb
    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.
    – user30184
    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.
    – user30184
    Apr 25, 2018 at 6:12
  • when I select AutoGPKGStart(), it doesn't create virtual tables...I'll find another trick ; thanks anyway !
    – vidlb
    Apr 25, 2018 at 7:41
  • Spatialite-gui is good reference implementation.
    – user30184
    Apr 25, 2018 at 8:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.