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

Fixing a problem

Yesterday was wild. Flash back to 6 years ago and I transferred a ESRI Process over to QGIS/PostGIS and it happened to be a 911 addressing database. As I was walking out the door, the client said "So how do we deliver the data to the state" and through a bunch of...

First install of the new TN 911 Database

First install of the new TN 911 Database

If you're just tuning in I built a postgis database for the TN NG 911 project. What this has turned into is "small counties who can't/don't want to run with an ESRI solution". I posted the code up on github (and I realize every day I suck at github). So what did I...

Playing with pg_featureserv

Playing with pg_featureserv

Which is really all this post is about - learning something new. This year I hit a lot of Conferences and a talking point I've done for the last few years has been the tn 911 project. So during the talk I always had this graphic up: QGIS makes the edits. PostGIS holds...