3

I have two polygon shapefiles in QGIS

I would like to have the polygon IDs of B listed in the attribute table of A for the case that they overlap.

As a demonstration:

intersect of two shapefiles

I tried combinations of spatial queries, intersects and spatial joins but I won't find an answer. Spatial join only allows statistical summaries of attributes (sum, average, mean).

I also tried using the model builder but didn't find the right arrangement of tools.

6

A virtual layer would work well here:

virtual layer dialog with query

resulting table

First hit 'Import' to add your layers then add the query:

SELECT A.id, group_concat(b.id)
FROM A LEFT Join B
ON ST_Intersects(a.geometry, b.geometry)
GROUP BY A.id

group_concat will concatenate all results into a comma seperated string (as your above screenshot). If you want to separate by something other than a comma you can define a custom separator.

If you want to name your columns you can use 'AS ...' for instance:

SELECT A.id AS a_id, group_concat(b.id) AS b_intersect ...
| improve this answer | |
  • Thank you. That is, among the current answers, the one that fits to my case best. I work with qgis quite long but didn't know this function. – AndyB Jan 30 '17 at 7:18
  • @AndyB I'm glad it worked for you! I just recently learned how to use virtual layers myself. Useful when you want some of the advantages of a database without actually implementing one. Remember to accept the answer if it worked for you. ;) – Jesse McMillan Jan 30 '17 at 8:44
1

Intersect is the right tool. Not sure what happened when you had tested it.

Polygons A and B with each attribute, A_id and B_id.
enter image description here

Intersected output "test_intersect" and its attribute table (RHS). enter image description here

| improve this answer | |
  • thank you. Unfortunately, it doesn't fully meet my requirements, because I need the IDs of A to remain unique instead of split-up. – AndyB Jan 30 '17 at 7:19
  • @AndyB Thanks for your reply. I now understand how intersect alone did not meet your expectation, especially so compared to Jesse's elegant solution. :) – Kazuhito Jan 30 '17 at 7:27
1

Here's an approach that would use the aggregate function in R to get your final data output.

  1. Run an intersection of the two layers. This will result in a layer with each and every combination of intersecting polygons.
  2. Export the attribute data only and load it in R as a data.frame.
  3. Then using the aggregate function to group by the larger polygon ID values (using the c function to concatenate all the smaller polygon IDs to a single row output). E.g., if your data.frame object is called dt then: aggregate(polyIDs1 ~ polyIDs2, data=dt, FUN=c)

Heres a good description

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