3

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

enter image description here

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):

enter image description here

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?

3
  • What is incorrect?
    – Ian Turton
    Jan 10, 2023 at 19:32
  • question edited
    – jpinilla
    Jan 10, 2023 at 19:39
  • 1
    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".
    – Vince
    Jan 10, 2023 at 21:24

2 Answers 2

5

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';
0
4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.