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.
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.
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: 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. QGIS 3.8 added support for an additional filter operation called order_by in aggregate functions. Using this allows us to pick an attribute from the feature with the largest area among all intersecting feature.
array_last(aggregate(
layer:= 'zoning',
aggregate:='array_agg',
expression:=zoning_sim,
filter:=intersects($geometry, geometry(@parent)),
order_by:=area(intersection($geometry, geometry(@parent)))
))
Check out my other posts on aggregate() expressions to see other applications of this very powerful function.
I also did a talk explaining aggregate functions in depth. Check out the video below for more examples.
Hi Ujaval, thanks a lot for sharing this. I will use it in my next “qgis for experienced users” workshop!
Hi Stefan. Great to know! The aggregate functions are super cool and I am discovering more interesting use cases.
thanks for the tutorial, there’s not so many good tutorial available yet on theses complexes expressions.
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.
See the update2 which has a simpler expression and is easier to understand. The sort_by key helps sort the filtered features according to the area of intersection and array_last picks the value with the highest intersection area.
[…] my previous posts Summary Aggregate and Spatial Filters and Advanced Aggregate Expressions to Automate QA to learn more about the powerful aggregate […]
Reblogged this on Geosaber and commented:
Summary Aggregate and Spatial Filters in QGIS
[…] Este documento es una versión en habla hispana mejorada del artículo del blog de Ujaval Gandhi […]
[…] Este documento es una versión en habla hispana mejorada del artículo del blog de Ujaval Gandhi. […]
[…] QGIS now has built-in fuzzy string matching functions that can be used – along with Aggregate function – to do table join based on fuzzy […]
[…] https://spatialthoughts.com/2019/04/12/summary-aggregation-qgis/ […]
[…] Previous PostBackNext Post […]
[…] my previous posts Summary Aggregate and Spatial Filters and Advanced Aggregate Expressions to Automate QA to learn more about the powerful aggregate […]
Fabulous. Thanks for sharing. I don’t know if it would be possible with this function to capture the area superimposed on a polygon in another layer?
Hi ujaval,
Good we solve the second problem using the aggregate function?
Greatings Thomas
I have an address dataset that I want to autopopulate the address number(stnum) then the street name9label) then the emergency service number (ESN) for the full address when I set up the value relation widget all my sample valures check out so I know the syntax is correct i.e 123 wilson rd 002 but when I add a new address point the esn populates the intersecting layer correctly then I add the address number and select the closest road similar to your street example, but the full address doesn’t populate 123 wilson rd 002 the address field is blank why is that what could I be doing wrong?
How are you determining the address? Usually address numbers are interpolated and there are geocoding methods to get those. Street should work though. Can you share your expression and maybe a screenshot showing your setup?
I have to manually input the address but if I could find a reverse geocode that worked in the field without an internet connection that would be great. the esn auto populates from an intersecting layer, and I can do the same with the zipcode and city fields. But I don’t know where I am going wrong. How do I attach a screenshot to this post?
[…] Referensi: spatialthoughts.com […]
Hello Sir,
Thank you for sharing very useful reference.
I have a cadastral parcel of polygon features. How can i write the expression to select all the isolated polygons from the same feature with following conditions:
1. Must be greater than 10 m away from the nearest polygon certain area limit.
2. selected polygon must have area smaller than 100 sq m.
(Choki)
From Bhutan.
You don’t need aggregate expression. Try this. Buffer your polygon layer by 10m. Do Extract by location on the original layer and buffered layer using “disjoint” predicate. Then run “Add Geometry Attributes” and use “Extract by Attribute” to get all polygons with area > 100. Some of this workflow is described at http://www.qgistutorials.com/en/docs/3/performing_spatial_queries.html
My bad. This will not work. You will need an aggregate expression. Testing on a dataset, the following expression will work
Use ‘Extract by Expression’ algorithm. Make sure your layer is in a Projected CRS with meters as units. This expression tests whether the polygon only intersects with itself and no other polygon within 10 meters and its area is less than 100 sqm
aggregate(
layer:=@layer,
aggregate:=’count’,
expression:=$id,
filter:=intersects($geometry, buffer(geometry(@parent), 10))) = 1 and $area < 100
It is really interesting! Can I use aggregate also between the polygon and line layers? To define if the line has intersect with polygon? How many intersections and based of the count I classify my line layer. I tried to followed your suggestion but not succesful!
I think you can use the new
overlay_intersect()
function in QGIS 3.16. That will tell you if the line intersects with any of the polygons.Hello ujaval, thanks for your interesting description. The attributes that I need for the new polygon layer are in a point layer. What is the expression (point within polygon)?
You don’t need to use aggregate function unless you need to do the join dynamically. You can do a spatial join using Processing Toolbor algorithm ‘Join Attributes by Location (summary)’ to do this https://www.qgistutorials.com/en/docs/3/performing_spatial_joins.html
..with this expression it works.
aggregate(
layer:= ‘GWR_Kriechenwil’,
aggregate:=’concatenate’,
expression:=DEINR,
concatenator:=’, ‘,
filter:= within($geometry, geometry(@parent))
)
However, only if the attribute is available as a “string”.
Great! Your can also. use its with other type by converting it to string, to_string(“DEINR”)
Perfect! Now the numerical values are also written. Thank you very much, you are my hero.
Hey there. This post is gold. Helped me a lot. But i have one final question:
You found a solution for ordering intersecting polygons. Is it also possible with lines? Seems like overlay_intersects has no order by.
It should work for lines too. Use aggregate() function which will allow order_by
Thanks for the fast replay. Nzl already asked the same and said it doesn’t work (same result for me) and you suggested overlay_intersect which doesn’t order. With your method he’s taking the most upper / last drawn polygon.
I’d like to dig a bit deeper on the use case. Is it possible for you to send me a data sample and description of the results you are looking for? I’ll try to find an expression that works. MY email is ujaval@spatialthoughts.com
Hey there. In 3.24 they finally implemented: Feature: Overlay intersects sort by intersection size
https://www.qgis.org/en/site/forusers/visualchangelog324/index.html#id37
haven’t tested this yet, but this should solve my problem we emailed about. Thanks again!
Thank you for sharing, it’s very helpful. Would you mind to share summary aggregate for SQL
especially Postgresql ?
This post is about aggregate() in QGIS expressions. SQL has a similar set of functions https://www.postgresql.org/docs/9.5/functions-aggregate.html. You can use them in your query along with JOINS to get similar results. Some examples in this book chapter https://livebook.manning.com/book/postgis-in-action-second-edition/chapter-11/1
What if you want to perform a conditional multi conditional aggregate arrangement.. Let’s say you want a layer to auto calculate values of more than one features that intersect with it
Hi Ujaval, great, this really helped me. Thank you!
Nils
Hi Ujaval, Is it possible to show us, how to take some specific attribute of a point layer into the line layers? (I want to feed the point attribute data to my start and end point of the line layer, creating two columns such as “From” and “To”)
I am searching for this sort of expression or way long time, but unfortunately I did not find one yet, what you showed us in this example is very helpful, but i am unable to get the expression for my need.
There is a tool in the Processing Toolbox called ‘Points to Path’. That should do what you need without need of a custom expression.
Hi Ujaval, great post. I`m also following you in Twitter. I have a question with aggregate. Working with a layer (layer A) i need to retrieve the value of an attribute of the selected feature in a second layer (layer B). i use that to filter features in layer A which match this value. I tried using the aggregate function but i need to pass the correct expression in filter section, but i´m still stuck with it.
Hi Juan – if the field in Layer A is called “A” and field in Layer B is called “B” you can try something like
"B" = attribute(@parent, 'A')
Let me know if that helped.
Thanks for your quick answer Ujaval, didn´t get the selected feature attribute, I´m still working on that, the aim is to use the plugin dataPlotly, more context about the issue in this link:
https://gis.stackexchange.com/questions/435594/qgis-aggregate-expression-to-filter-selected-features-in-2nd-layer
I´ll let you know when i get the solution, might be useful for others. Thanks again