2

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.

enter image description here

3
  • Are you asking about this One-to-many spatial join with results in one row?
    – Taras
    Commented Aug 11, 2020 at 9:03
  • Yes, that looks like it will work. Thanks Commented Aug 20, 2020 at 8:52
  • Actually @JGH's answer is the same that I posted in that thread, so you can accept his/her answer as a valid one
    – Taras
    Commented Aug 20, 2020 at 9:06

3 Answers 3

3

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

1

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:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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