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

Model Designer Class June 25th 2024

Model Designer Class June 25th 2024

June 25th Location: Virtual Cost: $150 Time: 12pm to 4pm EST Link: https://www.eventbrite.com/e/qgis-model-designer-tickets-906763111877 The Model Designer Class makes a re-appearance after a tuneup. What is it? This 4 hour class will cover building Processing Models...

Conda and QGIS Part 2

Conda and QGIS Part 2

Coming back from Yellowstone has left me slightly adrift. I missed a meeting yesterday as I keep forgetting to add things to my calendar. So the last time I mentioned Conda was like 2018 and the whole point to the Article was "I can now have two versions of QGIS...

Calculating NDVI in QGIS

Calculating NDVI in QGIS

Titling these articles is getting more difficult. I almost called this my "Getting my Junior Forestry Badge" because that's what I told one client the other day as I confidently misidentified another tree on a walk. A forestry person I am not - but I've gotten pretty...