QGIS - Update SQL Layer

by David Crowther

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.

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.

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.

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

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.

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

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.

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

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’

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

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.

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

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.

And then choose the Set Filter button…

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

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

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

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