Diagrams for features

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

screenshot-from-2018-02-16-11-16-07

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

screenshot-from-2018-02-16-11-28-45

And our geometry is stored in a good old shapefile:

Shapfile 'cities.shp'
Shapfile ‘cities.shp’

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:

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

The attribute table of this new virtual layer looks like this:

Virtual attribute table 'cities_grouped'
Virtual attribute table ‘cities_grouped’

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!

One thought on “Diagrams for features”

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>