postgis

The Quarantine Server Shuffle

rjhale · May 14, 2020 ·

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

rjhale · Feb 19, 2020 ·

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:

I join the address point to the closest road using ST_LineLocatePoint
Does the closest point fit the address range on the street (double bonus I also check left and right).
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

rjhale · Dec 30, 2019 ·

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:

She will be slow to get started
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
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:

The Fifth Day of XYMas…..My Workstation

rjhale · Dec 20, 2019 ·

What? Hardware? You’ve only been complaining about Python for 3 out of the 4 articles….

So yeah I decided last night to talk about my computer…or at least my history of “computing” and how I ended up with this workstation. When young Randy walked out of college I’m not entirely sure that he had a computer. It wasn’t until something like 1998 where I made my first purchase of a large unwieldy desktop (Gateway). Most of my computing needs were handled at work (Federal Gov’t) where we had this mix of Sun Sparc stations, Intergraph Hardware, and some HP workstations.

When we made the move from ESRI Software on Unix machines to Windows machines I suddenly had to care about hardware and all the parts inside of a computer because the IT Department didn’t care. So we’d sit down and go “well we think we need this so lets double it” and we had some very very expensive computers back in the late 90s to early 2000s. How expensive? I can’t put my fingers on any proof but at one point we purchased a Dell Workstation that cost about $14,000 dollars. I think that was right about 2001 or 2002. We had a Silicon Graphics Workstation that I’m 100% sure cost something like $35000 in 1999 dollars.

hp workstation — I didn’t get rid of it. Vintage Dell running Windows XP from 2006

Flash forward to “Randy owning a small business” and what did I do? My first purchase was a Dell mobile workstation ($3000 pictured above). Next was a Dell lower end workstation that was about $1500. One day for fun I bought a cheap HP Laptop in late 2012. I think it was 14inch and it had a 100 GB hard drive and a whole 4GB of RAM for $350. I loaded Linux on it and started playing around in my spare time with it and trying to see what could I do GIS wise. Over time the answer was “I can actually do quite a bit in 2013 with this laptop”. Sometime that same year I started my move into a mixed environment of ESRI/FOSS4G. Sometime late in 2013 I decided to move my main workstation (Desktop) over to linux and run windows 7 in a virtual machine. That led to more experimentation.

What did I learn from all of this? FOSS4G runs light on the hardware end. I can work with cheaper equipment – not that I like or advise doing it but I can if I have to. I decided this year to go more high end than I have in the past so I bought a System76 Laptop.

What does this get me? Well – I’m looking at at least 4 to 6 years of service. I carry it out with me to clients. I might carry it into the field. I went canoeing with it once (no it’s not water proof but I didn’t feel like leaving it in the car). 32 GB of ram is a bit much – but I wanted as much as I could afford for longevity purposes. I went slim on hard drive space because more of my life is going “in the cloud” so to speak.

I’ve got another laptop with 16gb of Ram and it’s very capable – I just like the system76 one better. It’s a lenovo business class machine and it’s fine. I’ve done one thing with it and that was stick a SSd drive in it to speed it up – other than that it’s stock and boring.

What do I do when I get my laptop?

I also run whatever is current from Ubuntu. Yes – Current. I don’t do Long Term Releases except on servers and one desktop machine. Although I’m currently using Pop-OS and enjoying it (it’s based off Ubuntu).

…and I do more things – but this is a good first whack at a workstation. My assumption is when I go to a clients I have no internet. I can’t tether to my phone is my next assumption because cell service won’t be available. It’s pretty much me and what I take with me (so I take all the software). No joke – I went to a client’s location in NW Tennessee and had no phone service for 4 days (Thanks Sprint) and at least one day of 0 internet. I was at the mercy of the Hotel and the Client for internet most days and that wasn’t a given. I was left with my brain to get work done – no “hive mind” for me that day.

laptop — Stickers are the Prison Tattoo of the 21st Century

What do I tell my clients? Get what you can afford. Maybe go just a bit better than you think but this isn’t a long conversation of gaming computers vs workstation vs nvidia vs radeon vs etc etc. It’s actually nice to look at a laptop and go “If you don’t blow up you’re good for 4-6 years”. I’m all for equipment with warranties and not running hardware to death – I just don’t care a lot about hardware anymore. My current fantasy is linux on a chromebook to see what I can do with some lower end computing power where my battery runs a good part of the day. Can I have a decent workstation with the cheapest of cheapest hardware? I think so.

“Yeah but what if I run Windows?” That’s 200% fine and there is nothing wrong with it (and some days I wonder if it would make my life easier). You may need something a bit better than a business class machine but you don’t need a gaming computer to run your FOSS4G. If you’re running ESRI Software there is enough info out there on what you need – but whatever you get your FOSS4G will work just fine on it. I say that and don’t be completely ignorant about your hardware – but you don’t need to worry as much as you think you need to worry. I’m big on not worrying these days.

So anyway – Hardware. Something I don’t worry a ton about BUT it is important BUT it’s less important than having good clean data.

TN 911 Server Updates

rjhale · Aug 30, 2019 ·

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.

postgis

Contact