If you want to be efficient with geometries, you should always think about the indexes.
The first think is to be sure that you have an index on parcel:
CREATE INDEX parcels_geom_idx ON parcels USING GIST(geom);
Then create a table with the union of water and wetland:
CREATE TABLE waterwetland AS
SELECT
(
st_dump(
st_union(water.wkb_geometry, wetland.wkb_geometry)
)
).geom as geom
FROM water, wetland;
(I removed the second st_union, I don't understand why it is here?)
That way you can create an index on it:
CREATE INDEX waterwetland_geom_idx ON waterwetland USING GIST(geom);
Don't forget to analyze to be sure the planner know about your table (especially if you do this inside a script):
ANALYZE waterwetland;
ANALYZE parcels;
Lastly, updates in postgresql are a little slow (because it does actually a lot of things) so if you really need speed and your table is not too big you can simply create another table:
CREATE TABLE parcels2 AS
SELECT
parcels.*,
req.area_m2
FROM
parcels
LEFT JOIN
(
SELECT
parcels.parcel_id as parcel_id,
sum(round(st_area(st_intersection(waterwetland.geom,parcels.wkb_geometry))::numeric,2)) as area_m2
FROM
parcels, waterwetland
WHERE st_intersects(waterwetland.geom, parcels.wkb_geometry)
GROUP BY parcel.parcel_id
) as req
ON parcels.parcel_id=req.parcel_id;
(untested code, but I think this should be the idea)
Create a new attribute with Field calculator using this expression (replace orange
on line 6 with the name of your "orange" layer):
area(
intersection(
$geometry,
collect_geometries(
overlay_intersects(
'orange', -- name of another layer
$geometry
)
)
)
)
/area ($geometry)*100
The expression applied on the green layer (here as dynamic label for demonstration purpose), calculating the area of the overlapping part of the orange layer, divided by the area of the green layer. The overlapping part is highlighted in red:
Best Answer
All fields are created using the Field Calculator. Install the refFunctions plugin if you don't already have it. This plugin enables the
intersecting_geom_sum()
function.$area
$area
"wetland_percent"
to the parcel area with this expression:Or if you don't want to create so many new fields, you can skip steps 3 to 5 and instead use this expression to calculate "wetland_percent"