North River Geographic Systems Inc

Geospatial Problem Solving

postgis

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 911 project. I actually haven’t said much about it in a while. Well…..it’s gotten interesting.

Back in 2000 I did a talk on PostGIS day about how this had worked and by that point the project was about 2 years old. At that point I had talked to a lot of people in TN on the project and the general feel was that this was a lone wolf project – in other words no one else would want to use it. Plus I was getting a little anxious on how to grow it. I was even advertising it as a “server” – which yes it’s all housed in postgresql/postgis with QGIS as a front end.

So I did something weird and tossed it up on github and started working on it. Cleaning up code. Issuing tickets to myself and generally working. Out of nowhere Kyle Snyder came in and cleaned up some code. Now there’s two of us “doing things”.

So what’s the point? Well the point is I hope more counties in TN actually use it. I’m going to discuss it next week and show a running example. Basically at this point we just about have the TN NG911 standard 95% “running” as published in the manual. Hopefully by next week we have some instructions. I’ve got a few menus set up for QGIS. You get a ton of flexibility “out of the box”. Plus all this cleanup is going to be good for Henry County as I’ll use it to update what they have after some more testing.

The next big thing is I’ve been experimenting with PyQGIS and the discussion between myself and Kyle has been “what can we offload to QGIS and what does postgis do better?”. Do we set up QGIS to do address ranges on roads? Maybe we set up some pre-address or auto-addressing. Possibly that’s a processing toolbox thing. What if we work it all out and leave it as a PostGIS script? We’ll figure it out.

Anyway – It’s pretty exciting. It is a bit messy currently. Kyle’s teaching me about Github Projects. I’m explaining my insane ideas. We’re pushing and pulling all that we can. It is nice to dump all this out into the open for everyone to pick at – maybe someone else in the 911 “address maintenance space” finds it useful.

Need some help? Yell.

Loading a DEM into PostGIS

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.

Dockering with Docker

It’s been a while since I posted anything mostly because I have no clue what to talk about these days. I’ve been working away on the Tn Address Server and one thing I wanted to do is to get it working in docker. Which – many of you are probably going IT’S NOT THAT HARD. Generally you are correct but it’s still a weird area for me to jump into. I’ve toyed with Docker for years – in general it works but I hardly ever have a reason to do much with it. Except now with the way I’m working on the TN address server I needed a way to “spin up” a postgis server and test everything and then take it down.

So I jumped back into Docker. Again.

You can build your own docker instance. You create a dockerfile and start adding whatever you need but I quickly decided I didn’t want to build my own dockerfile…yet.  After doing a bit of research Kartoza already has one built. Also – they have one for Geoserver . Geoserver has been one of those things that I don’t have quite integrated into the flow of the address sever yet – but I’m working on it.

Sitting down with Docker I had four things I wanted to do:

  • Functional database I can start and stop
  • Local Storage
  • I can sit in another room and work and still get to it on this machine.
  • I don’t want to pile of things installed on my computer.

Simple? Yes…mostly. It seems like for most of my career I’ve been dancing in between developer, system administrator, and geo person. I never seem to have complete control over any of the things except being a “geo person” scratches the itch more than the other two. So that started a day long “How do I…..?” which resulted in again reaffirming that stack exchange is the devil, documentation can be boring, and a lot of people can’t write anything to save their lives (myself included about half the time).

The biggest headache was keeping my data permanent on my hard drive. I can mount the geoserver docker to a directory  – why can’t I mount the postgis docker….and surprisingly there is a wealth of misinformation out there. In short – you have to make a volume for postgis to work – which is basically doing the following:

  • Create a /directory/somewhere
  • Create a volume: docker volume create –driver local –name pg_data –opt type=none –opt device=/directory/somewhere –opt o=bind

Finally I did the following:

docker run –name=postgis -d -e POSTGRES_USER=user -e POSTGRES_PASS=pass -e POSTGRES_DBNAME=tndemo -e ALLOW_IP_RANGE=0.0.0.0/0 -p 5432:5432 -v pg_data:/var/lib/postgresql/ kartoza/postgis:latest

Then

docker run -d -p 8580:8080 –name “geoserver” –link postgis:postgis -p 8080:8080 -v /media/data/demo/geoserver_data:/opt/geoserver/data_dir kartoza/geoserver:latest

PostGIS and Geoserver up and running in harmony…in docker. I was pleased. I can connect geoserver to postgis. I can connect to both things from my couch while I listen to the Baby Yoda Chronicles.

I was also a bit dismayed. Mostly at me.

To burn the amount of time I did getting to a functional setup was way too long. Not anyone fault as I have directions, functioning internet, and the ability to ask questions in the community. Which has been a problem for me as of late – I remember back to past failures and start fresh on a project and get haunted by the past failures. So much so I’ve been “tuning out” more. The pandemic has made things frustrating enough without my brain getting in the way. So I’m back to the art of learning this month and tossing out the old.

Now that this is running I can build and break the database at will now and hopefully get a running version of this that does everything I want it to do.

Maybe I’ll build a dockerfile just to say I did it.

Maybe I’ll do a few other fun things.

 

 

The Quarantine Server Shuffle

No I’m not doing a post about Covid. Well I sort of am doing a post about covid.

I have a client that placed me on retainer to help them sort through their GEO life. They are an ArcGIS/QGIS/Trimble/Fulcrum/Collector/Compass/Paper map kind of group. Which isn’t any different than anyone else I run into these days. ArcGIS does some of what they want. QGIS does some of what they want. Shapefiles are spread everywhere. Paper maps make an appearance when the digital side of things don’t make sense. They have a GPS and there is something of a workflow – it’s just not great. Every non-profit/conservation group I deal with wrestles with GIS. There is some sort of desktop software, some sort of pile of data. The system is so unimportant you spend 0 dollars on it until the moment it’s important and then there’s a certain amount of hand wringing and everyone just keeps doing what they are doing.

I have some stories to tell on this but the important one is: Pandemic. We were close to the end of things with new gps, new data collection techniques, and a debate on how to store the data. Then Covid happened and I received a text of “Hey we’re closing up for a while…like in 15 minutes we are closing”. Everything (software/data) is stored on one computer and they have one map sitting on AGOL. Probably the last email from that computer was the File Based Geodatabase that stored the important stuff. I don’t think the myriad shapefiles and photos made it out – BUT – that’s not the important part.

The MASH Bugout Episode

The important part is we were able to move the whole project up into “the cloud” with minimal effort. I had an instance of Postgresql/PostGIS running on a Virtual Machine out in the uncharted parts of the internet. So in 2 hours:

  • exported the data out of the File Based Geodatabase into PostGIS
  • Connect QGIS to the postgis instance
  • Connect the important person so she can see it all
  • Set up a backup so that we all feel better.

Which with the exception of a few things (like dealing with their ESRI License), this isn’t terribly out of the ordinary of a setup. I’ve done it for other clients. Why write about it? It’s the first time I’ve done this for a conservation group. Way back in my past life of Federal and early days as a consultant it was “spend one day on IT problems and spend 1 day on what they needed to buy”. You spent half the time being an IT Technical Sales Person vs the guy that helps you with your data. I take for granted how easy stuff is in 2020. If you have $240 US you can have a server sitting out ‘THERE’ (points towards Atlanta because I think that’s where it is). A “I can edit GIS data” powerful enough server. In 2001 my department received a server that was probably in the top 10 in the state for processing power for a small amount of time – I won’t even mention the cost but put 6 numbers down before you get to the decimal space. So Conservation people – in your next line item for IT stuff -> $240 dollars a year for the base of a GIS system (computer included in that).

So in short we:

  • Panic’d
  • Shoved the data into a VM.
  • Keep working with a slightly new setup.

So the rest of the story is going to play out something like:

  • PANDEMIC
  • Making the Fulcrum import a little bit smoother
  • Documentation
  • Cartography I hope
  • AGOL Account

…..and the ultimate question is “is this better than what they were doing” and so far I think we’re 80% better. It would be nice if we weren’t in the middle of a crisis BUT since they are also in the middle of a project this hasn’t been something with a deadline. So this week I lay out permissions, map templates, and we try and figure out “Training” to make GIS less this giant black box and fade into the background and make it normal…and with the pandemic we have some room to adjust and play around if it’s not working as they want. Hey and I mentioned ESRI more than once and it didn’t end with “we deleted it” because it has a role also.

 

 

 

Fishbones for the TN NG911 Address Server

It’s been a bit since I’ve talked about the TN address server. I’ve been slowly adding more functionality to it and some of that functionality has been more difficult than others.

If you’ve played in the address space any length of time you’ve heard of fishbones – and that is simply drawing a line from the address point to it’s “spot on the street”. Granted if you’ve ever done an addressing project from scratch (I had the joy to do that a few years ago in the Caribbean) it makes a lot of sense. You can quickly see problems where lines cross over other lines or where addresses have no lines at all.  Please read up here if you are so inclined.

There are probably multiple ways to do this  but I’ve gotten fishboning (if that’s a word) running in the TN NG911 Address Server. In PostGIS with less than 25 lines of SQL (and I’m quite proud of myself – 5 years ago I sucked at SQL. I still suck at it – but way less).  While it’s not quite ready for prime time it’s close – I’m just trying to figure out what I can pull out of this that makes the County’s job easier in the addressing realm. I’d love to move it to python at some point – and it’s probably doable (well I know it is – it’s just doing it).

Three things happen:

  1. I join the address point to the closest road using ST_LineLocatePoint
  2. Does the closest point fit the address range on the street (double bonus I also check left and right).
  3. Finish it up with ST_Makeline to join the spot closest to the street to the address point.

Which quickly shows something is wrong here:

Bonus for this one as is it’s not incredibly obvious what the problem is at first glance….UNTIL you see you have an addressing issue where you have 2, 3, and 4 on the same side of the street.

Hurrah for misspelled street names by leaving a W off

Anyway – there will be more announcements coming like this “running in the cloud” and other things. I’d like to get the fishbones running once a night or realistically run the one for the new address. Why not both probably. Anyway – pretty nice for an ArcView/FGDB Replacement.