# Combining data from overlapping polygons and assign [duplicate]

I have two multipolygons layers:

Layer A: Census blocks with data from 2010 (last census).

Layer B: A study about poor neighborhoods with data from 2016 (POOR NH).

Both layers have a "population" attribute.

Aim: to update the CENSUS data with the POOR NH information (more updated and accurate).

I want to merge and replace the data from layer B to A, but assigning the "population" value proportionally (and only in cases where census blocks contains "poor places").

As you will see, being two different data matrices (pointing to different objectives), the sizes of the polygons do not match (the POOR NH are generally larger). Therefore, what I want is to assign the values ​​from Layer B to Layer A, but respecting the proportions in which they overlap, simultaneously. That is, if a 30% (example: 1000) of Layer B is within 50% (2000) of Layer A, that '300' replaces the '1000' from the other layer. The new "population" value of layer A, instead of being '2000', must be '1300'. Of course, assuming a homogeneous distribution of the population throughout the census blocks.

I've tried with intersection, split with lines (converting the polygon Census layer to polylines) and spatial join, but all generates multiples duplicated rows, because each combination creates a new polygon with all the features from the previous ones.

I assume that what I want to achieve requires several steps, but I'm stuck.

How to combine data from overlapping polygons?

• Hello facu, must the problem be solved in QGIS or do you have the possibility to use postgis? Am I right, that in the end the census layer should have a value for the poor neighborhood additionally? Mar 9, 2019 at 15:21
• In ArcGIS the tool would be "Spatial Overlay" . I don't see an equiv in QGIS, you could just use Union and then calculate the respective areas with field calculator to do the proportionate reassignment. However, looking at your example it does not look like an assumption of homogeneity is very tenable as the area difference is proportionately large so the result is likely to suffer from significant MAUP. Mar 9, 2019 at 16:37
• @Michael: 1) i'm new to the GIS world, so i have no experience with PostGIS (yet), but i am willing to learn. 2) my aim is to replace (update) the "population" variable in the census layer, Mar 10, 2019 at 21:12
• @AnserGIS: about the MAUP, please take a look at the image i've just uploaded. As you can see, the "poor NHs" layer is divided into little segments (some match with the census blocks, other do not). // How do you recommend to do the "Union and proportionate reassignment" process? Can you give me some hints, please? Mar 10, 2019 at 21:36

If I assume, there is a running PostgreSQL with PostGIS, but the data is probably a shapefile and already not loaded to it, this could be a workflow from within QGIS. (Sorry, but the screenshots are from a german QGIS)

My testfiles are looking like this:

1. If not done yet, create a databaseconnection in QGIS as described in the manual.

2. In the Database-Manager load the QGIS-layers "census" and "poornh" to the database

1. After that you can open a SQL-window here to execute the statements. Of course you can use psql or pgadmin if desired.

Now we create a new table for the intersecting data poornh within census and percentile population of poornh. Serveral columns are not necessary, but I added them just for control/explanation.

Statement (!! in QGIS you mustn't add the comments after --... !!):

create table censpnhintersect( gid serial primary key, censid integer, -- ID of census block censblock varchar, -- name of census block pnhid integer, -- ID of poornh block pnhname varchar, -- name of poornh block pnhareaorig numeric, -- area of original poornh block pnhareaintersec numeric, -- area of intersection poornh pnhpoporig integer, -- population of poornh original pnhpopintersec numeric -- population partial to intersecting area )

The resulting tablelayout looks like this:

1. Now insert the data with

insert into censpnhintersect(censid, censblock, pnhid, pnhname, pnhareaorig, pnhareaintersec, pnhpoporig, pnhpopintersec) select c.id, c.block, p.id, p.pnhname, ST_Area(p.geom), ST_Area(ST_Intersection(p.geom, c.geom)), p.pnhpop, p.pnhpop * (ST_Area(ST_Intersection(p.geom, c.geom)) / ST_Area(p.geom)) from census c join poornh p on (ST_Intersects(c.geom, p.geom))

The value for the percentile poornh-population is population * (area of intersecting poornh polygon / area of census polygon)

And here the filled table:

1. At least we can update the population in the table census with the new desired values:

update census c set population = ((c.population * (x.pnhareasum / ST_Area(c.geom))) + x.pnhpopsum)::integer from (select censid, sum(pnhareaintersec) as pnhareasum, sum(pnhpopintersec) as pnhpopsum from censpnhintersect group by censid) x where c.id = x.censid

Here we make a select from our new table group it by the census-block and sum the poornh values of intersecting area and percentile population. This is then used to update census-population as explained in your posting.

Here is the resulting census table:

1. At least you can add the census table to QGIS and export it to other desired file formats.

Hopefully this helps in short a little with the problem.

• Seems to me a great answer! Just two notes: + ST_Intersects returns true when two polygons just touches each other, ST_Intersection can return a point or a linestring in that cases and ST_Area will return zero, maybe not a problem here since you are not dividing by it; + Can not verify since we are not seeing the area of the original blocks, but seems to be an error in the formula of the new population, you are weighing the original population by the intersected area instead of by the non-intersected one: `1 - x.pnhareasum / ST_Area(c.geom)`. Mar 13, 2019 at 2:08
• Hello Gabriel, thanks for the advice with the ST_Intersects. Could be especially a point, if it's desired to write the intersecting polygon also to the new table. The formula for the new population is how I understood facu's description"...That is, if a 30% (example: 1000) of Layer B is within 50% (2000) of Layer A, that '300' replaces the '1000' from the other layer. The new "population" value of layer A, instead of being '2000', must be '1300'..." Mar 13, 2019 at 7:22
• Yes, here is something like: a 50% (example: 400) of Layer B is within 10% (2000) of Layer A. By your graphic, new population must be nearest of the original values, also greater. In the question post, if a neiborhood covers the 100% of a census block, the block's population must be replaced by the neiborhood population (updated and accurate), if that is the case in your formula both populations are being added. It is just a detail about the calculation. The good thing is that you took the job to create a test project to the answer. Mar 13, 2019 at 10:42
• You are right. I did'nt really understood the intension of facu and why the math should look like in his example. But in the end I just made my posting like desired. Hopefully facu understands the idea and mechanic behind the statements and he should be able to modify as needed. Mar 14, 2019 at 4:22

If your going from B to A and most of the units from B are fully contained by A then ecological fallacy should not be too big a concern but what if a unit in A has a substantial subset of B units spanning across its border? You should at least check for this as below. If you wish to compare with other data in A then issues of scale and MAUP are still to be considered. This said, to make the comparison :

1) Create a field in B "AreaB" and calculate the area of the polygons.

2) Create a field in B "PopByAreaB" and calculate the population per meter2 in B.

3) Union A and B : "UnionAB"

4) Create field "UnionArea" in UnionAB and calculate the area of the polygons.

5) Create field in UnionAB : "PopEstimate"

6) PopEstimate = UnionArea * PopByArea

(NB - Do NOT multiply by the area of set A)

Now to check the potential for ecological fallacy :

7) Create a field "InOutArea"

8) InOutArea = UnionArea / ("AreaB" / 100)

That gives you the proportion each UnionAB polygon represents of its original AreaB. (NB, you will need to select only those rows with AreaB > 0 or it will return a divide by zero error)

9) use the "Group Stats" plug in to sum PopEstimate and average InOutArea per Census tract.

If there are polygons found where mean InOutArea is quite low.. I dont know what limit to use.. then check the proportion that the PopEstimate in cross boundary polygons represents of the summed population estimate per census tract. If it not very low then the result for that Census polygon needs a big question mark.