North River Geographic Systems Inc

Spatial Problem Solving

You are here: Home / Blog

Blog

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.


New Training Classes on QGIS

· ·

I know – possibly my most failed blog topic as I talk about this a lot and never get anything done.

Well – I finally got something done. Well almost done. The first part of January found me in Denver Colorado teaching a 3 day QGIS class for SolSpec  covering the ins and outs of QGIS. I have a 1 day intro class that has made the rounds for about 5 years. It’s well tested and works and hasn’t been adjusted much except for changes to the software.

I am serious. And don’t call me Shirley.

Now that I’m back and have a chance to reviews notes on what went well and what didn’t – I’m announcing two more classes to round out the QGIS trilogy: QGIS II and QGIS III. I want to call the whole thing: QGIS, A New Hope, QGIS Strikes Back, and Revenge of QGIS. I won’t as I don’t want sued out of existence.

Anyway – the next two days cover:

  • Project settings
  • Raster and vector tools
  • Processing
  • Processing modeler
  • Analysis tools
  • Maybe some python – I’m undecided on this one for day 3.

I end this all by pointing at PostGIS and yelling a lot. Well – not really yelling but pointing wildly.

Keenesburg Colorado

Why didn’t I do this years ago? I actually work more on client projects more than I teach but there’s been a pretty big upswing in people asking for training on QGIS. So I’m currently reworking parts and portions and getting ready to push this two more days out the door. As far as Pricing I’m working on a 3 day package deal for people and pricing for organizations.

Introduction to QGIS Class in Chattanooga – 1st Quarter 2019

· ·

…or somewhere close.  The QGIS class may be just outside of Downtown or in some trendy part where we can eat lunch. At some point……yeah I know nothing like specifics.

Due to popular demand – I’m actually going to attempt a QGIS Class locally. There are some people I’ve emailed BUT in the hope of more people I’m including a form for you to put some information so we can figure out when it’s a good time to hold the class. Everyone has different schedules and maybe this doesn’t happen, or it does happen, or it happens twice. Yeah – I know. This should happen early in 2019 and I’ll give everyone enough time to plan – but I need to know interest before I start putting this together.

Anyway – this will be the 1 day QGIS Workshop and this will be a “certified” class as we will throw money back towards the QGIS community. Price will be $325 for 1 day. Food not included. Fun included. Bring some questions, a laptop, and other things and we’ll learn something. I’m hoping for between 7 to 10 people.

2018 Year in Review

· ·

I’m going to do this a bit different than in previous years – I would do these huge multi-posts covering all sorts of things from work to life to social media etc. As I’ve not been particularly motivated to write much over the last couple of months I’m going to boil this down into one longer post.

Social Media.

In general, this year Social Media was one giant dumpster fire. I started removing people off Twitter, Linkedin, and Facebook. I actually unfollowed probably half of my facebook crowd just because.  I’m still fairly active on twitter – but the posts aren’t that serious or relate to work. I put less out there on social media – which if you follow me you’re probably going “no you’re quite active”. While yes – it’s silly stuff or music. I decided my personal life and social media need some separation. I probably tossed 300 people on twitter. Maybe 100 off Linkedin. I need to toss more. Everyday I see less need to be on Facebook except I have people from far and wide on there and enjoy the stories. The increasing security blunders and data mining though makes it harder every month to stay.

After some internal arguing over the Newsletter – I restarted it and am doing one about every 2 months. It’s tough – I may stop doing it in 2019. I really feel like it’s outlived it’s purpose for me as people are finding me through the QGIS community or the open source community. It does serve as a “I’m not dead yet” reminder to people though. 

Geohipster

I didn’t get a ton of interviews done this year – but the ones I did were a blast. I think I ended up with 3 – and they were a good 3 (or 4). Howard Butler, Tina Cormier, and Maggie Cawley all made the pages.  I did a quick search and pulled those out – if I missed one Sorry! I’ve got a few more lined up – and I’ll get to you just bear with me.

Life

So 3 or so years ago I figured out I was working too much. Which – as a one person shop it’s easy to do. So I started Canoeing again. Biking. This year I probably biked and hiked more than I’ve done in a long time. I need to do more of it because I like junk food. My habits are slowly changing and I’m shedding some weight.

I did get some spectacular sunsets in my canoe though

On the personal personal front – A good friend died back in 2017 and this year we made large steps into wrapping up his personal affairs. No will and no plan will cost you about a year and a half of blood, sweat, and tears for those that are left. It’s tough. Many a day I would work here and leave to go to his place and work…then leave and come back and work more on my stuff. It’s hard – I do wished he was back – but if he was I’d probably kill him.

How much Crap do you need?

Double Bonus this year – the jeep made it’s appearance back on the roads of Chattanooga. Too much neglect had left it in disrepair – and the cure for that is money. So tires, thermostat, hoses, 2 U joints, a top, some fuses, some paint….and it’s back and sucking gas like it’s the year 1999. I should get rid of it but it makes me happy.

and in this picture I hadn’t yet to realize a u-joint was about to break. Luckily it didn’t.

Conferences

I made way too many conferences this year: TNGIC, FOSS4GNA (in STL), 2 FOSS4G SE Meetings, and the Alabama 811 Conference. With all of those I did some sort of training on QGIS or I spoke about Open Source Software. 

Slightly different view of the Arch in STL

I had the first ever flat on the way to the 811 conference which made me late rolling into Montgomery Alabama. I left with plenty of time – IF NOTHING HAPPENED – and something did. I did break a land speed record making up lost time – but I was still late. Planning is not for dullards.  I have to allow myself some wiggle room where people are relying on me to be “there”.  In general I don’t – and I have to break that bad habit.

Wooo – the teacher is late for class. 

Finally – WORK

Work was up. I moved two clients into a mixed environment this year of ArcGIS/QGIS/PostGIS/Fulcrum. In doing so I learned a lot. What did I ultimately learn though from these two exercises:

  • Both were were concerned with data and making sure that data remained accessible, usable by everyone, and was maintainable. 110% doable with all the software I’m using.
  • In both cases the movement from ArcGIS to QGIS/PostGIS was mostly painless. What was painful was process. Customers don’t want to change their process. You have to change the way you work. So almost every wall I hit fell back to process. That made me tired. When I point and go “You can’t do it this way anymore” I’d no more get out the door before the phone rang “Hey we tried it the old way and it doens’t work this ain’t good”. Which – as a reminder ArcMap has been out now 20 years(?) – so I understand the change isn’t easy and it’s something I’ve got to work on more and more.

I desperately need to spend more time with postgresql/postgis. One of my clients called with a problem: the database was slow. Things were not working and I spent an inordinate amount of time trouble shooting database size, table size, triggers, the VM it was running on, and anything else I could figure out. I don’t want to be a half-ass database admin – BUT – I need to be knowledgeable if something doesn’t work right. So – I’ll be a better than a half-ass database admin.

I talked to more people this year. There is a general “want” to know about Open Source Software – be it out of general “I want to know” or “I’m tired of doing what I’m doing”.  I’m also forgetting ArcGIS Skills and that bugs me a bit. I haven’t cracked open ArcGISPro and really have no desire to do it. On the one hand I need to as a consultant. On the other hand  I’m busy enough with plenty to learn and enjoy on this side of things.

Conclusion

Overall I felt fulfilled with work this year – and that’s a rarity. I’m not burying jars of money in the yard but things are working better, I’m busy, and I’m funding my existence. I want to do more – and in doing so I assume I’ll have to get pickier in what I pick up as far as work. No was once again my favorite word as I did turn some clients away as it was work I couldn’t do or didn’t want to do. It’s hard saying no when you’re pushing possible income away I always have to stop and weigh “Is this the right thing to do for me”. In general – I think it is if I remember to stop and ask that question. Which – A happier me is better for everyone these days.

It was a good year. That’s weird to say. I’ve had enough bad years to make any normal person insane. I always second guess myself – but this year there was less insanity and less second guessing. It’s a good thing. More work. More technical. More enjoyment.

So – for those of you who helped – thank you. If you lent an ear – thank you. If you hired me to do something – I thank you most of all. If you stopped to hassle me at a conference thanks for the conversation. I don’t do what I’m doing without a little bit of a push from all of you.

So what do I do in 2019? Fight until I can’t. There’s a ton of bad data out there that needs fixed and explored. Bills gotta get paid. I need to learn more.



FOSS4GNA 2019 Call for Papers now open

· ·

The call for papers is open on FOSS4GNA 2019. The conference will be April 16-18 2019 at the Marina Village Conference Center in San Diego California.

Am I going to submit? I think so. I did some work with a 911 locally in TN and I think it’s worth talking about just because of the hoops we had to jump through to make everyone happy on the ESRI side of the house.So more than likely I will fall under policy and planning.

So – check it out. The official website is http://2019.foss4g-na.org/. If you’ve never been to a FOSS4G meeting you should give it a go. You get a massive amount of technical with people that want to talk to you. You meet developers and discuss the ups and downs of life and software.

I’ll see you there.


Contact