Blog

Loading a DEM into PostGIS

November 5, 2021 by rjhale

I haven’t mentioned the TN911 project in a while. Work happens on that when I have time or are motivated – I took a bit of a break to pursue some other tech rabbit holes. I ended up back on it due to some work with pyqgis/addressing questions which led me back to the TN 911 Standard.

My thought on the database is to let it do most of the work. There is some work that occurs in qgis but:

date attributes are assigned at the database
street concatenation happens there
a lot of quality control happens there

In the TN NG911 standard every point needs an elevation. TN also has LIDAR data to provide that elevation. Deriving the elevation with QGIS is easy enough – how hard would it be to store it all in PostGIS?

Not that hard. You can run this if you have postgis installed:

raster2pgsql -C -I -M -F -Y -l 5 -s 2274 -t 100x100 dem.tif tn911.dem > ng911_dem.sql

I leave it up to you to dig through all the commands in the raster2pgsql command.

Once loaded into the database with psql the DEM appears much like you’d expect in QGIS. I connect to the database and add it.

The nice thing is I can now update all the addresses and set up triggers to handle this with new addresses:

update tn911.address_points set z_val = round(st_value(rast, a.geom)/3.28081) from tn911.dem,tn911.address_points as a WHERE ST_Intersects(rast, a.geom)=true;

My biggest issue in doing this was actually the projection. The vector data is in EPSG:2274. The DEM data resides in EPSG:6576. I generally try to retain data projections – but I was having to transform (project on the fly) my calculations. Which made everything slower. So I moved the DEM to EPSG:2274.

Anyway – one more thing done for the standard. Next up some pyqgis and some data quality checks.

Finally pulling data out of OSM

November 5, 2021 by rjhale

……as opposed to pushing data into OSM.

So I’ve been working on a OpenStreetMap workshop for URISA. I had originally written it 6 or 7 years ago and it’s been touched in spots but not “re-written”. I am doing a top to bottom shakedown of the workshop and fixing slides and adding new information. It’s been like opening a time capsule of sorts of what has changed and what hasn’t changed in OSM/My Life over the last 7 years.

What does this have to do with anything?

When I had last touched the workshop I’m not 100% sure that Overpass was a thing. I know there was a tool to extract OSM data into QGIS and there was a tool in ArcMap to pull down data. You could always download a planet file or use JOSM to download data.

Over the last few years I became aware of overpass but hadn’t used it. I decided to dig into it just a bit for the benefit of the workshop. Overpass lets you specify tags (or attributes) and run that query against the OSM data. You can then download it. Of course you need to be gentle – running a large area with a lot of data will timeout – you’re better off grabbing a OSM file from Geofabrik and processing that.

As of late I’ve gotten fascinated by fire hydrants in OSM – so I ran a query of just TN for Fire hydrants. For the record there are 837 fire hydrants in and around TN. I posted the Overpass query below:

[out:xml][timeout:25];
// gather results
(
  // query part for: “amenity=fire_hydrant”
  node["emergency"="fire_hydrant"](35.00208, -90.34742, 36.61557, -81.64104);
);
// print results
out body;
>;
out skel qt;

So what? Well – now that I have this query written I can actually send it to overpass by command line:

wget -O output.osm --post-file=firehydrant.osm "https://overpass-api.de/api/interpreter"

I can now grab OSM data when I want it (within reason). I could set up a script to run once a week or month and see what has changed.

How does this fit into my favorite piece of software? QGIS has a plugin called QuickOSM .

QuickOSM is leveraging overpass to grab data. I can use place names like Chattanooga to constrain my download. I can specify my overpass file from above and use that. It will even let you parse a OSM file and extract data from that.

Anyway – it was a fun hour of killing time and pulling data out of OSM vs putting data into OSM. Plus if OSM is all you have – the Quick OSM plugin makes it easy to leverage that data to create a map.

Introduction to QGIS Dec 7-8 2021

October 18, 2021 by rjhale

I should have titled this “Exploring QGIS” – but you may not have read the last blog post. Anyway – I will be rebranding the Intro class into Exploring QGIS for 2022.

So – the last class I will teach of 2021.

What will we do? Explore QGIS and run through all the core functionality. It will be a good introduction to see if this software will work for your organization (Spoiler alert – it will). We have lessons, Q&A, and generally try to be rowdy, and break stuff.

I will be offering a “health check” before we do this. I’d like to see how comfortable you are with everything and how I can make it better/easier/more fun for you as the student.

Things we will cover:

Interface
Community
Data
Symbology
Processing
Layouts.

Part of the proceeds from the class funnel into the QGIS Software.

Dates: December 7th and 8th from 12 to 4 EST. Just 4 hours a day for 2 days.
Cost: $175 US
Link: Eventbrite
Location: Zoom – so it’s everywhere and no where.
I’ll be sending out data/PDFS the closer we get to the class.
Seats: 10

Anyway – come join in for a rowdy final QGIS class that is a good overview for the new user.

QGIS Class Updates

October 18, 2021 by rjhale

I’ve been sitting here thinking about the class offerings. So I have some updates, thoughts, and general “here is what is going to happen”. Last month I taught the Intro class and the processing and tools class back to back. That was pretty nice because it gave me a pretty quick look at what worked and what doesn’t and how the two classes line up.

The Good:

I have a Part 1 (intro) and a Part 2 (intermediate) done now on QGIS.
In general they matched up well and Part 2 built off Part 1 better than I hoped.
I didn’t make it too terribly complicated overall

The Bad:

Things I thought were brilliant didn’t work….i.e. Too Complicated.
Things I thought were lame went over extremely well.
SQL is too hard to throw at people the last hour of the last day for part 2.
Class Names are deceptive: “Intro” is more than intro and “tools and processing” is more than that. So I need to re-name the classes.
The second class needs to be tightened up.
There is always room to improve.

There is one more class coming to make three total classes. Think of it as an intro, intermediate, and final…and final may mean advanced or final may mean enterprise. Final may be final as I really don’t want to do any more classes beyond the three as a core offering. I need to start working on that and get a first draft by the end of the year.

Back when I started NRGS I was an ATP…..or CTP….whatever the name I was teaching ESRI classes. So having people not wanting to take an intro class because it’s “intro” isn’t anything new. All that made me really re-think the phrase ‘Introduction” and I will probably rebrand this as “Exploring QGIS”. That way it doesn’t say “intro”.

Anyway – it was nice to finally get 2 classes finished and have it be something I was happy with overall. I just finished up updating the “intro” class and I’m doing that with the second class. Next year will see one class getting taught each month. So all 3 get taught per quarter. That way I know the last week of the month is teaching and the other three I can work.

August 2005

August 29, 2021 by rjhale

OK – lets drag up some old memories that are a bit cloudy. Lets flash back to 2005.

Back in 2005 I was working for the Feds. I think at that point I was toying with leaving and doing something else: consulting, working for another company, etc. Really I was in wanderlust mode at that point and the least interesting thing I was doing was my job.

I was watching Hurricane Katrina unfold on television. When at work I was watching all the news websites and when I got home it was news until I went to bed.

New Orleans, UNITED STATES: A plea for help appears on the roof of a home flooded in the aftermath of Hurricane Katrina in New Orleans, Louisiana, 04 September 2005. New Orleans began counting its dead 04 September as US troops turned to the gruesome task of harvesting bloated corpses from the hurricane-torn city’s flooded streets and homes. AFP PHOTO/POOL/Robert GALBRAITH (Photo credit should read ROBERT GALBRAITH/AFP/Getty Images)

One question I had when this was happening was “how are they handling this from a GIS perspective?” Web maps were sort of a thing. We had mobile data collectors. You can’t do squat without electricity. Like I said, I was Federal and if I was going out in the field I’m printing maps off and dragging them with me. I didn’t have a smart phone. Were there smart phones? I don’t remember actually. Seems like my first smart phone was 2011 and that one wasn’t that smart and I’m slow to adopt new tech.

Flash forward months, maybe even a year, and two things happened. I was sitting at a conference and a local community college from Mississippi was discussing their Katrina experience: Living in an RV, passing around USB sticks, making maps for rescue, stealing gas for the generator, and taking turns sleeping. It was pretty amazing – amazing enough I remember that talk 16 years later.

Seems like a few months after that we had an ESRI tech on site and we were chatting and he talked about how this new thing called Google Maps had caught them off guard. Google Earth was the one piece of software I remember from Katrina. The news was using it. Search and Rescue was using and it seems like there were some complaints the imagery had some offset – and really the answer was yes there are problems but this wasn’t intended for search and rescue. Imagery was updated while the disaster unfolded.

This morning I’m looking at Google Earth 16 years later.

My group hated Google Earth. Well – not so much hate it just didn’t understand it. We tried to force departments not to use it because the imagery didn’t come from us – and ultimately I think we hated it because it was so easy. We felt we weren’t needed if you could look at data/imagery easily. No one cared about accuracy or data lineage – double click and boom you were looking at something.

I spent this week updating Waverly TN in OpenStreetMap. How does OSM look for New Orleans? It’s pretty good. Waverly was flooded out last week with 17 or 18 inches of rain in a small amount of time. 20 something dead. It was messy.

Anyway – no profound vision or words of wisdom. Get out if you can. Keep an axe in the attic. Lets hope this plays out for the best and plan for the worst. Will it be different or will this be our keynote address at every GIS conference for the next year?