One-to-many spatial join with results in one row in QGIS

Question

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?

Answer 1

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:

• SQLite GROUP_CONCAT

Answer 2

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.

Answer 3

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

Answer 4

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.