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!

New version of dutch PDOK services plugin

A short post in Dutch, to let us dutchies know of a new version of the ‘PDOK services plugin’ which eases the use of our national OWS services.

If you want you can install it, and for example view the different soli types of The Netherlands:

De Bodemkaart 1:50000 als WMS en WFS
De Bodemkaart 1:50000 als WMS en WFS

Another nice service is the 25cm Aerial Map of The Netherlands (Luchtfoto 2016 Ortho 25cm RGB).

Happy QGIS’ing all!

Visualizing 3D data with expressions

How to visualize point data with Z values? Let’s say: we have data about noise pollution in multi-storey buildings. The point data (apartments) looks like this:

selection_005The attribute table looks like this:

selection_004We see X and Y coordinates, and a Z (height) value. The DB column gives the actual noise data, which we want to visualize. How to do this?

One idea was to use diagrams. QGIS, however, gives only pie charts and histograms, which don’t satisfy in my case.

Another idea was the Point Displacement option in the styling of the layer. This didn’t work out well either, because I couldn’t arrange the points properly. I wanted to place them above each other, with the highest Z value on top.

The idea that did work out was to use Offset X,Y in the Symbol selector:

symbol-selector_007Using this, you can offset the marker symbols in horizontal (X) and vertical (Y) direction. Instead of just using X and Y values, you can also use an expression. Using an expression, you get the possibility to use values in the attribute table for changing the X and Y offset value for each point. That gave me the solution!

After a lot of trying I used this expression:

‘0, ‘|| to_string(-1 * “z”)

Let me explain. “z” is the height value I want to use. A positive Z value gives the marker a downward offset. I want it upward, so I took the negative of Z. Because the result of the expression has to be a string value, I used the to_string expression to do so. Because the expression changes both X and Y, and I didn’t want to change the X value, I used ‘0, ‘ in the beginning of the expression, and concatenated (||) the 2 expressions.
Here’s the result:

qgis-2-14-11-essen-verschovenpuntenmetexpressies_010Now you see all apartments in the different blocks: the markers at the bottom are the lowest in the block, at the top are the highest apartments. There are some gaps, because the data were incomplete. The small blue points indicate the original X and Y coordinates.

The possibilities of these expressions are myriad. And with adding text labels, you can use these expressions too, to place them exactly where you want (in the markers, beside the markers, whatever).

Good luck!

BGT import plugin

The Dutch Basisregistratie Grootschalige Topografie (BGT) is exchanged via gml.

Unfortunately the used gml application schema is quite involved and leads to incomplete imports in QGIS.

The BGT-import plugin is now available so that the gml files can be imported correctly.

To illustrate the point two screen shots (one wrong, and one right):

selection_276 selection_275