Rolling out PostGIS/QGIS Part II: Load your data

Jul 31, 2018 | GIS, QGIS

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



You may also like

FOSS4G Nashville 2022

For Posterity - here is the schedule for the FOSS4G event In Nashville. It's weird - I finally did some digging and this is the 4th Event I've run. We did one other meeting in Huntsville that I was half way involved in. We were talking last night at Jackelope Brewing...

ESRI’s File Based Geodatabase and QGIS

With QGIS 3.28.1 being released you get this one new thing that I thought I'd never see. Way back in June (2022) this popped up on twitter: Yes - GDAL 3.6 would have the ability to edit ESRI File Based Geodatabases. Back in 2014 GDAL gained the ability to read an ESRI...

Advertising and the Great Tweet

Business. It's complicated. One of the more complicated things I've done has been advertising. How do you advertise a small GIS Consulting Shop? Ads on "The Internet"? Magazine? Direct Mailings? Word of mouth? The answer is "word of mouth". That and Conferences. That...