Loading a DEM into PostGIS

Nov 5, 2021 | 911, postgis, Projections

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.sql

I 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.

You may also like

The TN 911 Project

So next week is the TN GIS meeting at Montgomery Bell State Park. Which will be the first conference I've walked into in 2 years and 2 months. I'm doing two things while I'm there - well three: Walk a lot Teach an OSM workshop Give a talk on the 911 project. So - the...

August 2005

OK - lets drag up some old memories that are a bit cloudy. Lets flash back to 2005. Back in 2005 I was working for the Feds. I think at that point I was toying with leaving and doing something else: consulting, working for another company, etc. Really I was in...

Drop Down Menus for the TN NG911 Server

The problem is I have too many options. Options are awesome except when you're dealing with people inputting data and you only want them to do it one way. QGIS has had the ability to build drop down lists for a good while and it's a very flexible way to work. For...