2

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.

  • I rather installed PostGIS and did the same query that took 155 s in just 0.4 s. – Albine Pro Apr 24 at 10:42
0

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)
| improve this answer | |
  • This is not what I'm looking for. I need too aggregate some statistics of neighbouring polygons, so dissolving is not the way. Btw. ST_UNION takes more time than plain select of b table. – Albine Pro Apr 24 at 9:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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