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

QGIS US User Group

QGIS US User Group

I've been too busy to write anything as of late. I have a lot to talk about - just not much time to do it. So FOSS4GNA happened on Sept 9-11 2024. Probably the biggest thing for me during the conference was we had two BOFs on QGIS. Granted - I think it was supposed to...

QGIS and LIDAR

So this will be my last "New Class" announcement for a bit. Back in the spring of 2024, I taught a 4 hour QGIS and LIDAR class at the TNGIC meeting (State of TN GIS meeting). I ran through it and shelved it and now I'm fixing the problem spots and smoothing down the...

NENA 911 Database in QGIS

NENA 911 Database in QGIS

I work with two counties in TN with the TN NG911 standard. Flash back 10 years ago and I hadn't really grasped how complicated address is. Do I like it? Mostly Yes. Occasionally I get questions from outside TN and I demo the two counties and how that works. A while...