3

I have two layers: 'A' (orange) and 'B' (purple). enter image description here

Using a Virtual Layer and group_concat I am able to list the polygons in layer 'B' that intersect with those in layer 'A' thanks to @Jesse McMillan's solution

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:

Shows desired attribute table

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

2
6

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):

enter image description here

Edit:

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:=', '
)

enter image description here

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.

4
  • This works well to create a field with the total area. My problem is still to create an attribute table with each polygon in 'Overlay' in one column and in another column a concatenated list of the fid of each polygon in 'Polygon' followed by the area of that polygon (in parentheses if possible). See screenshot of table in my question. – David May 18 at 14:26
  • Added an edit for what you want to do. – Babel May 18 at 21:13
  • 1
    This is perfect, thank you! Your comment concerning concatenating values in a field is well taken. I should explain that the purpose of doing this in my case is to create a table to be exported outside QGIS but which I wish to be able to update as I add new polygons. – David May 19 at 17:22
  • You might consider using a virtual field in the attributes for dynmic calculationwhen you add/remove/edit polygons – Babel May 19 at 20:40
5

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  
2
  • This solution works well to create the total area of polygons from 'B' that intersects with the larger polygons in 'A'. Again, my problem is still to create an attribute table with each polygon in 'A' in one column and in another column a concatenated list of the fid of each polygon in 'B' followed by the area of that polygon (in parentheses if possible). See screenshot of table in my question. – David May 18 at 14:29
  • I have changed the SQL,now it will be produce the desired output. you have to produce an inner select with the desired format and then select the group_concat from that – eurojam May 18 at 20:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.