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 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)
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 ;