This post is the continuation of Summary Aggregate and Spatial Filters in QGIS. I have been exploring aggregate functions more and have found interesting ways to automate tasks in QGIS. One such example is helping automatically keeping track of feature edits to help with Quality Assurance (QA).

Problem Statement: Users are editing multiple layers in a QGIS project. Their edits needs to be reviewed by a QA team. The QA team needs to automatically flag any edits that were done after their last review.

Here’s a test setup. points and polygons are 2 layers that are being edited. All features intersecting a polygon in the Grid is checked and reviewed periodically. If any feature in the points or polygons layer is edited after the review, the grid polygon containing updated features needs to be flagged.

The first step is to setup the layers to capture the time of last edit automatically. To enable this, add a new field of type Date & Time called last_updated in both the points and polygons layers.

In the Layer Properties, go to the Attribute Form for the last_updated field and add a new Default value expression $now and check Apply default value on update. This will automatically enter the current time whenever the feature is updated! Make sure to un-check ‘Editable’ for this field so users cannot manually update this.

Now the timestamps of all edits are being captured automatically. For the grid layer, you can add a field last_checked that is manually updated by the QA team when they review features in that grid. The goal is to compare the timestamps in last_checked from the Grid layer against timestamps in last_updated for both points and polygons layer. This is where the aggregate function comes in handy. It can query features from a layer and compare them against the feature from another layer. We also want these checks to be performed automatically, so we can use a virtual field that is evaluated on-the-fly using an expression.

Add a virtual field on the Grid layer named needs_check with the following expression. The expression checks if any intersecting feature in points or polygons layer have a timestamp that is newer than the current feature. The if function evaluates this condition and assigns ‘Y’ if it found matching features and ‘N’ if there were none.

if(
aggregate(
 layer:= 'points',
 aggregate:='max',
 expression:=day(last_updated - attribute(@parent, 'last_checked')),
 filter:=intersects($geometry, geometry(@parent))
 ) > 0 
 
 OR
 
 aggregate(
 layer:= 'polygons',
 aggregate:='max',
 expression:=day(last_updated - attribute(@parent, 'last_checked')),
 filter:=intersects($geometry, geometry(@parent))
 ) > 0,
 'Y', 'N')

As the features may belong to one of the many layers being edited, we can add another field layers_to_check which uses a similar expression to find out which layers were edited. Here I am using array_to_string function to generate a comma separated list of layers.

array_to_string(
array_remove_all(array(
  if(aggregate(
   layer:= 'points',
   aggregate:='max',
   expression:=day(last_updated - attribute(@parent, 'last_checked')),
   filter:=intersects($geometry, geometry(@parent))
   ) > 0, 'points', 'none'),
  if(aggregate(
   layer:= 'polygons',
   aggregate:='max',
   expression:=day(last_updated - attribute(@parent, 'last_checked')),
   filter:=intersects($geometry, geometry(@parent))
   ) > 0, 'polygons', 'none')), 'none'))

The result? You now have an auto-updating layer that tells you which grids need checking. Every-time you open the Attribute Table, the virtual fields will get evaluated and updated.

One limitation is that aggregate functions are currently not fully supported within virtual fields. So you will not be able to use these fields in the expression engine elsewhere in QGIS.

Check out my other posts on aggregate() expressions to see other applications of this very powerful function.