Approaches to delete Polygon overlap in PostGIS?

Question

Is there function to delete the areas of overlap between polygon features to achieve visualized results?

I'd like to check for each geometry (row) if a geometry is intersected, or in other words if a geometry is overlapped by another. If this is the case Id like "erase" the overlap from the other geometry without creating a hole. When there is no direct way to delete the overlap, is it possible to create new features from the overlaps, and then filter them out by a certain size threshold?

Maybe there is a simple way I did not see but please excuse PostGIS is new to me.

Is this related to https://gis.stackexchange.com/questions/363025/how-to-run-a-moving-window-function-in-a-conditional-statement-in-postgis-for-bu ? — dr_jts, May 27 '20 at 17:37
From the picture above, you could split (ST_Split) a polygon p1 with the exterior ring of a polygon p2 if they overlap. So now you have one big polygon p1, one small polygon p1 and the polygon p2: keep only the biggest polygon area grouped by the polygon ID (something like: .... GROUP BY id HAVING(MAX(ST_AREA(geom))) — obchardon, May 28 '20 at 07:46
But how do you know which boundary is the correct boundary ? — obchardon, May 28 '20 at 07:49
Considering your other questions concerning this issue; what are you going to do with theses buffers? For most tasks other than pure visuals, buffers can (and IMO should) be avoided (e.g. proximity/spatial relation analysis). And for visualizing them, the rendering application will usually do a better job at sorting out border issues than an approach on the geometric level. For reference: check out my answer here for a (different, but related) visual effect, and note the other answer that has a solution on the geometric level, both in QGIS. — geozelot, May 29 '20 at 11:32

score 2 · Answer 1 · answered Feb 11 '21 at 03:43

I've used this and it is good for detecting overlaps

Check whether table has overlapping polygons, in PostGIS?

Remove the limit and make it into a table as

CREATE Table my.table AS
SELECT *
FROM my_table a
INNER JOIN my_table b ON 
   (a.geom && b.geom AND ST_Relate(a.geom, b.geom, '2********'))
WHERE a.ctid != b.ctid

The table will potentially have the ids on which you can search and come back to your source table, perhaps modify only on that list of ids.

score 2 · Answer 2 · answered Jan 26 '22 at 11:58

So, one of the options for solving your problem could be this geo-tool, in the form of SQL-Code.

The input geodata, in the form of a polygonal grid with overlaps of neighboring cells, is shown in Figure 1

Figure 1

Run the SQL-code:

WITH 
tbla AS (SELECT ST_ExteriorRing(ST_Union(geom)) geom FROM poly_grid),
tblb AS (SELECT b.id, ST_Centroid(b.geom) centr_geom, (ST_DumpPoints(ST_Intersection(a.geom, b.geom))).geom geom FROM tbla a JOIN poly_grid b ON ST_Intersects(a.geom, b.geom)),
tblc AS (SELECT a.id, ST_MakeLine(a.geom ORDER BY ST_Azimuth(b.centr_geom, a.geom)) AS geom FROM tblb a CROSS JOIN tblb b GROUP BY a.id, b.id),
line AS (SELECT id, (a.geom) geom FROM tblc a WHERE NOT EXISTS (SELECT 1 FROM tblb b WHERE ST_Intersects(a.geom, ST_Buffer(b.centr_geom, 0.1)))),
poly AS (SELECT id, ST_MakePolygon(ST_AddPoint(geom, ST_StartPoint(geom))) AS geom FROM line)   
SELECT id, ST_Union(geom) geom FROM poly GROUP BY id

The output geodata, is shown in Figure 2

Figure 2

Original spatial solutions...

P.S. I hope the mismatch between input and output in your images is just a typo (cell id=4)

Translated with www.DeepL.com/Translator (free version)

Approaches to delete Polygon overlap in PostGIS?

2 Answers2