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

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

First install of the new TN 911 Database

First install of the new TN 911 Database

If you're just tuning in I built a postgis database for the TN NG 911 project. What this has turned into is "small counties who can't/don't want to run with an ESRI solution". I posted the code up on github (and I realize every day I suck at github). So what did I...

QGIS2Web and the Hunt for Bike Lanes

QGIS2Web and the Hunt for Bike Lanes

I was teaching a QGIS class a while back and at one point the discussion drifted into "So this OpenStreetMap thing" and I discussed how I enjoy making edits. Eventually it turned to "I'm trying to get Chattanooga's Bike Lanes into it in my spare time". One thing led...