First – I’m glad I’m doing this as it’s making me get back into the habit of posting a blog.
So I’m going to cheat a little and break up SQL and Spatial SQL. In my head it’s two things and technically maybe it’s only one. I am by no means a SQL Expert – but I wish to tell you a small story on why it is important and why I’ve been pouring more time into it.
About 6 years ago I had a turning point on data and work and the tools. I was moving out of a proprietary software life into the one I know now. I was sitting on a job south of here (in the Caribbean) and was wrestling through the world of databases. The job was centered in PostgreSQL and Postgis. I had spent until that moment letting my data be a series of “Flat” files or “Flat Containers that held data”. Pretty much all I did up until that point was Simple SQL clause with a where from a gui. Except I didn’t know it was SQL. All I knew was “push a button”.
So here I sit on a job and truly unable to function. So I started learning SQL on the job. Literally “What can I learn tonight that I will have to use tomorrow”.
Something like 6 years later I use it about every day. I’m not great at it but I’m getting better. What am I doing with it? Well – nothing grand but it’s helping me at least do some sort of quality check on my data and that’s like having a super power. I do one of two things when I get data now – i either load it into PostgreSQL/Postgis and pick at the data OR I use the virtual layers functionality in QGIS to look at it before I go as far as to stick it into QGIS.
For the purposes of this blog post – lets look at DB Manager in QGIS. If you add your shapefiles/scatch layers/whatever here – you can start to poke at it just a little.
You can throw SQL against your data layers. It’s not as powerful as having your data in a database (because I’m sure 100% of the SQL commands aren’t there) BUT – it’s a good first start at going “Hey this data isn’t as bad as I thought” or “Take this out back and burn it”.
How do you learn SQL? A book. A website. Load postgresql and start playing. My big learning experience came a few years later when I had reached the end of my rope with a client. I spent a week cleaning up their data in Postgresql. Granted I spent a ton of time searching the internet for an answer but it’s not time I’ve wasted. I learned a lot of good things. Saved a client relationship. Ended up having the best data I think I’ve ever had for a client at the end of that week.
Much like in the first day of XYMas – I think this is a skill I’ll keep using until I’m done. By done I mean the moment I run away from Geo with what’s left of my hair on fire.