North River Geographic Systems Inc

Spatial Problem Solving

You are here: Home / Blog

Blog

Rolling out PostGIS/QGIS Part II: Load your data

· · Leave a Comment

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

 

 

2018 TN Eastern Regional GIS Meeting – September 13th 2018

· ·

This free  forum is intended for GIS users and anyone interested in the GIS Profession.

September 13th 2018
8:00AM – 4:00PM
Eastman Lodge
Kingsport, TN

Event Details and Registration will be available at:
http://www.tngic.org/2018-regionals.htm#2018east

Keynote Speaker
Stacey Whaley – Sevier County GIS Director

Pre-Forum Classes September 12th , 2018
• Lidar Workshop (September 12, 2018, 1:00 PM – 4:30 PM) – Paul Dudley, State of Tennessee
• GPS Data Collection w/ Mobile Devices Workshop (September 12, 2018 1:00 PM – 4:00 PM) – Cliff Hoeffner, Duncan Parnell
• ESRI ArcGIS Pro Workshop (September 12, 2018, 9:00 AM – 12:00 Noon) – Ian Feathers, ESRI

Pre-Forum GeoCaching Event and Picnic Social September 12th , 2018

This Forum is Sponsored by TNGIC  and hosted by NetGIS.

QGIS 3.2: Drive Down Lists aka Value Relation Widget

· ·

This is cool.

So – North Road ran a crowd funding campaign to get this into the core component of QGIS. There was a plugin – and to be honest I discovered that about the same time as the crowdfunding was announced. The campaign put the plugin into the core parts of QGIS.

What happens is you have pick lists that can change based on other values. Have a pipe? It can be made out of W,  X, Y, and Z. Is it a Water Pipe? It can only be made of X and Z. Wastewater Pipes can only be made of W and Y.

I worked my way through setting this up – and it’s really not bad once you know how. Granted – had I listened to the above video more closely it would have made better sense starting out.

Here is my walk through. I have a point layer and that point layer has a type and a subtype. I stored this all in a Geopackage by the way. The type drives the subtype. So you pick A and you can only have red or blue. Maybe B is green and white. I made two tables to help with this process tableA is 2 fields: a primary key and a value. TableB has three fields: a primary key, a value, and a foreign key that relates back to Table A.  From that you can see Type A can only have a value of Red and Blue. Type D can only be Black and Gray.

tables

Here’s where it gets a little bit fun. My point layer has two attributes of concern: type and subtype. Type will be A,B,C, or D. Subtype will be a range of colors that Match the Type picked. If you right click the layer and select attribute forms you can assign a form for data entry. In this case for type the key column is the primary key and the value is the type. Note the Widget type is Value Relation!

type widget

Lets looks at the subtype field. The key column is the Foreign Key (the part that relates it back to table A). The value column is the subtype. The magic is this – if you look at the filter expression:“fk” = current_value(‘type’) . Check it out Below:

subtype widget

The foreign key equals the value of the type widget. When you add a point you’ll see this (nothing crazy). The type drives the subtype.

menu

BUT WHEN YOU USE IT:

So you have a drive down list. Utilities usage? There’s more functionality with this you can exploit through the field calculator and the widget lists. It’s another greater way to control your data editing. So don’t be lazy – create great data to drive your database.

 

 

 

 

 

 

 

Intro to QGIS Training – September 25th 2018 – Montgomery Alabama

· ·

The Intro to QGIS class is going to Alabama! Actually – Montgomery Alabama September 25th 2018.

It was picked to start off the Alabama Damage Summit. Also – with much luck this will be certified! Actually – there is no luck about it. It’s a certified class. Which means approximately 25 dollars per student goes back to the QGIS Organization.

Anyway – if you’re coming to the conference sign up! If you’re not ding the conference organizers and maybe you can attend. The more the merrier at this event.

What will we cover?

    1. QGIS Community
    2. Navigating the Interface
    3. Data and Symbology
    4. Geoprocessing and Plugins
    5. Editing
    6. Layout Manager
    7. Databases

The Database section might change a bit depending on time constraints. It should be a blast – so come down/up/over/back and learn some new software.

QGIS 3.2: Metadata

· ·

On the heels of the “document your data” post  I’m about to turn my writing towards QGIS 3.2.

One of the things you should worry about with your data is documenting. Why did you do what you did with attributes and fields? Amazingly enough – most of my clients don’t know or just go “OK” and add whatever they think they need. They are busy. Busy people sometimes don’t slow down enough. In the case of your data – slow is better.

Which brings us to – Metadata. I don’t know the current state of metadata in the world. I searched the internet. My eyes went crossed as I read things and I restarted this post.

If you look at 2.18 (Current Long Term Release), metadata in QGIS looks like this:

 

qgis 2.18 metadata

Not bad considering the this is a newer development in the life of this Open Source Desktop.

If you take a look at QGIS 3.2:

qgis 3.2 metadata

You can write metadata. You can save it. You can reload it for later editing. This was a funded feature for QGIS by Arpa Piemonte ( The Italian EPA – which I would assume is comparable to the US EPA) and implemented by North Road. How excellent is that? Actually – very excellent. Imagine if my fellow users here in the US pitched in for development? Good things will happen.

So my next go around I’ll most likely be using this feature for documentation. For fun I might document parts of the current project and see how it blends with the FGDC Metadata standards.

Any documentation is better than none – so write!

Contact