5

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:

enter image description here

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)

5

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.

| improve this answer | |
  • 1
    @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
  • 1
    @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

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.