2

I am looking for a sort of spatial (un)join function.

Let's say I have 2 tables, both with a large number of polygons. The first one has a pop/polygon column in his attribute table. The 2nd one has smaller polygons that fit exactly into the bigger ones has a sum (these polygons represent census limitations).

Is there a function under QGIS or PostGreSQL that would separate the population/polygon from the 1st table into the smaller polygons of the 2nd table.

Example of the situation and end result: ID1 of table_1 has a population of 4. ID1, ID2, ID3 and ID4 of table_2 fit into ID1 of table_1. The function would create a population column into table_2 where ID1, 2, 3 and 4 each have a population of 1.

In other words I am looking for a function to do the exact opposite of the "Join attributes by location" in QGIS.

4

In the example below, the SubRegions do 'fit' into the Regions, but I convert the geometry of the SR into centroid (UPDATE: This is better done using ST_PointOnSurface as suggested below) so I'm doing more of a 'point-in-polygon' type of intersect, rather than chancing that some edges aren't exactly coincident.

From there, simply intersecting the two sets of geometries and summing the 'population' field of the SR's and grouping by 'region' from R gets me what I need:

    select 
    r.region
   , sum(sr.population)
    from "Regions" as r
    join "SubRegions" as sr on ST_Intersects(r.geom, ST_PointOnSurface(sr.geom))
    group by r.region

To get the geometry back to the Regions if you want to map them, simply wrap a subquery around the intersect query and join on 'region' to the source region table, then grab what you need from both queries:

select 
r.geom
, data.* 
from (
select 
r.region
, sum(sr.population)
from "Regions" as r
join "SubRegions" as sr on ST_Intersects(r.geom, ST_PointOnSurface(sr.geom))
group by r.region
) as data
join "Regions" as r on data.region = r.region
  • 2
    It's safer to use ST_PointOnSurface because it "returns a POINT guaranteed to lie on the surface", which is not the case with ST_Centroid. – thibautg Jun 30 '16 at 10:54
  • @thibautg so it would be like using ST_Centroid but in the case where the centroid was outside a wacky polygon it would force it inside the polygon? I'll try this out! Thanks! – DPSSpatial Jun 30 '16 at 14:11
  • Yes! See here. – thibautg Jun 30 '16 at 14:14

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.