7

I'm trying to create a subset of one layer which includes the geometries intersecting with the bbox of certain features of the same layer.

I tried to create a Virtual Layer in QGIS with the following statement:

SELECT *
FROM layer 
WHERE st_intersects(
        (
        SELECT st_envelope(geometry)
        FROM layer
        WHERE ID IN (1, 2, 3, 4)
        ),
        geometry
    )

The statement does not raise an error. The problem is, that it only gives back the geometries intersecting with the bbox of the feature with ID = 1 although the IDs 1, 2, 3 & 4 are included in the WHERE clause as list.

How would I need to change the query to include all the given IDs in the resulting subset?

4

Your query doesn't work because st_intersects() expects a single geometry, not a table. The same query in Postgres would show ERROR: more than one row returned by a subquery used as an expression

To avoid returning duplicates and to avoid optimization fences, you can make use of the where exists clause:

SELECT * FROM myLayer a
WHERE EXISTS ( 
  SELECT 1 from myLayer sub 
  WHERE st_intersects(st_envelope(sub.geometry),a.geometry) 
   AND sub.ID IN (1,2,3,4,5));
2
  • Thanks for your help! Unfortunately the Query causes QGIS to crash. The Layer I'm querying has around 4700 features. Maybe that's too much to handle.
    – r.bs
    Oct 18 at 8:51
  • @r.bs it shouldn't crash... I have noticed crashes caused by virtual layer are often a consequence of a wrongly named layer (ex: keeping a reference to the alias "a" but not defining the alias) or unbalanced parentheses.
    – JGH
    Oct 18 at 13:30
4

Try this:

select t1.* from (
(select * from layer) t1
join
(select geometry from layer where id in (1, 2, 3, 4)) t2 --My ids were 65, 204, 212
on st_intersects(t1.geometry, st_envelope(t2.geometry))
)

enter image description here

1
  • Thanks for your help! Unfortunately the Query causes QGIS to crash. The Layer I'm querying has around 4700 features. Maybe that's too much to handle.
    – r.bs
    Oct 18 at 8:51

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.