Calculate area of polygons from one layer within polygon of second layer while matching criteria using virtual layers or field calculator QGIS

Question

I have a polygon layer with classifications ('class'), and a second polygon layer of area of interest ('AOI'). I am trying to calculate the area of each of three classes within each (overlapping) AOI polygon, and I would like to have them update when either of the underlying layers are updated. AOI is a virtual layer (buffer off polygon boundary)

The boundaries of the class polygons extend outside and across the AOI boundaries.

So I'm trying to add an attribute to the AOI table so each feature (red circles in image below) has an area of each class (blue, red and green fills from image below) that's within the circle.
The class categories are stored as integer values (1, 2, and 3) under class.code

I tried using the field calculator to aggregate the area within each AOI feature (to create a virtual field)

aggregate(
  'class',
  'sum',
  expression := $area,
  filter := intersects($geometry, geometry(@parent)) and "code" = 1
)

Which kinda works, but it gives the area of all of the intersecting polygons, so the area of a single class is larger than the area of the total AOI. I want the intersection, which should only count the area within the AOI but if I try to change "interescts" to "intersection", I get a value of NULL.

I also considered adding a subquery within the query that creates AOI, but I'm less competent at the spatialite SQL than the expression builder. Code to create AOI

SELECT st_buffer(wetlands, 3208), wetlands.fid
FROM wetlands

I tried adding st_intersection subclauses, but couldn't get them to work while filtering for matching classes and using the resulting buffer geometry

### Doesn't work
SELECT st_buffer(wetlands, 3208) as geom, 
   wetlands.fid as id,
   sum(area) FROM st_intersection(geom, class) 
      WHERE class.code = 1 AND st_intersects(geom, class) as low
FROM wetlands, class

I was looking at: Area of each polygon that intersects another layer and total area of intersections in QGIS when putting together my queries, but in this case all of the polygons to be aggregated fell completely within the AOI polygons, so the st_intersects approach above would work.

When I've done this in the past using static layers I would rasterize the class layer to a reasonable resolution (like 100 sq ft), then use zonal statistics to calculate the number of cells within each AOI polygon by class. An approach like this would be acceptable, but I'm looking for a "live" solution so the area by class could be updated as the underlying layers are updated, and I don't think there is either a way to create a virtual layer that is a rasterized version of another layer.

Answer 1

If you're just looking to fix your aggregate, then you should move the intersection logic from the filter to the expression eg

aggregate(
  'class',
  'sum',
  expression := area(intersection($geometry, geometry(@parent))),
  filter := "code" = 1
)

The filter should be returning a true/false which is why you get NULL once you change it to intersection.

Note however that area and $area can give slightly different results due to planimetric vs ellipsoidal measurements.