1

I have two multipolygon tables: t1 and t2. t1 contains 136 000 rows and t2 contains 5970 000 rows. t1 is always overlapping with t2.

The goal is to run difference to remove the area where t1 is overlapping t2 and then to merge the new difference layer with t2.

This is the code that I have been using so far (works fine on smaller sets of multipolygons). This code has been runnig for two days now, so I need to find a more efficently code.

CREATE TABLE difference AS
SELECT a.id, a. type, ST_Multi(COALESCE(ST_Difference(a.geom, output.geom),a.geom)) AS geom
FROM t2 AS a
CROSS JOIN LATERAL (
  SELECT ST_Union(b.geom) AS geom
  FROM t1 AS b
  WHERE ST_Intersects(a.geom, b.geom) 
) AS output ; 
7
  • Have you tried st_dump-ing them to singlepart polygons first? (st_dump(geom)).geom. Then union them back to multiparts when you are done
    – Bera
    Commented Sep 18, 2021 at 7:36
  • Or st_subdivide them
    – Bera
    Commented Sep 18, 2021 at 7:46
  • Agreed that subdivide will most likely speed it up. More background information to be found here blog.cleverelephant.ca/2019/11/subdivide.html Commented Sep 18, 2021 at 8:55
  • Do you have a spatial index on t1?
    – dr_jts
    Commented Sep 19, 2021 at 0:15
  • 1
    What version of PostGIS/GEOS? GEOS 3.9+ should be faster for these kinds of overlay queries.
    – dr_jts
    Commented Sep 19, 2021 at 0:16

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.