Selecting non-overlapping polygons from a one layer in PostGIS

Question

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

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

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.

Answer 1

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,

Answer 2

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.

Answer 3

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.