Spatial SQL in Databricks: ST_Point, ST_Contains, ST_Distance

0:04Hey, today I have the pleasure to host for the first time Kent Martin. Hi Kent. Hey Yousef, how are you doing? All good. Very happy to have you with me. But first, introduce yourself. Yeah, my name is Kent Martin. I'm a product manager here at Databricks. I've been at Data Bicks now for almost four years. And in that time I've uh owned the things that

0:27we've built related to geospatial um but also have supported teams that have worked on photon uh predictive optimization and uh more recently in the last year taking ownership over the SQL language surface area. So I work with the teams that are building out SQL scripting store procedures collations uh all those

0:49kinds of features. And since you mentioned those features, we already recorded many like many sessions on store procedures, recursive cities. So if you have time, go check them. And today we're going to be talking about geospatials. What's gatial? What are the new features that Databricks released? >> Yeah, of course. Um, yeah, geospatial big umbrella term. It can mean all the

1:12things related to the location of objects in the world, the representation of the physical world. Um, but basically everything on the planet has a location, right? If you're uh let's see, a person, if it's a vehicle, if it's a device, if it's, you know, anything like I can look outside my windows here and see trees

1:34and streets and light posts. Um, all these objects have a location and they all are connected to each other in some way. And depending on your business, which may be, you know, across any industry, um, location matters, right? I mean, we're not just talking about real estate. So, we have customers who care about, you know, autonomous vehicles. Um, and they track uh vehicle movement.

1:58Uh they look at ways to increase uh and improve, you know, safety to improve the driving experience based on uh you know, cellular uh signals and and what's connected in the car. Um all of this is driven by uh an understanding of location. So for anyone who's doing you know uh anything related to mobility data uh and the movement of goods or

2:21people they're going to work with uh location data and need to need to work with those uh with that data in a way that's uh efficient uh because geospatial tends to be um you know a little bit bigger data when you start talking about how things are modeled in the world. So for boundaries and things that belong to networks like roads or

2:43pipelines or cable networks, you know, all these are modeled as uh linear objects or polygon objects like the actual primitives. Um and that can sort of add up to larger data sizes and we can dig into sort of what that looks like um in the demo as well. Um but basically geospatial is uh you know just thinking about how to understand how

3:06location impacts your business. Now you you asked about what's new in data bicks. Uh maybe I can take a little step back and just sort of lay out how customers have been successful on data bicks over time with uh working with geospatial data. And the truth is data bicks is you know a spark platform and the spark ecosystem and uh you know

3:29common libraries used by data scientists have all worked on and and in Databricks um for the longest time. So there's been like a an a choose your own adventure kind of approach to working with geospatial data. So you might use you know Apache Sedona or GeoPandas or these other libraries. You know these are um certainly the right tool for the job at the time. What we've released is

3:52support for two new data types geometry and geography. So these are purpose-built for storing vector spatial data. We've also released over 80 expressions. They're all SQL expressions, but they're also available in Python and Scala. So there's, you know, appropriate dataf frame APIs to work with the to work with these expressions. Um, then on top of the

4:14types and the expressions, you know, the one of the common things that people do is do spatial joins, which is basically a way of understanding spatial relationships or spatially aggregating data. You might think of it as counting, you know, the number of events within a boundary, right? Points inside of polygons. Um, and so with our

4:36implementation, you know, we've really, uh, built a highly performant spatial join, uh, which is which is great for anyone who's doing that kind of aggregation work. So, we have types, expressions, really fast spatial joins. Um, and I'll also say like we haven't built everything u, but there's certainly a lot more to come. >> And that's out of court. I remember um

4:59in the past we used to have a library called or maybe a framework called mosaic. So we do not have to confuse it with mosaic which is or mosaic that's another uh let's say offering from the acquisition of mosaic. So is it something that what's the difference between this framework and what we what Databricks released recently? >> Yeah that's a great question. So when we

5:22talk about any of these you know libraries or frameworks that have existed where customers have used to do this kind of work what I'm saying now is there is a you know in the ven diagram of what's supported between those libraries and what we've built you know there's a lot of overlap and if you're doing anything with native SQL native types u you you know you should just use what's built in to Databricks you know there's a number of benefits that are

5:45going to result from using these types and expressions related to performance and efficiency mosaic was a Databricks labs project driven by uh some of the geospatial subject matter experts in the field and basically they built out a framework that you know basically could get the most out of Databricks uh so it was tightly integrated with new features

6:09that were in the platform with mosaic we offered vector raster and grid index grid indexing functionality uh which was uh which is fairly commonly used across all of our customers. But what we what the vision was for Mosaic was we really wanted it to be a bridge to what we built into the product. So we could accelerate in a Databricks labs

6:31environment because there was such a need for customers to solve geospatial problems that the field was kind of innovating and and trying to move quickly and built out this library for customers to use. And then working closely with the folks who were driving Mosaic and myself, you know, we collaborated on, you know, what was most important, kind of learned from how

6:53Mosaic was being used and this led to, you know, what we ended up building natively in the product. So Mosaic is sort of reaching an an end of life, right? As we, you know, I think the last version Mosaic is supported on is DBR13. So this is quite some time ago. um and Databricks um spatial SQL you know this

7:15is the sort of product uh offering we now have which are the expressions the types the joins it's all one big thing called spatial SQL um this was first available in private preview late in the in the 14 series uh but now we've all kind of accelerated through the preview phase or in public preview now as of DBR71 um and we're looking to you know help

7:37customers transition to this to this solution on the platform And maybe let's move to the demo. >> Yeah. You want to see a little bit of it in action? Let's do it. >> Yeah. >> All right. All right, Yousef, I've got this uh demo pulled up here. Let me just uh reset a couple things. Oh, these are the previous results. I've I previously run this code just to make it sort of simple to walk through. So, you know,

7:59all this stuff ran. I I I was running it uh I think in the last couple days. This demo looks at uh and works with a couple of data sets. One is the UNESCO world heritage sites. Uh so these are like you know important places in the world that people care about that we should cherish and and uh you know protect. Um and then a set of boundaries within the country

8:21of Italy. You know we uh picked Italy. Uh one of our good friends you know went to Italy recently and and we can kind of use this as like a fun way to look at spatial data and some of the features that we're using uh that we've released. So in this example, we've pulled open data, which is also good because anyone can go pull this data and experiment themselves. We could also share these queries or build it out in a notebook

8:44that we can share as well. And the key thing here is we're going to use a new expression called ST point. ST point is going to take a latitude and longitude and turn it into a point geometry. And from there, you're basically working with a native geometry type. So in this data, uh you can see what some of the UNESCO data looks like. Uh it's pretty uh verbose with the

9:06description. I'm just going to minimize that. And you can see, you know, we've parsed this data uh basically bringing it out into a table. And then if I come down here, the key line is using ST point now a native function. I'm taking the longitude and latitude that came with those sites. I'm supplying a spatial reference identifier. All your

9:28geographic information needs to be within a spatial reference and you should supply a spatial reference identifier. 4326 is one of the most commonly used, not always the most appropriate, but again we can kind of dig into spatial reference systems as we build out more documentation around the feature. Um, so in this case we're using 4326. And then I can go ahead and let's

9:51look at the table results and see what we have. So here we have a new site geometry column, right? This includes and displays that spatial reference identifier and also shows you the latitude and longitude in a well-known text format. So this whole uh format of the data here is very human readable. This is actually extended well-known text by including the SRD and the

10:14actual geometry description. Uh but the data is stored in a in a binary format. So this isn't the way in which the data is actually processed. It's processed in a much more efficient manner. So okay, we have our world heritage sites sort of geometries. Great. U now let's look at those Italian regions that I was talking about. So with the world heritage sites, we have a a collection of points. And

10:37now with the Italian regions, you know, we're working with boundary data. So boundary data is going to include a lot larger number of coordinate pairs that represent each vertex within a polygon, each vertex within the boundary. And so in this case again we've just loaded the data uh but we're using a different function uh the open data in this case uh was provided in gojson and we have a

11:02number of expressions that allow you to easily import spatial data from these common formats. So the common formats being well-known text or well-known binary or giojson or of course latitudes and longitudes all of those can be easily imported and brought in uh to a table. So again, similarly, we have the region geometry which includes, you know, this full description of the boundary. It's in the same format here

11:24displayed. Okay, we've now got some points. We've got these polygons. We might start to ask some simple questions. And I'll just go ahead and jump into one of those. So if we wanted to ask which regions have sites and do a very basic sort of accounting for the world heritage sites within each Italian region, we can run this simple query which does a spatial join. So here I'm

11:46taking the site and the region running this st contains operation. So for spatial predicates we support st contains st intersects which are probably the most common but of course we also support uh touches and equals and these other uh spatial predicates um that you'll find commonly with any kind of database system that supports spatial SQL. So yeah, we can go ahead and run

12:09the uh run this query here. We're looking at the fourth result. And so we can see, you know, the sites and actually uh come back here. Yeah, I can just see like the region that these sites are actually located in. So here I can see the region names. My Italian geography, not so not so great, but um here they

12:31are here. And we've matched up our regions within within these uh sorry, we've matched up our UNESCO heritage sites within these regions. Uh I will say like this is a this is a pretty simplistic example. Um but most customers are going to work with sort of largecale mobility data. Um we have customers who work in agriculture, who work in energy, who work in um you know

12:54distribution uh whether that's like largecale global logistics or that's something like uh you know food delivery services like Door Dash or something like that where you know they might be aggregating data in sort of in different ways but working with larger scale data and they're also going to care a lot about proximity and distances and things like this. Um so let's look at uh a

13:17couple ways in which we can look at measurements of spatial data. Uh again fairly simplistic example. If you wanted to understand which region in Italy had the sort of highest density of world heritage sites um you know largest number of sites given the area of the region. You know how could you do that? I'll just highlight a few of the key expressions. So there's ST area. We of

13:40course have other measuring uh expressions like ST perimeter, ST length, ST distance and a few other varieties of calculating distances. So here I can run ST area to calculate that. Uh I can also look at and calculate the density of the area and then I'm going to look at uh actually calculating that

14:02area based on two different approaches. So one, when I use the region geometry, this is the actual geometry field that we looked at earlier that uses the spatial reference identifier 4326. That spatial reference system stores data in degrees. And so when we pass in this geometry um into this ST area function, it's going to return the area in degrees,

14:25which most human beings do not use and is not like the standard way, but that is it's doing what the data provides. U but we do something a little smarter when working with geography data. So we've talked about geometry and geography which is probably worth its own explanation. Geometry computing data and calculating data on the plane and geography looking at data on the sphere of the earth. So accounting for the

14:48curvature of the earth. In this case, if we actually cast to use geography, um we automatically know that we should probably return this data in meters and so we do that in this case. Um or we can compute it also in kilometers just by applying a factor. So here we have area provided in degrees, meters and kilometers. Again, easy to compute these

15:11kinds of um you know area calculations just by uh using the built-in functions. Awesome. So, we've done area calculations. Let's take a quick look at the data in Italy and address one other issue that comes up uh with most customers data, which is the projection, right? The actual spatial reference system. In this case, we're looking at

15:34uh the Italian regional data. And instead of using a global spatial reference system like 4326, we can use a local national reference system which is using the 6875. Not I don't know this like off the top of my head, you know, we got to look it up too, right? Like most people don't have a bunch of SRIDs in their pocket. Um but here in this case, we're going to

15:57use an Italian spatial reference system. >> Uh and when you do that, you can go ahead. In this case, I'm looking at seeing um which national or which UNESCO heritage sites are within a kilometer of each other. Uh so I can I can use a few of the built-in expressions to answer this kind of question. ST buffer can

16:20create a buffer around a point. ST transform is actually used to transform the global spatial reference system to the local projection and this will affect the way the computation works for this data now. So if I go ahead uh I'm just kind of come down here to the end part. We've uh buffered the sites. Now I actually want to run the intersects

16:42again in this case to see which sites overlap. Um I also want to let's see if I want to actually compute the distance um that's based on this projection I can go ahead and use ST distance. I think this is the last function we wanted to cover in the demo. Um and using this distance on the projected data versus the distance on

17:05the uh on the global uh spatial reference system. You can see the difference between the two and we have a number of uh expressions for calculating distance as well. So there's SD distance sphere uh and SD distance spheroid. Again these are sort of like how accurate do you want your distance calculations to be. Some will use an actual sphere. Some

17:28will be based on the actual ellipsoid of the earth. So getting a higher precision which might have a trade-off in the actual performance of the calculation. uh but in the end I want to just show like when you're using a a specific projection um which is going to use when you're calculating the distance it's going to use the sort of the plane the cartisian um uh uh distance measurement in this

17:52case the there's not really much difference between using the spheroid and sphere and you know it's not it's kind of a moot point um I kind of got away from the sort of main uh question I was asking here which is uh which world heritage sites are are close by but we have the botanical garden here and there's a 14th centur 14th century fresco that are very

18:15close by okay Yousef I I may have lulled you into uh sort of the depths of spatial here but that's okay what I think's important about spatial SQL is there are now ways where you can easily look at spatial relationships spatial proximity manage the correct uh reference systems for your data and easily convert between geometries and geographies. And so it's just important

18:38for folks to know the trade-offs between calculating things on the plane and calculating things on the sphere. Um and and of course we're going to have more documentation that details this. So maybe this is a good time to kind of give a quick little overview of the docs u where we can point people to it. Does that does that make sense? >> Yeah, the documentation um the link will be in the description. You're gonna have bunch of more than I think 70 functions

19:02that you can use in SQL and also uh in Python and Scala as K mentioned at the beginning of the of this session. Yeah. So I I brought up the doc page a quick cover uh quick coverage of this you can just use the table of contents here on the right and you can see import I talked about creating geometries from well-known text or gojson again looking at measurements spatial reference

19:25related functionality. look through the whole list. There's lots available in here. The docs all include a specific example. So, if I go to ST transform, you know, I get specific examples on how to work with it. Uh, which is great. The other two things to call out in the docs, we also have details on the geometry data type, the geography data type, and in the next uh or at least at

19:47some point here in the near future, there'll also be a blog post. Uh, so stay tuned for that. I think that's all I wanted to share on uh spatial SQL and giving a quick overview, but I know that there'll be more content, more examples um and certainly um a lot more excitement coming around this feature in the near future. >> Awesome. Thank you so much, Kent. And thanks you.