Unified stack for geospatial workloads
Every day, hundreds of thousands of vessels broadcast their position, speed and heading via AIS. That data stream is a goldmine: commodity traders can sharpen ETA-based pricing, port operators can optimise berth planning in real time, and rescue coordinators can reach casualties faster. Now, with native geospatial functions, H3 indexing, Lakebase and Databricks Apps, a single platform can take raw AIS feeds all the way to production-grade operational applications, all with no specialist GIS stack required.
This blog presents a reference architecture, with open-source code, that does exactly that. We walk through five steps: ingesting AIS data into the Lakehouse, enriching it with spatial SQL, building ML-ready features, syncing curated tables to Lakebase for low-latency serving, and shipping an interactive vessel-tracking app. The pattern is repeatable for any geospatial use case.
What we built
NOAA publishes roughly ten million AIS position reports per day. The demo ingests three day's worth through a medallion architecture and serves the results to an interactive vessel-tracking dashboard, but the pipeline is designed to scale linearly across the full historical archive or a real-time stream. It is designed as an end-to-end blueprint that covers the five stages most geospatial projects need:
- Load data into the Lakehouse.
- Enrich it using spatial SQL functions.
- Transform it into ML-ready features (with an optional prediction model).
- Reverse ETL the curated data to Lakebase for low-latency downstream serving.
- Productionise the application using Databricks Apps.
Here is how each stage maps to the pipeline:
|
Step |
What happens |
Key Databricks capability |
|
Ingest |
Download daily AIS CSV files from NOAA and load them into a Bronze Delta table with schema enforcement and data-quality flags |
Auto Loader, Unity Catalog Volumes |
|
Clean |
Filter invalid coordinates, deduplicate positions within one-minute windows, enrich with vessel-type metadata |
Spark SQL, window functions |
|
Geospatial features |
Add H3 hexagonal indexes at multiple resolutions, build per-vessel LINESTRING trajectories with GPS-jitter filtering, compute current-location snapshots |
`h3_longlatash3`, `ST_Point`, `ST_MakeLine`, `ST_AsText` |
|
Reverse ETL |
Create a Lakebase (Postgres) instance, sync the Gold table via a snapshot pipeline, and grant the app service principal access |
Lakebase synced tables, Databricks SDK |
|
Serve |
A Dash app reads from Lakebase, renders vessels on an interactive map, predicts next waypoints using physics-based extrapolation, and lets users ask natural-language questions via Genie |
Databricks Apps, Genie API |
Architecture at a glance
The dataset
AIS transponders are mandatory on commercial vessels over 300 gross tonnes. The US National Oceanic and Atmospheric Administration (NOAA) publishes historical AIS feeds as daily CSV files. Each record includes the vessel's MMSI identifier, latitude, longitude, speed over ground, course over ground, heading and vessel-type code.
A single day of data contains roughly ten million rows. That volume makes it impractical to work with in a traditional relational database, but it is a comfortable workload for Spark.
Step 1: Ingest into Bronze
The first notebook downloads the raw CSV files into a Unity Catalog Volume and reads them into a Bronze Delta table. We enforce a typed schema at read time and add ingestion metadata such as the source file path and a validity flag.
bronze_df = (raw_df
.filter(col("MMSI").isNotNull())
.withColumn("event_timestamp", try_to_timestamp(col("BaseDateTime")))
.withColumn("event_date", to_date(col("event_timestamp")))
.filter(col("event_timestamp").isNotNull())
.withColumn("ingestion_timestamp", current_timestamp())
.withColumn("_is_valid",
col("LAT").between(-90, 90) & col("LON").between(-180, 180))
)The table is partitioned by event_date so downstream queries can prune efficiently.
Step 2: Clean and enrich in Silver
The Silver layer applies three filters: valid coordinates within continental US coastal waters, reasonable speeds (0 to 50 knots), and deduplication within one-minute windows per vessel. We then join a vessel-type dimension table that maps AIS type codes to human-readable categories such as Cargo, Tanker, Passenger and Fishing.
dedup_window = Window.partitionBy(
"MMSI", window("event_timestamp", "1 minute")
).orderBy(desc("event_timestamp"))
silver_positions = (bronze_df
.filter(col("_is_valid") == True)
.filter(col("LAT").between(20, 55) & col("LON").between(-135, -60))
.filter(col("SOG").between(0, 50))
.withColumn("row_num", row_number().over(dedup_window))
.filter(col("row_num") == 1)
)
A summary table aggregates each vessel's position count, average speed, bounding box and first/last seen timestamps, providing a quick lookup for the app.
Step 3: Build geospatial features in Gold
This is where Databricks' native geospatial capabilities shine. Three features are computed in pure SQL with no external libraries.
H3 spatial indexing. Uber's H3 system partitions the globe into hexagonal cells at varying resolutions. We index every position at resolution 5 (regional, around 250 km), resolution 7 (ocean-level, around 5 km) and resolution 9 (port-level, around 100 m). This enables fast spatial joins, traffic heatmaps and density analysis.
SELECT *,
h3_longlatash3(latitude, longitude, 7) AS h3_res7,
h3_longlatash3(latitude, longitude, 9) AS h3_res9,
h3_longlatash3(latitude, longitude, 5) AS h3_res5
FROM silver_ais_positions
Vessel trajectories. For each vessel and day, we collect ordered positions into a LINESTRING geometry. Before building the line, we filter out stationary GPS jitter (speed below 0.5 knots) and points that moved less than 50 metres from their predecessor. The result is a clean path suitable for visualisation and downstream ML.
ST_AsText(ST_MakeLine(
TRANSFORM(positions, p -> ST_Point(p.longitude, p.latitude))
)) AS trajectory_wktCurrent locations. A single-row-per-vessel table captures the latest known position, speed, course, heading and the full trajectory as WKT text. This table is the one we sync to Lakebase.
Step 4: Sync to Lakebase with reverse ETL
Lakebase is Databricks' built-in Postgres-compatible database, purpose-built for low-latency, high-concurrency serving. Rather than streaming raw AIS data directly into a relational database, which would struggle with the volume, we process everything in the Lakehouse first and then mirror the curated Gold table to Lakebase via a synced table.
synced_table = w.database.create_synced_database_table(
SyncedDatabaseTable(
name=f"{CATALOG}.{SCHEMA}.{SYNCED_TABLE_NAME}",
database_instance_name=LAKEBASE_DATABASE,
spec=SyncedTableSpec(
source_table_full_name=f"{CATALOG}.{SCHEMA}.{SOURCE_TABLE}",
primary_key_columns=["mmsi"],
scheduling_policy=SyncedTableSchedulingPolicy.SNAPSHOT,
),
)
)This gives the serving layer sub-second query latency on a table that is automatically kept in sync with the Lakehouse, no custom ETL pipelines or CDC logic required.
Step 5: Serve via Databricks Apps
The application is built with Dash and deployed as a Databricks App. It connects to Lakebase over a standard Postgres wire protocol and renders an interactive vessel map. Two persona-driven views demonstrate how the same data platform serves different operational roles.
Operator view: port operations controller
Sarah runs arrivals and berth planning. Her view shows:
- Live fleet map with vessels colour-coded by category (Cargo, Tanker, Passenger, Fishing), filterable by category and speed range.
- Click-to-inspect any vessel to see its historical trajectory, current speed, heading and a physics-based position prediction at configurable horizons (1 to 12 hours).
- Berthing exception queue. When a skipper submits an emergency request, it appears in Sarah's queue instantly. She can approve, deny (with notes) or investigate. The decision writes back to Lakebase in real time so every system, tugs, yard planners, pilots, sees the same state.
- OLAP and OLTP comparison tabs showing identical queries against the SQL Warehouse (analytical) and Lakebase (operational), side by side, to demonstrate Lakebase's advantage for point lookups and high-concurrency reads.
Operator View: Individual Vessel Details and Projected Trajectory
Operator View: All Vessels Colour Coded and Filterable
Operator View: Berthing Exceptions and OLTP Lakebase View
Skipper view: vessel captain
The skipper selects their vessel, views their current position, trajectory and 60-minute prediction on the map, and can submit berthing exception requests directly from the app. Exception types include emergency berthing, medical emergency, mechanical failure, weather delay and more. Once submitted, the request is written to Lakebase and appears in the operator's queue within seconds.
This two-way, low-latency write-back pattern is the key differentiator of Lakebase. It turns the Lakehouse from a read-only analytical system into an operational platform where decisions flow back to the same governed data layer. Predictions, approvals and exception records all live alongside the analytical data in one place.
Additional app features
- Physics-based waypoint prediction. Given a vessel's current speed, course and recent trajectory curvature, the app extrapolates future positions at multiple horizons. Uncertainty cones widen with time and turn rate.
- H3 density overlay for spotting congested shipping lanes at a glance.
- Natural-language queries via Genie. A floating chat panel lets users ask questions such as "Which tanker is moving fastest?" or "How many vessels are near Los Angeles?" The Genie API translates the question into SQL, executes it against the Gold tables and returns a result table.
Streaming: from demo to production
The repository also includes a streaming ingestion notebook that demonstrates how to move from batch to real-time with minimal code changes. The same cleaning and deduplication logic runs on a Structured Streaming pipeline using Auto Loader. Watermarking handles late-arriving data, and checkpointing guarantees exactly-once processing.
To move to production, only two things change:
- Swap the source from Auto Loader on CSV files to Zerobus, Azure Event Hubs AWS Kinesis or Kafka etc.
- Change the trigger from availableNow=True to processingTime="10 seconds".
Everything else, transformations, deduplication, Delta Lake output, Lakebase sync, stays exactly the same.
Why this matters
- Marine Insurance
Insurers need to know when vessels deviate into sanctioned waters or go dark. Maritime fraud costs an estimated two to four billion dollars a year, much of it detectable through the trajectory and H3 pattern analysis this pipeline already produces.
- Supply Chain Visibility
Real-time ETAs with automatic deviation alerts let supply-chain teams react before disruption hits. A one-day improvement in shipment visibility can reduce safety-stock requirements by ten to fifteen per cent.
- Environmental Compliance
International Maritime Organization (IMO) regulations require vessels to reduce carbon intensity, and port authorities need proof. The H3 density layer already shows vessel speed by zone, flagging violations in Emission Control Areas is a small step.
- Illegal Fishing Detection
Illegal, Unreported, and Unregulated (IUU) fishing costs an estimated twenty-three billion dollars a year. The trajectory and spatial-indexing features here are exactly what organisations like Global Fishing Watch use to spot vessels that linger, circle or go dark in protected waters.
Try it yourself
The full source code is available as a Databricks Asset Bundle. Clone the repository, set your target catalogue and deploy with a single command:
databricks bundle deploy --target dev --var="catalog=your_catalog"
databricks bundle run ais_pipeline --target dev --var="catalog=your_catalog"The pipeline automatically provisions the Lakebase instance, creates the synced table, grants the app service principal access and pushes connection details to the app.
What comes next
This demo establishes the data engineering and serving foundation. Planned extensions include:
- ML-based ETA prediction using historical trip data as labels, trained and tracked with MLflow.
- Trajectory forecasting with libraries like MovingPandas for shipping-lane-aware route prediction.
- Jurisdictional awareness using point-in-polygon checks against Exclusive Economic Zone boundaries.
- Real-time alerting for exclusion-zone incursions near offshore infrastructure.
-
Migrating the ingestion and transformation layer to Spark Declarative Pipelines (SDP)for incremental processing, built-in data quality expectations, and simpler lineage.
- Enriching AIS data with live commercial maritime intelligence feeds from vendors like Kpler (vessel ownership, cargo manifests, port calls, commodity flows) to move from raw position tracking to business-grade insight on trade flows and counterparty risk.
The important point is that all of these extensions build on the same Delta tables, the same Unity Catalog governance and the same Lakebase serving layer. No new infrastructure is needed.
Summary
Geospatial workloads have historically required a fragmented stack of specialist tools. This reference architecture shows that Databricks can handle the full lifecycle, from raw positional data through spatial feature engineering to low-latency operational serving, in a single, governed platform. Whether the goal is trading alpha, port efficiency or saving lives at sea, the pattern is the same: ingest, enrich, predict, serve.
We would love to hear how you are using geospatial data on Databricks. Share your use cases in the comments or reach out to your Databricks account team to explore what is possible.
2 Comments
