Diagrammen met objecten per locatie

Imagine you have a list of different features at locations and you want to display those on a map like this:

The QGIS diagrams expect a column for each pie or bar, but our features are all listed in one column:

And our geometry is stored in a good old shapefile:

So, we can use the ‘code’ field for joining as well as for grouping the items to one single row per location.
Since QGIS vector joins cannot handle 1:n relations, we are going to use a virtual layer for this. In a virtual layer you can use all sql that sqlite supports, including JOIN and GROUP BY statements.
So we create a new virtual layer ‘cities_grouped’ with this sql:
[code language=”sql”]
SELECT
c.name,
c.code,
group_concat(amenity) AS amenities,
CASE WHEN INSTR(group_concat(amenity),’university’) > 0 THEN 1 ELSE 0 END AS university,
CASE WHEN INSTR(group_concat(amenity),’airport’) > 0 THEN 1 ELSE 0 END AS airport,
CASE WHEN INSTR(group_concat(amenity),’station’) > 0 THEN 1 ELSE 0 END AS station,
CASE WHEN INSTR(group_concat(amenity),’harbor’) > 0 THEN 1 ELSE 0 END AS harbor,
count(*) AS cnt,
c.geometry
FROM amenities a
LEFT JOIN cities c ON a.code = c.code
GROUP BY c.code, c.geometry
[/code]
The attribute table of this new virtual layer looks like this:

We can use diagrams as symbology for this new layer, adding in all the new columns university, airport, station and harbor. I used the count column (cnt) for sizing the diagram and offsetting the labels.
Hope this helps you, and if you know an easier way for doing this please let me know!