Splitting population between overlapping polygon features

Question

I have a quadratic grid with a different population in each square as the first layer and I have polygons that I made with the ORS Plugin as the second layer.

In my previous question, I got some good help to calculate the population that each polygon covers in each square.

However, I now need to take into consideration that polygons overlap in some squares, and then I need to divide the population in those areas between the polygons. I am fine with assuming that the population is evenly distributed in each square, and I want to divide the population evenly among the polygons that overlap.

Please keep in mind that I may have several polygons overlapping. I also have many polygons, about 4.000, and squares, several millions.

OBS:

"ID" | The polygons IDs
"Id-2" | Squares in the grid’s IDs
"POP-2" | The population in each Square

Answer 1

In QGIS I can suggest using a "Virtual Layer" through Layer > Add Layer > Add/Edit Virtual Layer...

Let's assume there are two polygon layers 'grid_test' (red) and 'grid_test2' (green) respectively, see image below.

With the following query, it is possible to achieve the result, i.e. to to calculate the population that each polygon in 'grid_test' covers in each polygon in 'grid_test2'.

SELECT a.*, sum(b."popul"*st_area(st_intersection(a.geometry, b.geometry))/st_area(b.geometry)) AS res
FROM "grid_test" AS a
LEFT JOIN "grid_test2" AS b ON st_intersects(a.geometry, b.geometry)
GROUP BY a.id

The output Virtual Layer will look like as following

Note: Since we are talking about population, the application of the round() function can make sense i.e. round(sum(b."popul"*st_area(st_intersection(a.geometry, b.geometry))/st_area(b.geometry)))

---

References:

• Transferring flows (connections + values) between polygons
• Flow data distribution within features of overlapping layers
• Creating relations many to one in QGIS
• Calculating point layer values within polygon features in QGIS 2