2

I have a polygon layer of planning applications and I am wondering if anyone can give any advice and or has seen or written a blog post that might point me in the direction of creating relationships between polygons that exist wholly within other polygons using QGIS. For example placing the parent ID in a field of the child records based on a polygon being wholly within the parent polygon.

I am thinking that I might be able to do something like convert the polygon file to centroids and then analyse the resulting points to identify inclusion within the former polygon table.

As it stands the polygon / planning application layer is one single table so the ideal procedure would actually analyse bigger and smaller polygons within the same table rather than between a duplicate table but that procedure may not exist.

The final objective is to create planning application histories for sites.

  • Right I have been unable to create a totally full proof automatic process. The steps that I have followed so far that would appear to solve the problem are. 1) Create a copy file which just shows the centroids 2) Then do a Join attributes by location making sure that the centroid file is the TARGET vector layer - also ensure that * Take attributes of first located feature is selected. You should be left with what is essentially a Junction table. The only issue is that if there are multiple polygons beneath the centroid the oldest polygon is not always picked up. – Dalkeith Sep 3 '15 at 16:14
  • Following on (hit my text limit) I wonder how it ranks the polygons for pick up - is it based on the system key of the attribute table or maybe its spatial such that if there are two polygons the polygon furthest to south west or some combination is picked "first". If anyone knows how the first is chosen would be interested to hear. I did a short search but couldn't find anything. – Dalkeith Sep 3 '15 at 16:16
1

Interesting approach, but still it would have been challenging problem back in 2015: when you first posted this question. After that, there was one of big advancement in QGIS, Virtual Layer, which I think a good tool to solve this.

enter image description here

Let's say we have a big parent polygon and small child polygons, and we want to assign parent id only to the child polygon "2" (center, and only polygon within the parent polygon).

Virtual Layer syntax would be:

SELECT child.*, parent.id AS Parent_ID
FROM child, parent
WHERE st_contains(parent.geometry, child.geometry)=1

Then the output image is like below:

enter image description here

And the attribute table of such virtual layer contains both id of child polygon and parent polygon:

enter image description here

  • Thank you Kazuhito I will look into that. - specifically thank you for the SQL syntax - is that best used against a PostGIS database or should it work against SQL Server as well. I had issues gettomg tje st contains working against SQL Azure I am still working on getting this sorted. I know I will use it extensively when I can script these kind of queries directly in the database. None of this is part of my day to day work but just my general research. – Dalkeith Jun 25 '18 at 8:27
  • This is the link to a discussion I had about something similar using SQL Azure syntax. dba.stackexchange.com/questions/189622/… – Dalkeith Jun 25 '18 at 8:29
  • 1
    Hi @Dalkeith Thank you. I do not know anything about SQL Azure, but (imho) your syntax looks identical with what I have here. Only by my quick googling, I found a note that SQL Azure always returns null when the geometries do not share the common SRID. So... first check-point would be their CRS, maybe? – Kazuhito Jun 25 '18 at 9:21

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.