Unintended Joy

Jul 2, 2024 | 911, QGIS

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.

You may also like

Watershed GIS Part 1

Watershed GIS Part 1

I typically spend June wondering about what I'm doing, have done, and need to do work wise. One thing I keep thinking about is "the old days". By old days I mean the start of my career which was doing a lot of Watershed Mapping. Back in the mid 90's we would be...

Mergin Maps Training Class

Mergin Maps Training Class

I had a boss that would always start off with "So the Short story is...." and you'd be stuck for at least 30 minutes and maybe an hour listening to a story that didn't go anywhere. So the short story on the Mergin Maps Class.......... NRGS is a Mergin Maps partner. A...

Type in the TN NG911 Database

Type in the TN NG911 Database

THE PLAN.....there was a plan. Oh yes - the plan is to make editing and creating data in TN for the 911 addressing system to be as easy as possible. Menus and Triggers abound and now as two counties are using this with a third on the horizon - HOW do I make this...