# How to remove overlaps and keep highest priority polygon

I have a multipolygon table with overlapping and non overlapping multipolygons. Where they overlap I need to keep the one with the highest (=1) priority and erase away from the lower priority ones to make the table non-overlapping: ``````create table table1 (
prio    int,
geom    geometry(MULTIPOLYGON,3006));

insert into table2 (prio, geom) values
(1, ST_PolygonFromText('MULTIPOLYGON(((666263 7266050,666869 7259810,671789 7265158,666263 7266050)))')),
(2, ST_PolygonFromText('MULTIPOLYGON(((670720 7261236,668652 7264445,672966 7264124,670720 7261236)))')),
(3, ST_PolygonFromText('MULTIPOLYGON(((669401 7267012,670328 7263233,674677 7266157,669401 7267012)))')),
(4, ST_PolygonFromText('MULTIPOLYGON(((666263 7258563,666905 7254748,669615 7257315,666263 7258563)))'));
``````

I can maybe solve this with something like this but its going to require alot of code since I have ~40 prios.

``````select id, prio, geom from table1 where prio=1
union
select p1.id, p1.prio, ST_Difference(p2.geom, p1.geom) geom from
(select * from table1 where prio=1) p1
join
(select * from table1 where prio=2) p2
on st_intersects(p1.geom, p2.geom) and p1.id <> p2.id

--union
--select id, prio, geom from table1 where prio=2
-- ...
``````

Isnt there some easier way? (The database should have been created with a constraint(?) that doesnt allow overlap, but it wasnt... Now I have ~45k polygons that partially overlap)

Fortunately, with the `prio` dependency, this is easier to solve than a purely recursive difference aggregation.

I'd prefer the `LATERAL` statement (here in full verbose mode), both for clarity of the statement and performance

``````SELECT ST_Multi(COALESCE(
a.geom
)) AS geom
FROM   table1 AS a
CROSS JOIN LATERAL (
SELECT ST_Union(geom) AS geom
FROM   table1 AS b
WHERE  a.prio > b.prio
;
``````

over a (correlated, but performance fenced) sub-query in the function

``````SELECT  ST_Multi(COALESCE(
ST_Difference(a.geom, (SELECT ST_Union(geom) FROM table1 AS b WHERE a.prio > b.prio)),
a.geom
)) AS geom
FROM    table1 AS a
;
``````

However, there isn't much to optimize in this particular case, so you should get the same query plan for both; run the tests on your actual tables and their sizes, as that may make a difference.

Use the latter structure to `UDPATE`:

``````UPDATE table1 AS a
SET  geom = ST_Multi(COALESCE(
ST_Difference(a.geom, (SELECT ST_Union(geom) FROM table1 AS b WHERE a.prio > b.prio)),
a.geom
))
;
``````

Note that this is overall a very costly operation; you could work in an index lookup (`... WHERE a.geom && b.geom AND a.prio > b.prio ...`) to save `ST_Union` costs for geometries that don't intersect higher `prio` geometries, but if you expect that to be rare, it won't make much difference.

• @BERA I mean, the MultiPolygons make this especially costly; with dumped Polygons and the index lookup you should get much better performance! – geozelot Nov 16 at 12:24
• Perhaps faster to only union "blade" polygons that intersect the higher-priority one? – dr_jts Nov 16 at 19:27
• @BERA ha, no, that is to be expected! See, I assumed the ratio was 99.9% overlaps; with it being the other way around, you absolutely want to use the index! – geozelot Nov 16 at 19:37