5

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?

Improve this question
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 Commented Jan 17, 2020 at 12:25

1 Answer 1

Reset to default
7

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 the image below.

input

All features of 'grid_layer' squares have a 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:

Improve this answer
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) Commented Jan 17, 2020 at 12:07

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.