4

I have a problem of calculating area sizes of intersected polygons in postgis. I would represent the problem in an example (please see the picture). I have a gridsquare table with some attributes like color (green, blue, yellow) I also have an affected_area table with some attributes (like area_reference, the affected area is not multipolygon, can contains areas with the same area_reference). I would like to create a query as an end solution which calculate the sum sizes of the green, blue, yellow part-grids where they are intersect with the affected_area. The sum of green areas highlighted in red in the picture example. One area can also be fully within a grid. Any idea where should I start? Probably I should go with something similar: http://www.bostongis.com/postgis_intersection_intersects.snippet

enter image description here

3

Similar to what Zoltan has said, but you want to group by the color, which will then get you the sums for each color.

SELECT 
    sum(ST_Area(ST_Intersection(grid.geom, affected.geom))), 
    grid.color 
FROM grid, affected
WHERE ST_Intersects(grid.geom, affected.geom)
GROUP BY grid.color;

The final WHERE ST_Intersects(...) will not affect the answer, which is calculated by ST_Area(ST_Intersection(...)), but will significantly speed up the calculation for big areas, if you have a spatial index defined on grid.geom, and do no harm in case of small tables.

2
SELECT sum(st_area(st_intersection(grid.geom, affected.geom))) 
FROM grid, affected
WHERE grid.color = 'green' and st_instersects(grid.geom, affected.geom);

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.