A while back, I wrote a post about using Python to generate triggered notifications in PostGIS. The approach described in that post is somewhat fraught with peril and I’ve learned a little bit more since then, so I have a different approach, using PostgreSQL NOTIFY and LISTEN, which I’ll describe here.
The steps I followed are:
Create a spatial table:
Create a trigger function. This is where the formatting gets done. It’s best to keep the payload small, so this function creates a GeoJSON object that returns the geometry, record ID, table name, and an indication of the action. In order to keep the example simple, this one is hard-coded to handle inserts. Notice that the channel is named ‘actions’ in the trigger function. This is important for consuming the payload later.
Add an actual trigger to the table. As mentioned above, this example only handles inserts, but updates could be handled as well.
Next, build some sort of consumer to listen for the notifications. In this case, I’ll use a very simple Node app that uses ‘pg-pubsub‘ to handle the interactions.
At this point, just run the node application and it will wait for messages. Issue a simple insert in psql, like so:
The resulting console output looks like this:
This approach is safer for the database, but all things have trade-offs, so it’s best to research performance implications and such. It’s also a fairly accessible middle ground before jumping off the cliff into logical decoding. The basic tenets of this approach are to keep message payloads small and not to try to do too much processing in the trigger itself. The goal is really to get a notification out of the database so that an external application can do more heavy lifting.