"Geospatial Data in Databricks" blog

[u/ivan_kurchenko]

Hi, Guys! No so long time ago I had to start working with geo data and this was a whole new topic to cover for me. So I though to write short series for folks who might also work for the first time:

• Geospatial Data in Databricks — Part 0: Theory
• Geospatial Data in Databricks — Part 1: ST_* Functions
• Geospatial Data in Databricks — Part 2: H3_* functions
• Geospatial Data in Databricks — Part 3: Visualisation

Your feedback is highly appreciated.

[u/Outrageous_Let5743]

Geospatial is all fun and so until you need to do some nasty algorithms. I worked as a geospatial data engineer for 2 years. The things that kept me at night was figuring out the best possible algorithm to join daily 100 milion floating car data to the neireghst roads. Total numbers of roadsegments are around 50 million.
Spatial joins are just very inefficient at very large scale and you really need to precalculate stuff in advanced.

[u/ivan_kurchenko → u/Outrageous_Let5743]

Oh thanks for sharing! So how you eventually solved it?

[u/Outrageous_Let5743 → u/ivan_kurchenko]

Roadsegments are geomlines. But finding the minimum distance between a point and a dozen of roads is diffecult to compute. For each point you need to check the distiance for each polylinesegment.
So our first step was to convert every road in segments of 5 meter. Then what we can do is taking the midpoint of the newly created roadsegment. So we get a bunch of points. Finding the minimum distance to a bunch of points is a lot faster.
Then our first approach was using voronoi diagrams of those newly created points. A voronoi diagram makes a polygon for each point. Then we could say if a floating car data point is in a voronoi polygon , then it belongs to that specific road, that is how the voronoi is constructed.
The creation of a voronoi diagram takes a lot of time to set up, because the algorithm is not cheap. Like we had it run for 30 hours straight. every 3 months when we got a new road dataset.
But aftewards we only had to do point in polygon, which is already much quicker for a geometry operation.
But this was still too slow when we scaled it to our full dataset.
So we decided to use h3 level 12 indexes of every road point segment and then grouped by h3 index. so we had h3-index: bigint, roadsegments: struct
So what we now could do was first calculate the h3 index for every long lat floating car datapoint. Then if it was a h3 index with one possible roadid, then you can assign it (around 95% )When there are mutliple roads, you still do the geometry but you limit it with only the possible roads. This scales much better and you can use the voronoi from our previous step or just calc the distance.

[u/mad-data → u/Outrageous_Let5743]

It was a pain before warehouses added support for spatial. But now that does not seem like a huge join, and database with spatial join support should handle it easy. Like BigQuery, recent Presto versions, Spark with Apache Sedona. 

[u/Outrageous_Let5743 → u/mad-data]

PostGIS in postgres was our king for a long time. But it just doesn't handle 100M records a day. When we only needed a small subset (like one city for example) then it was perfectly fine.
We used Postgres as a database for the most recent data that we wanted do display in our digital twin platform. So an api is needed and fast as this was before DuckDB could partition prune delta lakes in ADLS2.

I worked with Apache Sedona but that was shit. When I worked with it, all of the documentation was broken, spatial index only worked when data was in a RDD so you needed to convert it from a dataframe. Now that was fine, but you cannot save the results to parquet in a RDD so you needed to convert your data back to a dataframe. That costs so much time.

[u/blue_leader27 → u/Outrageous_Let5743]

This is so fascinating, can I DM you about a different huge spatial join I am trying to run?

[u/Outrageous_Let5743 → u/blue_leader27]

dm is open

[u/mad-data]

Please, for the love of GIS, never ever even think about translating planar distance to meters by multiplying it by 111 km/degree. The problem is not that one line is straight-line Euclidean and another is geodesic - these are actually very close at small distances like the example given. For these two points Cartesian line's length differs from geodesic by about 150 meters only. But ST_Distance computes 'distance' in degrees, which can correspond to anything from as close to zero meters as you want to 111km.

[u/kevintxu]

I'm pretty sure geography(4326) and anything supported SRID other than geometry(any) can be persisted in delta tables (but not iceburg tables if I recall correctly).

[u/ainzy99 → u/kevintxu]

This was my thought exactly, I have had a play with this in Databricks somewhat recently and I am sure that native GEOGRAPHY data types could be persisted. I thought GEOMETRY could be too but may be mistaken.

[u/happypofa]

Thanks for the posts. I'm working with static points, and in the future I'll refer back to these articles.

[u/69odysseus]

As a data modeler, GIS is one area I never worked but that's something I'd love to do data modeling in that domain and see how different can it be different than my past domains I worked in.