Split multiple attributes from a big polygon to smaller ones

Question

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.

Answer 1

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