QGIS Cloud: Publish Live Data in Web Map

The QGIS Cloud Support Team is often asked if it is possible to integrate data from external databases into the QGIS Cloud Web Map, so that the map information in the QGIS Cloud Web Map is always up-to-date. In this blog article we would like to show how this is possible with PostgreSQL.

To publish data in the QGIS Cloud GDI, the data must always be stored in the personal QGIS Cloud database. However, it is often the case that you store and maintain data in a local database via third-party software. In order for QGIS Cloud services to always display the most up-to-date data, you need to regularly synchronize the QGIS Cloud database with the local database. This is, of course, inconvenient and impractical.

Fortunately, however, PostgreSQL provides the Foreigen Data Wrapper (fdw). The PostgreSQL documentation writes about this:

" The postgres_fdw module provides the foreign-data wrapper postgres_fdw, which can be used to access data stored in external PostgreSQL servers. "

Well, that does sound pretty neat. Let’s see how we can use this for our question above.

Imagine the following situation. You manage geospatial data in a PostgreSQL database on your internal network for a live vehicle tracking project. The network is well protected and access to the data is only possible via the intranet. At the same time, however, you are running GDI services in the QGIS Cloud that are intended to make parts of the geodata available to a broad public. This is again only possible via the personal QGIS Cloud database.

The data on the local system is constantly changing. However, the QGIS Cloud database does not know anything about the changes. What can you do to keep the data in the QGIS Cloud DB in sync with the local DB?

  1. regularly update the data, via a complicated process, in the QGIS Cloud DB.
  • this is complicated, because external processes are running and need to be maintained.
  • there is no live data in the QGIS Cloud DB, as the update is done in discrete intervals.
  1. use `postgresql_fdw
  • no complicated processes are needed for this
  • data is maintained live in both databases

Set up the postgres_fdw in the local database.

The goal of setting up the postgres_fdw is to replicate data from the local database to the QGIS Cloud database. The two database connections are defined as follows in the example:

Local DB

host=localhost port=5432 dbname=test_db username=test_user password=xxxx

QGIS Cloud DB

host=db.qgiscloud.com port=5432 dbname=aabbcc_ddeeff username=aabbcc_ddeeff password=123456

Both connection definitions here in the example are fictitious. So you have to enter the information that is valid in your case.

Before the following SQL commands can be entered we have to set up a way to send SQL commands to the database.The easiest way to do this in QGIS is to use the ‘DB Manager’. But first you have to if not already done, create a connection definition to the local DB in QGIS. In the DB Manager you can, after you have established the connection to the local DB, open the SQL dialog. Here you have to enter all of the following SQL commands. The connection definition to the QGIS Cloud DB is already created for you by the QGIS Cloud Plugin.

So that we can use the postgres_fdw the appropriate extension must be installed. Since it is not possible to install extensions in the QGIS Cloud DB, the extension must be installed in the local DB.

CREATE EXTENSION postgres_fdw;

The next step is to connect the QGIS Cloud DB to the local DB. To do this, we open the QGIS DB Manager and connect to the local database. In order to execute SQL commands, you need to open the SQL Editor in the QGIS DB Manager. In it issue the following SQL command.

CREATE SERVER qgis_cloud
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'db.qgiscloud.com', port '5432', dbname 'aabbcc_ddeeff');

The FDW connection is now established. What is still missing is the user of the QGIS Cloud DB, which now has to be mapped into the local DB:

CREATE USER MAPPING FOR user
SERVER qgis_cloud
OPTIONS (user 'aabbcc_ddeeff', password '123456');

How to find the login information to the QGIS Cloud database? Start QGIS and login to the QGIS Cloud Plugin with your QGIS Cloud account. Open the Database tab and hover over the name of the QGIS Cloud DB you want to connect to. Now the login information of the database will appear there.

So far we have now prepared everything in the local database to use the FDW.

Creating tables in the QGIS Cloud database

In our simple example here, we want to work in the local database with a table friends that contains the residences of my friends. I want to share them live via QGIS Cloud. Every change in the table friends in my local database should be visible immediately in my QGIS Cloud map. Granted, it is a very simple example, but it works exactly the same way with whole data models.

We now log in to the QGIS Cloud database aabbcc_ddeeff with the QGIS DB Manager and open the SQL Editor here as well. The table friends in the QGIS Cloud database in our case should have the same structure as the local table. But not mandatory.

Now the table friends is created with the same structure as on the local system.

CREATE TABLE IF NOT EXISTS public.friends
(
    id bigint NOT NULL,
    wkb_geometry geometry(Point,4326),
    name character varying(255) COLLATE pg_catalog. "default",
    CONSTRAINT friends_pkey PRIMARY KEY (id)
);


CREATE INDEX IF NOT EXISTS sidx_friends_wkb_geometry
    ON public.friends USING gist
    (wkb_geometry)
    TABLESPACE pg_default;

This creates all the necessary relations in the QGIS Cloud database.

Continue in the local database

Now we switch back to the SQL editor of the local database to set up the FDW there as well. For better order, we create the schema qgis_cloud_tables there.

CREATE SCHEMA qgis_cloud_tables;

In order for the connection between the local and remote table to exist, the table friends must be imported from the QGIS Cloud database to the local database into the foreign_tables schema we just created.

IMPORT FOREIGN SCHEMA public
LIMIT TO (friends)
FROM SERVER qgis_cloud
INTO qgis_cloud_tables;

This command imports the friends table from the public schema in the QGIS Cloud database (server qgis_cloud) into the qgis_cloud_tables schema.

Now every change in the table friends is also immediately written to the table friends of the QGIS Cloud DB and is displayed accordingly in QWC2. Thus, it is now possible to synchronously replicate and present data that is continuously stored in a local PostgreSQL database by a third party system into the QGIS Cloud DB.

There is one limitation at the moment. Editing the local table directly with QGIS is currently not possible in a reliable way. We are investigating the reasons.