Databricks Spatial SQL: Geospatial Analytics at Billion Scale

Summary

S&P Global Energy built a unified architecture on the Databricks Data and AI platform that consolidates geospatial data ingestion, querying, and AI-driven exploration into a single lakehouse, eliminating separate geospatial databases.
H3 hierarchical hexagonal indexing enables high-performance spatial queries at billion-point scale with sub-second query times using Databricks SQL Serverless.
Genie's natural language query capabilities and Databricks Apps allow users to explore and deploy AI-powered geospatial applications governed through Unity Catalog.

Databricks Spatial SQL: Geospatial Analytics at Billion Scale

Geospatial analytics at scale traditionally required separate databases, duplicating data and fragmenting insights. Databricks Spatial SQL eliminates these silos by enabling high-performance geospatial queries directly on your lakehouse, supporting billions of data points with sub-second query times. In this talk, S&P Global Energy demonstrates a unified architecture that consolidates geospatial data ingestion, querying, and AI-driven exploration on a single platform.
You'll learn how to index massive geospatial datasets using H3 hierarchical hexagonal indexing, build interactive map applications with Databricks SQL Serverless, and integrate Genie for natural language queries against spatial data. See how AI agents can explore geospatial information through conversational interfaces, and how Databricks Apps allows you to deploy custom geospatial applications powered by Spatial SQL, model serving, and governance through Unity Catalog.
🤝

Chapters

FAQs

What is Databricks Spatial SQL and what can it do?

Databricks Spatial SQL enables high-performance geospatial queries directly on the lakehouse, supporting billions of data points with sub-second query times. It eliminates the need for separate geospatial databases by bringing spatial analytics into the same platform used for data engineering and AI.

How does H3 hexagonal indexing work in Databricks?

H3 hierarchical hexagonal indexing is used in this video to organize massive geospatial datasets, enabling efficient querying at billion-point scale. It allows spatial data from sources like oil and gas well locations to be indexed and queried interactively through Databricks SQL Serverless.

How did S&P Global Energy use Genie for geospatial data exploration?

S&P Global Energy integrated Genie into their geospatial platform to enable natural language queries against spatial data, letting users explore energy datasets conversationally without writing SQL. AI agents powered by Genie can answer questions about spatial information across their full energy data portfolio, including gas, oil, and LNG datasets.

How does Unity Catalog support governance for geospatial applications on Databricks?

Unity Catalog provides governance and access control for geospatial applications built on the Databricks Data and AI platform, ensuring data security across all layers. In the S&P Global Energy architecture, Unity Catalog governs the spatial data, model serving endpoints, and Databricks Apps deployed on top of Spatial SQL.

Full transcript

[00:07] All right, welcome everyone. We're going to go ahead and get started. Our co-presenter will probably show in a few minutes here, but we can get going. Um real quick Uh please make sure to take a look at the forward-looking statement and give that a quick read.
[00:26] And we do ask that you would complete your surveys at the end of the presentation either at the Databricks event app or in the attendee portal. So, today is going to be a sequel of the spatial sequel that we demonstrated in the webinar with S&P Energy and
[00:43] Databricks a few months ago, where we went ahead and showed, "Hey, how can you visualize uh inside of a map application querying from Databricks at a billion-point scale?" Today, we're going to be showing how you can go ahead and use AI-powered
[00:58] map agents over that same scale to get answers. Um a little bit about me. My name is Hubert Boduszynski. I'm a geospatial software engineer at S&P Global Energy, which I have been in for 3 years now, working mostly in our data platform
[01:15] across uh products such as uh our AI-ready data and many um and specifically geospatial. Um quick agenda. We're going to go through the challenge and what we solved uh previously as long as uh as well as what
[01:31] we are looking to solve now, which we will demonstrate in the demo. Um and then go through the data architecture and just general overview of the architecture of what you will see in the demo and sort of what's to come.
[01:46] Uh even though today we're going to be focusing mostly on gas and oil well data, we have everything in energy. So, whether it is, you know, LNG data or agriculture products. Um we help our customers uh make decisions with
[02:02] conviction uh with energy related uh data sets. Two uh key platforms, one that I am uh part of right here on the left, the data platform named Advanced Data Workbench or as we call ADW, um is our data platform and our
[02:19] client-facing platform Core, two major platforms in energy. The uh left side of your screen uh in ADW, ADW is a very, very large platform consisting of many different open-source technologies, where we have different types of data citizens, so such as, you
[02:36] know, if you're a data engineer or a data scientist, we help you go ahead and ingest your data, enrich it, and deliver it to our customers. We use uh common capabilities, which are a common set of tools to help all of our data citizens
[02:52] go ahead and move data to for for into to to to our customers. Um one of the key places that we go ahead and and deliver data is to Core, which is that client-facing platform, which offers, as you see, real-time market activity, personalized dashboarding, AI-driven
[03:08] insights, and advanced charting tools, none other than of course maps. A little bit about the scale um in ADW, we process terabytes of data, uh have 30 over 37,000 tables onboarded with 75,000 uh daily jobs for over 1,000 active
[03:26] users. In Core, there's over 29,000 active users, 4,000 daily, 12,000 weekly um with five platforms in in a single pane of glass. And um quick show of hands, who's seen the webinar, who's seen uh the the talk of
[03:41] what we did before? Okay, cool. So, that we're going to be learning today. So, the key problem from what we saw before with Databricks and S&P Energy was moving source data into ADW, into our platform. And what we were forced to do before to support map
[03:56] querying was move data back and forth between a geospatial database. And so this caused data duplication because we had to go ahead and load data externally into a different system and had to pay for that ETL job and the cost of storing the
[04:13] data there. And of course this meant that we would deliver less fresh data to our customers. Whereas thanks to spatial SQL we were able to take our source data, ingest it into ADW, and query directly from there. So you get all these good things here
[04:29] that you see such as querying from a single source, um savings on saving on the cost cuz you're not paying for those extra ETL jobs now or the um extra compute of of storing your data there. And you get fresher data cuz you're querying straight from the source. And
[04:45] on top of that we were able to go and provide deeper insights using H3 indexing. Quick reference here to um the the blog and the webinar which provided which provides those data patterns and ETL patterns and query patterns
[05:02] to enable that performance that that you saw on that billion scale. So there's a blog where you can has a very deep deep dive um on how to achieve what we achieved. And if you prefer a webinar over a YouTube video. So that's kind of what we've done. That's that's where it's that's where
[05:18] we're at. So we what we wanted to do is say hey we solved all these things, right? We have this simplified architecture where we remove redundancies, saved on costs, achieved single second queries, and provided deeper insights. But we did all of this, right? And and to the end user
[05:33] what it would look like is see careful created charts of our data. You can see this gas and oil well data set right here that we will shortly go through with the demo where we would have these pre-created answers to the questions that our
[05:48] customers knew we wanted that they wanted the answers to. So, what we wanted to do is say, "Hey, how do we not stop there, right? How do we go ahead and anticipate for the for the questions that we, you know, for the questions that the customers might have that we might not, you know, directly supply them the answer with with a
[06:05] pre-created dashboard?" And so, this was kind of the the main question that we wanted to push through where how do we push customizability to our customers while using our trusted data. And so, soon you will see a demo.
[06:20] Just allow me 1 second here to switch.
[06:53] Hello. Awesome. Okay. So, here you see all of the North American gas and oil wells um uh in North America here, right? If I go ahead and click into any single H3 index here, which is many points uh that are grouped up into a single H3 cell, you
[07:08] can see that there are 9,544 um wells, which are again the the points in this single H3 cell. If I go ahead and click on another H3 cell, which is this darker purple one compared to that yellow one, we have 12.
[07:26] And so, if I zoom in towards um Texas here, this will fire a query to a SQL serverless warehouse where I get a much more granular view. This is because, you know, from the H3 index, there are different resolutions, different sizes
[07:41] of this of the H3 cells, um and all of these query patterns were outlined in the webinar that you can see, which is again not going to be the focus of today's um presentation. And so, the same idea, right? If I click on on a yellow cell here, it has a higher count of wells compared to something
[07:56] that's purple, right? Now, let me talk about these pre-created um dashboards that I was just pointing out to that we can say, "Hey, our customers know we want to be able to go ahead and provide um they want these types of answers, right? To these questions." Such as, "Hey, I have all of the uh from
[08:13] the server from a SQL Serverless Warehouse, the aggregation that was done on the back end of and summed up with the counts to the client, I can see the total amount of wells from the client side, right? And so, since it's just SQL Serverless
[08:29] Warehouse, I can go and ask different types of questions. This is asking the total amount of wells in every single H3 cell, but I can ask, since we have the data at SNP, what is the average production of every single inside of every single H3 cell for all
[08:45] of the wells, right? And so, if I go and show you the map view put this way, I can find out where the highest average production of wells are. And since we, you know, have have the data um and it's just a simple query for us, I can go ahead and ask here, "Take
[09:02] Exxon Mobil." In this map view port, I can see in this area on on the map, I can see Exxon Mobil has 102,000 wells, and I can say, "Okay, take give me Exxon Mobil's I need one second here.
[09:29] I can ask and I can find out the given any operator, find out the average production of Exxon Mobil's uh highest average Where is Exxon Mobil's highest average production?" And I can do the same for something like Oxy, right? So, I can go ahead and just compare the operators by name to find
[09:44] out where the highest average production is. And those types of flows, they definitely, you know, um they're important, right? You want to be able to give the customer this type of level of customizability where in you say, "Hey, you know, the types of questions um that will be asked." But, how do we go ahead
[09:59] and anticipate questions that uh we might not be able to answer right away with a pre-created dashboard like this. And so, this is where we will get into the second part of the demo
[10:16] where we use Genie. And so, I can go ahead and ask, "Hey, using Genie, show me the distinct H3 cells from my table um on this column using one of the um H3 APIs." Um and so, uh using the Genie tool, which we made from
[10:31] the Databricks app that you'll soon learn about, I can go and uh have the question passed in, and Genie can decide, "Okay, here is the question. What is the sequel that needs to be sent uh into my warehouse layer, and does it need to be added back into
[10:47] into the map?" And so, now this result will take a little bit of time here
[11:02] And you can see over here, uh we have computed in this case the H3 centroids, uh the center points, of our uh application. Awesome. Um and then here, you can see this is just as Genie decided, this is just another map layer that that is added into the map. And so, since it's
[11:19] just another map layer, I can go and customize it as need be, in this case with the centroids. And all this is coming back from from our warehouse layer from spatial sequel, right? And you can see this uh cell ID that was added. Now, I want to go ahead and ask and then so
[11:36] that answer the question of like, "Hey, I have this uh query layer where I can, you know, go and take any H3 API, any ST function, and and and just query it against my database." Now, I wanted to get to that same answer that I was showing with the pre-created dashboard uh just by putting in some
[11:53] text here. So, in this case, I asked to show a box plot um showing me the H3 cells that have the highest count of wells. And a box plot was generated for me, and if I just go and cross-filter on it, I can go and get the uh find out which
[12:09] H3 cells have the highest count of wells. And therefore, you can also uh do the inverse of finding out where the lowest counts of wells are. And just to prove out the concept to you,
[12:25] we can, you know, with any type of dashboard that that that wants to be generated, we can take something else here like a histogram, and essentially get the uh the same effect here a little differently with log-scaled um counts. So, another map layer is added, and I
[12:41] can just go ahead, as you can see, uh this layer was generated, and cross-filter to get those uh highest amount of wells in those H3 cells, right? And again, these are all just layers. So, if I exit out here,
[13:02] you can see um we have different ways to achieve the same result, and throwing, you know, more giving the user more customizability into our data. Um Michael, do you want to go into the second part here for the intelligent platform? Sure.
[13:30] All right. Well, the good news is Hubert's the star of this talk, so you're not missing anything if I'm not quite here. Um yeah, so I just want to kind of fill in some of the details on the Databricks platform. You guys saw the hopefully all of you caught the keynotes today, so you're starting to hear things that'll
[13:45] come up here. I'm just trying to put them in context of um the great work that uh Hubert and S&P Global have done uh with Databricks that that we've done together. So, um so, you know, just kind of demystifying a little bit, when you think of uh the lakehouse,
[14:02] you know, you think of all the great components that you see. You have, you know, the lakehouse itself, you have the lake base, you have lake flow for connecting to different uh sources and doing ETL and streaming. Um you might think about um Unity Catalog for governance.
[14:17] You might think about open formats, you know, and spatial, of course, that that has a uh special place in our hearts, you know, good old shapefiles that don't quite seem to go away. Geo I mean, if you ask Esri, they would say, you know, we have all kinds of great formats, so that's your fault. Um but there's all kinds of great formats out
[14:32] there. Um Postgres and PostGIS, Delta Lake and Iceberg, etc. So, you when you think about the lakehouse for geospatial, the number one thing I'd want to kind of convey is that this is not a bespoke or special thing that's different than
[14:47] what you might think of when you think of Databricks. So, we really just want to kind of demystify that a little bit and just say there happens to be a category called spatial data, but you also happen to want to do a lot of advanced things like AI at the end of that processing. And so,
[15:03] um geospatial kind of rides along with each of our features is is really what we're trying to um to get to. So, um so, there's that little call out with spatial SQL. Kind of the easiest way to think of where product has focused the most at Databricks is uh introducing around um I'll go to the
[15:19] next slide. Introducing around 100 functions. Um they cover kind of the range of the core things that customers have asked for for from us. There's always a lot more that customers want to do that maybe we haven't implemented, um but they do cover kind of the range of importing different uh
[15:35] uh data from uh really in our case it's more the columnar kind of imports you might think of with well-known text, well-known binary, GeoJSON, um good old, you know, just coordinates like XY coordinates, um the ability to export out of our internal native geometry types into um those same kind
[15:52] of interchanges. And then things that we get into um various measures, various accessors about the um geometry geography themselves. And then uh you may want to do things like um you know, your uh topological relationships, your ST joins and intersects.
[16:07] So the functions kind of cover the core set of what customers have asked for from us for um vector data. So that's where product is focused the most. Um but with that we have um you know, kind of a range of improvements that we could kind of pull a thread on. So um I've been at this for
[16:24] about 4 years full-time at Databricks doing geospatial as a product specialist. Um and so um way back in um even before December of last year, going back even a year before that, we introduced in private preview what we're now um getting to GA on finally kind of
[16:42] this month, actually. Um and so we have um a lot of kind of like things around performance, how do we drive up performance of spatial joins, um how do we sort of first we had to materialize into spatial types, not just have in-memory operations. So we've been pulling that thread through um into
[16:59] public preview that happened in August of last year. And then as we've extended through um the end of last year into this year, we've done things like improve our driver support, um like ODBC driver support. Um we've driven spatial join performance to uh a a high level that we're really satisfied with now,
[17:15] which is one of the gates for getting to GA. And uh and meanwhile we've been aligning with these great spec improvements such as parquet, iceberg, V3 specification, the the ability to to handle our own Delta Lake open source format, which
[17:31] gets into open source uniform, which allows you to kind of work between iceberg and and Delta Lake. And Delta sharing of course. So it's kind of that whole world plus the Spark world allowing for now spatial types with Spark 4.2. So that'll come
[17:48] out with Databricks DBR 19. Keep an eye on that. So this whole ecosystem has been a huge effort to get to this GA mark. And that's really where we are. So we have our spatial sequel for geometry is at GA. That happened really just last
[18:04] week actually. So that means it's all the way through to our Databricks sequel warehousing side of Databricks. And of course you can get it on our other compute surfaces as well. So this is kind of the big milestone that kind of powers how customers like S&P can go to production and do powerful
[18:20] things with high confidence. So just sort of taking a little bit of a step back to orient you to how to think of geospatial on Databricks. You know, on the kind of in your distributed journey on the right side of that slide there's the product features
[18:35] that we offer. So we have these GA functions and types, native types for geometry and geography, plus the 100 functions I talked about. We have about 35 H3 functions, right at that number. Those are like right there available. Any compute you go to in Databricks
[18:51] you're going to find those functions. So you don't have to go searching, you don't have to ask your account executive to give it to you. It's just there for you. And then you know, there are these great Spark frameworks. So Spark frameworks are powerful. We're the original inventors of Spark at Databricks. So has
[19:06] a huge affinity for us. Um and those Spark frameworks allow um, you know, popular open source uh, Sedona, you know, the ability to bring that into our compute. Um, you have, um, um, you have GeoBrick, which is a project that I happen to sponsor at Databricks, which isn't going to do
[19:22] everything you ever wanted, but it kind of focuses on places we feel like customers are asking us to help. Um, and, um, and then you get into like our great partner, um, libraries that they offer, their Spark frameworks. Um, in no particular order, we have, you know, Carto's Geo, uh, their analytics toolbox
[19:38] for Databricks. Um, and so if you're using Carto, you may want to use their toolbox, and then with, um, ArcGIS with Esri, um, they have their GeoAnalytics Engine, which is very powerful, as well. So, Spark frameworks give you a lot of options that's outside of our actual product, but you can see how you could use those to complement, uh, whatever else you're
[19:54] doing. And then, of course, single-node libraries come into the picture. So, uh, as you're probably very familiar, Spark has these capabilities for, um, user-defined functions, uh, various ways to bring in custom code that you can distribute. And so, single-node libraries really struggle the most
[20:10] because they get too much data. So, we try to back off of that as far as overloading single-node libraries and allow more limited data to be provided, but still spread the, um, libraries across the cluster and give them, um, parallel, uh, compute power. So, uh, that may be new to some of you,
[20:25] but that's kind of the spectrum of, uh, kind of how we think about it. And then just take kind of zooming out, you have all these different formats that that exist. You have, um, you know, needs to, um, get them in and get value out of them. And and so, the formats themselves might be a barrier of how do I kind of break through the formats? So, there's
[20:42] different, uh, different ways to do that. The GeoBrick project that I sponsor has a number of readers and writers that you could use to, um, to do that. Um, but of course, there's many ways to do that. Uh, but once you're kind of working with it in the Databricks platform, um, you have all of our native stuff, plus all of, um, our
[20:58] great partners, um, open-source libraries, uh, kind of all the above is available to get you to the answers that you want. And then ultimately, you may choose to serve that with a partner, with your own custom app, or you might choose to use some of our built-in capabilities. And so, just on that like kind of three
[21:14] lanes to think about Databricks built-in has you know, the AI/BI side has just gone to GA with map support. There will be more coming. So, if you get into it, please let us know. There's There's more that we do need to
[21:30] do there, of course, but it's a great first start to support geometry, geography types in our AI/BI. Um things like polygon type maps with like more of a chloropleth where you want to color or tooltip based on different attributes. Um and point maps is kind of what we
[21:46] have today. And then of course, apps and various third-party options. So, real quickly cuz you've heard this in the keynote as I just transitioned. You know, what what you just kind of have gone through with the demo and what Hubert was talking about.
[22:01] You know, you really don't want to have to build a new dashboard for everything that a customer might ask. So, so the new world is to really to talk to your data with your data versus having someone else go and answer the question and show you that show you what they produced. You
[22:16] want to kind of do that in a live way. And so, that's really where Genie comes from. I'm going to kind of bump through these to make sure I don't slow us down too much here. And so, you know, we're kind of we're trying to kind of tackle different things with what Genie and our ability to help you
[22:32] sort of organize your agents, organize your data, have highly curated value out of your underlying assets. And so, you know, kind of making sure that that that hallucination problem that's been out there for a long time doesn't sort of come into
[22:47] things that you're putting in front of your own end users is what we're what we're looking at. And so, that's where you know, Genie agents comes in powered by like our our Genie ontology you heard about. The ability to set up these Genie spaces. All that kind of comes together governed
[23:04] by a Unity Catalog. Um, and so you have the ability to have confidence that nothing's leaking out. Um, you know, one of the great things about Databricks is that we have these partnerships with all the um, foundation models, all the frontier providers. So, the the conversations that you're having
[23:20] with those models is not actually leaving the boundary of of your security boundary. Um, so that's really uh, gives you good confidence that you're able to um, to not have things leak out. It's staying uh, governed and protected. Um, and so ultimately all this is sort of like available, um, you know, across the
[23:37] spectrum of um, the Genie spaces side where you can curate, the ability to to build these different dashboards, and then these APIs that are um, really what um, what Hubert uh, was getting into how you're hitting the API side and bringing your own app to to bear to do that.
[23:52] Okay, so I'm going to pass it back to Hubert. Thank you, Michael. All right. No worries. So, then I'm going to highlight a little bit of a deeper dive of what was under actually powering the demo. The three main parts that we'll go over. First, um, Genie which takes that natural
[24:09] language and translates it to spatial SQL and um, push the results to the maps. Uh, this LLM layer which we use uh, for model serving to call um, the LLM endpoint. And Wasm for some of the browser and server side uh, tools that that you saw
[24:26] there. So, these are going to be the or these are the Databricks bricks of our app starting off with uh, model serving. So, we use a GPT-5-2 um, for some of the LLM calls. So, there
[24:41] were some suggestions that came up that you have what a user might want to ask. So, this is this layer. Um, quick disclaimer, we did use model serving but um, in the app we're actually going to move to Unity AI Gateway um, which was also announced today earlier in the in the keynote.
[24:56] Um, we have this lakehouse layer which as Michael pointed out this is you know where where spatial sequel lives and so with all the ST functions and S3 H3 functions that we used in the demo to drive the performance that you first saw in in the demo this is that layer and
[25:14] Genie knew as you saw in the in the demo all of these these APIs and all these functions to go and be able to make the calls for what for some of those H3 centroid that that that you saw in the in the in the demo. Quick thing you know you can add
[25:29] multiple tables into your Genie space so in this case you know in the example you see here you can take something like separate table for well well production or take well operators and add multiple assets and generate you questions into your Genie space such as hey give me the count of wells by their
[25:46] current um status group whether they're active or inactive and those questions are generated for you. Now for the actual Databricks app On the browser we use a lot of awesome libraries including Kepler GL, GeoDa, Open Assistant, DuckDB and and WASM
[26:03] and this uses an Express server where we have multiple plugins Genie to translate those natural language queries such as hey give me H3 centroids into spatial sequel that's executed in our Genie space
[26:18] analytics which can power or does power those sequel warehouse queries that you saw in the beginning and serving plugin to go ahead and have those direct LLM endpoint calls and so I do believe there's a session going on this quite literally at the at the same time right
[26:34] now so you might be hearing about it first here but all of this was built with AppKit so all these plugins that you see are kind of are they are available to you out of the box right now so AppKit is an type script SDK to help you connect to all your
[26:49] Databricks services. So in this case that the really awesome part, and you'll see it here, our entire server is 35 lines of code. And so, to walk through uh for you to uh end-to-end flow, if I go ahead and I'm an uh user and I say, "Hey, give me the
[27:06] H3 centroids in the map." That user that natural language query gets passed into our Genie tool, and the natural language is then passed through the Genie plugin into our Genie space, where it's translated to spatial SQL, executed in in the lakehouse, and if a map layer was
[27:24] deemed necessary by the Genie tool, it will be added to the map. And uh just to hit home on what Michael was saying here, Genie, you know, um the API exposes a lot of um good things for conversation. So, if Genie were to um give you an incorrect result here, you
[27:40] know, it has the full conversation history to um start a new conversation, uh check the messages that were already sent, and return the SQL query or the or re-execute a SQL query. Um wrap uh wrapping up here, um you we want you to try this out
[27:56] yourself. So, it's still work in progress, but we're going to have um a community app so that you can go and take your data, um and hopefully explore it how how we do with uh S&P Energy. So, again, this is built with AppKit. We're going to get a blog that's going to come with this soon.
[28:11] Um again, shouting out the the resources, the blog and the webinar, those that has those data engineering and query patterns where that you can use on your um massive data sets to go ahead and and drive that performance. Michael, do you want to wrap up with the resources here um that are remaining?
[28:32] Yeah, just just to point them out. Um so, we have the um the older artifacts that we did um that builds on this with the S&P um kind of the blog and webinar that we did that shows some of the how to visualize using H3, um some patterns that um S&P was trying to work through and just
[28:48] documented well and so we we went ahead and explained that with them. Um, we had the GA announcement as one of the resources, the public preview announcement that um, was um, back in um, August actually. Um, and then some things around joint performance and then a blog I did with uh, Lake Flow SDP just
[29:06] to ex- just to explain how to put an app together with Lake Flow powering the data being populated uh, to kind of put that together to to hopefully give you a resource. So, I think that's all we have. Yeah, are we out of time? We might be. Okay. Thanks.

Learn more about the Databricks Data and AI platform.

The information provided herein is for general informational purposes only and may not reflect the most current product capabilities or configurations.