I'm working with GPKG files built with QGIS3. I tried to execute some spatial queries but it was realy 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) ) 

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

I'm working with GPKG files built by QGIS3. I tried to execute some spatial queries but it was realy 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' alway return an empty set. Every table has its rtree_. What's wrong ?

Also I need to use geometries from other table as a 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) ) 

I'm working with GPKG files built with QGIS3. I tried to execute some spatial queries but it was realy 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) )