Geospatial Insights With Databricks SQL: Techniques and Applications

Summary

• Spatial SQL capabilities: This video explores how Databricks SQL supports spatial analysis enabling analysts and engineers to extract more value from location-based data using familiar SQL patterns compatible with PostGIS.
• New data types: Coming in public preview are Geometry and Geography data types that enable native support for spatial data. Combined with H3 global grid indexing and spatial join optimizations, this makes complex geospatial analysis accessible on the Databricks Data and AI platform.
• Practical applications: This video covers common geospatial challenges, customer success stories, demonstrates spatial join performance techniques including tessellation, and previews the roadmap for expanding spatial capabilities.

Geospatial Insights With Databricks SQL: Techniques and Applications

Talk by Kent Marten, Staff Product Manager, Databricks ; Michael Johns, Lead Geospatial Product Specialist, Databricks
Spatial data is increasingly important, but working with it can be complex. In this video, we’ll explore how Databricks SQL supports spatial analysis and helps analysts and engineers get more value from location-based data. We’ll cover what’s coming in the Public Preview of Spatial SQL, when and how to use the new Geometry and Geography data types, and practical use cases for H3. You’ll also learn about common challenges with spatial data and how we're addressing them, along with a look at the near-term roadmap.
Here's more to explore:
The best data warehouse is a lakehouse: https://www.databricks.com/product/databricks-sql
How to migrate legacy data warehouses: https://www.databricks.com/resources/ebook/migrate-your-legacy-data-warehouse-databricks
See all the product announcements from Data + AI Summit: https://www.databricks.com/events/dataaisummit-2025-announcements

Chapters

• 0:00 Introduction and overview
• 1:15 The geospatial lakehouse
• 3:22 Ecosystem and data market
• 5:13 New geospatial data types
• 7:15 Spatial join performance
• 9:00 Tessellating large polygons
• 11:38 Spatial join demonstration
• 14:47 Customer success stories
• 16:46 Spatial SQL roadmap
• 20:27 Conclusion and Q&A

FAQs

What is the geospatial lakehouse concept?

The geospatial lakehouse combines the flexibility and cost-effectiveness of a data lake with spatial analysis capabilities. It enables organizations to analyze both vector data and GPS/mobility data alongside other business data on the Databricks Data and AI platform.

How do the new Geometry and Geography data types help?

These native data types enable SQL queries to work directly with geometric shapes and geographic coordinates. They follow PostGIS conventions, making them familiar to geospatial developers and enabling efficient spatial operations.

What is the best platform for spatial SQL?

The Databricks Data and AI platform provides native spatial SQL with Geometry and Geography data types following PostGIS conventions. The platform includes H3 hexagonal indexing for efficient large-scale queries and support for raster analysis. Native geospatial types in Delta and Iceberg formats with Spark API support enable portable, scalable spatial analytics on the Databricks Data and AI platform.

What is H3 and how does it improve spatial analysis?

H3 is a global grid indexing system that tessellates Earth into hexagons at various resolutions. This video shows how H3 expressions in Databricks simplify complex geospatial queries and improve performance for large-scale spatial analysis.

Full transcript

0:00 Introduction and overview

Uh yeah, thanks for coming to this session. We have a a lightning talk, so we're going to cover some things rather quickly. Um I'll introduce myself. I'm the product manager for the SQL language area at data bicks and also for all things geospatial. Uh Michael, want to introduce yourself? Oh, yeah. Hi everyone. Michael Johns. I'm geospatial product specialist at data bricks. Work on our field engineering side and work closely with end team.
All right, we're going to talk about the future. The future could change. I hope it doesn't, but that's what this slide means. Buyer beware. All right. In this talk, we're going to cover uh three main topics. Uh kind of an overview of where we're at today. The geospatial lakehouse today. Uh Michael's going to talk through some customer challenges and solutions and successes. Uh and then I'm going to talk about our spatial SQL roadmap.
Uh make sure we're all on the same page with where we're at with some of the native capabilities in the platform. All right, first topic, geospatial lakehouse. Um so yeah, we talk about the lakehouse, we're talking about the unified data lake, data warehouse, full governance and security. Um, the things I want to talk about here though are that it's really easy to get started doing spatial analysis with vector data and with mobility and GPS data as well.

1:15 The geospatial lakehouse

It's it's like they open the doors. It's like they're coming in and, you know, they they heard me say vector and they're like, "Let's get in there." Um, but with spatial SQL, this is currently in private preview. Um, it's it's super familiar to anyone who's used PostGIS. uh any relational database with spatial support. Uh so we're bringing all that into data brick SQL. Um so I imagine there's probably folks in this room who've tried the preview and have evaluated it.
Uh with spatial SQL you get a large set of common spatial expressions. And then with our our more more recent runtimes support for geometry and geography data types more on that front. So familiar uh the lakehouse we also consider this kind of the set of capabilities uh really flexible really fast uh so flexibility of course you can use SQL and I imagine most folks here are probably thinking more predominantly if they're using data brick SQL they use SQL uh but we also have Python and Scala APIs available for all of our spatial expressions uh when using H3 the global grid indexing system with built-in expressions in data bricks.
This is also again we kind of think of it as like a flexible way of working with any type of vector data but also you can combine it um with raster data and do some combined analysis. Uh H3 is great because it really scales well and data bricks is perfect for um scaling that kind of data which is stored uh as integers. So we have vector analysis kind of easy to get started with. Uh we have mobility GPS type data uh easy to work with H3.

3:22 Ecosystem and data market

And then if you want to get into, you know, more advanced analysis, raster analysis, do it yourself, build your own types of applications, you know, there's a there's a a pathway for all of these as well. Um so this is where I would say the platform really is flexible. Um and the flexibility comes from the ability to work with the whole ecosystem of really common uh spatial libraries right so whether we're talking about geopandas uh sedona and other tools um you know these are really common for anyone using data bricks or who has been doing spatial work on the platform for some time um I also want to call out though with data bricks apps um you can now build your own sort of purpose-built applications uh using you know streamlit dash other developer application frameworks.
Um this is really something that's come to the platform I think in like the last 6 months um or so. Um and there's lots of really good examples that have been built and I know there's probably another session or two which is focused on building apps. Uh that's something worth checking out. We've got lots of integrations with partners. So if you're working with any of the kind of big spatial partners and spatial vendors out there, um, know that we are too and continuing to build new integrations on that front.
And then the last thing I just want to mention, uh, you can't really do your analysis without your data and authoritative data to augment and enrich with. So the data bricks marketplace, uh, is a great place because we've got overture maps data sets now. Uh, we've got, you know, acue weather, lots of authoritative data sets in there. encourage you to use it because it's all instant access, easy to use uh right away. So, we've got sort of the full spectrum of uh use cases supported on the platform.
Let's talk about geospatial data types. So, this is one of these common, you know, questions that we always receive from customers. How do I store my vector geospatial data? Do I work with well-known text? Do I store it as binary? What's the column type? etc. Now with data bricks runtime 164 in private preview we've support uh geometry and geography data types this is an exciting moment for us we've been yeah go ahead and why not um I mean it's the end of the day get the blood flowing right like uh this has been a long time in the making uh I would say you know how did we get here right so this is really built on the foundation of the park and iceberg communities earlier this year ratifying the specs to store geometry and geography uh within those formats.

5:13 New geospatial data types

Uh and just yesterday the delta RFC was posted uh published uh so you can see that and I think my main point here is that there's going to be full interoperability between all these formats so there's no stress there's not you know some hidden secret or difference between these um and the data bricks team has been a part of these communities and contributing as well uh so really excited about this on the storage front you know complete interoperability but it's not just storage uh we als We also have uh the Apache Spark community has approved support for geospatial data types.
Um and this really standardizes you know how spark platforms can can work with geospatial data and really I think the point here is around um between the table formats between the engine side you're now using the same type across all these um so makes it really easy to sort of um work with data makes your workloads portable you know all the benefits of being a part of these open uh systems okay uh I think the Last slide I wanted to cover here on sort of like the current state of things with geometry and geography types now supported in data bricks runtime 164 in the preview.

7:15 Spatial join performance

We've also uh made some advances to support faster spatial joints. Uh so of course when we talk about spatial SQL there's expressions the types uh but really we have a a big focus on making sure that we have worldclass performance for executing spatial joins and spatial queries. Uh in the preview there's some known limitations. Maybe this is too much detail to share in this session but hey this is we're here. Um but we only support SD intersects.
Um it uh works with moderately sized data. Um and if you're working with sort of larger data larger than say 10 megabytes on one side of the join up to 2 gigabytes um you can enforce um a broadcast spatial join by using a hint. So what is what am I getting at here? We're working on accelerating spatial joins in the platform. Right now we have customers who are testing these joins and working through their workloads with these capabilities.
And yes, sometimes they say, "Hey, I have a use case where the performance isn't quite what I would expect given their existing sort of stack." And then other times we have customers who've shared anecdotes where they're like, "Hey, this is five times faster than my existing solution." So we know we're on the right track. Um, and you know, at this point I'm just saying I can't wait for everyone to test it, provide feedback, but also see for yourself.
Um, we have a number of other kind of internal uh, milestones that I I'll talk about a little bit later, but this is the current state of the union, right? Um, with that, I think I'll turn it. He can talk through some of the customer challenges. Yeah. So uh this is a lightning talk so lower your expectations on how far we go with this. I want to touch uh the outlines though of a couple of challenges and solutions that uh um seem interesting to what we generally bump into with customers.

9:00 Tessellating large polygons

The first one is around uh teslating of large area polygons. So if you're using H3, if you if you've used that at all, used it on data bricks, uh one of the challenges is just uh customers try to rationalize how do I handle large area polygons. A lot of times customers will go to H3 compact. Data bricks has a built-in expression for that. Want to make sure you know about that. Um but also there's custom teslation uh compaction patterns that you can also use.
So the ability to uh take advantage of another one of our expressions that may not be as well known is uh H3 teslate as WKB. If you look at the bottom of this slide, you'll see three columns where normally if you do like a cover as or a polyfill operation, you end up with one column which is the cell ids that uh cover that geometry. Tesla's WKB takes that further and adds additional information. It it describes what um of the cell ID is core to the geometry when you cover it and it also goes on and does a chipping operation that at the boundary does an actual ST intersects to determine the actual WKB chip.
When you put all that information together you have three fields of information that become powerful for applications like a custom compaction. There's many others of course. Um so uh to make that a little more real on the right side of the slide you'll see um just a quick example. So let's assume that we already have data that has gone through Tesla latest WKB at resolution five. Okay. And so uh if we want to do a custom compaction, we could say let's filter that on core equals true.
Let's say let's keep all of the resolution five uh as part of the final result because they're core to the actual tessellation. And let's union that with another uh resolution of our choice in this case nine. So um we're able to say let's union with filter on core equals false. Uh so we drop um uh we basically keep the five cell ID but go ahead and do a tessillating operation on the WKB that we have at the boundary of that initial phase.
And then we have at the end of that a five and nine custom compaction. Obviously I can't go into that very far but that's a good pattern just to be familiar with that um test lead WKB helps you in that situation. Now, at the end of that process, if you don't want to maintain the chips for whatever reason, you can obviously just drop those and now you have just cell IDs again, but you have a custom compaction. So, I wish I could say more about that.

11:38 Spatial join demonstration

Um, so, uh, another common one, uh, obviously just talked about this a bit. Um, so I just want to kind of make it real with an example is joining spatial data, right? So, uh, uh, how how how can you do that using these new features? Uh to do that, I'm going to flip over to a notebook. And I actually have time. They gave us a couple more minutes. So I'm going to go ahead and run this, which is always dangerous live, right?
Um so I'm going to hit run on this notebook. This is running on a serverless cluster in data bricks. It's using our built-in functionality. And this is a example of a spatial join that uses overure maps uh data. So, as we scroll through, um, and this may take a second to run, but as we scroll through, we have our point data, our places in the overture maps data set. Now, like said, this is really s uh what we have out of the box right now is one side of the join has to be moderately sized data.
So, we're kind of following that spirit. Obviously, you could go to any scale with data bricks in different ways, but for this example, we're going to keep it at moderate scale. Um, so we have just, you know, 850,000 points in the state of Florida. uh you can see that it's using uh if you can see the screen well I'm not sure you can see that it's using our built-in geometry uh data so we have this uh geometry column it didn't have to be called geometry I just happen to call it geometry um and so we have this column we're just calling as text so we can see the WKT output so we can uh you know look at that now um but we're using our built-in geometry uh for the point data and uh then we have the building data uh 7 million buildings that we're going to do in this point and polygon join Um again uh using our built-in geometry and you can see the polygons below and of course all the other uh field data that's available to that table.
So um we come down and uh we want to do our point and polygon join. Um and so uh you know described some of this. So this is just a kind of a real example of how to do it. Um when you look at ST intersects the predicate we're going to call um we're basically just saying the polygon geometry and the point geometry is what we're using as the uh parameters to the basically the join condition and uh we're adding this broadcast hint at the top to say I want to go ahead and push up to you know up to two gigs if the data will fit go ahead and do that as a broadcast join which is the most efficient uh way to do this.
Uh so let's see how fast that ran. So it ran in under 15 seconds. Uh so that you know that was running while we were talking. Um but that point and polygon at sort of that scale um you know 15 seconds and that produced 300,000 results of uh matches on points in the buildings uh in some way they intersect the buildings. Now we could go further. I don't have time to get into it, but you know, we could look deeper at the plan and you'll see that, you know, we want this photon broadcast spatial join to be what's uh uh sort of uh used as the plan, what the planner came up with.
So, you can kind of check your work with explain on this on your um on your query just to make sure things are actually doing what you think. Um, so let me go back now and um, you know, this just kind of summarizes what we just went through just in case something crashed or something on the uh, internet. Um, but um, two more quick examples. Um, so uh, I want to highlight Door Dash uh, as one example. Door Dash wanted to improve their routing services.

14:47 Customer success stories

So I think probably all of us have used Door Dash at some point in our lives. you have dashers, you have customers, and really what Door Dash was uh realizing was they were doing a lot of on the-fly calculations. Um, and so when it came time to order being placed, they were having to figure out uh then and there to calculate an estimated time of arrival and that was getting expensive as you think of all the uh concurrent operations they have going on.
So they said, let's let's take a step back and let's do this uh maybe in a way that helps us out on data bricks. So they used our H3 APIs um and then they were able to take all of their serviceable areas and turn that into cell ids for the point data that goes with the locations and then they added to that um open- source routing machine and were able to premputee an adjacency matrix for uh what what ends up being six billion cellto cell locations.
So they did a pre-mputation at that scale on all data used open OSRM to help them with the routing kind of uh calculations and they were able to run OSRM on data bricks which is interesting because you actually um don't have to make as many um uh kind of web calls uh if you're running it outside of data bricks they were able to kind of be efficient in that side. When it's all said and done they were able to take those premputed results cache them and then um get a 10x performance.
Um so instead of having to trade off um timeliness or high high performance or accuracy um they were able to really get the best of both worlds in a way that's really satisfied this use case. Um there's a blog on this uh these are in the slides of course so you'll be able to look at that more if you want. A second quick example and a final example for me uh involves uh natural disaster mitigation. So Excel Energy does a number of different things.

16:46 Spatial SQL roadmap

One of the things they do is wild wildfire mitigation, if I can say it. Um, they've invested $500 million since 2020 in this initiative. This is a very crucial initiative to their business. Um, and so they wanted to really take this further uh with data bricks. uh they were able to use our private preview functionality with spatial SQL combine that with H3 indexing and they were able to transform in uh real- time weather information into lakehouse ready data.
So from that really good data engineering they were able to then build uh you know and bring on their ML models and then take that further with some generative AI initiatives that are coming online now. So um all of that together is that terabyte scale of data uh for the weather data and they were able to achieve um uh 3x uh better coverage uh of their data with this process. Um four times better accuracy and 64 times faster processing.
So a really good success all around for uh Excel and their partner Nusat which is now part of Love Litics. Uh if if if I didn't mention that before. Um so that's that's my section on challenges. Let me pass it on to. Thanks. Yep. All right. Um, I love those success stories. If anyone wants to join forces and have their own success story published, reach out to me. Uh, let's just talk a little bit about the spatial SQL roadmap uh as we get towards the end of the session.
Um, so I mentioned earlier for us on the spatial SQL side, we're focused on three tracks. the actual engine part which is the expressions, the data type part which is like the storage and the performance side of things. Um the thing I want to convey here is on the engine side uh we're focused on complete photonization of all of our expressions. Um our you know our our testing shows that the photon side of things is going to make uh spatial queries run as fast as possible.
And then we're still looking for more feedback from customers around expressions that aren't supported on the data type side. Uh yeah, now we support geometry and geography. Uh these are based on the specs as we talked about in the parquet and iceberg specs uh that you may have already seen. Uh they support one SRID or one coordinate reference system per column. Uh so that manifests itself uh in data bricks as just supplying an SRD.
So when you go ahead and create a column, you might say geometry 4326. Um and it's all pretty standard at that point. Uh the types allow us to store statistics that are bounding box statistics. So really useful for spatial queries. Um which is great. And then on the performance side of things, so although I mentioned in preview we support ST intersects uh and there's some limitations around scale of data, we definitely plan to remove all these limitations or address them as soon as possible. most of which will happen in the public preview.
Uh so support for all the common spatial predicates st contains and so forth. Um and then also uh sort of a keen eye towards optimizations where we can rewrite queries on behalf of customers so they don't need to and apply rules uh that we can within the query planner. Um this is the type of stuff that our team is focused on to make sure we deliver the best performance. Um, in the end, we won't just have a broadcast spatial join.
We'll actually have a a shuffle spatial join uh to address really large scale data. Uh, so just to wrap up the timeline, which I imagine most folks want to know about, the spatial SQL private preview is now over. There's no more uh enrollment into this preview and we're planning to go public preview with the very next release. So for data bricks runtime 171, that's in July. uh and for DBS SQL just given our rollout schedule that would fully roll out um uh by the middle of August at this point.

20:27 Conclusion and Q&A

Um one thing to note in data bricks runtime 170 uh so for those of you who are using the private preview right now or are using H3 you're no longer required to have Photon enabled on a classic cluster. So all of these expressions are available outside of Photon. Um so just to make note of that. Uh and then of course I'll just call out um we're always looking for feedback. So I I say here as we move towards GA um it's really important for us to get validation u from all of you who end up using the public preview and have used the private preview up to this point uh to make sure your use cases are supported and as performant as you would expect.
Um the last thing I'll say about spatial SQL and working in data bricks 100% you want to use the types. No more storing data as well-known text or well-known binary unless it's, you know, absolutely necessary for some existing application. Um, but you want to store data as types and leverage the built-in spatial expressions for the best performance. Okay, with that, um, yeah, I mean, this is a huge time. We we had an applause mid session because we talked about geotypes, but hang on, hold your hold the next applause.
Um, I just want to say there's a few other talks. uh you know this is the audience here you guys probably care about um later today that's why people are leaving for no other reason um the 410 session and then uh Wednesday and Thursday there's a few other talks as well that might be of interest um encourage you guys to go there um if you can please fill out the survey it helps us make better content I know this was a quick lightning talk hopefully we did all right but please fill it out and we'll do better next year and continue your learning journey that's it thank you so much for

Learn more about the Databricks Data and AI platform.