2

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

enter image description here

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.

5
  • Is this related to gis.stackexchange.com/questions/363025/… ?
    – dr_jts
    Commented May 27, 2020 at 17:37
  • 1
    Yes I'd like to know it in general.
    – DrSnuggles
    Commented May 27, 2020 at 17:41
  • 1
    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
    Commented May 28, 2020 at 7:46
  • 2
    But how do you know which boundary is the correct boundary ?
    – obchardon
    Commented May 28, 2020 at 7: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
    Commented May 29, 2020 at 11:32

2 Answers 2

2

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.

2

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 enter image description here

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

enter image description here

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.