# Calculating the area of an intersection in PostGIS

I've got two tables in a PostgreSQL/PostGIS database, `blue` and `purple`, both holding polygons:

Now I need to calculate the overlapping area, for each polygon of the blue grid with only one of the purple polygons (the one in yellow):

For that, I've built the following query

``````SELECT b.name,
SUM (ST_AREA (
ST_INTERSECTION (
b.geom,
(SELECT geom FROM purple WHERE id = 'A')
)
)
)
FROM blue b LEFT JOIN purple p
ON ST_INTERSECTS (b.geom, p.geom)
GROUP BY b.name, b.geom
;
``````

However, the calculation is incorrect in some cases (i.e. producing figures that exceed the total area of the squares in the grid). What's wrong with the query?

• What is incorrect? Commented Jan 10, 2023 at 19:32
• question edited Commented Jan 10, 2023 at 19:39
• The question is confusing, in that you ask for the area "for each polygon", but your code does a `SUM()`, which indicates you actually want "the sum of areas for the neighboring polygons". Commented Jan 10, 2023 at 21:24

If 'A' is realy unique id

``````SELECT b.name, ST_AREA ( ST_INTERSECTION ( b.geom, p.geom ) )
FROM blue b, purple p
WHERE p.id = 'A';
``````

As it is written, the query considers any polygon from the `purple` layer, regardless of its ID. Then, since you don't use the joined polygon but rather re-query the layer and fetch the `A` polygon, the area of intersection with the unrelated `A` polygon is computed.

For example, the lower left `blue` polygon is touching 3 `purple` polygons, so the area computation will be done (and summed) three time, each time considering the area of intersection with the `A` polygon.

You would need to filter by ID on the join condition and use the joined geometry:

``````SELECT b.name,
SUM (ST_AREA (
ST_INTERSECTION (
b.geom,
p.geom
)
)
)
FROM blue b LEFT JOIN purple p
ON ST_INTERSECTS (b.geom, p.geom)
AND p.ID = 'A'
GROUP BY b.name, b.geom;
``````

PS: but as pointed by @RainForest, the `sum`/`group by` seems unnecessary as the intersection and area work with eventual multiparts