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 ;
st_dump
-ing them to singlepart polygons first?(st_dump(geom)).geom
. Then union them back to multiparts when you are donet1
?