postgis

rjhale · Mar 20, 2019 · 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’;

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:

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:

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.

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.

PostgreSQL Hack

rjhale · Feb 21, 2019 ·

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.

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

rjhale · Sep 6, 2018 ·

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.

Adding Triggers to Geopackage

rjhale · Aug 30, 2018 ·

In between work I get a chance to play around with software. I’ve been digging around postgis more and more these days trying to get a better understanding of what it will do or won’t do. It’s a never ending learning experience for me. Sometimes it pays off – the never ending tutorial left me suggesting we dump a database to a geopackage.

I’m great at using geopackage in sentences like “USE GEOPACKAGE” and “Hey – what do you think about geopackage?”. Since I’m actually officially using it I decided to do some digging. It’s a SQLite container. Since it’s a SQLite container I should be able to do some things with it – like…..

I can use the spatialite gui and throw SQL against it and change the tables.

While talking to another person the question started with ‘Well – why can’t we just do the data collection in geopackage?’. Which we did some back and forth and in some cases this would be acceptable vs doing a full postgis install.

So my first question is: Can I make a trigger in a geopackage? The answer is yes. For those of you who don’t know what a trigger does – it responds to some action in a database. In postgresql, I put multiple triggers in a database for a client. Some triggers calculate lengths and some areas and some make a note of who edited a feature.

After some digging I found the syntax and wrote a trigger for SQLite/Geopackage in the form of:

CREATE TRIGGER update_esn AFTER INSERT
ON addresses
BEGIN
update addresspoints set “esn” = (select esn from polygon where st_within(new.geom, geom));
END;

I used the spatialte gui to add the trigger. So if I pass this off to a client they can edit an address and have it automatically pull in the emergency service number. Magic? Eh – close enough for me.

While it’s not going to work for a multi-user situation (or will it – I’ve not investigated) – it works. So now I’m adding widgets to QGIS to aid in data collection.

Rolling out PostGIS/QGIS Part II: Load your data

rjhale · Jul 31, 2018 ·

I did it – I’m at Part II. I actually did it. Wooooooo. For some of you – this isn’t news – but – for some it will be.

For the Record – Part 1.

So you’re sitting with a client and you’ve documented and asked “WHY” so much they really want you to leave – it’s time to start loading their file based geodatabase into an actual database. Here’s where it gets weird for about 5 minutes. In those 5 minutes where you’re getting ready to load data you really need to look at your client (hopefully sitting with you) and go “OK – you’re no longer calling yourself a technician – you’re a database person now”. They flip. You attempt to calm them – but yes – database person. Which isn’t particularly appetizing but this pile of data you’ve been hoarding in flat files for a good bit is a geographic database.

Most of the database admins I know are actually weirder than I am – which says a lot. One of my most spectacular “butt chewings” as a federal employee was the one time I did something to an oracle database and broke it. The admin had a fit. So much so when the yelling stopped several people told me “it’s not you – he’s on medication for this type of thing – you know – oracle DBAs and all – it happens so don’t take it personally”.

If you search the internet using Google for “Load Data into PostGIS” here is what happens.

google results not using qgis database manager

To spare you some reading – the first return mentions shp2pgsql. Actually most of the returns on the search will mention this. So much so you might think “I’m going to dump this file based geodatabase out to a shapefile and load it”. Don’t. For the sake of not over explaining this part – open QGIS and use your browser to navigate to your file based geodatabase and QGIS will read it. Same with shapefiles. Same with about anything. If you’re leaving the land of file based geodatabases, as my client was, here we sit with a way out. Add that data to your map display in QGIS.

qgis screen

If you open the Database Manager in QGIS you can do quite a bit with the data. If you have PostgreSQL/PostGIS running (as we do) you can connect to it and import data. You don’t have to make a jump to shapefile then load your data. You can do it here. Reproject, define primary keys, and apply spatial indexes all from a user interface. With one click your data gets pushed into postgresql/postgis. For a lot of my clients and potential clients this is unknown magic. Your data will change just a hair. Lower case your columns for the love of all that’s good in the world. Apply spatial indexes. Document if something significant changes.

So go back to my second paragraph where I went “you are now a database person”. You’re taking all these flat files or flat tile structured things and making them interact with each other via Spatial SQL. Which – not that big of a deal if you’ve got a full enterprise commercial roll out of Something Something Server Enterprise. It is a huge deal if you’ve never been able to do that. You get a large set of tools at your disposal you didn’t have before – plus the tools with QGIS you’ve got something very flexible in the making. Go ahead and order those books you’ve been eyeing. Join a listserve or three and ask questions. I still suck at SQL – but less than I did a few years ago.

There are still a pile of things to worry over like ESRI’s subtypes and domains and projections. Maybe you’re projections aren’t right. Maybe there is raster loaded in this File Based Geodatabase. What about permissions? Editing? You’ve just upgraded your Geographic Information System from a bunch of files to a database. With great power comes great responsibility – so you need to jump back into learning mode.

………and where does that lead me – to Part III and “Hey why did my attributes turn into numbers you said this was just pushing a button?”

postgis

Contact