I'm trying to count points within a polygon layer (hexagonal grid), grouped by an attribute of the points. There are some solutions on here (Python console, SQL virtual layer) but I can't seem to get it to work.
An example to illustrate:
Points:
| ID | Attribute |
|---|---|
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | A |
| 5 | B |
The output I want for example:
| polygon | countA | countB |
|---|---|---|
| 1 | 2 | 1 |
| 2 | 3 | 13 |
| 3 | 6 | 2 |
I've tried this: Count points in polygon grouping by attribute using PyQGIS but the counts come back as 0 everywhere.
Use the following QGIS expression on the polygon layer to get the number of features in layer 'Points' with the value of the field called "attribute" = A:
array_length(overlay_contains('Points', $id, filter:=attribute='A')) Label generated by concatenating the above expression three times for A, B and C:
There is a possibility of using a "Virtual Layer" through Layer > Add Layer > Add/Edit Virtual Layer....
Let's assume there is a point layer called 'Random points in polygons' and a polygon layer called 'municipality_1' with their attribute tables.
With the following query, it is possible to count points within a polygon layer, grouped by an attribute of the points.
SELECT poly.*, countA, countB, countC FROM "municipality_1" AS poly LEFT JOIN ( SELECT poly.id AS idA, count(1) AS countA FROM "Random points in polygons" AS poi, "municipality_1" AS poly WHERE st_within(poi.geometry, poly.geometry) AND poi.attribute = 'A' GROUP BY poly.id ) ON poly.id = idA LEFT JOIN ( SELECT poly.id AS idB, count(1) AS countB FROM "Random points in polygons" AS poi, "municipality_1" AS poly WHERE st_within(poi.geometry, poly.geometry) AND poi.attribute = 'B' GROUP BY poly.id ) ON poly.id = idB LEFT JOIN ( SELECT poly.id AS idC, count(1) AS countC FROM "Random points in polygons" AS poi, "municipality_1" AS poly WHERE st_within(poi.geometry, poly.geometry) AND poi.attribute = 'C' GROUP BY poly.id ) ON poly.id = idC The output polygon layer with its attribute table will look like this:
