QGIS expression engine has a powerful a summary aggregate function that can do spatial joins on the fly. This enables some very interesting uses.

One such use is to enable faster and more accurate data editing. For example, when you are digitizing a new feature and want to auto-populate a field based on its relationship with another layer, or want to restrict user input based on a spatial query.

To demonstrate this feature, I downloaded the land parcels and related data layers from City of San Francisco Public Data Portal.

Problem 1: Auto-populate a field in the Parcels layer from the Zoning layer

The city is divided into multiple non-overlapping zoning districts. Rather than entering the zone data manually, we can setup the attribute form to populate it automatically. Open the Layer Properties dialog for the parcels layer and switch to the Attribute Forms tab. Select the field that you want to auto-populate and un-check the Editable button. We will now enter an expression using the aggregate function as the Default value.

Enter the following expression. You can use any of the geometry functions (intersects, within etc.) to setup the filter.

aggregate(
 layer:= 'zoning',
 aggregate:='concatenate',
 expression:=zoning_sim,
 concatenator:=', ',
 filter:=contains($geometry, geometry(@parent))
 )

This expression means that we want to query the zoning layer and fetch the value of the zoning_sim field. The aggregate will be calculated using only features which pass the filter criteria defined in the filter parameter. Here we are defining a spatial filter to get only the feature that contains the polygon we just digitized. Here geometry(@parent) refers to the digitized feature and $geometry refers to the geometry of features from the zoning layer.

Now you can start digitizing. As you add polygons, the value of the zoning_sim will be fetched from the intersecting zoning layer. This is done on-the-fly and can save a lot of effort in manually entering the correct value.

(Click the image to see hi-res version)

Problem 2: Restrict the choice of street name to streets close to the digitized parcel

We have another field called street in the parcel layer that contains the frontage street name. It will be useful to get the canonical street names from a street layer rather than typing it manually. Even better would be to allow the user to pick from the streets that are close to the lot.

Open the Layer Properties dialog for the parcels layer and switch to the Attribute Forms tab. Select the street name field and choose the Value Relation widget. We can setup this field to lookup the names from the street attribute in the streets layer. The trick is to enter a spatial filter expression to select the roads that are close to the digitized geometry. You can use an expression such as below.

intersects($geometry, buffer(@current_geometry, 0.0005)) 

Now as you digitize the parcels, nearby street names will be populated in the drop-down box and presented to the user. There are duplicates because of multiple line segments of the same street are present within the search distance.


(Click the image to see hi-res version)

Value Relation widget is pretty cool. You can check out this post by Randal Hale to see how to do drill-down forms using them.

You can learn more about aggregate functions in the QGIS Documentation. Special thanks to Oto Kaláb whose answer on gis.se inspired this post.

Update 1: Lene Fischer asked me on twitter how can one write an expression to pick up an attribute from the feature with the largest area among all intersecting features. The resulting expression is a bit complex, but it does the job. Below is the code with explanation

/* ###################### Explanation #################### */
-- Array 1: Get areas of intersecting geoms, call this array1
aggregate(
 layer:= 'zoning',
 aggregate:='array_agg',
 expression:=area(intersection($geometry, geometry(@parent))),
 filter:=intersects($geometry, geometry(@parent))
 )
-- Array 2: Get list of zones, call this array2
aggregate(
 layer:= 'zoning',
 aggregate:='array_agg',
 expression:=zoning_sim,
 filter:=intersects($geometry, geometry(@parent))
 )
-- Find index of max of Array 1, call this index
array_find(array1, array_last(array_sort( array1 )))
-- get the index from array2
array_get(array2, index)

-- wrap everything together
-- NOTE: array_sort is only available in QGIS 3.6 onwards

/* ################## End Explanation #################### */
/* ############### Final Expression Below ################ */

array_get(aggregate(
 layer:= 'zoning',
 aggregate:='array_agg',
 expression:=zoning_sim,
 filter:=intersects($geometry, geometry(@parent))
 ), array_find(
		aggregate(
 		layer:= 'zoning',
 		aggregate:='array_agg',
 		expression:=area(intersection($geometry, geometry(@parent))),
 		filter:=intersects($geometry, geometry(@parent))
 		),
	array_last(array_sort(aggregate(
 		layer:= 'zoning',
 		aggregate:='array_agg',
 		expression:=area(intersection($geometry, geometry(@parent))),
 		filter:=intersects($geometry, geometry(@parent))
 		)))))

7 thoughts on “Summary Aggregate and Spatial Filters in QGIS

  1. Could you write a little about how your espressão (code) works that picks up an attribute of the resource with the largest area among all the intersecting resources? I can not deploy …
    Your work is incredible, congratulations.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.