I was originally going to call this unintended consequences but – there was a small amount of joy at the end.
In Tennessee you have 6 layers that are required by the NG 911 group. So you end up with a lot of information depending on the county being poured into the address layer. You may have fire hydrants, fire stations, EMS locations, etc all appearing in the address layer.
You could export out the fire hydrants into a separate layer. You could – but then you’ve broken your data to a degree because then you always have to refresh it wither weekly or monthly just to keep that shapefile updated. Yes I said Shapefile.
We did this for a bit:
Virtual layers in QGIS work great. I always dig back through my blogging and it’s been a while since I’ve mentioned Virtual Layers. You create those in much the same manner you would a file (Layers-> Crete Layers) except you are prompted to add or import a layer. From there you can execute some SQL and have a virtual layer than updates as you update the data. You don’t update the virtual layer itself – just the underlying data. Bonus – it works with Mergin Maps.
Of course I worry about losing the QGZ file. It doesn’t happen much but upon occasion it does happen. So after some talk we offloaded the problem to Postgresql/Postgis in the form of a view. Views can be slow – but we aren’t going to have too much with this small county so:
CREATE VIEW hcgis.fire_hydrants_view
AS
SELECT
a.id,
a.oirid,
a.geom,
a.address,
a.strucdesc
FROM tn911.address_points b,
tn911.address_points a
WHERE strucdesc = 'Fire Hydrant';Selecting data isn’t hard. You could very easily pull this out however you want it. Symbolizing data isn’t hard – there are enough tricks in QGIS we can go this route. I like making the server work a little. So we pulled Fire Hydrants into a view. We also pulled a few more things out to save some heartburn for crazy last minute requests.
What about a materialized view? I could have but this isn’t a lot of data. Bonus it does pull into Mergin Maps. Granted with a view vs a viirtual layer you could edit the fire hydrant layer – but – for me the pros outweigh the cons.
Anyway – the small opensource 911 project kicks along unintentionally bringing some fun to a 911 in TN near you.
