In between work I get a chance to play around with software. I’ve been digging around postgis more and more these days trying to get a better understanding of what it will do or won’t do. It’s a never ending learning experience for me. Sometimes it pays off – the never ending tutorial left me suggesting we dump a database to a geopackage.
I’m great at using geopackage in sentences like “USE GEOPACKAGE” and “Hey – what do you think about geopackage?”. Since I’m actually officially using it I decided to do some digging. It’s a SQLite container. Since it’s a SQLite container I should be able to do some things with it – like…..
I can use the spatialite gui and throw SQL against it and change the tables.
While talking to another person the question started with ‘Well – why can’t we just do the data collection in geopackage?’. Which we did some back and forth and in some cases this would be acceptable vs doing a full postgis install.
So my first question is: Can I make a trigger in a geopackage? The answer is yes. For those of you who don’t know what a trigger does – it responds to some action in a database. In postgresql, I put multiple triggers in a database for a client. Some triggers calculate lengths and some areas and some make a note of who edited a feature.
After some digging I found the syntax and wrote a trigger for SQLite/Geopackage in the form of:
CREATE TRIGGER update_esn AFTER INSERT
ON addresses
BEGIN
update addresspoints set “esn” = (select esn from polygon where st_within(new.geom, geom));
END;
I used the spatialte gui to add the trigger. So if I pass this off to a client they can edit an address and have it automatically pull in the emergency service number. Magic? Eh – close enough for me.
While it’s not going to work for a multi-user situation (or will it – I’ve not investigated) – it works. So now I’m adding widgets to QGIS to aid in data collection.
