Search code examples
sqlpostgresqlgeospatialpostgisspatial-query

Calculate the percentage of polygon inside the buffer in postgresql


I have a table named Operation with multiple points as geom and I have created a buffer of 100m and added as a new column to the same table. I have another table named Residential which has multiple polygons, the goal is to find the percentage of polygons are inside each buffer and add it as a column in the Operation table. I am not sure how to proceed with this.

SELECT AddGeometryColumn ('public','operations','buffer',4326,'POLYGON',2);
UPDATE operations SET buffer = ST_Buffer(geom::geography,100)::geometry;

ALTER TABLE operations ADD COLUMN pts int;

UPDATE operations o 
SET pts = (SELECT count(*) FROM supermarket s
           WHERE ST_Contains(o.buffer,s.geom));

I have done till this, the following lines are not suited to get the percentage. How to approach this.

    ALTER TABLE public."Operation" ADD COLUMN res_percent double precision;
UPDATE public."Operation"  
SELECT      
  ST_Intersection(ST_MakeValid(r.geom),o.buffer) AS intersection,   
  ST_Area(ST_Intersection(ST_MakeValid(r.geom),o.buffer))/ST_Area(r.geom)*100)) 
FROM public."Residential" r, public."Operation" o 
WHERE ST_Intersects(o.buffer,ST_MakeValid(r.geom));

dbfiddle


Solution

  • Use ST_Area to get the area of your polygons, extract the area of their intersection using ST_Intersection, then finally use both intersection area and polygons area to calculate the overlap proportion.

    Example

    Given two overlapping polygons,p1 and p2, in a table called t :

    enter image description here

    We can use ST_Intersection to get the intersection of both polygons:

    SELECT ST_Intersection(p1,p2) FROM t;
    

    enter image description here

    Now we can use ST_Area to calculate the area of this intersection:

    SELECT ST_Area(ST_Intersection(p1,p2)) FROM t;
    
        st_area     
    ----------------
     601950.9523732
    (1 row)
    

    So, using ST_Area in both intersection and polygons you are able to calculate which percentage of a polygon overlaps with another, e.g.

    SELECT 
      ST_Area(ST_Intersection(p1,p2))/ST_Area(p2)*100 AS perc_p2, 
      ST_Area(ST_Intersection(p1,p2))/ST_Area(p1)*100 AS perc_p1
    FROM t;
    
         perc_p2      |     perc_p1      
    ------------------+------------------
     30.0839473794556 | 37.9061430278047
    (1 row)
    

    Demo: db<>fiddle

    Given your description your query should look somewhat like this:

    SELECT   
      ST_Intersection(r.geom,o.buffer) AS intersection,
      ST_Area(ST_Intersection(r.geom,o.buffer))/ST_Area(r.geom)*100
    FROM residential r, operations o
    WHERE ST_Intersects(o.buffer,r.geom);