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