3

I'm trying to use QGIS to profiling polygons in a layer. There is also another grid layer, each grid contains its own demographics information.

example1

Assume the demographics are evenly distributed within the same grid. Therefore, I'm deriving the value for any demographic attribute of an outlet by summing the sections in different grids weighted by the area. and the calculation for area A, for instance, population, would be:

example2

I know I can use the clip to have the intersects layer, use the field calculator to get the areas and then calculate the population for A1 and A3 separately.

How could I aggregate the sum population and add the population back to area A in the original polygon layer?

1
  • I'd like to point out my initial approach is to first calculate the actual area of the intersects in sq km, then use it to divide over the area of the grid to get the weight for sum. In real case, this would require additional calculation steps and probably projection to another CRS. @Taras 's approach saved me from those efforts – HanRF Jan 17 '20 at 12:25
2

Tested on QGIS 2.18 and QGIS 3.4

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

I have tried to recreate your example. There are two overlapping polygon layers called "layer"(yellow) and "grid_layer"(blue) accordingly, see image below.

input

All features of "grid_layer" squares have 10.000 m² area.

With the following Query, it is possible to calculate the sum population and add the population back to the original polygon layer namely "layer"(yellow).

SELECT l.*,
    ROUND(SUM(g."Population"*st_area(l.geometry)/st_area(g.geometry)), 0) AS pop_l,
    ROUND(SUM(st_area(l.geometry)/st_area(g.geometry)),4) AS l_area_calc,
    ROUND(SUM(st_area(l.geometry)),4) AS l_area
FROM "layer" AS l
JOIN "grid_layer" AS g ON st_intersects(l.geometry, g.geometry)
GROUP BY l.id

The output layer with its attribute table will look like

ouput

where
'pop_l' is the population that had to be calculated
'l_area' is the initial area of "layer"'s features
'l_area_calc' is the calculated area of "layer"'s features where they intersect with "grid_layer"

To save the Virtual Layer as a shapefile via Right click > Save as... > ESRI Shapefile.


References:

1
  • Thank you Taras, that's fantastic. I tested it out and yes it works (there's a syntax error in your query, the extra comma before FROM). However, since it is a virtual layer, I suppose it will be calculated every time reopening the project. So if scaling up the performance would be a problem. I tried to physically save the layer, but QGIS only allow us to save as a style file. May I ask whether there is a way to physicalize it? (that's also why initially I wanted to add the population field back to the original layer) – HanRF Jan 17 '20 at 12:07

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.