User Guide¶
This user guide will demonstrate how to prepare a project to be used with the Guided Offline Editing QGIS plugin. It will focus on the prior steps, before the plugin can be used, as using the plugin is really easy. It will guide you through the preparation process, from modifying a PostgreSQL database so that it can be used with the plugin, to creating a to-be-offline-edited project.
Example context: collecting data about wetlands¶
Imagine that you, Alice (or Alexander if you’re a man, but we will use Alice from now on), work for a biodiversity NGO named We Love Biodiv.
Inside We Love Biodiv, you are the Database Administrator and also the one who knows QGIS best. Your colleague, Bob, knows nothing about databases, SQL, and only uses QGIS to make beautiful maps and for data analysis. He doesn’t understand the DB Manager dialog, he can’t figure out where to look for PostgreSQL layers, and he thinks that creating relationships between layers in QGIS is too complicated, same thing for creating forms (sounds familiar to you?).
Now, We Love Biodiv is involved in a project whose goal is to collect data about wetlands on a specific territory. For this project, you and bob will survey wetlands on the field, then upload collected data into PostreSQL. You are in charge of designing and creating tables and views in database, then you have to set up forms in QGIS so that Bob can use them to enter field data.
PostgreSQL prerequisites¶
Let’s assume you have a PostgreSQL service on host db.priv.welovbiodiv.org
that listens on TCP port 5432
. On that cluster, you have a database named
wlbdb
. You will use that database to store and protect collected wetland
data.
Schemas and tables¶
Inside the wlbdb
database you have at least two PostgreSQL schemas named
ref
and wetland
. The ref
schema contains tables with reference
data. For example, there is a taxon
table containing names of all species
known to be present in your country, and a city
table that hold all cities
information with their perimeters.
The wetland
schema will hold collected data about wetlands. Inside that
schema you will create tables and design a data model as normal as possible.
For the sake of simplicity, we will assume there are only two tables: the main
wetland
table with a geometry
column, and a wetland_type
table
containing the set of all wetland types. Of course, the wetland
table will
have a type_id
column which is foreign key referencing the wetlandt_ype
table. On the taxon side, since multiple species will be observed on a given
wetland, and conversely a species will appear on multiple wetlands, we use an
association table named wetland_taxon
(Fig. 1).

Fig. 1 Database model¶
Note
Each table that will be edited in QGIS must have an integer primary key, even if a natural key already exists (for that column use a unique constraint and index): this is what the QGIS built-in Offline Editing plugin expects so that it can match offline and remote features (when a feature is updated or deleted for example).
You may ask: what about the city table? Well, there are multiple choices. You
can add a city_id
column in the wetland
table and ask users to select
the right city. Or you may populate automatically this column using Postgis
intersection functions. Or you may not put this information inside the
wetland
table and create a view which join the city
table on
intersection. This is database design issue, not the topic here, so we won’t
discuss it further.
We also recommend to create a qgis
schema and to save QGIS projects
inside that schema.
Note
You do not need to create any table inside the qgis
schema. The
first time you save a QGIS project to PostgreSQL and select the
qgis
schema as destination, QGIS will automatically create a
qgis_projects
table and use it to save and load projects.
Use PostgreSQL power for consistent data¶
A few words about table design.
Please put as much energy as you can to ensure complete and consistent data. And first, try to reach the highest normal form possible. See https://en.wikipedia.org/wiki/Database_normalization for more information about database normalization.
Next, PostgreSQL is not a spreadsheet. It is a feature complete, professional grade, database management system, with many bells and whistles. Creating a table with just the columns and their types is nothing but throwing your data away. Instead you can use all the constraints, views, materialized views, and triggers machinery to make sure that missing values are computed (or else the row is rejected) and that columns are consistent (if one is empty, another must be provided) for instance.
In other words, make your projects SQL-first and data driven.
A few examples:
when you add a
name
or atitle
column with a not null constraint, you should also check that the string cannot be empty (with acheck title <> ''
constraint). Otherwise, you will get surprises when importing CSV files,check that only valid geometries are saved (with a
check st_isvalid(geometry)
constraint),make sure columns are consistent. If, for instance,
col_a
andcol_b
are mutually exclusive, meaning either the first or the second must be filled, but not both at the same time, and not both can benull
, you can write something like this:check (col_a is not null and col_b is null) or (col_a is null and col_b is not null)
. Or if you havecount_min
andcount_max
columns, you can add acheck count_min <= count_max
constraint,imagine you have a table with
point
geometries, and you want to know in which city lies each point. You have multiple options to handle this. First, you can have acity_id
column in the table, which is a foreign key to theref.city
table. Then a trigger will computecity_id
for each inserted or updated row, using Postgisst_intersects
function. Or, and this is a better approach in my opinion, you can create a materialized view that also usesst_intersects
, without thecity_id
column in the main table, which is more normal,on tables that are not to be loaded within QGIS (more on this below), use all PostgreSQL data types. For example, use an
int4range
type column instead of twointeger
columns for minimum and maximum. The range type will automatically ensure that minimum is less than maximum and also provides operators to test whether a value is within a range. See https://www.postgresql.org/docs/current/rangetypes.html. Another example: when you have a hierarchy of rows, do not use the classical two columns solution,id
andparent_id
. Instead use theltree
data type on a single column which is more powerful. See https://www.postgresql.org/docs/current/ltree.html,add comments on schemas, tables and columns. Especially on columns: QGIS will use these comments to provide help for each field when generating forms.
But be aware that there are also some limitations on the database side.
Regarding the Postgis extension as an example, you cannot add an exclusion
constraint on a table with polygon
geometries to say that polygons must not
overlap. This is because there is no operator in Postgis doing the same thing
than the st_overlaps
function. Of course you can work around this
limitation, for instance by creating your own operator (using create
operator
) but this is not native. Moreover, you cannot easily add a constraint
to prevent holes between polygons. In summary, if you want a table where
polygons partition an area (that is, no holes and no overlaps) this is not
easily done in Postgis. This is better handled in QGIS. Since QGIS 3, each
layer can prevent saving if there are invalid geometries, or if there are
topological issues (Fig. 2).

Fig. 2 Geometry and topology check in layer properties¶
On the other hand, with QGIS you will have limitations on the way you use Postgis. Since some tables will be loaded as layers within QGIS, on these tables:
you cannot use some PostgreSQL types, such as
int4range
orltree
. QGIS will simply ignore these columns. You won’t see them in attribute table or in generated forms,if you want to edit them offline, then the primary key must be an integer, even if you have already another natural key,
generated identity columns (with
generated by default as identity
) are also not supported, so you must useserial
type for now.
Note
Changelog for QGIS 3.8 asserts that identity columns are supported: https://qgis.org/en/site/forusers/visualchangelog38/index.html#feature-postgres-provider-add-support-for-generated-identity-columns . But without further explainations. To my knownledge, after a little testing, this is not the case. Identity primary keys in generated forms are not handled as well as serial columns.
Permissions¶
Now, let’s discuss PostgreSQL roles and permissions.
Note
Since you’ll be modifying data directly inside PostgreSQL, we recommend to have one PostgreSQL user for each real person that will edit data. Avoid common accounts. This is also for tracability reasons.
Say you have two PostgreSQL users named alice
and bob
. These users
needs to query data in the ref
schema (but they must not modify it since
this is reference data) and edit data in the wetland
schema.
So you need to grant, to both alice
and bob
:
USAGE
permission on both schemasref
andwetland
,SELECT
permission on theref.taxon
andref.city
tables,USAGE
permission on all sequences in schemawetland
,SELECT, INSERT, UPDATE, DELETE
permissions onwetland.wetland
andwetland.wetland_type
tables.
But instead of granting these permissions directly to alice
and bob
, we
recommend to create a dedicated role, named gis
for example, grant the
permissions to gis
, and assign alice
and bob
to the gis
role.
See https://www.postgresql.org/docs/current/role-membership.html for more
information about role membership. In this manner, if a new user must have the
same permissions, you just need to assign the new user to the gis
role.
In a PostgreSQL console, this would looks like the following.
create role gis nologin noinherit;
grant usage on schema ref to gis;
grant usage on schema wetland to gis;
grant usage on all sequences in schema wetland to gis;
grant select on all tables in schema ref to gis;
grant select, insert, update, delete on all tables in schema wetland to gis;
grant alice to gis;
grant bob to gis;
Note
One final word about users. Even if you, Alice, are the Database
Administrator, we recommend not to grant all permissions to alice
user. Instead, you should log in as a dedicated user for administration
tasks, for example named dbadmin
. alice
is the user you should
impersonate when you wear your QGIS User hat. When you wear your
Database Administrator hat, for example when granting permissions like
above, you should use dbadmin
.
QGIS prerequisites¶
Now that your wlbdb
PostgreSQL database is all set, rock solid with lots of
constraints, and ready to receive field data about wetlands, let’s talk about
QGIS environment.
Common configuration ID in authentication database¶
How both Alice and Bob will open the same QGIS project and connect to PostgreSQL using their own distinct credentials? The trick is to use a common configuration ID in QGIS authentication database.
The configuration ID is a string of exactly seven characters that refers to some credentials used by QGIS. Whenever you save new credentials (username/password, private key, …) within QGIS (for example to connect to a WFS server or to a PostgreSQL database), QGIS creates a new configuration in its authentication database. And each configuration is identified programmatically by this 7-chars ID. Usually, this ID is randomly generated by QGIS. When you need some credentials to connect to something, QGIS uses this ID to refer to the credentials, and save this ID inside the project so that no password is stored in clear text in the project. See https://docs.qgis.org/3.4/en/docs/user_manual/auth_system/auth_overview.html#authentication-database for more information.
A QGIS authentication database (authdb for short) is private to a user. So for a QGIS project to work for both Alice and Bob, the project must refers to the same configuration ID in both Alice authdb and Bob authdb. Thus, the configuration ID must not be randomly generated, but must be set to a value shared across all users.
Back to our example, let’s assume that the shared configuration ID for all We
Love Biodiv users is the string wlbauth
(length: 7). Consequently, Alice
must create a new authentication configuration in QGIS (menu
and click on the
+ button on the right side), with the following information
(Fig. 3):
name: a human readable name for this configuration. For example Alice wlbdb credentials,
Id: click on the lock button to prevent the Id to be randomly generated. Then, enter the shared ID
wlbauth
.Username and Password: Alice’s credentials to connect to
wlbdb
PostgreSQL database.

Fig. 3 Alice’s authentication configuration in QGIS using wlbauth
ID¶
Of course Bob must do the same on his side. He opens QGIS on his computer then
creates a new authentication configuration using the same wlbauth
ID.
Everything else is specific to Bob:
name: Bob wlbdb credentials,
Id:
wlbauth
,Username and Password: Bob’s credentials to connect to
wlbdb
PostgreSQL database.
Plugin configuration¶
The next step is to configure the Guided Offline Editing plugin so that it can connect to PostgreSQL and find all projects that have been prepared for offline edition.
For that, each user can edit the QGIS3.ini
file in the QGIS
folder
under its profile folder. To find your profile folder, in QGIS, go to
.
Then, in the file manager window, navigate to the QGIS
folder, and edit the
QGIS3.ini
file.
In QGIS3.ini
, you need to add a section [Plugin-GuidedOfflineEditing]
and inside this section you can add the following parameters:
host
: hostname of the PostgreSQL server (default:localhost
),port
: TCP post for the PostgreSQL service (default:5432
),authcfg
: configuration ID in QGIS authentication database for PostgreSQL credentials,dbname
: name of the PostgreSQL database to connect to,schema
: name of the PostgreSQL schema containing theqgis_projects
table (default:qgis
),sslmode
: does PostgreSQL connection need SSL ? (allowed values:enabled
ordisabled
, default:disabled
).
In our We Love Biodiv example, Alice and Bob should add the following lines to
their respective QGIS3.ini
file.
[Plugin-GuidedOfflineEditing]
authcfg=wlbauth
dbname=wlbdb
host=db.priv.welovbiodiv.org
schema=qgis
Tip: deploying configuration in We Love Biodiv organization¶
As you can see, thanks to the common wlbauth
value for the authcfg
parameter, both Alice and Bob have the same plugin configuration, although
their usernames and passwords for connecting to PostgreSQL are different. Even
if a new user Charlie is involved in the wetland project, as long as he saves
his PostgreSQL credentials under the wlbauth
ID, he will have exactly the
same configuration.
So, instead of each user modifying its QGIS3.ini
file, you can deploy the
common configuration on all workstations within We Love Biodiv using the
QGIS_GLOBAL_SETTINGS_FILE
environment variable if you already do so.
See https://docs.qgis.org/testing/en/docs/user_manual/introduction/qgis_configuration.html?highlight=organization#deploying-qgis-within-an-organization for more information about deploying common QGIS configuration inside an organization.
Preparing the project¶
Finally, you, Alice, can get your hands dirty in QGIS, preparing a project for offline edition. So Let’s get started.
If not already done, create a new Postgis connection using the wlbauth
configuration ID for credentials. Open the Data Source Manager and
the PostgreSQL tab. Click the New button and fill the
form (Fig. 4):
Name: whatever name you want for this connection, for example:
We Love Biodiv DB
,Host:
db.priv.awelovbiodiv.org
,Post:
5432
,Database:
wlbdb
,Authentication: under the configuration tab, choose Alice wlbdb credentials.
You can then Test Connection using the button, and you should see a success message.
Don’t forget to check “Also list tables with no geometry” and “Allow saving/loading QGIS projects in the database”.

Fig. 4 New Postgis connection dialog¶
Project properties¶
You can now create a new empty QGIS Project. And before loading any layer you should set some properties and save proejct to PostgreSQL.
Go to Fig. 5). On the General tab:
(set the project Title using a human-readable and user-friendly string (eg. “Data collection about wetlands”).
we recommend to Save paths as Relative. This is not mandatory for now but will be in future versions of the plugin where one can mix PostgreSQL layers and local layers.

Fig. 5 Project properties dialog¶
On the Metadata tab, you can (and should) fill some metadata about the project. On the SCR tab, you can chosse the default SCR for the project. You can also set default styles if you want.
Then you must save the project to PostgreSQL. Go to qgis
schema, and give the project a PostgreSQL valid
name, that is no space, no accent, no uppercase letters, no dash (-
), only
lowercase letters, digits, and underscore (_
). For example, give it the
name wetland_collect
.
Note
PostgreSQL allow any character in its identifiers but requires to
surroung them with double-quotes ("
) if the do not obey the previous
rule (eg. "Wetland_data"
because of the uppercase W
).
Load layers¶
Using either the built-in DB Manager plugin or the Data Source Manager, add all needed layers to your project:
the
ref.taxon
table,the
wetland.wetland_type
table,the
wetland.wetland_taxon
table,the
wetland.wetland
table.
You can organize layers as you like. We suggest to create at least two groups inside QGIS legend:
a group entitled “Editable layers” where, as the name indicates, you put the two editable layers
wetland.wetland
andwetland.wetland_taxon
.a group entitled “Helper layers” where you put other layers.
Again, you can do as yo like. But having the group “Editable layers” makes it clear for users what layers can be edited.
Other recommendations:
give a human readable and user-friendly name to each layer. Don’t stick with the computer name
wetland_taxon
for example and rename itAssociation between wetlands and taxons
,in the project properties, you can make some layers mandatory or read-only. Go to Data Sources tab. There you can check for each layer either the Read-Only checkbox or the Required checkbox (Fig. 6).
and switch to the

Fig. 6 Declare in QGIS read-only and mandatory layers¶
Create relationships between layers¶
Still in Project Properties, switch to the Relations tab, and create a relation for each foreign key in PostgreSQL. QGIS can create relations automatically if you click the Discover Relations button. It will try to discover foreign keys between loaded layers. But make sure to check each of them and to add relations that QGIS missed (Fig. 7).

Fig. 7 Declare in QGIS relations between layers¶
Improve forms¶
Now, you have to improve QGIS auto-generated forms as much as you can.
For a given layer, QGIS has already done useful things. For example, it has chosen sensible widgets depending on column types (range widget for integer columns, calendar widget for date columns, and so on), and it has put a required constraint whenever a column has a not null constraint.
QGIS has also taken into account PostgreSQL comments for each column to add a help message that will be shown when the mouse cursor is hovered above a field without clicking. That’s why it is important to create comments on columns into PostgreSQL.
But you can do better. First, you should give a human-readable and user friendly alias for each field. Otherwise, QGIS will use the column name.
Second, you should replicate PostgreSQL constraint whenever possible. For
example, if you have an integer column count
with a constraint check
(count >= 0)
, QGIS will not catch this constraint in the form, and allow
negative values by default. You should then enforce that the minimum value is 0
in the form.
Of course, you will not be able to copy every PostgreSQL constraint like this.
For example if you have a mutually exclusive constraint for two columns
col_a
and col_b
(meaning that if col_a
is not null
then
col_b
must be null
, and conversely, and both columns cannot be null
at the same time), it is not possible to enforce this constraint in QGIS form.
In such cases, the best you can do is to explain the constraint in PostgreSQL
comments.
comment on column table.col_a is 'Represents something. If this field is not empty, then col_b must be left empty.'
comment on column table.col_b is 'Represents other thing. If this field is not empty, then col_a must be left empty.'
To improve a form for a layer, go to the layer Properties (double-click on the layer in the legend, or right-click the layer and select Properties) and open the Attributes Form tab (Fig. 8).
Please, spent as much time as you can to make the best form possible for your users.

Fig. 8 Configure attributes form for a layer¶