10

I have two feature Datasets. Both are Polygons. Dataset A has very big Areas and Dataset B just small ones. Now, B still overlaps in 2 or 3 Zones from Dataset A.

I want to perform a spatial join and put the result comma seperated 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?

10

In QGIS I can suggest using a "Virtual Layer" through Layer > Add Layer > Add/Edit Virtual Layer...

Let's assume we have two polygon layers "Layer_A" (brown) and "Layer_B" (green) respectively, see image below.

example

With the following query, it is possible to achieve the result, i.e. to perform a spatial join and put the result comma seperated in just one row.

SELECT a.*, GROUP_CONCAT(b.info) AS concat_b_info
FROM "Layer_B" AS b, "Layer_A" As a
WHERE ST_INTERSECTS(a.geometry, b.geometry)
GROUP BY a.id

The output Virtual Layer will look like as follwoing

result

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 for above example will look like:

aggregate(
  layer:='Layer_B',
  aggregate:='concatenate',
  expression:="info",
  concatenator:=',',
  filter:=intersects($geometry,geometry(@parent))
)

References:

| improve this answer | |
  • 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 at 10:58
3

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.

| improve this answer | |

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.