Data

QGIS 2.99 – Data Source Manager

rjhale · Sep 11, 2017 ·

Notice I didn’t say QGIS 3.0 – I’ve been playing with 2.99 and it’s not ready for prime time – so don’t start working with it then go “But Randy you wrote about it! So I downloaded it to use in production and sometimes it doesn’t work and everyday there are updates”. That’s because it’s 2.99 and Not 3.0.

Post FOSS4G I’ve had some itches to scratch. Part of that has been diving into some technical things I’ve been wanting to work with. I’ve done a slight dive into Docker. I’ve been reading up on R. I’ve been working with PDAL.

The other thing I’ve been doing is jumping back into QGIS. I’ve been distracted with a lot of other things with work like “paper work”. The good news is I won’t be going to jail for tax evasion or screwing up client’s work. The bad news is you are stuck with me a bit longer and I feel like typing.

One of the things I remember from the intro to QGIS class is “button pushing” – as in “How do I add data?”. ArcGIS Desktop has the one all-knowing button. QGIS has 12 buttons if I don’t count the Virtual Layer button. When I first started using qgis back in 1.8 it was a bit annoying. It grew on me. I will admit it’s a bit overwhelming for some people.

There is a button for vector, raster, postgis, spatialite, ArcGIS Mapserver Layers, etc. It seems about once or twice a year a discussion would start in the QGIS community of “We need one button to add data”. Emails would be exchanged and I assume some of the developers would talk amongst themselves.

Guess what?

Behold the “One Button”. That one button now opens the Data Source Manager. The data source manager combines everything (including the browser) into one menu. When I first started using it it was a tad annoying. I like it now. Plus it’s going to be a bit easier to explain “Open the Data Source Manager” vs “Click on the arrow next to WMS and Look for Add ArcGIS Mapserver Layer.” You can add data as you normally would or drag and drop it in using the browser.

QGIS 2.99 is still under heavy development – so I wouldn’t dive into it just yet. Just know that changes are coming – and they are pretty awesome.

“From the Trenches” talk at FOSS4G – Part 1

rjhale · Jun 28, 2017 ·

This will be a bit rambling. It’s been a while since I’ve put anything substantial up here because I’ve been busy. I broke it into parts because this started turning into a rant.

First off – I’m headed to FOSS4G in Boston to speak (and teach a workshop – but more on that in a bit). The talk in Boston is “FOSS4G from the Trenches” – I think. I really can’t remember what I said the title would be. I do know what I’m going to talk about.

The Crux -> Spatial is Special mostly.

I’ve said it and probably everyone else has said it -> Spatial isn’t Special. A few years ago it was the fun thing to scream. To a degree it’s true. 99% of my clients are running FOSS4G software these days. In some cases it was an economic move and in some it was a “We need a map” and they dove into PostGIS and QGIS. They didn’t spend a ton of time talking to sales and going to training. Then they call me. Why call me? Well it’s reported I know what I’m doing in this area – ALTHOUGH – I think this year has shown me I have a lot more to learn.

Three things really stuck out the first part of this year:

My skills as a “GIS Person” are relevant in one regard and irrelevant in another. Yes Spatial isn’t special as anyone can build a web app to run a buffer and return data. Until the data comes back wrong – then I’m special again.
No one is updating their software like they need to. It doesn’t take a rocket scientist to update their software BUT…..
I like data exploration and data science – BUT – building data from scratch isn’t irrelevant.

Part 1 – Skills.

Spatial is special and your data are important.

I’m dealing with three groups of people right now. I’m not even going to mention what software they are using. No – I will. ArcGIS, PostGIS, and QGIS with the data residing in Spatial Tables, File Based Geodatabases, and Shapefiles. Two are Utilities and one is a conservation group.

In every case: no metadata, no data lineage, and no thought to data completeness. I went on a rant a few months ago that Python wasn’t GIS – guess what – I’ll probably be scripting some of the data fixes….Assuming I don’t pull it all into PostGIS to fix it and then shove it all back into whatever data format people are using. In all of that I’m supposedly mentoring a couple of people and I refuse to talk about desktop software – I keep hammering on “get the attribution/data lineage right” then we will do analysis.

Another client went on a data collection binge. They bought a lot of hardware and are looking at collecting high precision data points in Fulcrum. Which led to a discussion on accuracy of phones and software and things in general with Bryan at Fulcrum. I’ve collected data with my phone. I’ve never much thought about the accuracy as I’ve always worried about the data I’m collecting. I always fall back to “well probably 40 feet (or 10ish meters)” and went back to my angry rambling about not owning an Ianything.

Anyway – I get the whole “spatial isn’t special” phrase – it’s not special because he software is everywhere these days. It’s not special because GIS is slowly fading into a tool for your chosen profession. Except here I am – my profession is geospatial and sometimes you need the grouchy guy to come in and scream at people and fix things that get broken and offer advice. Datum huh? Tables who? Projection says what?

Which brings me to my next rant – update your software. It’s not rocket science but……….

PostGIS and Me.

rjhale · Jan 27, 2017 ·

It’s about time to start the PostGIS talking….

Way back when I had a project and the company to which I was subbing tossed me into FOSS4G way over my comfort level. I had used open source operating systems off and on – most notably Linux (Ubuntu/Fedora/Debian) and OpenBSD. The project was PostGIS heavy and to be honest I was lost. You had geometry columns and other columns and these stupid foreign keys. ST Commands. Don’t get me started on those. “What do you mean I just can’t go ‘buffer this table’?”

Anyway – so started a slow-moving exploration of PostGIS. I realized this last weekend I still have a lot to learn and I have more to unlearn.

My first dive into PostGIS was a database for a forestry client. It took a week to combine and shove 100 shapefiles into PostGIS. Over the weeks I started normalizing the database. It’s been running pretty flawlessly for almost 2 years. The only thing I haven’t done is to set up some scripts to ‘age’ the tree stands. I never received all the approximate planted dates and I know how I want to do it but no incentive to make it happen now.

As of right now I’ve got 4 projects running in PostGIS. It’s a bit scary – I’m not that great at it. I am learning. I keep threatening to do a class on PostGIS to go with the QGIS one and I haven’t gotten quite there yet.

Anyway – expect more rambling up here on PostGIS. Some will be insightful. Some will be boring. Some will just be “here is what I did to fix this problem”.

Like today.

One of my clients wanted to go open source on their GIS. So they did. They downloaded all the parts and using QGIS they started importing data into PostGIS. If they messed something up they would pull the data out into a shapefile. Edit it. Delete the PostGIS table. Push it back into PostGIS. Before they called me wanting help they were inching along. Not a GIS person in the house – just a group of people wanting answers. That’s how easy this has gotten. Let that sink in for a minute. It’s a Geographic Information System and no one was “trained to do it”. It’s all open source also. Just read instructions, download, install, and start working.

Today I started normalizing the database. I moved everything locally and just started developing scripts to “fix” things. Which is another side to this I need to get better with – scripting. Why did we not normalize it before? They were busy building it out and I walked in at the half way mark. It is eye-opening how much time I spend making it right vs starting out that way. Of course I would argue that they’ve been working and answering questions with some degree of accuracy so it’s right (mostly).

I also started a database for a non-profit of their water testing points. In PostGIS. This may be the first time I’m doing this right from the start – which actually makes this 5 databases I have running in PostGIS.

Anyway – back to the problem. One of their point tables had kept the primary key but lost its sequencing. So when they added a point they had to manually go “42” for the primary key vs the database just pushing it forward one digit. It’s not the end of the world – just a terrible way to live in 2017. Ninety Five points total with the largest number being 100. Somehow they skipped 5 numbers.

So you know what – I rebuilt the sequence:

create sequence <sequence> increment 1;

alter table alter column id set default nextval(‘sequence’::regclass);

alter sequence <sequence> owned by <schema.table.id>;

select pg_get_serial_sequence(‘schema.table’, ‘id’);

select setval(pg_get_serial_sequence(‘schema.table’, ‘id’), max(id)) from <schema.table>;

So the next added point gets a primary key value of 101.

…and I know that’s a lot of stuff for one problem. Also – it has nothing to do with GIS – just databases. So I turned into a DB Admin for a moment. Sorry to the DB Admin I cussed so heavily back in 2007. I knew not what I was doing.

To end it – some of you reading this are going “OMG – We know about PostGIS you are so far behind”. Some of us grew up with flat files and other insanity and it’s only been the last bit for me that I’ve seen the light (so to speak) on data. Treat your data like a database.

With all of this PostGIS knowledge so now what am I doing? Building triggers (what’s a trigger?) and doing a lot of searching and reading. I’m Doing some analysis of the data. Fixing it. I’m importing Fulcrum data at times.

What am I getting out of all of this? Clients who get Answers. A decent knowledge base on databases. Some personal satisfaction which (truth be told) can get a bit thin when you’re consulting. At least with me.

Updating Roads in Chattanooga TN in OSM

rjhale · Jan 8, 2017 ·

I’ve been attempting to get better with PostGIS. I’ve also been trying to take baby steps back into the world of OpenStreetMap. I’ve also been trying to take baby steps back into my City’s Open Data Initiative.

Why? I’ve been away for long enough – plus – never quit learning.

I’ve only so many hours in a day. To be honest with my attempts at getting back outdoors and doing things that interest me, I’ve more or less stopped a lot of side projects. With the weather currently being disagreeable to most things outdoors I’ve decided to start diving back into a few things I’ve left hanging.

The OpenStreetMap group here in town is a bit hit or miss. We have a high school that maps. We have a few people diving in here and there. There’s no coordination to the madness and I tried, as did a few of us, to coordinate some of it. I finally gave up. So I do a *few* things in OpenStreetMap here locally but not much.

I made a decision to:

Map as many Churches as I can (more on that later).
Fix the Roads

Fixing roads is a bit of a chore. I hate touching interstates because I don’t understand the relations involved. There’s probably some “OSM Roads” email list but I just don’t want to get that committed. I’m happier working by myself for a bit or in smaller groups. I’ve also never took a look at the road names in OSM – I’ve worried with alignment but never “names”. About the time I was moving back into Chattanooga mom called, “They changed two road names in the neighborhood!”. I actually think road names had been changing for a while in the county as the County GIS department did their best to clean up confusing road names for 911 purposes. I *think* this data is then fed to the city or maybe the city is changing road names at the same time. Whatever the case – names are changing and it’s already fixed in Google Maps.

Chattanooga has an open data portal. I’ve been randomly mucking around looking at data and found the Chattanooga city addresses. I also found that they’ve already been donated to the openaddresses project. I downloaded them and loaded them into PostGIS. There are 102761 points. Each has a street address. I grabbed the roads from OSM and the city boundary from the open data portal. Through series of maneuvers in SQL that would horrify the more well educated – I actually compared the two and built some foreign keys and other things to make joins easier. Through that my favorite color scheme in the world – Red Green. Green is right as far as I can tell and Red isn’t.

What I did find:

my join was a bit sloppier than I wanted but overall I can pick out the problems between the Chattanooga’s address data and the OSM data.
I spent more time than I should have reading the FGDC Address standard – BUT – it helped (plus SQL examples).
There aren’t a lot of errors but enough. In some cases there was a completely new name applied by the city to a street. In many cases the road in misspelled in OSM (which is probably a holdover from the days of TIGER Data). Some are from the high school. They are great at editing but not so much at cleaning up. I need to work on that.
The cities address data extends into the county by about 1000 feet. Which means when I clipped my roads I lost some data I need and will need to fill back in. Thanks to the power of scripts and me being anal retentive – not that big of a deal and I’ll deal with it as it becomes an issue.

Of course the big question is “Is the cities address data correct?”. As a GIS person I say Yes. As a guy that has worked in GIS for 25 years now I’ll say “probably because I’ve talked to no one over addressing”. So I’ve making a list and print it out and over the coarse of the next few months I’m going to inspect many of the things I’m seeing as wrong. There are three on the way to my parent’s house. I’ll probably even hassle the city/county a bit for information. I’d like to get them more involved if at all possible.

Why fix it? Why not. OSM is enough of a force that it should get fixed – BUT – as GIS people our attention is diverted by what the clients use. You never hear someone say “Well I was using OSM to find my way to the mall…..” it’s usually “Google It” or “what does Google/Waze/Something else say”. It’s not that hard to fix problems once you have a list.

Anyway – more on this as the months progress. This will most likely occur “as time permits”.

Repeatable Fields in Fulcrum

rjhale · Jan 8, 2016 ·

I hardly ever talk about fulcrum except on twitter. I’ve had a license for a while and I use it somewhat randomly depending on the client. This last year I’ve been ‘perfecting’ (for lack of a better term) my toolset as a consultant. Which for anyone that reads this blog or stumbles upon it I spend a ton of time on QGIS and other things. This year you’ll see more stuff on Fulcrum and PostGIS and a few other assorted tools. Even ArcGIS.

I started using Fulcrum a few years ago. There was a job in the USVI where it became crucial because it ran on everything and it was simple enough to deploy a field crew with an hours worth of training. At that point I started growing disenchanted with high end GPS equipment and liked the idea of data collection on my phone. Accuracy? There are ways to satisfy that itch if you love your big yellow GPS unit.

Last night started me thinking on how I’m going to move the forestry guys to tablet based data collection. I’m not a forester. At best I’m a hack geologist that found more love with maps than with rocks. This spring I had a chance to go out and work with them on a job. I learned two things – A. They love pencil and paper (no batteries) and B. I’m horribly out of shape.

The short story on their timber inventories has been:

Get a property owner with timber to inspect
Get me to generate a ‘grid’ that they load into GPS units
They walk and inventory the timber at those points with pen and paper
Data is entered manually and a report is generated

It makes me tired. Sorta. I’m a fan of what works but there needs to be an easier way.

So my plan is to move this into Fulcrum. The one problem I’ve not sat down to look at until this week was repeatable fields. There is excellent documentation on the fulcrum site and I sat down and started reading and watching videos. Once I get this nailed down I’ll look at tossing the app up for other people to use. Of course I’m assuming their workflow isn’t so different no one can use it (again – I’M NOT A FORESTER)

Right now I know they need three things in the report: the GPS Identification, Tree type, Tree Diameter. There is more than that – but for now it will be a start to prove the concept. If I prove the concept maybe we move into digital data collection finally.

It’s easy to build apps with fulcrum – it’s all Drag and Drop. It does take a little bit of planning though

For a start I’m adding three fields: name, type, and size. The name will be the ID of the plot point (which I generate). Type and Size will be repeated for each point. There might be 1 tree. There might be 15 trees. Because this field will be repeated…..I’m going to put them into the “repeatable” widget/field. In this case if you look at the image above I’ve called the repeatable widget ‘Tree Data’. You drag and drop a field under the repeatable section. Once it is there – you can keep using it over and over to fill out your data.

Because the GPS points are generated in QGIS they don’t need to be collected – but they do need to be displayed in the application. They are adding more information to data that already exists (think of doing a fire hydrant survey where you’ve already surveyed fire hydrant locations but you need to know color or fittings). So I upload the gps data into fulcrum:

The saving grace in the US Virgin Islands was Fulcrum would run on anything. When I sync this to my phone my brand new app looks like:

So I’ve generated all these in QGIS. Now if you touch one of the points you get a name (or say you walk to a point):

If I touch it again I can edit the attribute data of the point. The point has a name – it just needs a type and a size entered the repeatable field (called Tree Data).

When I tap Tree Data (the Repeatable field) you see your two values to be entered:

I just start adding data. Once I’m done with this type of tree and size of tree – I click the check mark. I want to add another type and size – click the plus button and keep adding.

I’m collecting multiple attributes for one data point. Well – if you dump this out as a shapefile you have 1 extra DBF that can be joined to the point. For me dumping this data into spatialite is preferred – and I get everything in one nice neat package. Of course you can do the same with several of the exports – one being ESRI’s File based Geodatabase.

In QGIS I now have points and a table and I can join them to the point or look at the data…or consolidate it with my other spatialite database.

I’m off to a good start. The next visit to the Forestry Client and I’m taking a tablet and showing them the joy of skipping paper. Hopefully after that they become excited enough to finish out the application. You can do this with more than trees: Fire Hydrants, Building surveys, etc.

Improvements – Once the first demo is done I’m switching to classification fields since they know what trees they are going to run into in the area. I also want to constrain other fields to keep them from having trees with a 50000 inch diameter. It’s a work in progress though – finally. I actually added a status field (which si viewable in some screenshots so they know what has been done and what hasn’t.

Data

Contact