PostGIS Overlapping Features – How to Calculate the Overlap of Two Polygons in PostGIS

overlapping-featurespostgispostgresqlvector

I am trying to calculate the percentage overlap of two vector polygon layers and saving the result as a separate column. The code below is working:

CREATE TABLE parcels_all_shapefile_new AS
SELECT DISTINCT a.*, COALESCE((ST_Area(ST_INTERSECTION(a.wkb_geometry, b.wkb_geometry))/ST_Area(a.wkb_geometry))*100, 0) AS aw
FROM parcels_all_shapefile_flood2 a LEFT OUTER JOIN filter_ancientwoodlands b ON ST_Overlaps(a.wkb_geometry, b.wkb_geometry) OR ST_Within(b.wkb_geometry, a.wkb_geometry);

However I am getting an extra ~50,000 rows than the original table. The DISTINCT function has worked for me in the past to deal with this issue, when multiple polygons are intersecting with each other.

Can anyone see what I am missing? Also does anyone know how to deal with a situation when a polygon overlaps multiple polygons and you want to add the percentages together as a single result?

UPDATE: The below answer worked except I had to change INNER JOIN to LEFT OUTER JOIN, here is the code:

CREATE TABLE parcels_all_shapefile_aw AS
SELECT  
    a.ogc_fid, a.poly_id, a.title_no,
    SUM(COALESCE((ST_Area(ST_INTERSECTION(a.wkb_geometry, b.wkb_geometry))/ST_Area(a.wkb_geometry))*100, 0)) AS aw
FROM parcels_all_shapefile_flood2 a 
    LEFT OUTER JOIN filter_ancientwoodlands b ON 
        ST_Overlaps(a.wkb_geometry, b.wkb_geometry) OR ST_Within(b.wkb_geometry, a.wkb_geometry)
GROUP BY a.ogc_fid, a.poly_id, a.title_no

Best Answer

Assuming the ancient woodlands polygons do not overlap with each other, the following should do what you want ... of course you will need to fill in the columns

CREATE TABLE parcels_all_shapefile_new AS
SELECT  
    a.<columns>, -- << specify the columns you want
    SUM(COALESCE((ST_Area(ST_INTERSECTION(a.wkb_geometry, b.wkb_geometry))/ST_Area(a.wkb_geometry))*100, 0)) AS aw
FROM parcels_all_shapefile_flood2 a 
    INNER JOIN filter_ancientwoodlands b ON 
        ST_Overlaps(a.wkb_geometry, b.wkb_geometry) OR ST_Within(b.wkb_geometry, a.wkb_geometry)
GROUP BY a.<columns> -- << same list as in the SELECT clause
Related Question