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));
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.
Given two overlapping polygons,p1
and p2
, in a table called t
:
We can use ST_Intersection
to get the intersection of both polygons:
SELECT ST_Intersection(p1,p2) FROM t;
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);