Spatial indexes in GeoPackage files

Question

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

Answer 1

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.