1

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

Aravinth
  • 91
  • 1
  • 12

1 Answers1

2

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);
Jim Jones
  • 18,404
  • 3
  • 35
  • 44
  • Thank you, I have tried to implement it but I had an error that 'ERROR: GEOSIntersects: TopologyException: side location conflict at 13.5274576 52.423403700000001 SQL state: XX000' – Aravinth Jun 09 '21 at 10:44
  • or even this if the problem does not go away (workaround to make the query run): `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 residential r, operations o WHERE ST_Intersects(o.buffer,ST_MakeValid(r.geom));` @Aravinth – Jim Jones Jun 09 '21 at 10:58
  • @Aravinth start first with a `LIMIT` to see if it works, then use `EXPLAIN` to see if the index is being used at all. – Jim Jones Jun 09 '21 at 16:41
  • If I use the LIMIT it works, I tried to use the Explain but the Index is not used while doing the query. what can be done to use the index, I have tried to rectify it but ended of without any progress. – Aravinth Jun 09 '21 at 19:33
  • @Aravinth add query and the result of explain to your question, otherwise there is no way we can give you a decent suggestion. – Jim Jones Jun 09 '21 at 19:54
  • @Aravinth you using an equal operator between an integer an a geometry `geom = 45`. You don't have to be sorry.. we all started from the beginning one day :) But as you can see, a question about "overlap proportion" can quickly be deviated to invalid geometries, how to create a fiddle and using explain. It is absolutely fine to ask, but keeping the conversation very long sort of makes the whole post incomprehensible for other users. So, I suggest you to first get familiar with creating a fiddle and ask another question about performance, otherwise it will never ends here. Happy coding :) – Jim Jones Jun 10 '21 at 05:50
  • I have added the fiddle, the calculated percentage of the polygon within the buffer is not added into the table. Kindly have a look. – Aravinth Jun 21 '21 at 15:47
  • 1
    @Aravinth is this what you're looking for? In the end I corrected the `update` query https://dbfiddle.uk/?rdbms=postgres_12&fiddle=db0feeb2b03e71dcebfaa8c77685cfa9 – Jim Jones Jun 22 '21 at 07:01
  • Yes, this is the expected result. Thank you for the help and for clarifying the concept. – Aravinth Jun 22 '21 at 08:53
  • @Aravinth great! +1 for the fiddle – Jim Jones Jun 22 '21 at 09:22