by David Crowther
Question:
Is it possible to store the QGIS Style of my spatial tables in their source database?
Answer:
In this previous FAQ we discuss how you can utilise a QGIS Layer Definition File to store both the database connection details and the styles for your source data e.g. data from a SQL or PostGIS database - https://www.cadlinecommunity.co.uk/hc/en-us/articles/360007330017-QGIS-Layer-Definition-Files
In addition to using Layer Definition Files, you may actually want to simply store your preferred styling in your spatial database, to save having to create and share lots of Layer Definition Files.
This is very easy to do for both PostGIS and SQL Databases, and in this example we will Save and Store the Layer styling in my source SQL Database.
In this example, I have made a connection to both my LLPG Addresses (Point) Layer and my BLPU (Polygon Extent) Layer within my QGIS Project. When I open these SQL tables their styling will change from session to session as they open un-styled.
Using the Layer Properties I can then apply a suitable style for each layer.
For example, for the LLPG Address layer we have chosen a Symbol Marker with blank point and white halo.
For the BLPU Extents Layer we are using a Grey Polygon that has 65% translucency.
In the Layer Properties for each Layer you can now choose Style > Save Style > and instead of saving a Layer Definition File, this time we will choose In Database (mssql). Give the Layer Style a Name and a Description and press Save.
In your source database there will be a table called Layer_Styles that previously would have been empty.
But once you save your Layer Style a new record is added to the Layer_Styles table which contains the details of your layer and the style that you applied.
In fact the styling is saved in the styleSLD field as XML, which if you want to you can directly edit from with the database.
Now if you open the same SQL table into another session of QGIS, the connection will look in the Layer_Styles table and see that there is a styling record for the chosen table and so the database styling is auto applied to your layer once it is added.
You can also store multiple Styles for the same layer in your source database. In this example for the BLPU Extents, I have created and then saved two database styles – one showing the polygons as simple grey areas.
And one which is thematically classified using the BLPU Class.
If you know that one of the styles for the same layer is the default, when you create the database style you can tick the box to use as the default style for the layer.
Having saved the two BLPU styles, if we view the Layer_Styles table in the SQL database we can see there are now 2 more records, both for the BLPU table, and one of those is the default style option (1).
In QGIS, if we now drag and drop the BLPU SQL table into the map window, QGIS will use the ‘default style’ associated to the SQL table to style the layer – in this case the Grey Polygons.
However, in the Layer Properties, you can choose Styles > Load Styles > From Database (mssql). This will then make a connection to the Layer_Styles table in the SQL database and see that there are two styles related to the layer.
If we now choose the alternate – BLPU Class – style and apply that to the layer, the layer will change style in the QGIS map.
So here we have another option which allows you to control how your layers are styled in your QGIS Projects. This is a great option, as it negates the need for multiple Layer Definition Files and also allows you to control default styles for your layers as well.