1

I have one layer with overlapping polygons just like in this screenshot.

enter image description here

I would like delete all overlapping polygons. Something like that (yellow polygons).

enter image description here

I tried to use this code:

SELECT pol1.*
FROM
polygon pol1 ,polygon pol2
WHERE
ST_contains(pol1.geom,pol2.geom) = false

SELECT st_union(geom) FROM (SELECT geom FROM polygon UNION SELECT geom FROM 

polygon) as foo

but it seems my skill in PostGIS is weak and I don't understand some function. I don't have any idea of how should it look like.

I'm using PostgreSQL 10 with PostGIS 2.5.2.

  • Yes, the largest area and I have in attribute table: mather (polygon which is on the below) and level (hierarchy) for each polygon. – Michal De Aug 28 at 13:29
  • I tried ST_UNION but my code created one, big polygon. Others my codes with ST_UNION didn't work. – Michal De Aug 28 at 13:32
2

If I correctly understood the translation of your question, try running this script:

If these are horizontal lines, then:

create table line_to_poly as
WITH closed_contours AS (
SELECT ((ST_Dump(ST_Polygonize(geom))).geom)
       FROM sourse_table)
       SELECT (ST_Dump(ST_Union(geom))).geom FROM closed_contours

If polygons, then:

create table un_poly as
SELECT (ST_Dump(ST_Union(geom))).geom FROM polygon

Good luck to you,

  • It works but when I use this script, geom is changed. I can't join to my table. Thanks for Your support. – Michal De Aug 28 at 19:00
  • No. I would like to have a new table with old geom and data from orginal table. I don't know, how I can share my table. Sorry for my ignorance. – Michal De Aug 29 at 10:33
  • I have error: Each UNION query must have the same number of columns – Michal De Aug 29 at 11:31
  • SELECT a.geom FROM polygon a UNION ALL SELECT b.geom FROM un_poly b (I don’t know the names of your columns; as a result, list them, for example a.id, a.name...) – Cyril Aug 29 at 11:37
  • remember that the structures of the joined tables must be the same, i.e. if in one of the tables there are no columns they need to be created ... – Cyril Aug 29 at 11:50
1

You could use ST_Union to generate one big multipolygon and then ST_Dump to separate its component polygons into separate records. If you then needed to get attributes from the original data you could join based on geometry.

1

In situations like this where the intent is to select existing geometry rather than construct new geometry it's preferable to avoid using overlay operations (like ST_Union) since they are less performant and can alter data slightly.

Instead, it's better to use spatial relationships. This is a classic case of "finding things which aren't related to other things". In this case the relationship is ST_CoveredBy (or ST_Within). Here's some example data and SQL to compute the desired relation. (To make this performant there should be a spatial index on the geometry column.)

WITH
data AS (
    SELECT * FROM (VALUES
        ( 'A', 'POLYGON ((100 200, 200 200, 200 100, 100 100, 100 200))'::geometry ),
        ( 'B', 'POLYGON ((300 200, 400 200, 400 100, 300 100, 300 200))'::geometry ),
        ( 'C', 'POLYGON ((100 400, 200 400, 200 300, 100 300, 100 400))'::geometry ),
        ( 'AA', 'POLYGON ((120 380, 180 380, 180 320, 120 320, 120 380))'::geometry ),
        ( 'BA', 'POLYGON ((110 180, 160 180, 160 130, 110 130, 110 180))'::geometry ),
        ( 'BB', 'POLYGON ((170 130, 190 130, 190 110, 170 110, 170 130))'::geometry ),
        ( 'CA', 'POLYGON ((330 170, 380 170, 380 120, 330 120, 330 170))'::geometry ),
        ( 'AAA', 'POLYGON ((330 170, 380 170, 380 120, 330 120, 330 170))'::geometry ),
        ( 'BAA', 'POLYGON ((121 171, 151 171, 151 141, 121 141, 121 171))'::geometry ),
        ( 'CAA', 'POLYGON ((341 161, 351 161, 351 141, 341 141, 341 161))'::geometry ),
        ( 'CAB', 'POLYGON ((361 151, 371 151, 371 131, 361 131, 361 151))'::geometry )
    ) AS t(id, geom)
)
SELECT a.id
FROM data AS A
LEFT JOIN data AS b ON a.id <> b.id AND ST_CoveredBy(a.geom, b.geom)
WHERE b.geom IS NULL;

Note that the id encodes the nesting of the geometry, and is provided just to make it obvious that the answer is correct.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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