Removing overlaps and keeping highest priority polygon using PostGIS

Question

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 (prio values >1) ones to make the table non-overlapping:

create table table1 (
prio    int,
geom    geometry(MULTIPOLYGON,3006));

insert into table1 (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?

(This is the query I ended up with:

SELECT a.prio, ST_Multi(COALESCE(
         ST_Difference(a.geom, blade.geom),
         a.geom
       )) AS geom
FROM  table123 AS a
CROSS JOIN LATERAL (
  SELECT ST_Union(geom) AS geom
  FROM   table123 AS b
  WHERE a.geom && b.geom and a.prio > b.prio --The intersects operator greatly improves performance (from hours to a few seconds) with my data since most polygons does not overlap and doesnt need to be prioritized.
) AS blade
;

)

Answer 1

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(
         ST_Difference(a.geom, blade.geom),
         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
) AS blade
;

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.