Visualize Postgres JSON data in QML widgets

As promised some time ago in “The new QML widgets in QGIS – When widgets get unbridled” we still owe you some fancy unicorns, but first let’s have a look at another nice feature that has been introduced in QGIS 3.4 LTR,  the reading of PostgreSQL JSON and JSONB types.

With JSON you have a lot of possibilities for storing unstructured data. In our case, it’s mainly interesting when the data are stored as an array or a JSON object. Let’s have a look at two examples.

Visualize Postgres JSON data with common widgets

With the usual QGIS widgets “List” and “Key/Value” you are able to display JSON arrays and simple JSON objects.

JSON array as List

Simple JSON object as Key/Value

Or of course both as plain text in the “Text Edit” widget:

Say hi to Postgres JSON in QML widget

Probably, your JSON data does not look really nice with the aforementioned widgets, luckily since QGIS 3.4, you are free to create your own QML widget. Since QGIS already loads the JSON data into structures that are supported by QML, we can use all the JSON data within the QML code.

Let’s assume you have the JSON array from above and you like the elegance of the blue of Jacques Majorelle. You create your personal list widget by adding the JSON field as an expression:

You will have your very personal list:

JSON also allows storing more complex data, like for example a list of objects. In that case, you will reach the limits of the common QGIS widgets.

Let’s assume you have a table looking like this:

nomenclatura name link
Apis mellifera mellifera European dark bee https://en.wikipedia.org/wiki/European_dark_bee
Apis mellifera carnica Carniolan honey bee https://en.wikipedia.org/wiki/Carniolan_honey_bee
Apis mellifera Buckfast bee https://en.wikipedia.org/wiki/Buckfast_bee

In JSON it would be stored like this:

With the QML Widget you can use the QML TableView to visualize:

Or, even more powerful, you can create your super individual table using the model and create each row by using a QML Repeater.

Additionally, you can use a lot of fancy stuff like:

  • mouse interaction
  • animation
  • opening an external link
  • … and so on

The QML code for that looks like this.

And that’s it

I hope you liked reading and you will enjoy using it to make beautiful widgets and forms. If you have questions or inputs, feel free to add a comment.

… and in case you still asking where the promised unicorns are. Here’s is a super-fancy implementation 😉

Posted in GIS, Non-commercial, PostgreSQL, QGIS, QML, Scripts

The new QML widgets in QGIS – When widgets get unbridled

Individuality is the definition of freedom. And freedom is the fundamental requirement of man’s mind. QGIS possibly cannot give you all the freedom you require in life. But at least a lot of freedom in how you manage your work. QGIS 3.4.0 LTR was released last week and it comes loaded with features supporting big freedom in the configuration of your projects.  Let’s focus on the QML Widget. QML is the smart casual look of widgets. With the help of some simple code, you will be able to visualize your data in the attribute form like never before. You can display beautiful charts, complex JSON data, and fancy colored unicorns. 

How it’s done

Let’s start with an example. In the Attribute Form configuration in the Layer Properties you have first to activate the drag and drop designer. Not only can you drag the field- and relation-items from the available widget list to the form layout list, but also a QML Widget. When you drop this item, it creates an “instance” of a QML Widget. This means, you can drag and drop as many QML Widgets as you like to have on your form and configure each of them individually.

QML (Qt Modelling Language) is a user interface specification and programming language. It allows developers and designers alike to create highly performant, fluidly animated and visually appealing applications. QML offers a highly readable, declarative, JSON-like syntax with support for imperative JavaScript expressions combined with dynamic property bindings. Source: Qt documentation

It’s a bit like an HTML page on the attribute form but very well integrated with Qt.

On dropping the item, the configuration dialog pops up. After closing you can come back to it by double-clicking the item in the form layout list, like you do it with containers and tabs. If your don’t know QML that much yet, the default snippets from the drop-down can create an example of a rectangle, a pie chart or a bar chart. This could help you to create your own widget. On the right you can see a preview of your widget in real time. There are powerful layout possibilities to design it according to your ideas. For more information about it see the QML layout part of the Qt documentation.

But a chart makes no sense if there is no data. Of course, you can enter the data directly into your QML code, but most likely you need the data of the features to be visualized. This brings us to expressions. You can use them like you are used to in Default Values, Constraints and Display Messages. You’ll find the well-known expression builder widget in this configuration as well.

Using expressions

So let’s assume we want to visualize who holds what share of a forest. These forests are owned by the country (national), the canton (cantonal) or private. To keep it simple we have three attributes for that: national_share,cantonal_share and private_share.

After creating the default pie chart you will find this snippet in the QML code text area:

Let’s set the field expressions into the PieSlice-values. Just select them in the expression widget and add them with the + into the chart.

As you can see the expressions in the code are wrapped inside expression.evaluate("<expression>"). This means there are no limits in using expressions.

You are open to use more complex expressions like e.g. for the title property of the pie chart:

Or in case the task with forest shares would be solved with relations to other layers by filling up a model with the children and the children’s share. This is possible by using expressions with the help of the expression functionrelation_aggregate.

More information about expressions can be found in the QGIS Documentation.

Back to our example. The result will look like this on the attribute form. It visualizes the share values in the pie chart.

The visualization is not (yet) updated in real time when the values change. But this would be a nice thing to have in the future… If you would like to support this, please contact us.

And that’s it

I hope you liked reading and you will enjoy using it to make beautiful widgets and forms. If you have questions or inputs, feel free to add a comment.

… and in case you still asking where the promised unicorns are. Well you have to wait for the part 2 of this article 😉

 

Posted in GIS, Non-commercial, QGIS, QML, Scripts

QGIS speaks a lot of languages

QGIS is a real cosmopolitan. Born in Alaska sixteen years ago, it has spread all over the world since. Thanks to its open source mentality, it finds not only in economically strong countries big usergroups. No question, that beside all the developers, there is a bunch of brave translators giving everything to make and keep QGIS multilingual. It’s translated in over forty languages – even to Mandarin Chinese and Esperanto. Not only the application, but also its plugins.
And since the feature-loaded long term release 3.4.0 even the QGS-Projects themselves.  Thanks to the friendly support of QGIS Usergroup Switzerland and the QGEP Project.

How it comes

Plugins are often shipped with pre-configured project files. To provide them in the users individual language, you’ve been required to translate the project manually in the properties and store it separately. When you needed to change something, you have been coerced to update every single file. This is a big effort and fault-prone. So there appeared the idea to have translation files for each required language, and when the user opens the only one project, it will be translated to his specific language. And that’s, what this new functionality does.

How it’s done

Like QGIS and the plugins, the projects are translated with the Qt translation process. Means, it makes the translation according to a Qt Compiled Translation Source File (.qm file). When the user opens a project, QGIS checks for a .qm file laying in the same folder like the .qgs file, having the same name like the .qgs file and having the language-code as postfix of the users language (the language configured in the QGIS settings).
So when the user opens a project named “citybees.qgs” that is originally in English, but his QGIS language is German, it checks for a file named “citybees_de.qm”. The project’s layer names, field-aliases, container names and much more will be translated to German and the project will be automatically stored as “citybees_de.qgs”. So the user has his German project version and can use and edit it like he wishes. Super easy.

Start from the beginning

Swiss people love honey and so they are diligent beekeepers. So, let’s assume you want to provide a project about beekeeping in cities to your Swiss customers. Because in Switzerland people talk four different languages, you need to have the project multilingual in German, French and Italian. We skip Romansh, not because it’s less important or in the Romansh speaking parts are no cities, but because QGIS does not support Romansh (if you’ll propose this one day, you will have my vote).

Anyway. Let’s see what you have to do, to deliver the projects with the .qm file for German, French or Italian.

1. Create the project

You create your project about beekeeping and store it as “citybees.qgs”. You don’t have to care about languages at the moment. You name everything in your language. Assumed it’s English, you name the layers “apiary” and “area” and the fields “fid”, “bee-species”, “beekeeper” and so on.

On changes you will edit always this project and no translated projects.

2. Generate Ts File

In the project properties in the section General there is the part to generate a translation source file. First, you select the source language, to have this information in programs you’ll edit the file (like Qt Linguist or Transifex) afterwards. Per default the language of your QGIS is selected here.

When pressing Generate TS File you will find in your projects folder the new file “citybees.ts”.

But wait a minute, why are we generating a .ts file when we need a .qm file?

The .ts file is the translation source file and it’s the uncompiled .qm file. The .qm file contains compact binary format code Qt can make the translation of programs with, but you and your brave translator cannot read it. So you create a .ts file looking like this:

You see it’s simple XML code that contains mainly untranslated text in the <source> element and empty space for the translated text in the <translation> element.

You could enter your translations directly in this file using the text editor and then build the .qm file with the command lrelease, but it’s preferable to use tools like Qt Linguist orweb-based services like Transifex or Weblate.

3. Translate your File in Qt Linguist

You open Qt Linguist an you select the target language of the .qm file you want to build in the end. Let’s choose German.

The file opens and you see a list of entries described by the Context. The context is, where the strings are located in the QGIS project.

The string “beekeeper” stays in the context project:layers:apiary_1f7b5e82839c:fieldaliases and this means it’s a field or alias of the layer apiary_1f7b5e82839c in the project.

The translation is done simply over the graphical interface. To confirm your translation you can set the check mark.

4. Finally build your .qm file

You compile the translation – means build a .qm file – by simply select Release as… in the Qt Linguist and store it as “citybees_de.qm”.

And now your customer will be able to open your project in German 🙂

What’s translated

Most of the needed parameters like layer names and fields are translated. There could be still some strings in your use case that are not like for example action titles or labels. But the solution is designed, that it’s extendable for more project parameters (see the next chapter). So don’t worry if you will find parameters that cannot be translated yet. They possibly will be in the future.

Translatable strings:

  • layer names
  • layer group names
  • form attributes like tab titles and group box titles
  • relation names
  • field names and aliases
  • value relations

With field names and aliases it has a special behavior: while we should not translate the field names itself because they can be used as identification, the aliases are translated only. In case there is no alias in the original project, the translation of the field name would be written as an alias in the translated project. The field name stays the same. So you can just generate and translate without the fear of overwriting field names.

Content translation

There is a possibility to have a translation of the content as well. In particular using the value relation widget.

Let’s assume, you want to have the values in the field bee_species translated as well. Means the following bee species should be German:
– European dark bee
– Carniolan honey bee
– Buckfast bee

You can solve this by using the value relation widget. Means you create a non geometrical layer “beespecies” and enter the following values:

nomenclatura name_en name_de name_fr name_it
Apis mellifera mellifera European dark bee Dunkle Europäische Biene abeille noire ape nera
Apis mellifera carnica Carniolan honey bee Kärntner Biene abeille carniolienne ape carnica
Apis mellifera Buckfast bee Buckfastbiene abeille Buckfast ape Buckfast

And configure the field bee_species of apiary as value relation widget:

And now comes the magic:

When you create the .ts file it includes not only the field name bee_species but also the referenced value name_en:

When you now “translate” the name_en to name_de the field is referenced to the German values of the entry:

Getting technical

Let’s have a quick look into the source code, shall we?

Generate Ts File

When you press the Generate Ts File button in the project properties, in the background happens the following:

First QGIS scans for the translatable strings in the currently loaded project and collects everything in a QgsTranslationContext object. This object contains the filename of the .ts file and all the collected translatable strings.

The strings are collected by firing a signal called requestForTranslatableObjects delivering the QgsTranslationContext. Every object that contains translatable strings connects a slot registering the strings in the received QgsTranslationContext:

This will be a growing list of strings as new features are added and missing bits are discovered.

Translate by QTranslator

The translation is made using the QTranslator. It’s loaded with the .qm file on reading the project and on loading of every single translatable string, it’s called to translate:

That’s it

I hope you liked reading and if you have questions or inputs, feel free to add a comment.

Enjoy this cosmopolitious feature! 🙂

Posted in C++, GIS, Non-commercial, Programming, QGIS

Using Threads in PyQGIS3

While porting a plugin to QGIS3 I decided to also move all it’s threading infrastructure to QgsTasks. Here three possible variants to implement this.
the first uses the static method QgsTask.fromFunction and is simpler to use. A great quick solution. If you want need control you can look at the second solution that subclasses QgsTask. In this solution I also show how to create subtasks with interdependencies. The third variant, illustrates how to run a processing algorithm in a separate thread.

One thing to be very careful about is never to create widgets or alter gui in a task. This is a strict Qt guideline – gui must never be altered outside of the main thread. So your progress dialog must operate on the main thread, connecting to the progress report signals from the task which operates in the background thread. This also applies to “print” statements — these aren’t safe to use from a background thread in QGIS and can cause random crashes. Use the thread safe QgsMessageLog.logMessage() approach instead. Actually you should forget print and always use QgsMessageLog.

using QgsTask.fromFunction

this is a quick and simple way of running a function in a separate thread. When calling QgsTask.fromFunction() you can pass an on_finished argument with a callback to be executed at the end of run.

Subclassing QgsTask

this solution gives you the full control over the task behaviour. In this example I also illustrate how to create subtasks dependencies.

NEVER, EVER, EVER use print in the QgsTask outside from finished(). finished() is called on the main event loop

Call a Processing algorithm in a separate thread

You can simply execute a processing algorithm in a separate thread thanks to QgsProcessingAlgRunnerTask. This class takes a processing algorithm, its parameters, a context and a feedback objects and execute the algorithm. QgsProcessingAlgRunnerTask offers an executed signal to which you can connect and execute further code. executed sends two arguments bool successful and dict results. If you want to retrieve a memory layer you can pass the context as well by using partial or lambda.

If you’re wondering what parameter values you need to specify for an algorithm, and what values are acceptable, try running processing.algorithmHelp('qgis:randompointsinextent') in the python console. In QGIS 3.2 you’ll get a detailed list of all the parameter options for the algorithm and a summary of acceptable value types and formats for each. Another nice possibility is to run the algorithm from the gui and check the history after.

I hope this post can help you porting your plugins to QGIS3 and again if you need professional help for your plugins, don’t hesitate to contact us.

Posted in Non-commercial, PyQt, Python, QGIS

How to filter features in QGIS using the graphical processing modeler

This article describes a new algorithm for the processing modeler called feature filter algorithm. If you are already familiar with ETL concepts and the graphical modeler, you can directly jump to the section the feature filter algorithm.

Building workflows for repetitive tasks

When building workflows for simple or complex geodata infrastructures, one of the most common tasks one encounters is to extract some of the features and copy them to another destination. Sometimes they need to be modified and a few attributes calculated or deleted, maybe even the geometry needs to be adjusted or in some fancy situations one even wants to generate a couple of objects from one input object. This process is often called ETL (Extract, Transform, Load) and it is something that is worth mastering as a GIS expert.

Let’s imagine a situation where we sent a field worker out to collect information about public infrastructure, equipped with a brand-new tablet and the latest and greatest version of QField. To make his task super easy, we prepare one single layer for him with an attribute type which can be set to Bus Station, Car Parking or Train Station. Now back in the office we want to integrate this back into our spatially enabled database which has been designed with 3 target tables.

 

 

Easy enough to go to QGIS and select those features by type one after the other and use a bit of copy-paste. And maybe fiddling a bit with the attributes. But hey, after all we are a bit lazy and on the one hand like to have an ice cream later on that afternoon and on the other hand like to avoid errors. Those who are lucky enough to know SQL and have full access to the database are well enough equipped to do the job.

Short introduction to the graphical modeler

For those who just want to quickly do this job visually within QGIS, there is a tool called modeler in the processing plugin. With the help of this tool it is straightforward for everyone to automate processes. To get started with the modeler, simply enable the processing plugin and click on Processing > Graphical Modeler.

Within the modeler, there are Inputs and Algorithms available. Inputs are there to define variables, algorithms on the other hand transform those variables. In its most simple form, there is one vector feature source (a layer) as input and one algorithm, for example a fixed distance buffer which in turn has one output layer with all buffered features. Such a model can be saved and reused. To run a model directly from the modeler, click the play button on top. Once saved, it appears in the processing toolbox. Every time a model is run, the input layer can be handed to the model. Or it can even run in batch mode on a list of layers or files.

With this in place, the job of doing the buffer can now be run on 200 input layers without any manual interaction. Simple as that.

Pro tip: processing models do not have to be complex. They can also be used to preconfigure single algorithms so when an algorithm is run, the parameters which you never change are predefined already. For example you can add a Simplify geometries to 1 meter algorithm which only takes a layer as parameter and has the 1 meter tolerance built-in.

The feature filter algorithm

Now back to the job of splitting the infrastructure layer into 3 different layers. To do this job visually and easily within QGIS, there is now a new algorithm available in QGIS 3.2. It is called Feature Filter and available in the processing modeler.

To make use of it, we open the processing modeler and first add a new Vector Features input and name it Infrastructure. Since we know in this project we will always deal with points, we can make already specify that in this first dialog.

 

Let’s now add a Feature Filter algorithm and use the following configuration:

The Infrastructure layer is set as input, and we define three outputs for Train Stations, Bus Stations and Car Parking. All layers will be final outputs on which no further transformations will be applied within this model and they will be directly written to a new layer.

 

Now it’s time to run our new model and check that it does what it promised.

We can also uncheck the final output checkbox and send filtered features to further processing algorithms. For example sending them through a buffer based on an attribute size (although as a QGIS professional you know you should rather be using styles than modifying the geometry in most situations in such cases).

Conclusion

With this new algorithm built directly inside the core of QGIS, the processing framework is now able to transform and refine features of a dataset with the same precision as an open heart surgery.

Of course you can get more creative in the filter criteria.

Apart from the obvious ones to do geometry modifications, there are two particularly interesting ones if you liked this one

  • The Refactor Fields algorithm allows calculating new fields or rename fields based on expressions
  • The Append plugin allows adding those features to an existing vector layer such as a database table

The data from this walkthrough is available for download as infra.zip.

If you would like to test this new feature but do not yet have a concrete use-case in mind, here is a task for you: get an openstreetmap extract, import it using ogr2ogr and split the lines into different layers roads, rivers and railways, the polygons into lakes, forests and cities, the points according to your own liking.

If there is big enough interest for this, we might write another blog post on this topic.

 

We would like to thank the QGIS user group Switzerland for making this project possible through funding.

 

Tagged with: , , ,
Posted in Expressions, Non-commercial, Processing, QField, QGIS

PostgreSQL/PostGIS Administratoren Kurs 17.05./18.05.2018 in Zürich

Der Kurs richtet sich an PostgreSQL Benutzer, die ihre Administratoren-Kenntisse erweitern wollen. Es werden verschiedene Ansätze erläutert, um den Einsatz ihrer Datenbanken zu optimieren, und anhand von verschiedenen Beispielen geübt.

Am Ende des Kurses sind die Teilnehmer in der Lage, eigene Datenbank-Server, Datenbanken sowie deren User zu erstellen, verwalten und zu tunen. Zudem sind sie fähig, sich die dafür benötigten Informationen selbst zu beschaffen und kennen die wichtigsten Plattformen und Dokumentationen.

Die folgenden Themen werden behandelt:

  • Einführung in PostgreSQL
  • Serverkonfiguration (unter linux)
  • Administration Datenbank
  • Administration Dabenbanknutzer
  • Rechte und Sicherheit
  • Maintenance
  • Monitoring
  • Analyse Performance (Tipps and Tricks)
  • Indexen

Der zweitägige Kurs (9:00 – 17:00) kostet 890 CHF pro Person. Die Kurskosten beinhalten die professionelle Unterstützung während des Kurses, das eBook PostgreSQL Administration Cookbook, 9.5/9.6 Edition, das eBook PostgreSQL 9.6 High Performance, sowie die zwei Mittagessen.

Ein Kursleiter (Marco Bernasocchi) für 4 bis 5 Teilnehmer, 2 Kursleiter für 6 bis 10 Teilnehmer.

Posted in Courses, PostgreSQL

Marco becomes QGIS.org Co-chair

We are very proud to announce that one of our founders and directors Marco Bernasocchi was elected as QGIS.org project steering committee (PSC) co-chair.

With over 10 years of involvement with QGIS (he started working with QGIS 0.6) Marco will serve for the next two years as one of the board members of the QGIS.org association. He is excited to get the chance to work together with the PSC and the fantastic QGIS community to push QGIS even further.

We wish him and the rest of the elected PSC two very successful years full of QGIS awesomeness.

Rock on QGIS!

read more at QGIS Annual General Meeting – 2018

Posted in Featured, Non-commercial, QGIS

Porting QGIS plugins to API v3 – Strategy and tools

The Release of QGIS 3.0 was a great success and with the first LTR (3.4) scheduled for release this fall, it is now the perfect time to port your plugins to the new API.

QGIS 3.0 is the first major release since September 2013 when QGIS 2.0 was released. During the release cycles of all 2.x releases, the QGIS Python API remained stable. This means that a plugin or script meant to be used in QGIS 2.0 is still working in QGIS 2.18.

The need for a new major release was principally motivated by the update to newer core libraries such as Qt 5 and Python 3. But it also offered a unique opportunity to the development team to tackle long-standing issues and limitations which could not be fixed during the 2.x life cycle. Inevitably, this introduced multiple backward incompatibilities making scripts and plugins unusable in QGIS 3.

In this post, I’d like to share some notes from my latest ports. Obviously, if you need professional help for porting your plugins, don’t hesitate to contact us.

Step 0 – Unit tests

You should already have your code covered by unit tests, but I know, the world is not perfect and at times we have to cut edges and, unfortunately, often unit tests are the ones getting cut.
Porting to a new API version is a great moment to go write unit tests helping to make sure that your plugin will keep on working as before the port.

Step 1 – fix * imports

Before going on, please go and remove all your * imports (like from PyQt4.QtGui import *). They are bad and qgis2to3 cannot handle them. There is no need to already change them to the PyQ5 version, just remove them and add the propper PyQt4 imports. We’ll handle moving to PyQt5 in a later step.

From PEP8: Wildcard imports (from import *) should be avoided, as they make it unclear which names are present in the namespace, confusing both readers and many automated tools.

Step 2 – Versioning strategy

Since having a source code repository is a mandatory requirement for publishing a plugin on plugins.qgis.org, I assume you already know what code versioning is and why you absolutely should be using it.

APIv2 branch

Unless you absolutely want to make your code run on both API 2 and 3 (which might be possible) I strongly suggest to create a branch or your current version called qgis2, API2 or legacy or whatever you want to call it. From now on this branch will be responsible for all your future (probably mainly bugfixes) releases for the 2.x series of QGIS. Remember to edit the metadata.txt file and add your minimum and maximum version (not mandatory but nice for clarity):

Master branch

From now on your master branch will be where all your future development for the 3.x series will happen. Remember to edit the metadata.txt file and add your minimum version:

Step 3 – install the helpers

We created a repository with two dedicated tools to help you migrate your QGIS 2 plugins to QGIS 3: qgis2to3 and qgis2apifinder. Both tools are distributed as a single Python package installable via

Please note that often for system-wide installation you need sudo.

All the sources and more information can be found at https://github.com/opengisch/qgis_2to3

Step 4 – Python 2 to Python 3 and PyQt4 to PyQt5

The qgis2to3 tool is a copy of the files found in QGIS scripts to allow for quick downloading and simple installation without the need of downloading the whole QGIS repository. This is a set of fixers for the python 2to3 command that will update your Python 2 code to Python 3. The additional fixers will also take care of the PyQt4 to PyQt5 porting as well as some other things.

Running the qgis2to3 command will show a number of changes required. These changes can be applied with -w flag

Step 5 – Check for API v2 usages

The qgisapi2finder tool helps you find usages of the QGIS API version 2 and gives hints about potential required changes for API version 3.

It is based on a machine parsing of https://qgis.org/api/api_break.html so the results are as good as the information there.
Also, being a simple text parser, it just gives a hint where to look at. It is by no means a complete tool to find all the possible API incompatibility.

Methods are matched using only their names and not their classes, so there might be various false positives. Also, if the same keyword has been edited in various classes, qgisapi2finder will show you all the available suggestions for that keyword.

You can run qgis2apifinder to get hints on the existence of obsolete code requiring manual porting and suggestions on how to actually deal with it. Please note that qgis2apifinder does hide some very frequent words like [‘layout’, ‘layer’, ‘fields’] from the analysis. You can show those with the --all flag.

Step 6 – update your code

From here on it is all looking at each hint, updating the code and rerunning your tests. A properly configured IDE (stay tuned) could also help in the process.

Some more information can be found at github.com/qgis/QGIS/wiki/Plugin-migration-to-QGIS-3

Also, take a look at the PyQGIS API documentation now online at python.qgis.org/master.

I hope this post and tool can help you porting your plugins to QGIS3 and again if you need professional help for porting your plugins, don’t hesitate to contact us.

Posted in Featured, Non-commercial, PyQt, Python, QGIS

PostgreSQL back end solution for quality assurance and data archive

Did you know that the possibilities to make a full QGIS back end solution for quality assurance and archiving in PostgreSQL are immense? SQL has it’s well known limitations, but with a little bit creativity you can make quite nice solutions just using triggers and rules. In this post I’ll explain what we did lately based on a project with a customer. He needed to assure the consistency of data but still give his employees the possibility of a fast feeding of the data collected on the field to the database. Another request was to keep every status of the data with the information about the changes (archiving).

It’s always the question, where to put the logical part of the solution. QGIS is quite powerful with constraints, but the undeniable advantage of a back end solution is, that you can use any front end – no matter what configuration you have on QGIS or what Feature Manipulation Engine (FME) you use – without influencing the guarantee of data validity.

Situation

It’s all about trees

At least for that customer we got lately. The customer owns pieces of land all over Switzerland. On this pieces are forests and in the forests are – as expected – trees. Well, mostly – if you are not a bark beetle or a squirrel – you don’t care about a single tree. Except if there is something special with it. For example, a branch that could fell down on your brand new Citroën DS or if the tree has a disease that could kill the whole forest, that is actually needed to convert the carbon dioxide (from your DS) into oxygen.


The issuetrees (yellow) lie on the forest (green) – and the forest lies on the land piece (brown).


And the (Entity Relationship Model) ERM looks like this. A land can have zero, one or more forests – and a forest can have zero, one or more trees with issues.

It’s not really about trees

The situation is, that a lot of field workers (so called tree-inspectors) work with our mobile solution QField, where they can collect the data while standing in the middle of a wild forest with one foot in a rabbit hole and the other one in the stinging nettle. It’s quite possible and usual that there can be some problems entering all the data correctly. Typing issues on the tablet while running away from wolves or just lack of concentration because of the beauty of the swiss forests.

And it’s about lots of front ends

But there are not only the tree-inspectors. There are the office-clerks working with QGIS and planning, when the problems on the tree has to be solved. And finally there are the woodsmen solving the issues and setting the status to done on QField again. So there have to be a lot of projects using the same data but with different configurations. If you make all the quality assurance on the front end you won’t have time to care about the trees anymore and beside of that it’s fault-prone.

Quality assurance in the back end

Data integrity with constraint functions

There are simple constraints like that a field is not empty and more complex constraints with a lot of logic regarding the content of the field.

Simple constraints

Lots of data integrity issues can be solved by using simple constraints like NOT NULL (column must not assume null), UNIQUE (column must be unique among all the rows in table) or Primary Key and Foreign Keys constraints.

Checks and constraint functions

For more special cases or not really technical constraints, we can use checks. Here for example: If the issue is done, then it needs to have a donedate. But not if done is not TRUE (NULL or FALSE).

And if these cases are more complex and not technical at all, we can put it to a function and use the return value (for example the error message) as condition. In the following example we want to assure that assignee is the name of one of the employed woodsmen. Of course it can be NULL too.

And the function live.chk_assignee_valid:

So with many of these constraints, we can assure a lot and the data are fully correct. But this is not always comfortable to use. Why? Go on reading…

Using of a “data quarantine”

Let’s imagine that the tree-inspector collected all day data in QField. Standing in the middle of the mentioned stinging nettle and rabbit holes, running from wolves etc… Of course he made some mistakes while collecting data. In the evening he returns tired to the office, already thinking about the dinner meal his wife is cooking (or his husband, of course), and wants to upload the data from the QField project to the database. And what happens? Lot’s of error messages. He thinks about to solve them tomorrow, because his wife (or his husband) can get quite angry when he is late for dinner. But if he does it tomorrow, the data are only stored on the device and nowhere else overnight. He need to have them in the database. No matter, if correct or not. And this leads to the idea of the “data quarantine”.

Use Case

All data entered to the database (valid or not) need to be stored. The entries accepted from the so called live tables with all constraints, are stored normally. The entries failed because of the constraint, are stored in another table. In the so called quarantine table. So you have for every live table another quarantine table. This means, we need another table structure existing parallel to the live tables. We do it in two schemas: The live schema and the quarantine schema.

So the tree-inspector synchronizes his QField without any problem to the database. The correct entries are written into the live tables. The incorrect into the quarantine. Actually all the data are coming into the quarantine and there is a Trigger passing them through to the live table. If they success, they will be stored in live and removed from quarantine. Otherwise they keeps staying in the quarantine. Same situation when the quarantine-clerk later corrects the data entries in the quarantine. On an update they are pushed into the live-table. If success, all good. Otherwise the entry keeps staying in the quarantine.

Structure

And how we do that?

It’s all solved by using triggers. SQL triggers are procedural code that are automatically executed on an action on a table or view. For this solution we actually need two trigger per quarantine table. After insert into or update quarantine table, a trigger should be fired for every entry, doing this:

Insert the same entry into the live table. If success, then delete the entry in the quarantine table. Else write the info to the current entry in the quarantine table.

Probably you noticed the problem with the recursion, but let’s not think about it at the moment 🙂

Code

In PostgreSQL we can use trigger functions. Means you have the triggers on the table calling the functions.

Trigger on table quarantine.issuetree after update

Trigger function (simplified)

Trigger function used for the solution when inserting into live

And this is the function with the logical part with success and failing.

As you can see, we use here an id called quarantine_serial. We can not use the primary key in the quarantine, because here everything is accepted and so nothing of the entered data (not even issuetree_id) has to be be unique. But to identify the entry in the quarantine table we create the serial quarantine_serial.

Trigger function used for the solution when inserting into or update live

Actually the trigger function before is not usable. Because it works only to insert new data into the live system. Now we remember the use case. The trigger here in the quarantine does not know if the tree-inspector created a new issuetree or updated an old one. On synchronization he made an INSERT INTO to the quarantine with all entries. But these could be new entries (new trees) or already existing ones in the live table. So the trigger function has to decide, if it’s an insert or an update on the live table.

Recursion problem

The problem with the recursion is that we have a trigger after update of table issuetree in quarantine. This trigger calls the function, and the function (in case of fail updating live) updates the quarantine.issuetree with the error-message. So there is another update and the trigger is fired again, and again, and again… ♪Across the universe♬

We could solve the problem by checking the depth of triggers in PostgreSQL:

And it looks like this

The yellow points are the issue trees in the live. If we create another one and have a mistake in it (GPS Id wrong), then it’s stored in the quarantine (pink). When we correct the data it’s written over the quarantine trigger into live. If succeeded, the point changes the color to yellow.

Actually the yellow point appears (live) and the pink point(quarantine) disappears, because the entry is inserted into live and deleted in quarantine.

Archiving all data

There are different reasons why you need to archive data. Maybe somewhen you want to show your grandchildren, how much forest we still had today before the sky got dark. But this was not the reason for the mentioned customer, but legal reasons:
When the woodsman cuts the last bamboo tree of the forest and this was the only food for the very last living panda bear of Switzerland, we need to know who created or changed this entry in the database and what tree should have been chopped down instead.

Third schema “archive”

So we created a third schema parallel to live and quarantine. The archive schema. This means every table in live does not only have a quarantine table accordingly, but also an archive table too where all the old status of entries including the timestamp, when it has been archived.

Of course not only the changed live data are stored in the archive, but also every changed data from quarantine.

Use Case 1

The tree-inspector enters an entry of an issue tree that already existed in the live table to the quarantine (1). The after insert trigger is fired and it tries to write to the live table. And with success. The entry is written to the live table (2). This means, before the entry in live is updated, the old one was copied to the archive table (3). Then in the same transaction the entry in the quarantine is deleted (1). Means the old status is copied to the archive too (4).

So there will be the updated entry in the live-table (2), no entry in the quarantine-table (1) and two entries (3 and 4) in the archive table.

Use Case 2

The tree-inspector enters an entry of an issue tree that already existed in the live table to the quarantine (1). The after insert trigger is fired and it tries to write to the live table. And it fails. The entry in the quarantine will be updated with the error-message (2). The old status is copied to archive (1). The office clerk makes no the changes to this entries. The trigger is fired and this time it could write into the live-table with success (3). So the old entry is copied to the archive (4) and after deleting the entry in the quarantine, there will be the second old status of quarantine (5) in archive too.

So there will be the updated entry in the live-table (3), no entry in the quarantine-table (1 and 2) and three entries (1, 4 and 5) in the archive table.

Structure

And how we do that?

It’s solved by using triggers too. We actually need only one trigger per table, but not only in quarantine, but also in live. It has to be fired before every update of every entry, doing this:

Insert a copy of the current entry into the archive table with the status it had until the update we are doing right now.

Code

It’s the same code for the live and the quarantine table triggers. So only the ones for the quarantine are explained.

Trigger on table quarantine.issuetree before update

Trigger Function (simplified)

And the archive-tables have a default time-column to store the time, when the entry has been archived:

That’s it

That’s what I just needed to tell you. It was a very interesting project and I liked working on it.

Thanks for reading so far. If you have questions, improvement suggestions or anything else to tell me, then please comment it.

See yah! 🙂

Posted in Non-commercial, PostgreSQL, QField, QGIS, Scripts

Interlis translation

Lately, I have been confronted with the need of translating Interlis files (from French to German) to use queries originally developed for German data. I decided to create an automated convertor for Interlis (version 1) Transfer Format files (.ITF) based on the existing cadastral data model from the Swiss confederation (DM01AVCH).

The ILI model file conversion has been achieved manually once. This was quite simple since the used model is an extension with little to no difference with respect to the confederation model which already exists in several languages.

Next was to automate the conversion of the ITF files.

A program developed by Swisstopo called DM01AVCH_Translator existed to translate confederation model’s ITF files. Originally developed in 2008, the solution is sadly no longer maintained by Swisstopo and was available on Windows only. Moreover it can’t be completely automated since some interaction is required in the GUI and some tweaks in the output file are needed.

So I decided to develop a dedicated and fully automated solution which I’d like to share since it is easily adaptable to new scenarios and hopefully can avoid troubles to those who are playing with Interlis files!

You can find this utility, written in Python, called ITF_Translator on https://github.com/opengisch/ITF_Translator

ITF_Translator

ITF_Translator is capable of translating Interlis v1 transfer files (ITF) to another language thanks to a dictionary text file. Currently restricted to German, French and Italian, it is a simple operation to add support for other languages.

ITFTranslator class from itf_translator_generic module creates a translator object based on a custom dictionary file whereas some custom translations rules can be added.

Two extensions of ITFTranslator exist already and contain everything needed to translate DM01AVCH  (cadastral data model from the Swiss confederation) and MD01MOVD  (cadastral data model from Canton Vaud). These classes are ITFTranslatorDM01AVCH respectively ITFTranslatorMD01MOVD.

Dictionary file

The dictionary file is a text file composed of line formatted as follows:

german_translation;french_tranlsation;italian_translation

with the following rules:

  • line beginning with ‘#’ and blank lines are ignored
  • no spaces are allowed, use underscores ‘_’ instead

Lines are read from the top to the bottom. If a translation key is repeated, the last one will be used.

The existing dictionaries for ITFTranslatorDM01AVCH and ITFTranslatorMD01MOVD are based on the dictionary from Swisstopo’s tool.

Usage example

To translate the file input.itf  based on the DM01AVCH  model from French to German:

A file named output.itf is created and contains the translation.

Rules

The ITFTranslatorDM01AVCH and ITFTranslatorMD01MOVD extend ITFTranslator class and implement required additional rules to correctly translate the respective ITF files. These rules exist to handle non reversible translations. For instance in the DM01AVCH model, “element_lineaire” in French can be translated in German to either “linienelement” or “linienobjekt” depending on the topic. Hereby, we have the opportunity to easily add some context dependant rules which could handle any specific use-case.

Looking at the code ITFTranslatorDM01AVCH demonstrates how easy it is to create translators for other models. Rules are objects of the class SpecialCaseRule

The goal of these rules is to define the translation of a table within a precise topic. Dictionary based only translations indistinctively treat every occurrence of the words in the source file. The proposed approach is convenient because it combines simple dictionary files which are valid in most cases, and rules to handle specific scenarios.

An example of a rule defined for ITFTranslatorDM01AVCH is:

It solves the example cited previously, specifying that the translation from French to German of the table “Element_lineaire” in the topic “Bords_de_plan” is “Linienobjekt” while the dictionary file says the translation of “Element_lineaire” is “Linienelemen” for any other case.

Tagged with: ,
Posted in Interlis, Non-commercial, Python
Contact
OPENGIS.ch GmbH
Mythenstrasse 37A
8840 Einsiedeln
Switzerland

Email: info@opengis.ch
Twitter: @OPENGISch
Mobile: +41 (0)79 467 24 70
Skype: mbernasocchi
Support QField development