I got to writing this after having tried grouping points in polygons with expressions for too long, even with aggregate. so what do you do when expressions fail you?
TL;DR version: You can use SQL and Spatial SQL (In this case SQLite and not PostGIS functions, although most are very similar) directly on layers in QGIS.
Just long enough version: I recently re-discovered this fact (apparently, I already upvoted the answer that led me here) when trying to group and count point within polygon for an atlas table.
What you can do for something like that, or any other workflow that feels easier for you to perform in spatial SQL, is simply open the DB Manager and scroll down to Virtual layers, what you’ll find there is your project layers.
You can then open a new SQL Window where you can just write whatever query you want.
A short example using Natural Earth’s Countries and Airports layers.
What it does is count the number of airports in each country.
SELECT c.NAME ,COUNT(a.name) n_airports
FROM Airports a, Countries_50m c
WHERE ST_Within(a.geometry, c.geometry)
GROUP BY c.name
The same way I used ST_Within, You could use any other spatial SQL function, like transforming your data, getting its coordinates or checking relationships.
I would recommend checking the superb Introduction to PostGIS free workshop which is a terrific introduction to everything SQL and spatial SQL.
The original query I wrote gives us a table with our grouped values, we can load this table as a new virtual layer (without geometry, just as a table).
We can also add the geometry for the countries to get this new layer as a new countries layer with a minimal table (only the countries name and how many airports does it have).
All you have to do to load this layer (or just the table) is check the Load as new layer checkbox and click on Load.
Bonus: We can later edit our Query Layer’s SQL statement by right clicking the layers name in the Layers panel and click on Edit Virtual Layer…, you can then edit the query, for example check only the countries in Africa.
SELECT c.NAME ,COUNT(a.name) n_airports
FROM Airports a, Countries_50m c
WHERE continent="Africa" AND ST_Within(a.geometry, c.geometry)
GROUP BY c.name
And that’s it, you can now use SQL directly on your layers in QGIS.
Some helpful links
- SpatiaLite latest SQL functions reference list - The virtual layers use Spatialite SQL functions
- The Introduction to PostGIS Workshop.
- Anita Graser’s Answer that led me to explore this option.