• Blog posts

    Bio

    by David Crowther

    DC_QGIS_Storing_Styles_01.png

    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

    DC_QGIS_Storing_Styles_02.jpg

    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.

    DC_QGIS_Storing_Styles_03.jpg

    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.

    DC_QGIS_Storing_Styles_04.jpg

    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.

    DC_QGIS_Storing_Styles_05.jpg

    For the BLPU Extents Layer we are using a Grey Polygon that has 65% translucency.

    DC_QGIS_Storing_Styles_06.jpg

    DC_QGIS_Storing_Styles_07.jpg

    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.

    DC_QGIS_Storing_Styles_08.jpg

    In your source database there will be a table called Layer_Styles that previously would have been empty.

    DC_QGIS_Storing_Styles_09.jpg

    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.

    DC_QGIS_Storing_Styles_10.jpg

    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.

    DC_QGIS_Storing_Styles_11.jpg

    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.

    DC_QGIS_Storing_Styles_12.jpg

    And one which is thematically classified using the BLPU Class.

    DC_QGIS_Storing_Styles_13.jpg

    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.

    DC_QGIS_Storing_Styles_14.jpg

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

    DC_QGIS_Storing_Styles_15.jpg

    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.

    DC_QGIS_Storing_Styles_16.jpg

    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.

    DC_QGIS_Storing_Styles_17.jpg

    If we now choose the alternate – BLPU Class – style and apply that to the layer, the layer will change style in the QGIS map.

    DC_QGIS_Storing_Styles_18.jpg

    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.