North River Geographic Systems Inc

Spatial Problem Solving

rjhale

Tallahassee FL FOSS4G Meetup on April 3rd 2019

· ·

Florida people – well at least panhandle Florida people – there is a FOSS4G meeting happening in Tallahassee Florida on April 3rd 2019.

For more information I would direct you toward: Seth and Rick

When

  • Wednesday, April 3 2019
  • 6:00 – 8:00 PM

Where

  • Stearns Weaver Miller Weissler Alhadeff & Sitterson, P.A.
  • 106 E. College Avenue, Suite 700, Tallahassee, FL 32301
  • *This is the Highpoint Center off of College and Monroe. Enter from College Avenue and go to the 7th floor. Meeting will be in the boardroom.

Food and Drink Options

  1. The firm of Stearns Weaver Miller et. al. have graciously offered to buy beer for pizza to anyone that wants to take advantage.
  2. I’m running a pizza and beer potluck for anyone that might have conflict of interest concerns about free food and beverage. I’ll pick up a pizza and beer on the way over and you guys can throw me some cash out of pocket to help cover costs.
  3. Bring your own.

Thanks again to everyone for their interest in participating, and a big thanks to Christopher Smith for his help securing a great space for us to meet and talk about FOSS GIS!

Leave a Comment

· ·

So I’ve been neck deep in Postgresql/Postgis which has been a bit fun but a bit maddening.

One problems I ran into was “What is this column?”. Generally if a client hands me the database I start working with it and do a few things but I don’t worry about their columns in the database. People keep weird things for no reason and I’ve learned to stop asking so many questions on why.

In this case the client had a PDF describing the standard they were working toward. They also had some extra fields they needed for other applications. So I found myself constantly cracking open the PDF or emailing them going “Why is this and what is this?”. If only there was some metadata….

…….but they have this in a PDF and it is good:

So the comment command to the rescue. I’m going to attach the definition to the field: comment on column oirid is ‘leave your comment here’;

Command line magic

Yeah I know that was a lot to add for a comment and I did it all from the magic of the psql command. If I take a look at the table from QGIS using DB Manager now:

View Comments in QGIS.

At this point I know what the column is even if this is a bit overkill. The question is now “Can I get this easy enough to get the client to document their database on the columns I don’t know?”

Actually that’s easy with DBmanager. I can go in and edit an existing table:

Edit your columns in DB Manager

From here I can edit a column and make comments. If I write up some directions the client can jump in and start documenting the more scattered things they have the in database. They can even leave a comment on the table to describe why you have the table.

Comment your columns

I’m constantly overlooking DBManager and the things you can do with it. In this case you can edit an existing table and add comments to help with documenting your database/project. You can import data into PostGIS. You can toss SQL against the database.

QGIS 3.6 is out

· ·

So hopefully you’ve noticed that QGIS has moved up to version 3.6. The new release is called Noosa.

Before I Dig into more of the Changelog new features here is the big thing: 2.18 is finally gone as the Long Term Release.

If you’ve been following along QGIS gets released quite a bit. Three times a year we get a new version and once a month we get a “service pack” (to steal from the Arc side of the world) to address bugs and small issues.

I love it when a plan comes together.

As you can see there is a plan. If you’re working in a setting that needs stability then you need to stick with the 3.4 LT Release. If you’re wanting to run on the edge then keep upgrading to 3.6 then 3.8 and onward.

Best part – if you stick with 3.4 – you can stick to worrying about your data and learning about the cool things QGIS will let you do.

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.

Contact