Highways and Hexagons: Processing Large Geospatial Datasets With H3

Summary

• Geospatial matching: Matching GPS locations to roads and local government areas (LGAs) involves complex geospatial operations on large datasets.
• H3 indexing approach: H3 spatial indexing isolates single-candidate matches efficiently, reducing computational overhead and improving scalability.
• Spark optimization: Broadcasting and partitioning techniques optimize memory usage, autoscaling, and data parallelization for production-scale workloads.

Highways and Hexagons: Processing Large Geospatial Datasets With H3

Talk by Olivia Ren, Solution Architect , Databricks ; Petr Andreev, Senior Data Engineer, Mantel Group
The problem of matching GPS locations to roads and local government areas (LGAs) involves handling large datasets and a number of geospatial operations. In this deep dive, we will outline the challenges of developing scalable solutions for these tasks. We will discuss our multi-step approach, first focusing on the use of H3 indexing to isolate matches with single candidates, then explaining use of different geospatial computational techniques to accurately match points with multiple candidates. From technical perspective, the talk will showcase the use of broadcasting and partitioning techniques, their effect on autoscaling, memory usage and effective data parallelization. this video is for anyone interested in geospatial data, spark performance optimization and the real-world challenges of large-scale data engineering.
Here’s more to explore:
Production ready data pipelines for analytics and AI: https://www.databricks.com/solutions/data-engineering
The Big Book of Data Engineering: https://www.databricks.com/resources/ebook/big-book-data-engineering-2nd-edition
See all the product announcements from Data + AI Summit: https://www.databricks.com/events/dataaisummit-2025-announcements

Chapters

• 0:00 Introduction and context
• 1:34 Data collection and value
• 4:22 Platform architecture
• 7:01 Matching problem overview
• 13:29 Hybrid H3 geospatial approach
• 21:18 Optimization and scaling
• 30:11 Using native spatial functions
• 33:12 Production and data modeling
• 39:14 Future of geospatial on Databricks

FAQs

What is H3 indexing?

H3 is a hierarchical spatial indexing system that divides the globe into hexagonal cells, enabling efficient geospatial queries and matching. This video demonstrates its application.

How do you match GPS locations to roads?

This video shows a multi-step approach using H3 indexing to isolate single candidates first, then employing advanced geospatial techniques for complex matches.

What optimization techniques improve geospatial performance?

Broadcasting and partitioning techniques optimize memory and parallelization on Spark. This video covers practical optimization strategies for large-scale datasets.

Why is geospatial data engineering complex?

Geospatial data involves numerous computational operations and large datasets, requiring specialized techniques and optimization on the Databricks Data and AI platform.

Full transcript

0:00 Introduction and context

Cool. Welcome everyone. Thanks for making it to 400 p. m. session on the first day. I'm really really glad you're here because it truly is an effort at this point um in the day. Um yeah, so today we'll be talking about highways and hexagons. Um this is a presentation that came out of our project that we've done. um with our clients. Um on stage here you'll see myself from mentor group. We are a um consulting part partner with data bricks in a apac.
We do consulting all across different clouds. Uh we provide sort of uh premier tier consulting for data bricks as well. Um there's a lot of us. There's about 900 of us uh in in Apac and a whole bunch of specialists as well. I'm here today um with from databicks. She's representing Austral who unfortunately couldn't make it um to the session today. Um but yeah, cool. Um so let me introduce my customer Australian. Um they're based in Sydney, Australia.
Um they're the uh transport agencies for Australia and New Zealand. Uh their mission is to provide national service that helps transport operations run smoothly across the state borders. So within Osrose TCA functions as subsidiary and center of excellence focusing on the certification and technology in the transport sector. So how does the telematics ecosystem actually operate? Um it all starts from the manufacturers.

1:34 Data collection and value

So manufacturers they produce the GPS and telematics devices um designed to capture the vehicle data things like um how how the speed they're driving on what's the location they're at operational uh performance etc. But before these devices can be installed into the vehicles they must pass rigorous testing that TCA sets for them. So TCA sets strict standards uh for performance, reliability and safety. Um and only once the cert the certified devices has been passed the test they will be installed on the vehicles and once instal devices get worked actually collecting real-time vehicle data and this data is then be transmitted to Osroads via TCA uh where it's aggregated and enriched and from there OSROS make this data available not just to the government agencies but also to the industries and also the uh commercial organizations.
So the data that we collect through this process are the following. U the main ones are the last three ones when we're talking about the position information, vehicle weights, mass information as well as the road network access information. So why do we need to collect this data and who is interested in it? Um let's start with the national heavy vehicle registr. So in Australia if you want to drive a heavy vehicle you need to have a permit and that permit uh defines the pre-approved rules for the vehicles to follow.
Uh so for the compliance and informance purpose the HBR needs across uh need to be across the roads access uh roads data and the usage patterns to ensure the trucks are actually on their um permitted routes. Uh next we talk about the road infrastructure managers and local government agencies LGAAS authorities. Um so they they use this data to understand how their transport networks are actually working things like roads um the bridges and other assets.
So why is this important? Because it's actually direct directly related to the risk and infrastructure in integrity. So imagine you're a console engineer. you designed this bridge that is supposed to carry like three s 300 uh southern trucks every year but then OSRO's data reveals that it's actually carrying 600 southern trucks each year so that actually doubles the load which means that the bridge is going to wearing out much faster than it's supposed to be right so and that process poses a significant safety and maintenance risk so there are two different SW systems um that Austral ROS has been using since 2019 um as one was decommissioned they adopt another one.

4:22 Platform architecture

So um but looking at the data volumes combined all together uh it's not hard to find that um we have the challenge of the data scale over time. Um they are expecting to have the telematics devices increasing from currently 35,000 to 90,000 in the future for the coming five years. So that means the data platform that we designed has to be robust and scalable enough to handle three to five times of the current data volume.
So how the data is flowing through the Oros data management system. First of all you have the TCA receives the talatics data uh ranging from different frequencies. It can be from near realtime or monthly loads and they ingest the data and do quality checks to make sure only the higher quality data is then moved towards the analysis. Next comes to the uh geospatial matching in the middle. Um this has done in the multiple levels from the roads to localities to states and even for um specific point of interest.
Finally the data is aggregated and prepared for reporting and then it can be presented in the um aspgs for the end user. So there are five different types of matching um that we we have in this situation but for the purpose of this presentation we're only focusing on the road and LGA matching. So this is the current design. At technical level we have all the different types of the data that is covering the location um routing um the boundaries and mass in different formats coming in gojson json hex xml um they being ingested into databicks data intelligence platform um and then currently we're using shapely and datab bricks h3 functions to process the data uh this is where your data engineers and data science get to work in the same platform on the same set of data for pipeline development and model development.
After data has been standardized, aggregated and enriched, DAS then can pick it up and start to query the data from DBSQL. It can also being pushed to PowerBI, Rapid Miner and ArcGIS for the end user. So most of the heavy lifting of the matching happens in the middle. I'll hand it over to talk about what challenges they encountered and how they tackled them. Thanks. Um cool. All right. Um now that we have a bit of a background and we know why we do this and why even bother with this problem.

7:01 Matching problem overview

Um let's talk about matching. Um the problem of uh road matching, LJ matching. Uh and if we think of it, the problem could be broken down into three steps. There is the problem of just matching to a single geometry. There's a problem of matching to multiple geometries or multiple locations. And there's a problem of getting it to production which is uh mostly a boring thing for everyone who uh ever developed code. Um any uh anyone here who used Hree before?
Uh cool. Lots of hands. That's good. I I won't be spending too much time introducing it because it's hard to introduce matching without Here. So it's kind of a qu catch 22. Um but yeah let's look at the matching problem and let's um uh distill it into what it actually is. So first of all we get um a set of data that comes in uh from our trucks that drive from sort of locations um say A to B and that data comes in with whole bunch of metadata but essentially it's just latitude longitude points that we care about.
On the other side of the stream uh oh sorry pipeline stream is reserved word uh on the other side of the pipeline we've got geocscape maps and these ones come in uh as you can imagine them as different layers of the system you've got um your state maps you've got your u LGA or council maps you've got your uh localities roads and so on uh and they come as JoeJSON geometries um which is a format you're probably familiar with and then we've got a problem that we need to solve.
We need some sort of algorithm that can figure out join the two together and give us an answer of uh that a given truck was in a given location. It was uh in a given council. It passed through a set of roads and so on. Um you can obviously scale that to any kind of uh points of interest that you interested in or any um things like bridges, motorways and so on. Um all right. So if we kind of visualize our problem, first of all, we've got um uh our position data that comes in.
That's our um latitude longitude points. Um here I've plotted them against a um very uh dear to me park in Melbourne. Um a fictional truck traveling down them. So while we get these points in the other set of data we get our geocscape roads. These come in as line strings. So they're essentially arrays of uh sets of points uh and they're separated into 200 meter segments. So while you you might see um here on the map you might see this as a single line. it actually is a whole bunch of segments that we get um which creates a bit of problems when you process it but such such as such as the data that we get.
The other data set that we um receive is LJGs or councils uh and this creates boundaries between sort of different authorities in Australia. Uh these come as polygons. So they create uh a full shape. Um and it's still just an array of points. um and they if you kind of trace them together you'll get a full um polygon. These are quite large. They come in um on average at 2700 points. So they're fairly heavy objects um when you start working with them.
Uh and the reason for that is they become very complicated geometries. You can see that um if you were to just draw a square, you'll probably need four points. But if you need to draw something that represents a natural uh boundary such as body of water or something like that, you you would um need a lot of points to kind of draw out that shape. All right. So um one of the things that we do straight away that once once we receive the data, we try to convert it and compress it.
Um the reason for that is again we receive large geometry objects with lots of spaces. They're great for human readability. Um but just by converting them to a binary format um we compress them two and a half times. And um in from one side you you can think of it as yes you you reduce the amount of storage that you store on your platform but at the same time compressing it into um a binary format reduces your memory load because once you start processing that data there's less to uh kind of sit in your memory.
It reduces your IO so while you're reading it's quicker because you're reading less data and obviously uh it reduces any kind of uh transformations between different nodes on your database prep on uh your your spark uh system because they don't need to travel for your network um or at least two and a half times less data needs to travel for your network. Um so once we talk uh now that we have kind of an idea of what matching is uh there's a few approaches to it.
Um in essence um it's kind of a scale. You start at uh the left hand side where you've got indexcentric um approach. And what this does is essentially uh splits your map into a set of uh indexes or bins um and it gives you an approximate um uh estimate of where your data is located. Um in on on the opposite side of that you've got traditional sort of spatial analytics. Um some of you who have used uh spatial analytics in uh SQL server you might know them as SD functions.
Um these ones u they give you a lot more precision obviously because they're mathematically um mathematical computations they give you explicit answer uh for your requirement but they are very compute heavy. So essentially your trade-off is between accuracy and um uh cost of compute. Um what the generally people have come up with is a in the middle approach which is the hybrid approach. Um, and this is what we're going to talk about today.
Um, and how we've set that up. So, just before we jump into into that, a high level overview of HP. Um, so here you can see there's um a few levels of resolution. Um, what HP does, it's a set of hexagon. It's a way to split your map into a set of hexagons. And these hexagons have a hierarchical structure. So um each resolution has a child resolution underneath. So a hexagon will have hexagons inside of it. And it makes it easy to navigate up and down the u scale of the uh system that you work with.

13:29 Hybrid H3 geospatial approach

Why do we need to split your your data into hexagons? Well, the reason is that it allows you to join across disparate data sets really easily. It gives you this um uh it gives you an ability to work in the same universe in the same kind of types of object. Um and probably the final question is why bother with Here? Um simple answer is it's developed by Uber so it's got to be good, right? Um but a real answer is it's really well integrated in data bricks.
It's almost seamless. It runs um super well. So uh here's the reason to use H3. Cool. So um how did we land on that hybrid approach and what would you do in in case you're working with uh geo you find yourself working with geospatial systems on data bricks first of all you you have to ask yourself some questions before you choose on a system um the first question you want to ask is do I just want to do bin do I just want to bin my data into some sort of location that can be approximate and can give me a high level overview of my system um this lead you down that path and for that H3 is absolutely excellent.
It gives you very high speeds uh to do that. It's it's very lightweight. Um so uh this would give you sort of a perfect outcome. Then on the other side, if you start working with multiple things uh and you start doing things like matching and you start caring about uh different types of geometries like points or uh polygons or lines u you probably need to touch vector space analytics in a way in one way or another. Um and with that comes a few questions.
So do you value scalability over accuracy or vice versa? If you value scalability and you just want uh kind of um some sort of way of structure your bin uh and or matching or um do it at fairly low precision or fairly high precision depending on how far down you go in HP, you end up down this way and it gives you an ability to kind of play with the resolution sizes with hierarchical structures um and uh gives you options there.
Um, in our scenario, we really care about accuracy. We want our data to be matched correctly. We want to match to correct roads because that assigns um correct permits to trucks, right? We want to match to a correct council because that represents their area and their data and they own it. Um, so we kind of have to go down this way. And in this area, we start asking the question of um what is the scale? If you're working with a small system, you're just doing a PC or you um doing it as a once- off, um traditional geometrical approach is excellent.
It's easy to set up. It runs really quickly, perfect for your system. In our scenario, we're working with uh very large scale sort of processing. We're processing 30,000 trucks, sending data every 30 seconds, right? You get a lot of uh lots and lots of records. So we have to uh go down that hybrid approach of H3 plus geometry. So let's see how that looks. Um at a very high level that structure is essentially get our position data latitude longitude then get a tree uh index from that uh position data. um for maps, get our uh polygon geometries, convert them to that WKB format to get the compression uh efficiency and then uh tile those geometries with H3 indices.
And we'll we'll dive that into that. But now you'll see that we're we're working with our points and working our geometries in the same um index. So we're working with H3 now. And we can easily join the two together and kind of find um find those matches. So let's visualize that. Here's a set of our geometries that we've seen before. Um I'm just selecting them from a SQL table. Um we what we do here we cover them uh with H3 and this is a databicks uh native function.
So we specify a resolution resolution 9 here which is 100 almost 200 meters at an edge uh essentially and this gives us uh a set of indices or h3 hexagon indices as an array right so for each of the geometries we get um a set of indices and this is how it looks like in terms of the um if you visualize it so for each array um even at fairly large resolution you already get hundreds and hundreds of indic So you can imagine that if you start getting um higher resolutions where your hexagons are smaller, you will you will tile these geometries really uh with massive amounts of hexagons.
So thousands and thousands of those. Um so really have to be careful about resolutions. Um but uh once we've got that, we also do our um uh points. We get the H3 index from latitude and longitude. Uh and here on the bottom you can see that given a set of points with uh let's say longitude you get an H3 index back. Um now that we're working the same system we can easily join the two together and we know that um these given points belong to these hexagons and these hexagons belong to these geometries.
So here's the answer of where your um uh point belongs. Um very similar but ever so slightly different for roads. So that those were councils. They were polygon geometries. They were fairly large objects. Um, roads are lines for them. We do the same system. We kind of convert them to binary objects. We then um have to do a bit of padding because imagine if it was a road, it was a single line in our uh database. We want that to um so geocape doesn't distinguish between uh two-lane roads, four lane roads, six lane roads and so on.
So we do a bit of padding just to make sure that we capture those uh records that might be on the edge of the road or um I think with um poor quality of satellites in Australia sometimes it could be 50 meters off the road and it's still technically on that road. So um get it gets a bit tricky on the outback. U but after that it's the same principle. Convert both to H3 and now we can work in H3 system and kind of join the two together.
So let's visualize that. We've got our road segments uh and we've got our points. We pad our road segments. Um we do a default padding of 10 meters on each side. Um and that gives us um pretty good representation of an average road. And then we do uh tile them with H3 indices. So we get um a minimum number of H3 hexagons that would cover that whole geometry. Once we've got that, we can get the points um and get H3 from those as well and we can easily join the two together.
Um cool as with all good things uh they kind of can't come to an end. So the problem becomes um the problem of edge cases and this is kind of a standard thing in software engineering. Um unfortunately crept into geospacecial as well. Uh but the you can see that there's a few scenarios here for roads. You might have multiple roads. You might have an inter intersection or um sort of scenarios where a lot of them are within that single hexagon.

21:18 Optimization and scaling

Um and same for councils. You would have conditions where that hexagon is on the border between the two and you need to figure out where it belongs. So what do we do here? Um this is where kind of your creativity comes to to an end. Um you you can create a lot of different approaches. For us the most simple approach that um satisfied majority of the cases um by majority being nearly 95% of the cases was just distance.
The closer you are to the road that's probably the road you're on. Um so for the in a in a situation where we get multiple uh road candidates in a single um hexagon we would just calculate the distance to that uh geometry and that's probably where which one we're going to pick. For uh LJAS it's much simpler. Just check the containment check that your point is within that object and then you get your answer. Um, and at this point you're probably wondering why would you not increase the resolution?
Why not go further at high resolution and um not have these edge cases? What we found is that um particularly with when you get thousands of data points u in our case millions and millions um you still get those edge cases regardless. you can go down to nearly um highest resolution possible and you'll still find that 1% or 0. 1% of cases where that where it sits on that edge. Um as a kind of fun joke, we've we've seen points sitting literally on that line uh between the two.
So objectively you don't know where it is. Um but uh essentially what you start uh deciding uh with your resolutions is how can you address your 95% of cases at least amount of compute possible and then do manual checks or not manual but uh do uh more exper expensive checks for that last 5%. So for us these resolutions worked really well uh particularly for LGAAS. The number of cases where this falls into this category is 5% for our data.
So for 90% of data we process it at high resolution get answers really quickly but then for that 5% we just um do the do um more expensive checks. So what do we do? How do we do these multiple matching checks? So um here's an example and this is what it looks like in the data. We've got a H3 index uh that matches to two different geometries Yara city and Bara city and um obviously it's uh this is how it looks visualized.
Um what we would do is we would do a point in polygon check. We would check that this point belongs to polygon and we would do it through a uh pandis udf. Um, we would take our geometry values, uh, WKB geometry, take our latitude, longitude, pass it to our Pendis UDF, and then use Shapely to do the intersect check. Um, fairly straightforward. Um, similar for roads. If you've got scenarios where you overlap multiple roads, you use the same pattern with UDFs and you do um, your checks and you calculate that distance. um instead of calculating containment you calculate distance everything else is the same.
So uh fairly straightforward pattern but unfortunately out of the box it just doesn't work. Um there's a lot of uh optimization issues that we had to go through and the reason why it doesn't work straight away um is due to join explosions. So the first thing that happens is that you start with a uh your points table. Um and this is going to get your latitude longitude points plus your H3 index. Um and then on the other side you're going to get your geometry table which is your um hexagon uh ids and corresponding geometry to them.
So once you join the two together, you end up having a much bigger table and you also carry a lot of the geometries with you. So um what you end up having is partition skew. So the first uh the example here it has a um a city that has a set of customers in it and one particular city has a lot more customers. So you end up having a skewed partition. What that means is that when Spark is processing your data it it starts with uh some tasks finishing within say 30 seconds.
Some of them might take 18 minutes, some might take an hour and a half. At that whole time, your worker nodes are waiting for that last task to finish. So, you get idle sort of CPU uh zero CPU usage, but one of the workers is trying to finish something. So, um in our scenario, the skew happens because there's a lot of points, especially inside of the city, uh like hexagons inside the city where there's a lot of traffic.
So you'll have um say Melbourne central or San Francisco central you'll have ton of points in there that would skew uh your your data but then regional or uh far away areas they won't have anything. So an easy solution to that is to repartition your um your data after the join. Um there's a few ways to do it. Talk to me after the session. They're a bit more technical. Um you can do it through hints. you can do it through um kind of explicit uh pispark uh partitioning but this wasn't it.
So we've done the partitioning we've we've got rid of the skew and then we hit a different problem. Um and this one is um it's got a interesting calculation on it but as any software engineer does the first thing you do is not read the docs is to throw more compute at it and that's exactly what we've done. So when we had this when we faced this problem, we were like, how about we just spin up a massive cluster and just try have it crunch through this data.
Uh this was with client approval. That was their idea. But um to give you a reference of how big that cluster was, it by the time the cluster spun up, it already cost you a cup of coffee. Like it was that expensive. So um we've got that cluster spun up, started crunching through the job, and then it ran out of memory. So then we started scratching our heads and thinking how do we solve this problem? What actually is the problem?
So we did this uh calculation and essentially what we found is that for an average geometry um I've shown you that it's quite large. It's uh about 80,000 bytes per uh geometry. So if you take that average you take 300 million records uh that come in monthly or 400 million depending on the scenario. um you then take the 5% of your records that you actually want to check. You end up having nearly a uh terabyte of um data that you need to process and that's what was uh happening on our cluster.
It was trying to read that. It was trying to cach that. It was trying to pull it all uh between different nodes. So it eventually ran out of memory. Um and to deal with that issue um uh surprisingly the easiest way to do it is to broadcast that data and have that data available to every node. So instead of carrying that geometry with your uh data frame if um instead of carrying your geometry with your data frame here we would broadcast that geometry and then have it available in memory and access it at runtime.
Um there's a few technical sides on that. I can talk to anyone who's particularly interested in that after the session. Um, but now that I've scared you with all this um optimization problems and everything that we've been breaking our heads over, uh, databick surprised us with um, oh actually sorry, wrong slide. Uh, this is the difference between optimizations. So unoptimized pipelines uh, about three times as slow optimized much more efficient.
Uh, for reference these are compute hours. These are uh hours that it takes this hours on the CPU. So that's not how long the job runs, it's how long it it takes to crunch the data between say eight worker nodes. Um but yeah uh luckily if you are about to build your solutions or about to migrate to databicks or doing any kind of uh new work um data bricks is introducing SD functions and it makes all the optimization problems that we faced um a lot more straightforward and kind of just throws it out of the park.

30:11 Using native spatial functions

So how would you use SD functions and datab bricks? Um here this is uh this code snippet is essentially the same uh process that we've showed you with UDFs but now it's fully in inside SQL uh fully databicks native. So the steps that uh are here first we do our standard procedure we convert our points to H3 we get that um H3 index uh then we try to subset only the data that has multiple matches. So uh we know that uh a given geometry has a given h3 index.
So if you see that h3 index appear multiple times in the table it has to match to multiple geometries. So you know by default that you got to you got to do an do some processing on that. So once you've got that um what we do then is uh we do a whole bunch of joins with within that CTE and we take our uh latitude longitude convert it to a point. We take our geometry, convert it to a geometry object using the u wkb function and then we do a uh sd contains operation to just check that this point is within that geometry.
Um super trivial operation. You get an answer straight away. Um and um you don't need to write any UDFs. You don't need to know pi pispark or anything. You can just do it all in native SQL. Um similarly for roads um pretty much the same operation. Um you don't need to use the limit. I just forgot to remove that. Uh but uh essentially instead of doing the containment, we do the distance computation and this gives us that uh that range.
Um databicks got a whole bunch of SD functions available. It could be distance containment. It could be um intersection or overlap or any kind of thing that you might might need to use. So have a look at their website. We've got um I think maybe some slides at the end. Um but yeah, super cool and straight out of the box it just blew away all the kind of work that we spend doing optimizing that. Um uh we so so nearly nine times improvement over the original sort of uh UDF work. um particularly um I've looked at sort of spark um plan and I still saw uh that it spark is still skewed but even though we saw skewed operations out of the box it's still way better than anything we we come up with.
So absolutely excellent um sort of set of functions. Um so yeah. All right. Um now the less interesting part. So now that we've got this solution that works with single matching, multiple matching, how do we get it to a production state? How do we build that into a system that we can um develop and um have it be usable today, tomorrow, two years after and so on. Um the first thing is obviously kind of getting our maps in place.

33:12 Production and data modeling

Um this one is fairly straightforward. This just gets your data from source to uh bronze layer and all it does is that probably every six months or whenever you get your maps um you will load your data and you'll persisted into a set of tables. Uh at the second step we do a that operation where we add WKB and WKT geometries. Um WKB for our job processing. Um WKT for our analysts because it's human readable. uh it's a text format so it's pretty much just JSON.
Um uh but we essentially give that availability to both users. Um the final bit is we create these uh H3 indices mapped to a set of geometries and this is what we we use for uh those join join operations. Cool. So um now onto a silver layer. um how do we design a data model that allows us to um do all of our geospatial operations? So this is this probably doesn't look like a star schema, but it is. This is a star traditional snowflake schema.
We've got our fact table, our position data. So this is our uh latitude longitude points. And then we've got a set of dimensions that represent our geospatial matchings. So, we've got our position matched to localities, LGA's, roads, and so on. Um, with on the far side there, we've got our H3 uh representations of the reference data. So, we take our reference geometries, apply the um tiling operations on those geometries, and we get our H3 tables.
Probably the critical part is that you get new data all the time. Uh city infrastructures change all the time. So you have to keep you have to keep this tables in um slowly changing dimensions. Um this one's we use a CD type 2. Um and for reference data we keep track of the map version. So we know when the data was loaded, was it 6 months uh 12 months ago? And then we keep um the flag whether this data is latest in the uh is latest field.
Cool. Um so now on to the kind of doing the actual matching operations and uh for context this is a very trimmed down um process. Um I've cut out a lot of other work that we do on the platform. So there were used to be 30 odd tasks but just for the purposes of matching these are the only ones involved. So every night we run uh the process that ingests our position data, latitude, longitude and um we uh we get that data into our position table.
We then have um our uh matching operations running in step two and all of these run in parallel um uh because we we've set up we set them up as separate dimensions, right? So we can just run them in parallel, append to separate tables and have no concurrency issues. Um uh another part of it, another important bit is that the um uh the single matching and multiple matching here you you'll see um position locality match and position locality multiple match.
They can also run in parallel. The reason for that is that even before we process the data, we know that it will whether it will need to be checked or not because we know which hexagons have multiple matches and which ones don't. Um I'm spending a lot of time emphasizing here why we want that to be parallelized. Um, sounds trivial, yet it cuts down your time. But if you think of it as a batch workload that you run every night, um, say today you had two dimensions, six months later you have six dimensions.
Suddenly your job is no longer two hours long. It's 6 hours long. By the time it runs, finishes running. Your analysts don't have the data at 8 a. m. in the morning. So you want to shrink that window as much as possible from the get-go. And this is why we try to paralyze it as much as possible. Uh, the final step there is just to deal with points. Uh these ones um we just tag anything that didn't match to uh a geometry as unmatched so we don't um reprocess it later on.
Cool. And probably the last final bit is um the most frustrating part is what you do when you get new data, new uh new maps come in. So as a data engineer, you probably have an analyst that come uh comes in and says, "Hey, I need to reprocess this data." Um what we do is we ask for a window. We say if you want to reprocess the last two weeks, three months, uh so on, give us that window. We'll then set uh for all the tables that are dimension tables, we'll set their flag to false.
So we know that um this data is no longer valid because it's mapped to an incorrect map. So then in the next step, we rerun the tasks. We know that uh we don't need to specify a window here because we know that if the last flag was three months ago, we'll just reprocess data from three months. Um and then in a final step, we just do uh the matching. So just do the cleanup, make sure that anything that's unmatched is still unmatched so it doesn't get reprocessed.
Cool. So this was our kind of path to production. Um now on tocape functions and all the all the cool stuff in the lakehouse. Yep. I'll be quick for this one. Um, so data bricks job spatial lakehouse provides flexibility to users depending on your use case and the data types the formats you are working with. You can decide which library you want to use here. So built on open source and open standard it supports the diverse library integrations like apache sedona geomasa um shape that we used here and geopanda and all that.

39:14 Future of geospatial on Databricks

So with the recent advancements in geospatial SQL, we now have over 80 SD functions available including the efficient geospatial joins operations on the native ge spatial types like geometry and geography. This enhancement supports functionalities like fast data conversion, quering and spatial join. So in terms of timeline of the spatial SQL, I steal this slide from our product manager. they were announcing it in a different room.
Uh with the DBR7. 0 onwards, you will no longer have the require you no longer required to use um Python for any of the geospatial functions. And this is the last release uh with the spatial in private privile. And shortly after the data and AI summit, the public pre which will uh start with the standard clusters and then move on to civilist notebooks and jobs and then the data warehouse shortly after. and we're targeting G um in the later of the year.
Finally, um in order to create the S synless end to end experience, our product manager is continuously working on AIBI dashboard for maps. So, we now have the point maps that allow users to render latitude and longitudes points as coordination. Um and then we have the core class maps to visualize the data across the ge geographical regions with the shading of the colors based on the values such as the income, the population and other metrics.
H3 maps and other maps are coming in a road map. So this is an example of the core plus map. As you can see the use case could be um using that map to um map the revenues and populations across different region. Finally, um data bricks apps could offer the opportunities for geospace spatial data apps to be rapidly developed and served to the end user. So packages like um deck gl and its python bindings like pyac can be embedded with the frameworks like string lit um gradial and dash which are all supported um by datab bricks apps.
So combined with other components you can add to your your app dynamics charts uh or the plots which you can cross filter and drill down through the tableau data um this could make it a powerful combination. So that's all we want to cover for this session. Thank you for joining us.

Learn more about the Databricks Data and AI platform.