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

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...

Montana

Montana

I said this year I was cutting back on conferences....and then someone called from Montana. I am headed to Montana to teach the Intro to QGIS Class. I'll be doing that Tuesday April 16th - it will be the 1/2 day edition. At some point I'll also be speaking. That...