During the pandemic, people noticed how well they could work remotely, how productive meetings via video call could be, and how well webinars worked. At OPENGIS.ch, this wasn’t news because we have always been 100% remote. However, we missed the unplanned, in-person interactions that occur during meetups with a or . That’s why we’re very pleased that last week we could join the Swiss QGIS user day for the second time after the pandemic.

OPENGIS.ch has been invested in QGIS since its inception in 2014, actually even before; our CEO Marco started working with QGIS 0.6 in 2004 and our CTO Matthias with version 1.7 in 2012. Since 2019, we have also been the company with the most core committers. We can definitely say that OPENGIS.ch has been one of the main driving forces behind the large adoption of QGIS in Switzerland and worldwide. 

Looking at the work done in the QGIS code we’re by far the most prolific company in Switzerland and second worldwide only to North Road Consulting. On top of it, we were the first – and still only one of two- companies to sustain QGIS.org at a Large level since 2021.

This makes us very proud and it is why we’re even happier to see how much that is happening around QGIS in Switzerland aligns with the visions and goals we set out to reach years ago.

The morning started with a presentation by our CTO Matthias “What’s new in QGIS” featuring plenty of work sponsored by the Anwendergruppe CH.

DXF Improvements, the release of SwissLocator 3.0 with swissalti3d and vector tiles integration, and an update on the advances towards solid curve handling in QGIS, a prerequisite for properly handling AV data in Switzerland, were only some of the many noteworthy points he touched.

The highlight of Matthias’ presentation was the better OGC API Features support in QGIS, which was also highlighted in a subsequent talk about Kablo, showing how the next generation of industry solutions (Fachschalen) will be implemented.

Slides: Neues aus der QGIS Welt - QGIS Anwendertag 2024

Following was a short presentation on the project DMAV, Christoph Lauber introduced a project that aims to implement an industry solution for official cadastral surveying with QGIS.

Adrian Wicki of the Federal Office for the Environment (FOEN) and Isabel presented how OPENGIS.ch and the partners Puzzle and Zeilenwerk help the FOEN with the SAM project with assess the hazards of flood, forest fire, or landslides, and warn authorities and the population. With an agile project organisation, the complex project succeeds in fulfilling requirements by applying user-centred development concepts. QGIS is used for visualizing and analyzing data and helping forecasters gain insights into the current situation.

Slides: BAFU_SAM

Andreas Neumann from ETH Zurich and Michael presented the qgis-js project. QGIS-js is an effort to port QGIS core to WebAssembly so that it can be run in a web browser. Although still in the early experimentation phase, this project has great potential to leverage interesting new use cases that weren’t even thinkable before.

Slides: https://boardend.github.io/qgis-js-demo/ 

Olivier Monod from the City of Yverdon presented Kablo, an electricity management proof of concept of the next generation implementation for industry solutions developed in collaboration with OPENGIS.ch.

By applying a middleware based on OGC API Features and Django, Kablo shows how common limitations of current industry solutions (like permission management and atomic operations) can be overcome and how the future brings desktop and web closer together.

Slides: kablo-qgis-user-days

Obviously, it wasn’t just OPENGIS.ch. Sandro Mani from Sourcepole presented the latest and greatest improvements on QWC2, like street view integration and cool QGIS features brought to a beautiful web gis. Andreas Schmid from Kt. Solothurn presented how cool cloud-optimized geotiff (COG) is and what challenges come with it. Interested in the topic? Read more in our report about cloud optimized formats. Mattia Panduri from Canton Ticino explained how they used QGIS to harmonise the cantonal building datasets and Timothée Produit from IG Group SA presented how pic2map helps bring photos to maps. 

To round up the morning, Nyall Dawson from North Road Consulting did a live session around the world to show the latest developments around elevation filtering in QGIS.

In the afternoon, workshops followed. Claas Leiner led a QGIS expression one while Matthias and Michael showed how to leverage QGIS processing for building geospatial data processing workflows. 

The first QGIS model baker user meeting took place in the third room. The participants discussed this fantastic tool we developed to make INTERLIS work smarter and more productive.

It was a very rich and constructive QGIS user day. We came home with plenty of new ideas and a sense of fulfilment, seeing how great the community we observed and helped grow has become.

A big thanks go to the organisers and everyone involved in making such a great event happen. Only the beer in the sunshine was literally watered by the rain. Nevertheless, there were exciting discussions in the station bistro or in the restaurant coaches on the way home.

See you next time and keep contributing

The PostgreSQL Connection Service File pg_service.conf is nothing new. It has existed for quite some time and maybe you have already used it sometimes too. But not only the new QGIS plugin PG service parser is a reason to write about our love for this file, as well we generally think it’s time to show you how it can be used for really cool things.

What is the Connection Service File?

The Connection Service File allows you to save connection settings for each so-called “service” locally.

So when you have a database called gis on a local PostgreSQL with port 5432 and username/password is docker/docker you can store this as a service called my-local-gis.

# Local GIS Database for Testing purposes

[my-local-gis]

host=localhost port=5432 dbname=gis user=docker password=docker

This Connection Service File is called pg_service.conf and is by client applications (such as psql or QGIS) generally found directly in the user directory. In Windows it is then found in the user’s application directory postgresql.pg_service.conf. And in Linux it is by default located directly in the user’s directory ~/.pg_service.conf. 

But it doesn’t necessarily have to be there. The file can be anywhere on the system (or on a network drive) as long as you set the environment variable PGSERVICEFILE accordingly:

export PGSERVICEFILE=/home/dave/connectionfiles/pg_service.conf 

Once you have done this, the client applications will search there first – and find it.

If the above are not set, there is also another environment variable PGSYSCONFDIR which is a folder which is searched for the file pg_service.conf.

Once you have this, the service name can be used in the client application. That means in psql it would look like this:

~$ psql service=my-local-gis
psql (14.11 (Ubuntu 14.11-0ubuntu0.22.04.1), server 14.5 (Debian 14.5-1.pgdg110+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

gis=#

And in QGIS like this:

If you then add a layer in QGIS, only the name of the service is written in the project file. Neither the connection parameters nor username/password are saved. In addition to the security aspect, this has various advantages, more on this below.

But you don’t have to pass all of these parameters to a service. If you only pass parts of them (e.g. without the database), then you have to pass them when the connection is called:

$psql "service=my-local-gis dbname=gis"
psql (14.11 (Ubuntu 14.11-0ubuntu0.22.04.1), server 14.5 (Debian 14.5-1.pgdg110+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

gis=#

You can also override parameters. If you have a database gis configured in the service, but you want to connect the database web, you can specify the service and explicit the database:

$psql "service=my-local-gis dbname=web"
psql (14.11 (Ubuntu 14.11-0ubuntu0.22.04.1), server 14.5 (Debian 14.5-1.pgdg110+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

web=#

Of course the same applies to QGIS.

And regarding the environment variables mentioned, you can also set a standard service.

export PGSERVICE=my-local-gis

Particularly pleasant in daily work with always the same database.

$ psql
psql (14.11 (Ubuntu 14.11-0ubuntu0.22.04.1), server 14.5 (Debian 14.5-1.pgdg110+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

gis=#

And why is it particularly cool?

There are several reasons why such a file is useful:

• Security: You don’t have to save the connection parameters anywhere in the client files (e.g. QGIS project files). Keep in mind that they are still plain text in the service file.
• Decoupling: You can change the connection parameters without having to change the settings in client files (e.g. QGIS project files).
• Multi-User: You can save the file on a network drive. As long as the environment variable of the local systems points to this file, all users can access the database with the same parameters.
• Diversity: You can use the same project file to access different databases with the same structure if only the name of the service remains the same.

For the last reason, here are three use cases.

Support-Case

Someone reports a problem in QGIS on a specific case with their database. Since the problem cannot be reproduced, they send us a DB dump of a schema and a QGIS project file. The layers in the QGIS project file are linked to a service. Now we can restore the dump on our local database and access it with our own, but same named, service. The problem can be reproduced.

INTERLIS

With INTERLIS the structure of a database schema is precisely specified. If e.g. the canton has built the physical database for it and configured a supernice QGIS project, they can provide the project file to a company without also providing the database structure. The company can build the schema based on the INTERLIS model on its own PostgreSQL database and access it using its own service with the same name.

Test/Prod Switching

You can access a test and a production database with the same QGIS project if you have set the environment variable for the connection service file accordingly per QGIS profile.

You create two connection service files.

The one to the test database /home/dave/connectionfiles/test/pg_service.conf:

[my-local-gis]
host=localhost
port=54322
dbname=gis-test

And the one for the production database /home/dave/connectionfiles/prod/pg_service.conf:

[my-local-gis]
host=localhost
port=54322
dbname=gis-productive

In QGIS you create two profiles “Test” and “Prod”:

And you set the environment variable for each profile PGSERVICEFILE which should be used (in the menu Settings > Options… and there under System scroll down to Environment

or

If you now use the service my-local-gis in a QGIS layer, it connects the database prod in the “Prod” profile and the database test in the “Test” profile.

The authentication configuration

Let’s have a look at the authentication. If you have the connection service file on a network drive and make it available to several users, you may not want everyone to access it with the same login. Or you generally don’t want any user information in this file. This can be elegantly combined with the authentication configuration in QGIS.

If you want to make a QGIS project file available to multiple users, you create the layers with a service. This service contains all connection parameters except the login information.

This login information is transferred using QGIS authentication.

You also configure this authentication per QGIS profile we mentioned above. This is done via Menu Settings > Options… and there under Authentication:

(or directly where you create the PostgreSQL connection)

If you add such a layer, the service and the ID of the authentication configuration are saved in the QGIS project file. This is in this case mylogin. Of course this name must be communicated to the other users so that they can also set  the ID for their login to mylogin.

Of course, you can use multiple authentication configurations per profile.

QGIS Plugin

And yes, there is now a great plugin to configure these services directly in QGIS. This means you no longer have to deal with text-based INI files. It’s called PG service parser:

It finds the connection service file according to the mentioned environment variables PGSERVICEFILE or PGSYSCONFDIR or at its default location.

As well it’s super easy to create new services by duplicating existing ones.

And for the Devs

And what would a blog post be without some geek food? The back end of this plugin is published on PYPI and can be easily installed with pip install pgserviceparser and then be used in Python.

For example to list all the service names. 

>>> import pgserviceparser
>>> pgserviceparser.service_names()
['my-local-gis', 'another-local-gis', 'opengisch-demo-pg']

Optionally you can pass a config file path. Otherwise it gets it by the mentioned mechanism.

Or to receive the configuration from the given service name as a dict.

>>> pgserviceparser.service_config('my-local-gis')
{'host': 'localhost', 'port': '54322', 'dbname': 'gis', 'user': 'docker', 'password': 'docker'}

There are some more functions. Check them out here on GitHub or in the documentation.

Well then

We hope you share our enthusiasm for this beautiful file – at least after reading this blog post. And if not – feel free to tell us why you don’t in the comments