Summing values from spatially joined table with QGIS Field Calculator

Question

Can the Field Calculator be used to sum values from a related table via a Spatial Join?

I have two vector polygon layers A and B, each with a numerical column VALUES. I want to update "VALUES" in layer A with the sum of "VALUES" from layer B where geometry from B is contained within geometry A.

So far I have the expression:

geomcontains('Layer B', 'VALUES')

and what I want is, to sum up, this.

I have seen other ways to do this via plugins like SpatialJoin etc, but they all involve modifying either saving new layers or modifying the structure of Layer A, which I think is fairly cumbersome - is there a simple solution? (In MapInfo this is extremely simple, using Update Column)

Answer 1

You can use the Toolbox for the spatial join (Processing Toolbox > search: Join by Location) and save the result to a temporary layer. The temporary layer is only saved in your QGIS project. Then you can join the temporary layer to 'layer A'.

You can find the processing toolbox in View > Panels > Toolbox or right-click over the toolbars.

Answer 2

It's taken me nearly 8 years but I finally found the answer!

Open Field Calculator to update field "VALUE_A" in Layer A, and use the following expression:

aggregate('Layer B', 'sum', "VALUE_B", within(centroid($geometry), geometry(@parent)))

Use Show Help to learn other functions you can use in each of the arguments:

aggregate(layer,aggregate,expression[,filter][,concatenator=''][,order_by])

I have found this does time out for larger datasets, if this is an issue then you can select a subset of overlapping polygons in both Layer A and Layer B, check 'Only update selected features' (in Layer A), and use the following modified expression:

aggregate('Layer B', 'sum', "VALUE_B", is_selected('Layer B', @feature) AND within(centroid($geometry), geometry(@parent)))

Finally, if you wanted to update with a text value from the containing polygon you can use 'min' as the aggregate instead of 'sum'.

Answer 3

As it says on the "refFunctions" plugin's page:

This plugin is deprecated!

The plugin is outdated as of QGIS 3.14 due to the introduction of the new functions for geometry overlay expressions

Since QGIS 3.16 the functionality of the "refFunctions" plugin was conveyed to the QGIS core.

---

The "refFunctions" plugin offers intersecting_geom_sum() function to do that task.

Activate the Field Calculator on your Layer A and try,

intersecting_geom_sum('Layer B', 'VALUES')

This function sums the geometries' attributes (Values in your case) of the target layer (your Layer B).