Area of each polygon that intersects another layer and total area of intersections in QGIS

Question

I have two layers: 'A' (orange) and 'B' (purple).

Using a Virtual Layer and group_concat I am able to list the polygons in layer 'B' that intersect with those in layer 'A'. I found that solution at Listing IDs of polygons within another polygon in the Attribute table.

SELECT A.fid, group_concat(b.fid)
FROM A LEFT Join B
ON ST_Intersects(a.geometry, b.geometry)
GROUP BY A.fid

However, I also wish to include the area of each polygon in an attribute table the area of each polygon in 'B' that insects with one in 'A', and the total area of the intersecting polygons, like this:

Can this also be done using group_concat() and a Virtual Layer?

Answer 1

You can do this using QGIS expressions and e.g. create a new field with the Field Calculator.

If you have a layer 'polygon' (orange in the screenshot) and another layer 'overlay' (blue outlined), on the second layer apply this expression:

aggregate (
        'polygon',
        'sum',
         $area,
        filter:= intersects(
                   $geometry, 
                   geometry(@parent)
         )
      )

Screenshot: the expression (+ a format_number function to round) is used to create the value of a label: it calculates the sum of all orange polygons included in each blue polygon (compare the area of each orange polygon):

---

To creat a concatenated field (your field group_concat(b.fid) in your screenshot - but see the comment at the bottom), use this expression in field calculator (your Total area B (ha) field can be created in the field calculator with the expression from above):

aggregate (
    'polygon',
    'concatenate',
    fid  || '(' ||  to_string (round ($area,1)) || ')' ,
    filter:= intersects(
    $geometry, 
    geometry(@parent)
    ),
    concatenator:=', '
)

Comment

I don't know how much sense it makes in your case, but as a general rule, you should keep the content of your attribute fields atomic: thus only one value per field. Concatenating different values in one field and even combining different information (fid and values) in most cases does not make sense as it can't be used for anything you normally use attributes for (like calculations, statistics, data driven styles etc.).

The only use for such concatenated strings I can imagine is for informing purposes in an output, like labels or text fields. But in this case, you could use the expression from above directly there, e.g. as label source - you don't have to create a separate field for that. Major advantage: the label gets updated when your data changes.

Answer 2

Other solution is to use your SQL statement for the "Virtual Layer" and change it to:

SELECT fid, group_concat(b_full) as formated, sum(area) from (
       SELECT a.fid, st_area(b.geometry) as area, b.fid || ' (' ||   st_area(B.geometry) || ')' as b_full  from A
       LEFT JOIN B ON ST_Intersects(a.geometry, b.geometry))
group by fid