https://community.databricks.com/t5/technical-blog/the-quot-where-quot-problem-real-time-spatial-engineering-with/ba-p/151192 <H3><SPAN>Introduction: Modern Data Engineering has a Location Problem</SPAN></H3> <P><SPAN>In the world of data engineering, the "What" and "When" are often handled with ease. We know what was bought and when it was delivered. But for industries like logistics, retail, and telecommunications, the most critical question is often </SPAN><STRONG>"Where?"</STRONG></P> <P><SPAN>Historically, processing spatial data at scale has been a "bolt-on" experience. Data engineers had to juggle external libraries like Apache Sedona, manage complex UDFs, or deal with the overhead of manual spatial indexing. These silos led to fragmented pipelines and performance bottlenecks.</SPAN></P> <P><A href="https://www.databricks.com/product/data-engineering/spark-declarative-pipelines" target="_blank" rel="noopener"><STRONG>Databricks Lakeflow SDP</STRONG></A><STRONG> (Spark Declarative Pipelines) </STRONG>now&nbsp;<SPAN>bridges that gap.&nbsp;Native spatial types and functions mean your data engineers can write geospatial logic in the same data pipelines they already run; no special tools, no additional libraries, and no context switching.</SPAN></P> <H3><SPAN>The Lakeflow Advantage: Connect, Pipeline, and Orchestrate</SPAN></H3> <P><SPAN>Databricks Lakeflow was built to simplify the entire data lifecycle. By bringing spatial support into Lakeflow SDP, we’ve eliminated the friction of moving between specialized GIS tools and your production ETL.</SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><STRONG>Native Spatial Types:</STRONG><SPAN> Support for </SPAN><A href="https://docs.databricks.com/aws/en/sql/language-manual/data-types/geometry-type" target="_blank" rel="noopener"><SPAN>GEOMETRY</SPAN></A><SPAN> and </SPAN><A href="https://docs.databricks.com/aws/en/sql/language-manual/data-types/geography-type" target="_blank" rel="noopener"><SPAN>GEOGRAPHY</SPAN></A><SPAN> types means no more converting coordinates to strings or complex arrays.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Photon-Accelerated:</STRONG><SPAN><A href="https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-st-geospatial-functions#topological-relationships" target="_self"> Spatial Predicates</A> are vectorized in C++, allowing the engine to process up to millions of geometries per second.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Declarative Simplicity:</STRONG><SPAN> You define </SPAN><I><SPAN>what</SPAN></I><SPAN> the spatial transformation is; Lakeflow SDP handles the </SPAN><I><SPAN>how</SPAN></I><SPAN>, including state management and incremental processing.</SPAN></LI> </UL> <H3><STRONG>The Foundation: Native Spatial SQL &amp; Data Types</STRONG></H3> <P><SPAN>Most spatial pipelines store coordinates as doubles, string, or binary, and hope for the best. With the recent update to Lakeflow SDP, storing and processing spatial data has changed at the engine level. Lakeflow SDP now utilizes native column types that maintain bounding box and spatial reference metadata with every value. That metadata allows for </SPAN><STRONG>Data Skipping</STRONG><SPAN> and </SPAN><STRONG>Spatial Joins</STRONG><SPAN> that are significantly faster than 3rd Party libraries. </SPAN><SPAN>Lakeflow SDP supports:</SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><STRONG>GEOMETRY:</STRONG><SPAN> For planar coordinate systems (X, Y), ideal for local maps.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>GEOGRAPHY:</STRONG><SPAN> For spherical coordinates (Longitude, Latitude) on the Earth’s surface, essential for global logistics.</SPAN></LI> </UL> <P><SPAN>Once your columns are spatial types, you operate on them with ST_ functions, the same function family used across PostGIS and other spatial databases, so there's nothing new to learn. </SPAN><A href="https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-st-geospatial-functions" target="_self"><SPAN>Databricks ships over 90</SPAN></A><SPAN> of these across various categories: constructors (</SPAN><A href="https://docs.databricks.com/aws/en/sql/language-manual/functions/st_point" target="_blank" rel="noopener"><SPAN>ST_Point</SPAN></A><SPAN>, </SPAN><A href="https://docs.databricks.com/aws/en/sql/language-manual/functions/st_geomfromwkt" target="_blank" rel="noopener"><SPAN>ST_GeomFromWKT</SPAN></A><SPAN>), predicates (</SPAN><A href="https://docs.databricks.com/aws/en/sql/language-manual/functions/st_intersects" target="_blank" rel="noopener"><SPAN>ST_Intersects</SPAN></A><SPAN>, </SPAN><A href="https://docs.databricks.com/aws/en/sql/language-manual/functions/st_contains" target="_blank" rel="noopener"><SPAN>ST_Contains</SPAN></A><SPAN>), measurements (</SPAN><A href="https://docs.databricks.com/aws/en/sql/language-manual/functions/st_area" target="_blank" rel="noopener"><SPAN>ST_Area</SPAN></A><SPAN>, </SPAN><A href="https://docs.databricks.com/aws/en/sql/language-manual/functions/st_distance" target="_blank" rel="noopener"><SPAN>ST_Distance</SPAN></A><SPAN>, </SPAN><A href="https://docs.databricks.com/aws/en/sql/language-manual/functions/st_length" target="_blank" rel="noopener"><SPAN>ST_Length</SPAN></A><SPAN>), and more.</SPAN></P> <P><SPAN>The key shift: these functions run inside your Lakeflow SDP pipelines, not in a sidecar library or a post-processing notebook. You ingest, transform, and query spatial data in the same declarative SQL you already use for everything else.</SPAN></P> <P><SPAN><span class="lia-inline-image-display-wrapper lia-image-align-center" image-alt="Overview of the 90+ spatial sql functions available in Databricks." style="width: 562px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/25204iEC4FF411C64EB94E/image-dimensions/562x490?v=v2" width="562" height="490" role="button" title="lakeflow_spatial_sql.jpg" alt="Overview of the 90+ spatial sql functions available in Databricks." /><span class="lia-inline-image-caption" onclick="event.preventDefault();">Overview of the 90+ spatial sql functions available in Databricks.</span></span></SPAN></P> <H4><SPAN>Performance Spotlight: Fast Spatial Joins</SPAN></H4> <P><SPAN>A common operation in geospatial engineering is the Spatial Join—for example, determining which delivery truck is currently inside which service zone. Databricks Spatial SQL (including Python and Scala bindings) </SPAN><A href="https://www.databricks.com/blog/databricks-spatial-joins-now-17x-faster-out-box" target="_self"><SPAN>outperforms traditional library-based approaches</SPAN></A><SPAN> by leveraging native bounding box metadata and engine-level spatial indexing. Because these optimizations are built directly into the product, you can achieve high-performance out of the box without the headache of manual partitioning or managing external libraries. It’s all about making the heavy lifting automatic, so you can spend more time putting your location data to work.</SPAN></P> <P><FONT size="4"><span class="lia-inline-image-display-wrapper lia-image-align-center" image-alt="Benchmark Chart showing improvement over a traditional library." style="width: 547px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/25205iBEA4F99B91B43E60/image-dimensions/547x286?v=v2" width="547" height="286" role="button" title="2025-09-blog-launch-of-spatial-joins-og-1200x628-1.png" alt="Benchmark Chart showing improvement over a traditional library." /><span class="lia-inline-image-caption" onclick="event.preventDefault();">Benchmark Chart showing improvement over a traditional library.</span></span></FONT></P> <H3><SPAN>Real-World Implementation</SPAN></H3> <H4><SPAN>1. Simulation: Austin Couriers</SPAN></H4> <P><SPAN>To demonstrate these capabilities, we implemented a real-time courier delivery simulation. This demo tracks a fleet of 50 couriers across Austin, TX, navigating a road network derived from <A href="https://overturemaps.org/" target="_self">Overture Maps</A>.&nbsp;The simulation can be configured to work with other named regions and to control various properties, including how much chaos to introduce.&nbsp;The simulation that emits the events is driven from </SPAN><A href="https://github.com/databricks-solutions/lakeflow-deliveries/blob/main/dab/src/courier_delivery_pipeline/explorations/austin_delivery_sim.ipynb" target="_self">austin_delivery_sim</A><SPAN> notebook.</SPAN></P> <LI-CODE lang="python">source = OvertureSource.local(OVERTURE_DIR) _sim_names = {f.name for f in fields(SimConfig)} _cfg = dict( overture=source, output=SimulationOutput(output_path=OVERTURE_DIR), bounds=BBOX, area_name="Austin, TX", region="US-TX", # Fleet num_couriers=50, assign_closest_courier=True, # False =&gt; random among available (ignores mode) assign_courier_mode="random", # closest | round_robin | random unavailable_after_delivery_sec=5.0, # Routing speed_km_h=73.0, # (default - this is 45 mph equivalent) use_osmnx=True, # OSMnx planned_route per assignment (default) assignment_use_osmnx=True, turn_penalty_sec=0.0, # to speed up the demo (default is 10 sec) # GPS gps_interval_sec=10.0, perturbation_meters=10.0, # Dispatch delivery_frequency_sec=0.5, assignments_per_dispatch_wave=0, min_assignment_separation_m=15.0, # Chaos delay_rate_target_pct=4.0, # — ~4% slow bucket (0.48–0.72 factor) max_abandonments_per_tick=1, progress_factor_std=0.11, # - tight normal spread (0.11) slow_progress_factor_min=0.48, slow_progress_factor_max=0.72, abandon_check_after_sec=60.0, abandon_check_route_frac=0.38, abandonment_threshold_pct=30.0, # - abandon at 1.3× expected abandon_hold_sec=18.0, assignment_route_workers=6, # parallel route solves per wave arrival_time_threshold_pct=10.0, delivery_delayed_interval_sec=30.0, delivery_early_interval_sec=30.0, # Runtime sim_duration_sec=1200.0, # 20-minute live run clear_output_on_run=True, ) config = SimConfig(**{k: _cfg[k] for k in _cfg if k in _sim_names}) mgr = SimulationManager(config, spark=spark) summary = mgr.initialize() print(json.dumps(summary, indent=2))</LI-CODE> <H4>&nbsp;<SPAN>2. Lakeflow SDP: Ingestion with Native Types</SPAN></H4> <P><SPAN>The simulation emits raw JSON events (GPS pings, assignments, and completions) into a <A href="https://docs.databricks.com/aws/en/volumes/" target="_self">Unity Catalog Volume</A>. Lakeflow SDP then picks up these events using <A href="https://docs.databricks.com/aws/en/ingestion/cloud-object-storage/auto-loader/" target="_self">Auto Loader</A> to build a live spatial gold layer.</SPAN></P> <P><SPAN><span class="lia-inline-image-display-wrapper lia-image-align-center" image-alt="Courier Deliveries Lakeflow data pipeline (click for higher res)." style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/25352iC05F27893B444E90/image-size/large?v=v2&amp;px=999" role="button" title="ldp_live.png" alt="Courier Deliveries Lakeflow data pipeline (click for higher res)." /><span class="lia-inline-image-caption" onclick="event.preventDefault();">Courier Deliveries Lakeflow data pipeline (click for higher res).</span></span></SPAN></P> <P><SPAN>Here is the Lakeflow SDP <A href="https://docs.databricks.com/aws/en/ldp/streaming-tables" target="_self">Streaming Table</A> that ingests the raw </SPAN><A href="https://github.com/databricks-solutions/lakeflow-deliveries/blob/main/dab/src/courier_delivery_pipeline/transformations/gps_event.sql" target="_self">GPS pings</A><SPAN> from the courier delivery simulation. The table reads JSON files continuously from the delivery_status/gps/ directory on a Unity Catalog Volume. Each row is one ping from a courier en route to a delivery, emitted every 10 seconds (default gps_interval_sec). The Source path is configured via the pipeline parameter event_base_path, e.g. '</SPAN><STRONG style="font-family: inherit;">/Volumes/&lt;catalog&gt;/&lt;schema&gt;/&lt;volume&gt;/overture/austin_default/delivery_status'</STRONG><SPAN>.</SPAN></P> <P><SPAN>The JSON fields (from GpsEvent dataclass):</SPAN></P> <UL> <LI><SPAN>event_type STRING — always "gps"</SPAN></LI> <LI><SPAN>courier_id STRING — unique courier identifier</SPAN></LI> <LI><SPAN>delivery_id STRING — active delivery identifier</SPAN></LI> <LI><SPAN>longitude DOUBLE — perturbed WGS84 longitude</SPAN></LI> <LI><SPAN>latitude DOUBLE — perturbed WGS84 latitude</SPAN></LI> <LI><SPAN>timestamp_iso STRING — ISO-8601 simulation timestamp</SPAN></LI> </UL> <LI-CODE lang="python">CREATE OR REFRESH STREAMING TABLE gps_event COMMENT "Live GPS position events from the courier delivery simulation. Each row is one GPS ping per courier per gps_interval_sec." AS SELECT event_type, courier_id, delivery_id, longitude, latitude, ST_Point(longitude, latitude, 4326) AS point, timestamp_iso, CAST(timestamp_iso AS TIMESTAMP) AS event_ts FROM STREAM read_files( '${event_base_path}/gps/', format =&gt; 'json', schema =&gt; 'event_type STRING, courier_id STRING, delivery_id STRING, longitude DOUBLE, latitude DOUBLE, timestamp_iso STRING', includeExistingFiles =&gt; true )</LI-CODE> <P><SPAN>Lakeflow SDP </SPAN><A href="https://docs.databricks.com/aws/en/ldp/dbsql/materialized" target="_self">Materialized Views</A> are used to visualize 100m and 1km geofences around each delivery POI—via <A href="https://docs.databricks.com/aws/en/sql/language-manual/functions/st_buffer" target="_self">ST_Buffer</A>—as well as mantaining active routes (see repo for more).</P> <H4><SPAN>3. Databricks Apps: From Pipelines to Insights</SPAN></H4> <P><SPAN>Data engineering doesn't end at the table. To close the loop, we deployed a </SPAN><A href="https://www.databricks.com/product/databricks-apps" target="_self">Databricks App</A><SPAN> that queries these Lakeflow Materialized Views. Using </SPAN>Pydeck<SPAN>, the app visualizes the road network (Overture Maps), the active courier routes, and the real-time geofence status—refreshing every 15 seconds as the pipeline processes new pings.</SPAN></P> <P><SPAN>The app provides global stats as the simulation progresses to readily measure in-flight deliveries and how many have been completed or abandoned. There is per minute Throughput windowed calculations for deliveries and GPS pings. Also, POIs and Couriers can be post-filtered for more granular tracking or triaging of delivery issues.&nbsp;</SPAN></P> <P><SPAN><span class="lia-inline-image-display-wrapper lia-image-align-center" image-alt="Databricks App UI - Couriers moving along their assigned routes towards delivery POIs (click for higher res)." style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/25302i17C79D52008E617C/image-size/large?v=v2&amp;px=999" role="button" title="app_deliveries.png" alt="Databricks App UI - Couriers moving along their assigned routes towards delivery POIs (click for higher res)." /><span class="lia-inline-image-caption" onclick="event.preventDefault();">Databricks App UI - Couriers moving along their assigned routes towards delivery POIs (click for higher res).</span></span></SPAN></P> <P><SPAN>A key requirement for logistics is knowing when a courier is approaching a Point of Interest (POI). We use</SPAN><SPAN>&nbsp;</SPAN><SPAN><A href="https://docs.databricks.com/aws/en/sql/language-manual/functions/st_dwithin" target="_blank" rel="noopener">ST_DWithin</A> for the actual distance tests between GPS pings and POIs, see <A href="https://github.com/databricks-solutions/lakeflow-deliveries/blob/main/dab/src/app/queries.py" target="_self">queries.py</A>. Here is a culminating query used by the app to reflect various GPS ping status for active couriers and whether they are within 1km or 100m of their target POI.&nbsp;Note: queries are composed in python, so there are some variables injected.</SPAN></P> <LI-CODE lang="python">WITH latest_gps AS ( SELECT courier_id, delivery_id, longitude, latitude, event_ts, ROW_NUMBER() OVER (PARTITION BY courier_id ORDER BY event_ts DESC) AS rn FROM {_table("gps_event")} WHERE {_vp_g} ), active AS ( SELECT courier_id, poi_id, target_lon, target_lat FROM {_table("active_route")} ), in_1km AS ( SELECT DISTINCT g.courier_id FROM latest_gps g JOIN active ar ON g.courier_id = ar.courier_id WHERE g.rn = 1 AND ST_DWithin( ST_Point(ar.target_lon, ar.target_lat, 4326), ST_Point(g.longitude, g.latitude, 4326), {d1} ) ), in_100m AS ( SELECT DISTINCT g.courier_id FROM latest_gps g JOIN active ar ON g.courier_id = ar.courier_id WHERE g.rn = 1 AND ST_DWithin( ST_Point(ar.target_lon, ar.target_lat, 4326), ST_Point(g.longitude, g.latitude, 4326), {d100} ) ) SELECT g.courier_id, g.delivery_id, g.longitude, g.latitude, g.event_ts, CAST(DATEDIFF(MINUTE, g.event_ts, current_timestamp()) AS INT) AS age_minutes, (i1.courier_id IS NOT NULL) AS in_1km_geofence, (i100.courier_id IS NOT NULL) AS in_100m_geofence, (ar.courier_id IS NOT NULL) AS has_active_route FROM latest_gps g LEFT JOIN active ar ON g.courier_id = ar.courier_id LEFT JOIN in_1km i1 ON g.courier_id = i1.courier_id LEFT JOIN in_100m i100 ON g.courier_id = i100.courier_id WHERE g.rn = 1;</LI-CODE> <H3><SPAN>Architecting for the Future</SPAN></H3> <P><SPAN>By unifying spatial intelligence with the Lakeflow engine, we are removing the "spatial tax" that has traditionally slowed down innovation. Whether you are calculating flood risks, optimizing delivery routes, or analyzing cell tower coverage, Lakeflow SDP provides the scale and simplicity needed to turn raw coordinates into actionable business insights.</SPAN></P> <H3><SPAN>Ready to get started?</SPAN></H3> <UL> <LI style="font-weight: 400;" aria-level="1"><STRONG>Explore:</STRONG><SPAN>&nbsp;Visit the Databricks <A href="https://www.databricks.com/product/data-engineering" target="_self">Lakeflow</A> and <A href="https://www.databricks.com/product/databricks-apps" target="_self">Apps</A> product pages.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Deep Dive:</STRONG><SPAN> Read our technical blog on </SPAN><A href="https://www.databricks.com/blog/databricks-spatial-joins-now-17x-faster-out-box" target="_blank" rel="noopener"><SPAN>17x Faster Spatial Joins</SPAN></A><SPAN>.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG style="color: #1b3139; font-family: inherit;">Demo:</STRONG><SPAN> Check out the </SPAN><A style="font-family: inherit; background-color: #ffffff;" href="https://github.com/databricks-solutions/lakeflow-deliveries" target="_blank" rel="noopener">Courier Delivery Simulation Repository</A><SPAN> to configure and deploy the example <A href="https://docs.databricks.com/aws/en/dev-tools/bundles/" target="_self">DAB</A> into your own workspace.</SPAN></LI> </UL> Mon, 30 Mar 2026 10:10:41 GMT mjohns 2026-03-30T10:10:41Z https://community.databricks.com/t5/technical-blog/the-quot-where-quot-problem-real-time-spatial-engineering-with/ba-p/151192 <P>In modern data pipelines, “what” and “when” are easy boxes to tick — but “where” has always been the outlier. Spatial data has long lived in silos, demanding external libraries, custom scripts, and painful overhead. That ends now. With Databricks Lakeflow SDP introducing native spatial support, geospatial processing becomes a first-class citizen in your ETL stack — fast, declarative, and deeply integrated. From real-time logistics to global service zones, it’s time to make “where” your competitive edge.</P> Mon, 30 Mar 2026 10:10:41 GMT https://community.databricks.com/t5/technical-blog/the-quot-where-quot-problem-real-time-spatial-engineering-with/ba-p/151192 mjohns 2026-03-30T10:10:41Z https://community.databricks.com/t5/technical-blog/the-quot-where-quot-problem-real-time-spatial-engineering-with/bc-p/152605#M971 <P>Really fantastic write-up&nbsp;<a href="https://community.databricks.com/t5/user/viewprofilepage/user-id/36003">@mjohns</a>&nbsp;... thanks!!</P> Mon, 30 Mar 2026 21:12:03 GMT https://community.databricks.com/t5/technical-blog/the-quot-where-quot-problem-real-time-spatial-engineering-with/bc-p/152605#M971 BradSheridan 2026-03-30T21:12:03Z