Fixing a problem

May 21, 2024 | 911, GDAL

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 conversations we ended up producing a geopackage. The ESRI Enterprise system consumed it. Life was good. Mostly.

Occasionally the Quality Check process would change on the receiving end and there was a problem. I switch back and forth from “ESRI can’t read this consistently” to “Geopackage isn’t quite working like I need”. Regardless of what I think, I need to fix it. The latest break happened last week.

There was a lot of emailing back and forth and exchanging of error logs. I have two counties running an open source setup. We’re in the minority. This has to be smooth so I can point and go “See it’s Open Source and it’s smooth and it works”.

The QAQC group sent us an empty ESRI File Geodatabase. From that I discovered a few issues with my postgresql setup I had missed. I also fell back to GDAL.

ogr2ogr -progress -append -f OpenFileGDB tn911.gdb -nln "Addresses" PG:"host=localhost port=5432 user='snoop' password='hahaha' dbname='henderson'" -sql "select id as objectid, geom, oirid, r_segid, a_segid, seg_side, gislink, structype, strucdesc, stnum_h, stnum_l, stnum, stnumsuf, building, floor, unit_type, unit_num, predir, pretype, name, type, sufdir, postmod, address, addr_esn, label, subname, vanity, zip, zip4, esn, city, county, state, lon, lat, x_sp, y_sp, z_val, gpsdate, addrauth, source, editor, geomod, geosrce, geodate, attmod, attsrce, attdate, status, delnotes from tn911.address_points"

In English:

  • Open the ESRI File Geodatabase TN911.gdb, specifically the Addresses Feature Class
  • Connect to Postgresql
  • Use SQL to grab what I need from Postgresql
  • I keep all the aliases and everything else in the File GDB that may be affecting pass/fail.


I had this long conversation at a conference with a student and her worry was “WHAT DO I LEARN? Python? R?” The only thing that has held consistent for me has been data centric skills. As of right now in my career that means having a good handle on GDAL. It’s an underappreciated skill set because all this other stuff you’re going to learn hinges on GDAL. I fixed a process at two client locations with one tool. I also took data from a database and shoved it into a proprietary dataset. With any hope we get another 5 years out of this process.

Anyway – Learn some GDAL.

You may also like

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

Photos in Mergin Maps

Photos in Mergin Maps

Not that this is new - Mergin has been doing this for a bit BUT I finally had the chance to sit down and use it. I have a client taking pics of houses to feed into the 911 dispatch system. Granted - not easy, not fun, and not his idea but he walked into this and has...

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