• Blog posts

    Bio

    by David Crowther

    DC_7th_01.png

    Question:

    What does the Update SQL Layer option do in QGIS?

     Answer:

    The Update SQL Layer option is available when you right click on a Layer in the Layers Panel,  but only when the Layer Source is within a Spatial Database e.g. PostGIS. It allows the User to filter the source data by choosing the columns they wish to retrieve and by selecting only specific records using Structured Query Language (SQL).

    For example, here we a have a Planning Apps Layer which has been opened from a PostGIS database and is being thematically styled using the ‘Progress’ field.

    DC_7th_02.jpg

    By right clicking on the Layer in the Layers Panel and choosing Update SQL Layer, the DB Manager tool will open and provide an extra (Tab) Window to the right of the Info, Table and Preview Tabs.

    DC_7th_03.jpg

    DC_7th_04.jpg

    The SQL Window at the top shows the current SQL filter against the Planning Apps Layer, in this case it is Selecting All records from the Table.

    DC_7th_05.jpg

    A simple update could be to add a WHERE statement to the SQL line e.g. Where the Progress value is ‘Passed’.

    DC_7th_06.jpg

    To test the SQL, press the Execute button in the window and it will detail the number of records which meet the SQL query and also show a Preview of the Attribute Table.

    DC_7th_07.jpg

    DC_7th_08.jpg

    To apply this SQL Filter to the Layer press the Update button.

    DC_7th_09.jpg

    Once the Layer is Updated the SQL filter is applied to the Layer in QGIS and now only the Planning Apps where the Progress value = ‘Passed’ are shown.

    DC_7th_10.jpg

    There is also a Query Builder tool which enables you to construct more complex queries and to apply Column filters very easily.

    DC_7th_11.jpg

    In the Query Builder:

    • choose the Table to Query – Planning Apps
    • the Columns you wish to show – id, progress, geom

    and add the Where query – progress = ‘Submitted’

    DC_7th_12.jpg

    The SQL window is now updated with the more complex query:

    DC_7th_13.jpg

    And when you press Execute the Table is filtered to now shown only those Planning Apps that have been ‘Submitted’, with only the ID, Progress and GEOM fields being shown and ordered using the ID field.

    DC_7th_14.jpg

    Pressing Update now applies the SQL to the PostGIS Layer in QGIS.

    DC_7th_15.jpg

    One final way to filter the data using the Update SQL Window is to use the Set Filter option.

    If we reset the SQL statement back to Select All Records and Show All Fields.

    DC_7th_16.jpg

    And then choose the Set Filter button…

    DC_7th_17.jpg

    ..you can build a similar query/filter e.g. Progress = ‘Declined’ by using the Fields Panel, the Operator options and the Sample Values list.

    DC_7th_18.jpg

    Pressing Test will check the SQL is correct and let you know how many records will be returned.

    DC_7th_19.jpg

    Once Applied to the Layer, the SQL Filter now only shows the 11 records where the Progress = ‘Declined’.

    DC_7th_20.jpg

    Finally, any SQL Query can be saved and are stored in your QGIS Projects to re-apply at any time.

    DC_7th_21.jpg