TransWikia.com

Query Geopackage using spatial index in QGIS DB manager

Geographic Information Systems Asked on February 24, 2021

I would like to do spatial join in QGIS DB manager on geopackage database.
The query should look like this

select * from Soil_C_cadaster a, 
(select * from Soil_C_cadaster
where forest_c is null) b
where st_touches(b.geom,a.geom)

It’s a cross join on the same layer with 13000 not so complex features but I’m limiting it to some 200 features in table b. Query takes more than two minutes, so I’m suspecting it’s not using spatial index by default as would PostGIS with spatial predicate.

Here is example of tested polygons:

enter image description here

In post like this, there’s written about use but just with one layer and search bounding box, not with more layers. I can’t imagine how to write it correctly.

One Answer

You could use ST_UNION to speed up your query:

SELECT a.* FROM soil_c_cadaster a, 
  (SELECT ST_UNION(geom) as geom FROM soil_c_cadaster WHERE forest_c IS NULL) b
WHERE ST_TOUCHES(b.geom,a.geom)

Answered by christoph on February 24, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP