Spatialite en verdwenen stijlen

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: http://qgis.nl/zwartsluis.zip
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.

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

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.

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].

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.

Diethard Jansen (GIS-hulp)