North River Geographic Systems Inc

Spatial Problem Solving

postgis

FOSS4GNA 2019 – The Short Review

· ·

I tend to spend probably more effort than I should telling people “Go to a FOSS4G Meeting”.

I made my……(think..think… 2013, 2015, 2016, 2017, 2018, 2019) fifth North American Event. Six if you count Boston – but that was International but lets count it – so six.

I think this might be a record for me with consecutive conference attendance. I’m a bit burned out on conferences currently. Usually gis conferences are more of the same – except a FOSS4GNA event isn’t. You never know what’s going to happen. If you make enough of these you become part of the group and to be honest – I could sit in a central area and just talk to people for 3 days. Which I did a lot of….more than I will admit. We talk life, projects, and software.

FOSS4GNA tends to skip around the country – this year it was in San Diego. Which was funny because when I said “Hey I’m going to San Diego people would immediately blurt out ‘OMG You’re going to the conference‘”. Well – no. Not that one – I’m going to the fun one. Jeff Johnson hosted at the Marina Village Conference Center. It was actually a better facility than I gave it credit for at first. It was spread out so you had to be outside. What you ended up doing while walking from one talk to another was stopping and going “Hey I’m in Southern California”. You would at some point stop and sit and not worry about things. Jeff had food trucks swinging by the conference center during breaks – you just wander out and get something. Food was plentiful. Every night there was a social of some sort. You get more done at the socials and over food and drink than you do anything else.

I drifted into more LIDAR talks than normal. I’m a bit blown away at PDAL – I don’t use it for work (yet) but I do play around with it in my spare time. The work going into it and the use cases around it are exciting. I’ve been working with Geoserver more and I went to talks on the new releases that are coming.I bounced into a few QGIS talks. I sat and talked to a lot of people – some that I knew and some that I didn’t. It’s nice hearing what people are doing with FOSS4G software and why people are there. I ran into 3 people from the Southeastern US – and that was nice. I’m not alone in this area screaming into the void.

I did my talk on Henry County’s 911 Server and had some great conversations with people after it was over. It’s nice to see other municipalities drifting into the open source software realm.

I enjoy hearing Paul Ramsey’s Keynotes. He tends to look at the economic side of what we do – that’s an important thing for me. Especially because I make my living doing this and I’m always trying to give back. The synopsis of his talk: Open Source Geo is at it’s “Mid Life” with about 20 years of development. At mid life you sit around and look and there’s not much money for FOSS4G development. There’s plenty in implementation because we are all about selling solutions. If you want to check out the slides – here they are.  What if you took your normal maintenance costs from your commercial software and threw it at an Open Source Project? Maybe hired a developer?

So – I know probably not the best conference summary but it’s hard to summarize the people. That’s really my main attraction to this conference. I can walk into a room of 500 and see people I know and miss people that aren’t there. I can catch up with friends – people are the most important part of this whole thing for me.

What about next year? Next year is the international conference in Calgary….

http://2020.foss4g.org/

….and you should go. I’m going to do my best to get there. Why? People. Come join the community of Free and Open Source Software Community. If you missed out on FOSS4GNA 2019 you missed a good time. If you can’t make 2020 in Calgary keep your eyes open for the next FOSS4G-NA in 2021.

 

PostgreSQL Hack

· ·

Many of you will look and go “OMG – Randy has a new hack for a database”. No – Randy is a hack at databases.

So about 4 or 5 years ago I was dropped into the idea of databases. I was working a job several thousands miles to the south and life was easier with a database as opposed to flat files. Up until that point I had flirted with databases and taken some online courses. I would make small databases but it wasn’t something I had to deal with day to day. I did deal with it indirectly – because at the time my employer was running ArcSDE on top of Oracle – BUT – as a lowly employee I wasn’t allowed to do much with it. That was the job of the admins. I made ESRI File based Geodatabases and was quite happy. Every now and then I’d even make Microsoft Access based ones.

So flash forward to this last year and I’ve been up to my neck in databases. A lot of you will shrug. You do amazing things with PostgreSQL and SQL server and it’s not that big of a deal. Last year I did my third big migration of flat Geo files into PostgreSQL/PostGIS. It was a fairly simple thing to do: QGIS, PostGIS, and I loaded Geoserver (but we still haven’t used it – I’m confident we will).

The client moved from a “ArcView/File based Geodatabse” environment to a multiuser database environment. They are a 911 and it’s a small county. They went from 1 person being able to work to 3 people working and viewing the data. I think currently they have 4 people adding data and updating data now. That’s a win.

My god the dots

What wasn’t a win was my moving of data into the database. File based Geodatabases don’t have a primary key. Maybe there is some way to deal with that these days – I don’t know. So they were using an extension called Attribute Assistant to deal with unique columns and sequences in ArcMap. I loaded the data and didn’t pay enough attention to what was happening.

To make a short story longer – I mucked up the unique column that the data had. I didn’t know it had to be assigned then never change – as explained “it just needs to be unique”. When I loaded it the Primary key and the Unique ID were off. Primary key was 4 and ID was 5. Look there is a primary key of 10 and a ID of 247. The unique ID had nothing to do with the county data – but everything with the state 911 database. So I calc’d the ID off the Primary key and that’s when things went stupid. So what magical GIS thing did I do? I didn’t.

I made a new index column.

update hc911.addresspoints set index = right(oirid, -6)::int;

Which ripped the number off the unique column and assigned it to a new field. I built a new sequence with a new incrementing value. I messed that up. I ended up redefining a new primary key. Fixed. No GIS knowledge applied – just databases.

How do you know you are tired? This is how!

Which – not a huge deal but for me a learning experience. I need to spend one day talking to the client and making out a path way before we migrate. Migrating data took 2 hours. Institutional knowledge probably would have taken 8 hours. The next one won’t have this problem. You’re going to have to talk to me a lot about your data and process. Way more than you want and that’s fine – we just need to talk.

Data is everything. To make everyone happy I set up a few cron jobs to dump data into Geopackage and Shapefiles using OGR. They’ve been quietly chugging along for 6 months now doing their work with no applause or fanfare. More people in the office are slowly going “hey you mean I can use QGIS to connect to the database? I can see the data?”.

I can say this whole move minus the ID hiccup has been a huge win for the organization. I was just lucky enough to be the guy to do it.

So if you’re an organization struggling with your commercial system – why not take a look at this side of the fence. We’re a lot of fun. If you’re wondering “Hey what should I learn these days” – I invite you to dive into PostgreSQL/PostGIS.


20 minutes at the East TN TNGIC Meeting

· ·

I went through a year or so of not talking at conferences. For local ones I’d show up and do something. I taught a workshop at FOSS4GNA 2018. I did a workshop at FOSS4G 2017 in Boston (and it sorta sucked).  I need to get out of that mode and talk more – but I’m happier being quiet these days and working with clients.

Anyway – at FOSS4GNA 2018 I discovered something – the OSGEO Suite had disappeared from Boundless’s Website and was replaced with a github repository. It wasn’t long after Don Meltz put up an excellent explanation of the OSGEO Suite. The OSGEO Suite being gone isn’t a problem. It does increase the amount of talking I have to do spend explaining it’s not a problem. I could even compile the suite – but my clients can’t. They are smart people – but the time to compile the boundless suite won’t be happening.

One of the things I keep running into is the idea of an Open Source Server. The OSGEO Suite was well known and I get people asking “Hey – can you set us up a geo server”. They mean the opengeo suite. I then launch into an explanation of what makes up a “geo server” and they go a bit blank and answer me “Yeah – one of those”.

So for the next bit I’m gonna be doing something like this:

I want to explain how this works to people. We’ve gotten a bit deaf by hearing “ArcServer” at every conference in TN as this one monolithic thing you have to install to make a web map…which really don’t need since AGOL appeared but…you want SDE and it’s not called that and you have two people editing but you need SQl Server but you don’t….

Anyway,  QGIS/PostgreSQL/PostGIS I’ve installed multiple times and as of late I’m experimenting with geoserver and what it can/can’t do. I have no grand delusions of creating ‘Randy’s Open Geo Spatial Super Duper Server’.  I want to get to the point where maybe mapserver is an option in this. So much to learn…so little time.

I suck at github – but I’ve been more active as I try to develop a skill set and get over the angst of pushing and pulling things. I’ve built a vagrant box to install PostGIS/PostgreSQL/Geoserver so I can have something to point at plus it gives me a starting point for an upcoming class I’m in the middle of building. The vagrant box isn’t pretty – I expect to change it over the next bit as it’s mostly pieced together from a lot of other people’s work (Coleman McCormick, Dave Smith, etc). It works though and I have everything but pgadmin 4 running in one spot. Which – I don’t think this is anywhere near “install this for a server” because this is mostly just for talking, training, and testing.

So why vagrant? Why not docker? I can gloss over vagrant faster at theses talks than I can docker.  I have 16 minutes to draw diagrams and about 4 minutes to publish one piece of data: QGIS -> PostGIS -> Geoserver -> Leaflet.

Anyway – if you’re up for the possibility of a train wreck of a presentation come by the East Tennessee Meeting in Kingsport next week as I try to explain how this works and hopefully you get excited enough to install some or all of these components and do something cool with your data.

Rolling out QGIS/PostGIS Part 1: Document

· ·

I hated putting Part 1 up there. I start a lot of Part 1 posts and never get to Part 2…..I’m planning on this being a multipart post. After a while of screaming ‘hey why don’t we put QGIS everywhere’ I had a client decide to replace ArcGIS as their primary GIS. We were working along with QGIS/PostGIS/Fulcrum/ArcGIS and it finally got to a point to just switch. I’ve done a lot of QGIS installs and a lot of ArcGIS installs and enough PostGIS/PostgreSQL installs It doesn’t worry me – what does worry me? Data.

Way back in 1995 I was working for the Federal Government doing commercial mapping and the one thing I took for granted at the end was “the data dictionary”. We would work a project and at the end one of the guys would sit down and crack open a spreadsheet program and document every layer we had made and why we had made it. I guess sometime in 1996 or close to that we had FGDC metadata roll out and to be honest I don’t remember if we continued on with the Data Dictionary or dove into the FGDC Standard.

The client had a file based Geodatabase they had been working out of for a good while. It was functional. It had domains and subtypes and all the things you would hope a properly built ESRI file based database would have. The one thing it didn’t have were “reasons”. As in “why is this field here and not here and why is this here?” There were a lot of domains. Not so many subtypes though.  Apparently there had been somewhat regular revolving door of consultants. Everyone added their spin on things and here I sat….adding my spin…or should I say changing it all. So that started a whole list of questions which ends with me writing everything down in Google Docs. Yes – 22 something years later I built a data dictionary to describe all the attributes and columns.

Domains
Domains in ArcGIS

If I look back at my time for data conversion and banging my head on the wall – 60% was asking “Why?”. Then documenting the responses. In some cases we just had to accept “that’s just the way it is” and in others I was able to trim some bloat out of the database. I still think there needs to be a second round of trimming on this database.

So I’m going to spend some time talking about this move. Then talk about QGIS 3.2 because that came out – and there are things in that release that make me want to upgrade them off the LTR right now. For those of you not familiar with QGIS once a year you get a Long Term Release. It’s awesome because “things don’t change” – it’s stable. So we have it in enough places it’s going to sit until October at least.

Part 1 for all of it’s rambling ends…or starts right here: Document the data. Sit with the GIS person and ask why to the point you’ll get on his or her nerves. Your default answer to everything should be “delete”. Except you don’t. Use  ArcGIS because there are going to be a few things you aren’t going to find talking to anyone.  Once you’ve written everything down you can think of then you can start. Take the goofiest layer you have and move it into PostGIS. Check it. Double check it. Check it again. Check Field Lengths. Check Date Fields. Check to make sure someone hasn’t “shapefiled” your data and you’re left with “descriptio” vs descriptions.

Example – this database had two fields that completely baffled me: Location and Place. Location was for “where” the data resided as in an address. Place was a placeholder for notes. I had no clue. Luckily the client was patient as I asked more and more questions. Note the screen shot.

QGIS Screenshot

The other reason I’m writing this all up here – I think this is about to be a service: Migrate over to a free and open source server that is commercially supported for clients that can’t afford all the licensing that comes with an ESRI option. Of course I don’t want to call it an ‘open geo suite’ since that name has been used. I don’t plan on making downloads. My one big heartburn was I didn’t load geoserver for them – I couldn’t justify the overhead and banging my head on Windows Server more plus they had a bare bones server. Being able to edit the data was more important than pushing services out. Maybe later – but for now – They are editing and making maps and making decisions based off where we are.

 

 

Last Year’s Hurricane Season in the USVI

· ·

So I’ll talk about this finally. I usually don’t talk about “work” unless it’s in generalities about software and processes. I probably should talk about work more.

I’ve never been in a hurricane. I think so far I’ve been in about a handful of earthquakes, stood on one volcano, and I think I’ve seen one tornado. Not that I’m looking to be in a natural disaster – I just don’t have a frame of reference for what happens during a hurricane or a tornado.

If you flash back to early September 2017 the Caribbean was gearing up for a hurricane and. Turns out two hit close together. I had the chance to work in the US Virgin Islands on an addressing project with AppGeo and Spatial Focus several years ago. It’s been a “wait and see” type of deal. We did a little and watch as red tape and things slow it down. I called a few friends down there before the hurricanes hit and wished them well. Overall – there wasn’t a ton of fear but a lot of concern. I was concerned because the 911 system isn’t great as the addressing isn’t wonderful in the USVI.

My memory is getting fuzzy – but it seems like both hurricanes hit and it was a while before anyone could call/email me. Maybe it was late September or October before the phone rang. They needed help to figure out where the tarps needed to go.  FEMA/USACE was headed down to bring tarps and supplies – maybe they were already there. A sneaker net of GIS data had cranked itself up on the island as thumb drives were passed around. I started trying to figure out “what made sense with the base data I had available”. I started digging into what USACE and What FEMA would do if your roof was damaged. More or less if you had 50% of the structure of the roof left they could put a tarp on it. I came up with a plan and contacted AppGeo and they tossed a person my way.

Structures were categorized:

  • 1 – 25% Roof Damage
  • 26 to 50% Roof Damage
  • 51 to 75% Roof Damage
  • 76 to 100% Roof Damage
  • Structure Destroyed
  • No Damage
  • No Imagery

NOAA flew imagery of the area. The imagery was a bit rough around the edges but it worked. Actually – let me back up – for what it was it was awesome. Granted it didn’t lend itself to any sort of automatic classification as there was a lot of building lean and some missing imagery. It worked though. That was a great free resource by NOAA.

Hey that’s a lot of points. Yes – 48380 points covering three islands. Granted I didn’t get every house as I had nothing much to start out with except OSM and some USDA NAIP imagery. I would guess I’m within 10% of an accurate building count. Some people have offices/live in shipping containers down there. WHICH – when I was there made me go “awesome” but when looking at the imagery at a shipping container blowing a half mile across a field – not so awesome.

It did give me flash backs to my old remote sensing days with the government. Although this was less refined – it was a game in the afternoons to see how many points I could collect in an hour. Some neighborhoods were easy. Some not so much as there wasn’t much left. The other nice thing that happened was sometime around December I switched over to QGIS 3.0 and started seeing what I could/couldn’t do with this almost released software. Several bug reports were filed so more good came out of this.

The data started making it’s way out to one agency after another. My big fear then was “what if someone uses this beyond ‘roofs'”. I wasn’t doing a full blow damage assessment because I was seeing some evidence that maybe the roof survived but the bottom floors didn’t. Plus I wasn’t on location.

It took me a bit longer to finish than I wanted – Volunteer projects tend to be the last thing I work on most days.

I did start pulling numbers out of some of the work – this is for St John Island:

Count Classification Percent
1487 1 – 25% Roof Damage 41.42
326 26 to 50% Roof Damage 9.08
131 51 to 75% Roof Damage 3.65
126 76 to 100% Roof Damage 3.51
1007 No Damage 28.05
17 No Imagery 0.47
496 Structure Destroyed 13.82
3590 Total 100

So 25% of the houses had no roof damage. 60% had something happen. More or less 15% were leveled. I’ve heard ancillary stories where almost all houses had water coming in them and in a warm tropical climate you get mold. Mold would make the house unusable for me.

Anyway – I worry about the 2018 Hurricane season that is upon us. Maybe it will be quiet – maybe it won’t be. AppGeo has Mapgeo for the islands. It worked out well. I’m going to try to “build” a database here so in case something happens and I’m not starting from scratch in case I get to help out again. I hope I don’t help as nothing happens.

 

Contact