PostgreSQL Notifications and QGIS

Feb 22, 2024 | QGIS

This usually turns up in the QGIS world every two years – So it’s my turn to talk about it. This month I did an emergency install of the TN 911 database. Basically client had a problem, we solved it in the most elegant way possible, and I’m still sorting out a few things. Generally it went smooth. We went from no database to being able to edit the existing data in half a day.

There were a few problems and I decided to sit down today and fix what I could. I think I’ve been actively using postgis for like 8 years now and there’s still things I learn. So after rewriting a couple of functions that recorded time stamps for metadata I started thinking about “multiuser”. When I did the first install we had about 4 people editing one fateful summer and I learned a lot on the human side of editing.

Today I added three more triggers to the setup which reference this post kartoza did a few years ago. I set up a signal/notification so that anytime an edit happens (be it insert, delete, or update) it fire’s off a signal to update qgis. Usually you have to manually refresh.

CREATE FUNCTION public.notify_qgis() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
        BEGIN NOTIFY qgis;
        RETURN NULL;
        END;
    $$;

CREATE TRIGGER notify_qgis_address_edit
  AFTER INSERT OR UPDATE OR DELETE ON tn911.address_points
    FOR EACH STATEMENT EXECUTE PROCEDURE public.notify_qgis();

Once that is set up, updates are visible if you have two people editing. I pulled up two QGIS sessions to show you editing. All this happens on a Save:

So that’s this years look at notifications. Make an edit. Have postgresql fire off a notification. QGIS Updates.

You may also like

TNGIC 2024

TNGIC 2024

So - if you're coming to the 2024 TNGIC (TN Geographic Information Council) meeting (April 9-11 2024) to talk about how no one understands what you do......I'm doing a workshop. What workshop? It's a new workshop. QGIS and LIDAR data. As to what I do with the class...

iNaturalist and QGIS

I am probably the worst for being able to properly name "things". There are big trees and little trees. There are the annoying bugs and the not so annoying bugs. There are ducks, birds, bigger birds, and the birds that hang out at my bird feeder. As much time as I...

Mergin Maps and DB Sync

Mergin Maps and DB Sync

I don't know if I would label this exercise as fun but I've learned alot. I don't do a ton of data collection these days. At one time a few years ago I was out in the field with Fulcrum, a laptop, and sketchy internet for days at a time. These days I'm hardly ever...