North River Geographic Systems Inc

Spatial Problem Solving

postgis

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 those of you in ESRI land imagine Domains and Subtypes on Steroids. In earlier versions you had widgets and currently you have Attribute Forms.

If you have a data layer in QGIS (all my layers are coming out of PostgreSQL/PostGIS) just right click the layer and go to Properties. You’ll notice all your fields have a chance to be a “widget”.

QGIS Layer Properties

Addressing data is probably 90% picking existing attributes. You don’t want to get too fancy in making up new stuff. For instance – Street Type.

USPS Sample Street Types

I ran out of fingers and toes on counting the number of street types. For the TN guys they all get a ESRI File Based Geodatabase with this information spelled out as Domain tables. How do you do that here? QGIS gives you three ways to make this happen:

  • Unique Values sums up an existing data set and you can get a pick list of existing data. The good thing is you don’t have to do anything really to make it work and the bad is you’ll get Avenue and Avenew. Misspellings aren’t your friend.
  • Value Maps let you fill things out or import a CSV. So if you save this information in a spreadsheet you can import it in.
  • Value relations pulls from an existing table. So if you build a table with this information you can pull that in by first adding the information to your QGIS session and then pulling it into the widget.

In order to cut out down the options, I pre-built all the tables needed into the database. I add them into qgis, group them, and collapse the group because No one needs to see them. Yet. You can’t use a relation widget with the table being added to QGIS.

QGIS grouped items

Back to the Attribute Forms and this is the boring part. I want to select the type table and apply it to the type field using a Value Relation. The important part is the key and the value is the description of that attribute. Both things could be “type” but I chose to have a type and a description:

QGIS setting up Type

Go through and start setting up your Forms. What if you have a second person working on this?

Save your style to the database source:

QGIS saves a table in the public schema and everyone with the proper permissions and access is good to go. Plus you don’t have to touch everyone’s QGIS session…well much. Users still have to load the tables to get started but good clean directions should work. Of course this does make me still debate a plugin that does part of this for you.

What? Yeah – it’s that easy. Everyone working with drop down menus and making good clean data.

 

 

 

 

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.

 

 

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.

The Seventh day of XYMas…PostGIS

· ·

You knew this was coming….which I should have put this in the SQL post but I really felt like it needed it’s own short article.

I was introduced to PostGIS about 6 years ago. It took me about a year to wrap my head around it. When I started using it my first inclination was “Well this isn’t much different than SDE” except I had never used SDE for anything more than data storage. Suddenly I could use SQL and muck around in the data and do things.

I enjoy postgis – I still suck at it. I sit and struggle with the simpler things (In my opinion) but it’s the most satisfying tool I use daily.

If you went back to my second day of XYMas where I said ‘Learn GDAL’ and I’m going to somewhat contradict myself and go ‘Learn Postgis’ and at the very least learn Spatial SQL. Earlier I was talking to someone who was just starting out her career in conservation and her question was “I want to learn more about GIS so what do I need to do?”. She has a wide array of tools at her command from the current offerings from ESRI to QGIS. My response was “Let’s load all your data into PostGIS/PostgreSQL and you start working with it” (that’s the one tool we don’t have loaded). Which then lead to more of a discussion on why I thought this was important. I know what will happen:

  1. She will be slow to get started
  2. There will be one day where she’s alone in the office and the tech bug will hit and she will start working her way into PostGIS
  3. Suddenly you realize you’ve got a whole new world of ways to look at your data.

Anyway – yeah I know I’ve not dove into anything practical – but I actually have no idea how to do anything practical in the short space these articles take up. All I can say is learn:

 

 

 

TN 911 Server Updates

· ·

The TN NG911 server got an upgrade this month with some quality assurance checks. After working with Henry County for the last bit we’re into more “what if” scenarios.

  • What if we ran checks for misspelled addresses or improperly labeled addresses?
  • What if we started filling out the fields we weren’t filling with out with our ArcGIS/Geodatabase setup?

Working with Chad Howard (911 GIS Guru for Henry County TN) we’ve started playing with data problems. One thing I learned is that the amount of quality assurance checks at the county level are lacking – not because anyone didn’t want to do it it’s just hard working with a basic arcgis license to do anything significant. That’s where postgis comes into play. I’ve got a total of three checks on the addresses planned with two implemented as of right now.

What if you accidentally add two address points with the same geometry? A red circle appears in one of the layers in your database (and in your QGIS session) alerting you to the mistake. What if you accidentally misspell an address street name and that doesn’t match a street? A blue circle appears. If you look at the example it looks like someone can’t spell. Actually the old setup was adding spaces to names. So instead of “Main” you had “Main “. That’s an easy enough fix we can do in a bulk edit.

I expect these next set of checks to slowly become irrelevant over the next bit as the client takes full advantage of QGIS’s forms for data entry. Once again we’re hitting spaces as opposed to nulls in the data in 80% of the cases. We should only have 4 pre-directionals – well at most 8 –  but for this county they only have 4.

We can bulk edit the spaces away in the database. It’s nice to have this table sitting in the qaqc schema just to check occasionally but I do expect that to drift off in significance the more we use Fulcrum and QGIS’s Forms for data input. These checks check everything from street types, names, to directionals. Like I said – over time these start drifting off in significance but it’s nice for now.

Anyway – more quality checks are planned but we’re over he first hurdle to making this even more useful and easy.

Contact