11

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?

0

4 Answers 4

12

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.

example

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

result

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:

1
  • Thank you Taras! That is an interesting way i did not think about. I just Posted another answer with the solution i came up
    – Skane
    Mar 17, 2020 at 10:58
4

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.

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

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.

result

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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