Calculate area parts of polygon intersections

Question

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

Answer 1

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.

Answer 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);