2

I was wondering if it's possible to remove any overlapping areas that occurs in a column based on a sample of the table for the shapefile with 2 important columns:

  1. Shape area
  2. Geom

I'm trying to find the total shape areas without any overlaps. I think it's best to minus the sum of the shape area with ST_Intersection but the function needs with 2 different geom: geography ST_Intersection( geography geogA , geography geogB ) which I took from the PostGIS site. I feel like I'm really close to figuring it out ie. if the table is called "table", then something like this:

SELECT sum(A.shape_area) as "sum with overlaps",
       ST_Area(ST_Intersection(A.geom, B.geom)) AS "intersected area"
FROM   table A 
JOIN   table B ON ST_Contains(A.geom, B.geom)
GROUP BY a.geom, B.geom;

But as you can see, my results were off as the sum - intersected area should be bigger than 0. Can someone point out my mistake?

Basically, my goal is to count any area once (regardless of the overlaps) enter image description here

Vince
  • 20,017
  • 15
  • 45
  • 64
Coffeee
  • 21
  • 2
  • 3
    You can remove the overlaps like this: https://gis.stackexchange.com/questions/379300/removing-overlaps-and-keeping-highest-priority-polygon-using-postgis. What units are you coordinate system in, degrees/meters? – BERA May 21 '22 at 16:26
  • @BERA Thanks for the redirect! I'm using 4326 SRID to transform the data so latitude/longitude degrees, I believe. – Coffeee May 21 '22 at 16:47
  • 2
    Referring to a table as a "shapefile" is very confusing -- Once loaded into the database, it's no longer in shapefile format. It's always important in self-joins to restrict the comparisons to prevent A/B and B/A pairs. The usual way to do this is by integer ID column constriant B.id > A.id. I wouldn't trust the comparison operator for GROUP BY geometries to function correctly. – Vince May 21 '22 at 18:06
  • Note that, geometrically this is more complex than you may think: while my answer in @BERA's link does indeed give you non-overlapping polygons, it depends on a hierarchy (which btw. you could simply apply e.g. by using id instead of prio) and can only remove overlaps non-symmetrically, e.g. when two polygons overlap, only one of them gets reduced - creating an equal cut in both is highly complex (and expinentially so for cases where more than two overlap each other). – geozelot May 21 '22 at 20:20
  • 1
    I also added a "randomness" to the prio. For example two polygons with prio 1 got the prios 1.123242345, 1.470274 so when these two overlapped someone always got erased. – BERA May 22 '22 at 07:42
  • 2
    @BERA good catch, for that case you had back then. With an incremental ID this is not necessary...but you could also e.g. use area as a deciding factor (for non-regular polygons, that is). – geozelot May 22 '22 at 09:50

0 Answers0