Spatial query to return multiple values

Question

I have a woodland scheme advice layer that has a polygon for each area of advice (brown layer in the picture). I also have a polygon layer of UK local authorities (red boundary layer in the picture).

I would like to perform a spatial query which returns the local authority name for each woodland advice scheme. This would work fine if each woodland advice scheme was found in just one local authority boundary.

However many are found across a boundary of two different authorities. For instance, one scheme sits across the boundaries of the authorities of Fife and Angus. The scheme is the yellow selected polygon in the picture and the red line is the authority boundary. How do I get the spatial join to return a value of Fife; Angus (or something similar to identify two values) instead of just Fife or Angus.

Answer 1

You can create a Virtual layer and make use of the group_concat() function to aggregate the names

Go to the menu Layer > Add Layer > Add/Edit Virtual Layer... and enter the following query. Replace the layer name for yours

select a.*, group_concat(b.name,'; ') as red_names
from brown a
join red b
 on st_intersects(a.geometry, b.geometry)
group by a.ID

Answer 2

If you want to have that information in the attribute table, try the aggregate function. Some examples here https://spatialthoughts.com/2019/04/12/summary-aggregation-qgis/

Answer 3

Use the following expression (works since QGIS 3.16) on layer polygon_2 that generates a list of the $id (or any attribute value you introduce here) of all intersecting features of layer polygon_1:

array_to_string (overlay_intersects('polygon_1', $id))

Screenshot: layer polygon_2 is labeled with the $id-value of the features from layer polygon_1 that it intesects. The selected multipart polygon (yellow) is intersects with feature 2 and feature 4 from layer_1: