FOSS4G 2022 general tracks

Lauri Kajan

Software developer at Gispo Oy


Streamlining QGIS workflows with PostgreSQL editable views and triggers
Lauri Kajan

When using QGIS as a user interface for a PostgreSQL- & PostGIS-based registry database, user experience plays a high role. The data schemas of a registry database can hold a complex set of relations and database objects which can be hard to set up within QGIS. This was the case with a waste soil transportation registry that we developed for the City of Tampere, Finland. The main goal of the registry is to optimize soil transportation from construction sites by communicating and making it visible what soil categories are available and needed where and when. The registry enables significant savings in transportation costs as well as substantial reductions in climate emissions.

When the relational data model gets complex, you can deploy different strategies for setting up the workflows within QGIS. One possible solution is to use editable views and triggers for enabling user-friendly workflows in QGIS. This was the case in our soil registry project. The data model, as it was, would have forced the user to create multiple new features to the database tables when he/she just wanted to add a single soil transfer from one construction site to another. This was seen as too tedious when repeated constantly. The solution was to make a database view that the user could edit in QGIS, in addition to deploying database triggers for creating the right database features, with the proper data into the correct database tables.

This presentation seeks to show how the strategy was deployed and what challenges we met during the development.

Use cases & applications
Room Limonaia