Collaborating on a QGIS Project saved in a PostgreSQL database
Posted by: Admire Nyakudya | in Database, QGIS | 5 months, 1 week ago | Comments

QGIS provides a couple of options when it comes to working with QGIS projects. All these formats have their strengths and weakness. When doing some work in QGIS we used to share around the QGIS projects but ever since storing the QGIS projects in a PostgreSQL database became an option we have been investigating using this as our primary method of collaboration.

When working with QGIS projects in an enterprise environment we encourage following these rules:

  • When connecting to the database either use PostgreSQL Service files or QGIS authentification framework. Do not use basic authentification as the passwords are stored as plain text in QGIS projects
  • When using images for SVG icons always embed them into the project.
  • Choose an appropriate CRS that relates to your area of interest. Local CRS would be preferred over global CRS
  • Store rasters in the DB or serve them as COGs that can be accessed as remote layers, or store them relative to a specific location where users will be able to access them.

Once all these options are satisfied then you can proceed to save your QGIS project in the database. The following procedure outlines how to connect to a database and store the project in the same database.

  • Prepare your PostgreSQL Service file i.e ~/.pg_service.conf
    [gis_service]
    host=localhost
    port=25432
    user=xxx
    dbname=gis
    password=xxx
  • Set up your PostgreSQL connection within QGIS using the service name you defined above.
  • Load all your layers into the QGIS Project.
  • From the Project menu select Save to PostgreSQL and populate the dialog as needed.
  • In the PostGIS connection properties enable 'allow loading/saving QGIS projects in the database
  • Ensure your database role has permission to create or use the qgis_projects table

Now the QGIS Project is saved into the database. But we now have a different kind of problem. How do we track which project is the most recent and how do we prevent users from overwriting each other's changes? To handle this, we can write a custom function and trigger in the database.

  • Open the DBManager and connect to your database.
  • Run the SQL below
    -- Function to copy old project into a the new row in the same project table
    CREATE OR REPLACE FUNCTION update_project()
    RETURNS trigger LANGUAGE plpgsql
    AS
    $$
    BEGIN
      IF NEW.content <> OLD.content THEN
            INSERT INTO qgis_projects (name,metadata, content)
            VALUES(OLD.name ||'_'|| now(),OLD.metadata,old.content);
      END IF;
      RETURN NEW;
    END ;
    $$;

    -- Create a trigger to populate the backup project with old copies of the QGIS project
    CREATE TRIGGER last_project_changes
      BEFORE UPDATE
      ON qgis_projects
      FOR EACH ROW
      EXECUTE PROCEDURE update_project()
  • Now all users can start working on the project and save them as they go. You can always revert to an older project if you feel someone has overwritten some important changes.

Current rating: 5

Comments

Have any questions? Contact us.
We would love to hear from you!
Subscribe to our Mailing list (spam-free, industry-relevant occasional emails)

Subscribe to our mailing list

* indicates required