Spatialite and lost styles

In this blog I will explain how to recover styles that are included in a SpatiaLite database but are lost after renaming or moving the SpatiaLite database to another location.

It will show how to use the QGIS Field Calculator to change the contents of de field f_table_catalog in the table layer_styles so it directs to the proper full pathname of the SpatiaLite database. Afterwards the styles included in the SpatiaLite database are displayed correctly!

Save styles in SpatiaLite

Since QGIS version 2.4 it is possible to save the style given to a layer loaded from SpatiaLite inside the SpatiaLite database. When a new project is opened and you load that layer the saved style is applied. Wonderfull, but if you rename or move the file the saved style is not applied.

How to recover lost styles

For this example we will use an example SpatiaLite database zwartsluis.sqlite which can be downloaded using the following link:

This example database (filesize = 8 MB)  is actually made using de QGIS plugin Basis NL from which I removed all data outside the small beautiful municipality of Zwartsluis. It contains buildings, addresses and location for houseboats and mobile homes.

For this example I have placed the SpatiaLite database after download in a folder named C:\geodata\buildings.

  • Open the dialog to load the spatial tables from Spatialite into QGIS. Select from menu Layer >> Add Layer >> Add SpatiaLite Layer… .
  • In the dialog use the [New] button to create a new database connection to zwartsluis.sqlite. Now select in the list of connections the connection to zwartsluis.sqlite and press the [Connect] button.
  • Select the option Keep dialog open, select the 5 tables and load these using the [Add] button.
load all spatialite layers from zwartsluis.sqlite
load all spatialite layers from zwartsluis.sqlite

We see that arbitrary styles have been given to loaded layers.

Layers are loaded but saved styles are not used.

Layers are loaded but saved styles are not used.

  • Now select the option Also list tables with no geometry in the dialog Add SpatiaLite Table(s). More tables are available for selection including layer_styles, select this table and use the [Add] button to load it in QGIS.
Load the table layer_styles
Load the table layer_styles

When we open the table layer_styles in the Attribute table dialog, we can see that the field f_table_catalog refers to another file location. We will fix this using the Field Calculator that can be used to change the contents of existing fields!

  • Select the layer layer_styles and open the attribute table for this layer.
  • To make the layer editable select the yellow pencil ( Toggle Editing mode) in the toolbar of the Attribute Table dialog.
  • To start the Field calculator dialog select the abacus in the toolbar.
  • Now activate the option Update existing field and select the field f_table_catalog in the field selector underneath this option.
  • In the text entry area give the new full filepath for zwartsluis.sqlite. Surround the text with apostrophes. Use forward slash instead of the backslash that is used on windows OS as a separator. When below the text area Output preview shows the correct filepath, press [OK].
replace contents field f_table_catalog
replace contents field f_table_catalog

In the Attribute Table dialog the field of f_table_catalog now shows the right filepath.

  • Select again the yellow pencil and acknowledge you want to keep the changes.
  • Start a new project and reload the spatial tables from the SpatiaLite database, now the saved styles from the SpatiaLite database are used.

To load public web services provided by the Dutch government, we use the PDOK plugin, written by Richard Duivenvoorde. Here you can select the WMST service brtachtergrondkaart to load this as a background layer. When you go to the extent of the layer pand you will be zoomed in to Zwartsluis. If you zoom in further the objects from the SpatiaLite database become visible.

Now loaded with correct styles!
Now loaded with correct styles!

Diethard Jansen (GIS-hulp)

Change predefined scales plus new PDOK services

New PDOK services

This post is mostly interesting for dutch readers, as our national OWS service ‘PDOK’ added some new services. And we made them (5000 layers) available via the PDOK services plugin.

Change predefined scales

But I also want to show that you can change the predfined scales that you see in the Scale part of the statusbar in QGIS. By default these go from 1:500 till 1:1.000.000.


But if you are a user who almost always uses your national WMTS service layers als background layers, you will have more QGIS fun when you use your OWN scales there.
Because using the QGIS scales on a PDOK WMTS service, you will see this:


…unsharp tiles because the dutch WMTS tileschema is not in line with the ‘oldfashioned’ scales that QGIS shows you…

But as said: you can determine the scales that you see in the dropdown yourself by loading a simple XML file. For PDOK I created two example xml files which you can download here as a zip file. Unzip it and it will show you two xml files.
Via Settings/Options select the Map Tools tab and remove all excisting scales by using the red minus symbol button. You can now ‘load’ one of the scale xml configurations, and the dropdown will now look like this:


Using these scales, optimized for those WMTS service, you will have crisp tiles:


In this way you can (as a heavy WMTS user) make it easier to view your services on the optimal scales. You can even remove some scales which you never use!

Shot version: choose YOUR best scale, Happy QGIS-ing!

QGIS goodies

Just a short post in case you missed it.

Since some time QGIS is coorporating with, so we can open ‘virtual shops’ all over the world to sell QGIS t-shirst, caps and mugs.

So: check your size, and go via this page to the nearest QGIS-shop and make your friends jealous with our great QGIS shirts :-)


Oh, and the QGIS project receives a small percentage of this money to buy refreshments for during the hackfests…

Use of the Flemish geographical data portal Geopunt in QGIS

Geopunt website

Geopunt is the central portal for geographical data of the Flemish government. Beside an online viewer, a catalogue is available from where data can be downloaded. A lot of these data are free to download (e.g. orthophotos), other data is available depending on your user profile (e.g. soil map)

Download through the Geopunt4Qgis plugin

Geopunt recently developed a plugin for QGIS. After installation, you’ll find these icons added to your QGIS menu:


Whereas you can look up addresses, POI’s and road constructions, the possibility to search the Geopunt catalog from Qgis (last icon) is arguably the plugin’s best feature. From a pop-up window you can search all geopunt data. These data can be either added to your project directly as WMS of WFS, or can be downloaded. Whe you opt for download, you are redirected to the respective download page of geopunt or agiv. In the case of vector data, you should opt for shapefiles.

Legends for shapefiles

QGIS can manage shapefiles (.shp) very well, but not the ESRI legend files (.lyr of .avl) that usually come with them. The consequence is that the vector layer is loaded without the desired lay-out (all polygons have the same color and a black line border):

The soil map of Flanders opened in QGIS

You will have to make the legend yourself through the Style menu in the dialogue window vector properties. Choose ‘categorized’ in case of a discrete variable and refer to the corresponding column in the dataset. Now click ‘classify’. All column values will have a different color assigned to them. Now you can manually change these colors. In the last QGIS release (2.6), the handy ‘color picker’ can be used for this purpose.

The Color picker tool in the Style menu

Subsequently, open an example of the map (e.g. a pdf) in a separate window, and sample the correct color for each of the categories by clicking on the example map. The soil map layer in QGIS now just looks like the original:

The soil map of Flanders in QGIS with imitated legend

The legend can be saved through ‘save style’ in the layer properties window for all your future projects as well to share with other QGIS users. Choose QGIS Layer Style File (.qml). My .qml legends for the soil map and map of biological value (BWK2) of Flanders can be downloaded here. Hopefully Agiv will provide these legend files in the future through the Geopunt data portal!

Mozilla Stumbler layer: loading TMS and XYZ tilelayers in QGIS

Mozilla Location Service (MLS) layer

Personally I’m very interested in the Mozilla Location Service (MLS), I wrote an earlier article about it on my work ( blog.

Key in this project is that both locations of wifi points and cell towers are crowdsourced and put in a open database, so everybody can use that data to determine his/her position on earth based on some info you get from your laptop or cell phone (without GPS).

This is only possible if as much people as possible start uploading this kind of locations, and as you can see on Mozilla’s overview map it is getting on steam now.

This map layer, showing all blue dot’s on places where locations have been recorded, is a ‘normal’ XYZ tile layer (epsg:3857) generated once a day by Mapbox if I’m correct. After reading Mishari Muqbil blogpost about how to add this ‘blue dot’ layer in OsmAnd android application, I was curious if I could also load this layer in QGIS.

After some searching I found the very nice ‘TileLayerPlugin’ from Minoru Akagi who also is the author of the beautifull Qgis2threejs plugin (have a look into it if you haven’t done so yet!).

So if you want to load xyz map tile layers, like OpenStreetMap, or this Mozilla Stumbler layer in QGIS, do the following:

– via the plugin manager, search for ’tilelayer’ and install the TileLayer Plugin.
– open it’s dialog (initially in the Web menu), and click the Settings button in it and point to some directory on your hard disk for the ‘External layer definition directory’
– in that directory, as you can read in the README at github, you have to put one .tsv file per xyz layer. Note that the values should REALLY be tab-separated, not space separted!
– for the MozStumbler file you need (one!) line like this (tab separated!):
MLSstumber MLSstumber{z}/{x}/{y}.png 1 0 13 -180 -85.0 180.0 85.0
To find the exact cloudfront-url you need, have a look into this json file: in

As soon as you have those bits in place you can see the blue dots. have a look, the coverage of my hometown is growing:


Arcgis rest service and other layers

But wait… isn’t that the Arcgis / arcgisonline World Topo Map I see there? Yes, it is, Adding one of these lines:
ArcgisWorldTopo esri{z}/{y}/{x}.png 1 0 16 -180 -85.0 180.0 85.0
ArcgisWorldImagery esri{z}/{y}/{x}.png 1 0 16 -180 -85.0 180.0 85.0
makes it possible to see other arcgisonline mapserver rest tile services in QGIS.

In this way it is also possible to load OpenStreetmap Tiles in QGIS, save:
OpenStreetMap © OpenStreetMap contributors{z}/{x}/{y}.png 1 0 19
as osm.tsv and see OpenStreetmap in it’s full glory.

I’ve put some tsv files in this zip: for you to try out.

Crowdsourced Street Level Photos

Another post of Mishari Muqbil made me aware of another crowd sourcing project: street level photo’s:

While still young, it’s a nice initiative to bring ‘power to the people’ instead of to the big companies :-)

QGIS 2.6.1 and QGIS 2.6 Documentation Released

QGIS 2.6.1

Just a short notice that the QGIS project (silently) released QGIS 2.6.1, a bug fix release for the latest stable version of QGIS 2.6 Brighton.


Some notable fixes include:

– composer stuff which was not working ok
– not all attribute columns showing in attribute table
– metasearch (CSW plugin) was not working in 2.6 out of the box
– some possible crashes are fixed now

QGIS Documentation 2.6

Bigger news: The Documentation team has released the 2.6 Documentation!

You can find it at or

The older (2.2) documentation has been updated to reflect the new 2.6 features. A new feature is that it now contains Help pages for all algorithms which can be used in Processing.

All new text is sent to Transifex. So most languages lost some percent on their ‘Total translated’ score, and can start working to regain their scores.

Thanks to all people who invested time into updating the Documentation and/or made the 2.6.1 release possible!

Visualize flows with FlowMapper

This article explains the presentation of flows on a map, using the FlowMapper plugin. For this demonstration, data on commuting patterns between 40 regions are used (from Statistics Netherlands).

Example of FlowMapper output (post-processed)
Example of FlowMapper output (post-processed)

Preparing the data

After the plugin is installed in the usual way, the manual can be found in the folder C:\Users\{username}\.qgis2\python\plugins\FlowMapper2_documentation.Three text files are required, with node coördinates, node names and a flow matrix. Three points must be stressed that are not mentioned in the manual. 1) The plugin does not work well with numbers with decimals. That can be solved by multiplying the numbers by (e.g.) 1000 and then round them. 2) As delimiters, both spaces and tabs may be used. The latter is especially useful when pasting data into a text file from Excel. 3) The node names should not contain spaces, because these are handled as delimiters. Replace them with underscores.

Continue reading Visualize flows with FlowMapper

Using QGIS processing scripts

One of the area’s that QGIS is constantly improving is the ‘Processing framework’, Formerly known as the sextante framework and written in java, it is rewritten in Python by one of the original authors Victor Olaya and made part of QGIS since about QGIS 2.0.

I think it is VERY usefull and in use a lot already, but not so much people are writing about this. In this blogpost I use it as a tool to run some pyqgis code, but Processing is much much more! Read about it in docs and manuals.

Recently there were some questions in the mailing list, which I thought would be fun to solve with a Processing script (instead of writing some lines of code in the python console, or creating a plugin).


Continue reading Using QGIS processing scripts