I have two feature Datasets. Both are Polygons. 'Dataset A' has very big Areas and 'Dataset B' just small ones. Now, 'Dataset B' still overlaps in 2 or 3 Zones from 'Dataset A'.
I want to perform a spatial join and put the result comma separated in just one row like this:
Zone_ID_A | Zone_ID_B 1 | 2,3 2 | 2,4 3 | 5 4 | 1,6 I know this is possible with ArcMap but i could not figure out a way to do this in QGIS, any ideas?
There is a possibility of using a "Virtual Layer" through Layer > Add Layer > Add/Edit Virtual Layer...
Let's assume there are two polygon layers: 'Layer_A' (brown) and 'Layer_B' (green) respectively, see image below.
With the following query, it is possible to perform a spatial join and put the result comma separated in just one row.
SELECT a.*, GROUP_CONCAT(b.info) AS concat_b_info FROM "Layer_A" AS a, "Layer_B" AS b WHERE ST_INTERSECTS(a.geometry, b.geometry) GROUP BY a.id The output Virtual Layer will look like
If all features from the 'Layer_A' should be involved in the final output i.e. where there is no intersection, please use the following query:
SELECT a.*, GROUP_CONCAT(b.info) AS concat_b_info FROM "Layer_A" AS a LEFT JOIN "Layer_B" AS b ON ST_INTERSECTS(a.geometry, b.geometry) GROUP BY a.id References:
Alright I figured out a way.
First I did a spatial join like you would normal do with the one-to-many option.
Then I downloaded the Plugin "Dissolve with stats". Here I dissolved the "Zone_ID_A" and choose the option "uniquification" at the field "Zone_ID_B". The Ouput was exactly what I wanted and descriped in the Original Post.
As an alternative, you may try the solution offered in this article "Summary Aggregate and Spatial Filters in QGIS". So, the expression in the Field Calculator will look like this:
aggregate( layer:='Layer_B', aggregate:='concatenate', expression:="info", concatenator:=',', filter:=intersects($geometry,geometry(@parent)) ) An up-to-date approach is to use one of the overlay functions e.g. the overlay_intersects() together with array_to_string().
This expression in the Field Calculator:
array_to_string(overlay_intersects('test', "info")) will return an array represented as a string of expression-based results for the features in the target layer ('test') intersected by the current feature, see the "result" field.
