I have three tables: water, wetlands and parcels. I want to update parcel table with area that is covered by water or wetland (they can overlap).
So I want to
- Merge water with wetland
- Dissolve and multipart -> singlepart
- Intersect this with parcels to calculate area:
Number of parcels is ~400 000, water 200 and wetlands 10.
The Query runs for 35 min and update ~2200 rows with correct results. This seems very slow so im probably doing something inoptimal. What can I do to speed it up?
update parcels set water_or_wetland_area = sub.area_m2 from parcels, (select parcels.parcel_id, sum(round(st_area(st_intersection(waterwetland.geom, parcel.wkb_geometry))::numeric,2)) as area_m2 from parcel, (select (st_dump(st_union(st_union(water.wkb_geometry, wetland.wkb_geometry)))).geom from water, wetland ) as waterwetland where st_intersects(waterwetland.geom, parcels.wkb_geometry) group by parcel.parcel_id) as sub where parcels.parcel_id = sub.parcel_id