Creating subset of layer intersecting with BBOX of certain features of the same layer using Virtual Layer in QGIS

Question

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?

Answer 1

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

Answer 2

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