I’ve been trying to write more as I get into this “New Life” thing since January.
I’m not a developer. There was a time I did give it a go and it wasn’t my thing. I can write small programs that scratch an itch but they aren’t for mass consumption. I have found a large amount of happiness in SQL and GDAL as of late and PyQGIS….but the PyQGIS blog post is another post for another time.
So here’s an update for the TN911 project. I had mentioned in a previous post I was working on another 911 database job and from that I’ve been learning how to be pickier with error checking the 911 data. I was doing a small amount of checks but nothing serious. I grabbed a backup of one client’s data and have been picking my way through it and looking at “what can be wrong”;
- The Pre-Direction could be wrong – that’s the part of the address that’s North/South/etc – so something like “North Main Street”. What if someone fat fingered it and made it “North East Main Street”. The person doing the addressing usually knows if something is off. I give them a list of predirs used in the database.
- Does the house address match the street address – in other words “North Main Street” on the point matches “North Main Street” on the line. I give you a list of things that can be wrong with that.
There’s multiple combinations of things you can check. So one day I started making a list and I ended up at something like 30 checks on the data. I’m still about 3 or 4 away from being done. Well – I think I’d be done.
My first thought was to make a schema called garage and shove all the tabular data checks into that. Of course the user could ignore that schema exists. I could run this as a Geoprocessing model and return the results BUT that remains on the user to run the checks.
Anyway – One check I was quite proud of was this one:
You have these Polygons called Emergency Service Networks. Each one has a Unique Identifier. Each road is designated as being in an ESN and it gets so far to even be the “right or left” side of the road. In this case ESN 126 has a road that isn’t broken crossing over the red line into ESN 121.
I’ve often wondered how I could easily check this with SQL. I could do some weird Clip/Union/Intersect thing in qgis and pick it out but I just want a list. Spatial SQL to the rescue. Intersect and compare values and I have a list.
I have a road that isn’t broken at the ESN. Is it the end of the world? No. Does it make for some awesome Data Gymnastics? Yes. Not bad for a script that takes 1 second to run. So for now I’m opting for the schema approach for now. There garage schema exists and it will give you tables to fix your data.
That’s turned into my “fun” thing to do now. Dig around and answer questions with SQL. It’s my Wordle. It’s my Sudoku puzzle for not being bored. Plus it does remind me a little of the “gold old days” of writing scripts in Arc Macro Language and just having fun with it.
