2

I'm trying to learn how to write geopackage SQL queries so that the r tree index is used, but I can't seem to find any good information. I'm just trying to write a simple query in the qgis db manager like so:

SELECT pts.* 
FROM pts, polys, 
WHERE ST_WITHIN(pts.geom,polys.geom)

it takes an extremely long time, but running the same query in qgis using the select by location tool is nearly instant. I figure that the select by location tool must be using a spatial index, while the query is not. I know that spatial indexes must be explicitly included in a geopackage query, but I can not seem to find out how to do so. how do I rewrite this query to use the rtree spatial index?

I am forced by work to use QGIS version 3.12.3 to create and access my geopackages,although running gdalinfo --version in the OSGeo4W shell indicates I am using GDAL 3.0.4

3
  • 2
    Did you try do run SELECT CreateSpatialIndex('table_name', 'geom_column_name')? in the DB Manager then your query again to see if speedup? See also medium.com/@wherelytics/… to use index in your SQL query
    – ThomasG77
    Jul 13, 2021 at 14:50
  • I did, no dice. I think it needs to have the spatial index explicitly called to work. in spatialite, for example, the following query runs quite fast. SELECT points.* FROM polys, points WHERE ST_Within(points.geometry, polys.geometry) = 1 AND points.ROWID IN ( SELECT ROWID FROM SpatialIndex WHERE f_table_name = 'points' AND search_frame = polys.geometry ); however this syntax does not seem to work in geopackages
    – Alex
    Jul 13, 2021 at 18:22
  • You can find SQL examples with a subquery and JOIN from erouault.blogspot.com/2017/03/…. I am not sure if the finding about JOIN being faster is true for all use cases.
    – user30184
    Jul 14, 2021 at 21:39

1 Answer 1

2

GeoPackage is using "rtree" as a name of the spatial index. The basic usage with subquery is

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)
1
  • I'd add that the convention for Rtrees in Geopackages seems to be rtree_thislayer_geom for layer thislayer, and coordinates are labelled as miny, maxy, minx and maxx Nov 10, 2022 at 20:19

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.