5

I have a polygon with vector point civic addresses inside it. Each civic address has a different number residence. I want to sum the total number of residence inside that polygon and it populate a field in the polygon. It is easy to do it manually but I need it to auto-update if another civic address is added or the number of residence changes. I'm new to QGIS and tried an expression but it didn't seem like I was able to reference the field of another point. How would I go about doing this?

Andrew
  • 127
  • 10

2 Answers2

5

It can be done in Field Calculator with function aggregate(). In point layer create new field with field calculator expression like this:

aggregate(
layer:= 'points_layer_name',
aggregate:='sum',
expression:=residence_number_field_name,
filter:=intersects($geometry, geometry(@parent))
)

Where layer is polygon layer name written like string, aggreagate is aggregate function, expression is field from values will be taken and filter is filtering features based on expression (in this case interesects layer geometry with geometry of parent layer).

For more info check Aggregates QGIS documentation.

You can use virtual fields for automatic updates, but keep in mind that these fields are stored in project, not in your data. If you export the layer virtual field is exported also, but ase normal field.

You can also set the expression as Default value in Attributes Form settings in Layer Properties (Attribute form setting documentation). But in this case it will update only new or edited polygon features (not when you add or edit points). Values have to be updated manually in attribute table rewriting field itselves with "residence_number_field_name" = "residence_number_field_name".

I also posted a similar answer for the opposite case (points taking polygons attribute values) How to refer to another layer in the field calculator?

Oto Kaláb
  • 6,895
  • 3
  • 29
  • 50
  • This is linking to a SpatiaLite db so the virtual layers only display for the project. Any way to get it setup for the server? I will keep testing in the mean time. – Andrew Mar 22 '19 at 15:21
3

For an auto-updating field, use the Field Calculator to create a Virtual Field.

enter image description here

Install the RefFunctions plugin to gain access to a variety of functions that are much easier to use than the built-in aggregate functions.

For example, use this function in the Field Calculator to count the number of points in each polygon:

intersecting_geom_count( 'pointlayer')

Use this expression to add up all the field values of points in each polygon:

intersecting_geom_sum( 'pointlayer', 'fieldname')

Note: Substitute the name of your point layer in the expression where it says 'pointlayer'. Substitute the field name where it says 'fieldname'.

If you want to use the built-in functions, you can indeed use the aggregate() function as Oto Kaleb suggests. However, I think you want to calculate a count rather than a sum:

aggregate( 'pointlayer', 'count','',intersects( $geometry, geometry(@parent) ))

As before, substitute the actual name of your point layer where my expression says 'pointlayer'.

Note that it's not necessary to include the terms like "aggregate:=", "expression:=" etc. Those terms are included in the examples to make them easier to follow, but the function works exactly the same without them.

csk
  • 24,827
  • 3
  • 32
  • 70
  • Currently this is setup as a spatialite database and multiple users should be accessing it. Will all uses need the plugin for the RefFunctions to work? – Andrew Mar 22 '19 at 14:42
  • Yes, with multiple users, all users would need the RefFunctions plugin to use the intersecting_geom_sum() function. That's a situation where you would want to use the built-in aggregate functions instead. – csk Mar 25 '19 at 18:19
  • @csk, if you embed this layer into a new project, the results will be embedded too. So, other user does not need those expressions/functions – PieterB Apr 04 '19 at 12:53
  • Virtual fields are automatically re-calculated whenever the layer updates, so they would need the RefFunctions plugin for a virtual field calculated with those expressions. If they use a static (regular) field, they don't need the plugin but the fields won't update. – csk Apr 04 '19 at 15:53
  • @csk, I don't know about a plugin, but using an aggregate function in a virtual field, the values are updated in an embedded layer. – PieterB Apr 04 '19 at 16:41
  • You only need the RefFunctions plugin to use the intersecting_geom_sum function. You don't need it for the aggregate function, because that's a built-in function. I provided two options that achieve the same thing. The first option is easier to use but it requires a plugin; the second option is more complicated but doesn't require a plugin. Those are the trade-offs. Pick whichever option you prefer. – csk Apr 04 '19 at 17:07