With Iniobong Benson and Matt Forrest

Listen

Show Notes

One of the main advantages of Spatial SQL is its ability to handle and query large amounts of geospatial data. With Spatial SQL, you can easily perform complex spatial operations and analyses, such as determining the distance between two points, finding the intersection of two polygons, or calculating the area of a polygon.

Spatial SQL also allows for the integration of geospatial data with traditional relational data, such as demographic information or financial data. This allows for more in-depth and accurate analyses, as well as the ability to create sophisticated maps and visualizations.

Here are some of the highlights from the conversation I had with Matt

So what is SQL and why is it you know why is SQL important when working with data?

SQL generally shows up as the Ask acronym SQL which stands for structured query language and it was developed I think in the 70s as primarily a way to query and retrieve data. It's evolved quite a lot since then you know into pretty much a standard around using and interacting with data. There's a brief period maybe in the 2010s when there was this thing called NoSQL which is sort of not only SQL or NoSQL but it was sort of a non-standard tabular database as nested data that looks more like JSON and it was good at handling sort of large data to a certain degree but SQL kind of came roaring back when cloud companies started to develop data warehouses which we'll talk a little bit about but it's very important because it allows you a very structured readable way to interact and query data you can also perform common you know sort of crud operations so your inserts you know updates, deletes, things like that you know with a database. So you can manage lots of different data with that and it gives you a very controlled way to actually interact with that data. So that's sort of what SQL as a whole is it's like I said it's become very popular and when you look at you know top skills that you know employers are looking for especially in analytics and now more so are in geospatial analytics SQL tends to pop up and be right up there with Python or any other toolkit that you might see. So I think it's going to become more and more a core part of you know the geospatial toolkit as things evolve over time.

So what is spatial SQL and could you maybe provide an overview of what you know it is and how it's been used in GIS and geospatial?

Yeah, so spatial SQL is no different than your standard SQL right you write it many times we'll use a lot of the functions you use in standard SQL. What differentiates it is two data types, really one is geometry and one is geography. So geometry is sort of your projected data and your geography is your data you know on the round surface of the earth. So those two data types go alongside every other data type you have you know things like strings, numbers, floats, booleans, dates, arrays, and so on and so forth. So you just have another data type that you work with. Now, of course, you need different functions to work with that data just as you would have different functions for those other data types and that has been standardized by OGC I think since 2003 so there's a standard for the spatial SQL naming conventions and formats. And you can go ahead and look it up but effectively any function or the majority of them will start with the ST_ which stands for spatial type. So that's why you see all the functions ST_intersects or ST_asText you see those are spatial SQL and that's the standardized language that they use.

How Spatial SQL is used in GIS

How it's used in GIS today I think is very interesting I see it from maintaining a central enterprise database of geospatial data people use it to maintain and manipulate geometries and create geometries programmatically people use it for analytics people use it for transactional you know data that's coming from maybe a web application or somewhere else all the way up to big data systems to you know manage large amounts of data in a data warehouse or something like that I think the most emergent spaces you know geospatial data engineering sequels one of the core toolkits of data engineers and I see more and more it is used you know from the data engineering space or managing large amounts of geospatial data

What are some of the common challenges that users might encounter when working with spatial SQL? 

Yeah there are a few I mean for me the first one is getting started setting up a database right unless you already have access to a database and you know an interface to interact with that database it can be pretty tricky you know if you've never worked with the command line you go to a website and that's kind of some of the first things you do it's gotten a lot easier I would say but that's historically one of the first kind of roadblocks more and more PostGIS is as you know sort of user interface installers that you can use so you're actually getting it on your computer is quite easy there are more cloud-based tools that allow you to do this for free to a certain point or for very inexpensive but that's one you know sort of pitfalls - getting it up and running on your computer 

The second is you know importing data, right in a traditional GIS setup you just have your files and you open them in your desktop toolkit or somewhere else right so you can just actually open it up and you know start viewing that in a database you actually have to put data into the database and that's something you have to do or you can create it right but if you have a shapefile or a GeoJSON or some of those files you actually have to do something to get it in there and you know you can use command line tools and things like OGR/OGR or you know there's a few other in native GIS tools to do that but QGS has actually also made this a lot easier to you know import data that you've loaded into QGIS via the user interface so another step closer to making that a lot easier 

The final one of course is learning SQL and I think this is a common challenge with everything geospatial is how much of a specific language or toolkit do I need to know to be really effective, do I need to learn all how much time do I need to spend learning it what's one of the things to focus on or how can I get there as fast as possible and those are the three big hurdles right so getting installed getting data in and then actually learning SQL now like I said both of those first two have some things that have been you know improved upon and made easier over the years as has learning SQL so you can actually learn SQL there's a lot of great tutorials I have a video I put out about this on YouTube about free courses you can use right now to actually study and practice but the scene can't be said as of the spatial toolkit there are resources out there but there's no kind of like I have a central resource on how to learn this that's one big area I think is missing.

Some of the resources Matt mentioned are listed below:

Matt’s 40+ Spatial SQL Resources and Guides

Spatial SQL Cookbook

SQL 101: Learn basics SQL for spatial SQL

Learn Spatial SQL YouTube playlist