I haven’t mentioned the TN911 project in a while. Work happens on that when I have time or are motivated – I took a bit of a break to pursue some other tech rabbit holes. I ended up back on it due to some work with pyqgis/addressing questions which led me back to the TN 911 Standard.
My thought on the database is to let it do most of the work. There is some work that occurs in qgis but:
- date attributes are assigned at the database
- street concatenation happens there
- a lot of quality control happens there
In the TN NG911 standard every point needs an elevation. TN also has LIDAR data to provide that elevation. Deriving the elevation with QGIS is easy enough – how hard would it be to store it all in PostGIS?
Not that hard. You can run this if you have postgis installed:
raster2pgsql -C -I -M -F -Y -l 5 -s 2274 -t 100x100 dem.tif tn911.dem > ng911_dem.sqlI leave it up to you to dig through all the commands in the raster2pgsql command.
Once loaded into the database with psql the DEM appears much like you’d expect in QGIS. I connect to the database and add it.
The nice thing is I can now update all the addresses and set up triggers to handle this with new addresses:
update tn911.address_points set z_val = round(st_value(rast, a.geom)/3.28081) from tn911.dem,tn911.address_points as a WHERE ST_Intersects(rast, a.geom)=true;My biggest issue in doing this was actually the projection. The vector data is in EPSG:2274. The DEM data resides in EPSG:6576. I generally try to retain data projections – but I was having to transform (project on the fly) my calculations. Which made everything slower. So I moved the DEM to EPSG:2274.
Anyway – one more thing done for the standard. Next up some pyqgis and some data quality checks.
