Real-Time Vehicle Tracking with Neo4j, Databricks Lakebase and OpenStreetMap

How to Cite This Book

If you use this book in your work, please cite it as:

APA: Chaudhri, A. (2026). Real-Time Vehicle Tracking with Neo4j, Databricks Lakebase and OpenStreetMap. Self-published. https://realtime-vehicle-tracking.github.io/

BibTeX:

@online{chaudhri2026vehicletracking,
  author  = {Chaudhri, Akmal},
  title   = {Real-Time Vehicle Tracking with Neo4j, Databricks Lakebase and OpenStreetMap},
  year    = {2026},
  url     = {https://realtime-vehicle-tracking.github.io/},
  urldate = {2026-08-28}
}

Cover

License

Copyright

Copyright © 2026 Akmal Chaudhri. All rights reserved.

Publication Information

First published: August 2026

The latest version of this book, together with updates, errata and additional resources, is available at:

realtime-vehicle-tracking.github.io

Book License

Real-Time Vehicle Tracking with Neo4j, Databricks Lakebase and OpenStreetMap is licensed under the Creative Commons Attribution-NonCommercial-NoDerivatives 4.0 International License (CC BY-NC-ND 4.0).

You are free to copy and redistribute this book in any medium or format under the following conditions:

• Attribution - You must give appropriate credit, provide a link to the license and indicate if changes were made.
• NonCommercial - You may not use the material for commercial purposes.
• NoDerivatives - If you remix, transform or build upon the material, you may not distribute the modified material.

The full license text is available at:

creativecommons.org/licenses/by-nc-nd/4.0

Code License

Unless otherwise stated, all code samples, notebooks, scripts and source files accompanying this book are licensed under the Apache License 2.0.

You are free to use, modify and redistribute this code, including for commercial purposes, subject to the terms of the Apache License 2.0.

The full license text is available at:

apache.org/licenses/LICENSE-2.0

This distinction means that the book’s written content is protected under the Creative Commons license, while the accompanying code remains freely available for use, modification and integration into your own projects.

The pre-clipped OpenStreetMap data files are available under the Open Database License (ODbL). See LICENSE-DATA.

Trademarks

Product names, company names and logos mentioned in this book may be trademarks or registered trademarks of their respective owners.

Their inclusion is for identification and educational purposes only and does not imply any affiliation with, sponsorship by or endorsement from the respective trademark holders. All trademarks remain the property of their respective owners.

Disclaimer

The information in this book, including all code samples, scripts and notebooks, is provided “as is” without warranty of any kind, express or implied.

The author makes no representations or warranties regarding the accuracy, completeness, reliability or suitability of the information contained herein for any particular purpose.

Examples are provided solely to illustrate technical concepts, patterns and software architectures.

Readers are responsible for independently validating all code, configurations and recommendations before using them in production environments.

To the fullest extent permitted by law, the author shall not be liable for any direct, indirect, incidental, special, consequential or other damages arising from the use of or inability to use, the information, code or techniques described in this book.

Attribution

Map data © OpenStreetMap contributors, available under the Open Database License.

Regional OSM data files sourced from Geofabrik.

About the Author

Akmal Chaudhri is a technical leader, educator and author with extensive experience in databases, AI and developer relations. He specializes in technical writing, developer education and community building, helping engineers and organizations understand and adopt complex technologies through clear, practical and engaging content. He is a frequent international speaker, a published author and a contributor to industry discussions on data platforms, AI and software development.

Today, Akmal works in developer education at Neo4j, where he focuses on technical content, workshops and community initiatives. While his professional role has evolved, this book represents an independent exploration of various data platforms.

Based in the United Kingdom, Akmal continues to work at the intersection of databases, AI and developer tooling, helping developers build modern data-driven applications.

For book updates, code samples and additional resources, visit the Book website.

To connect professionally or follow his latest work, visit LinkedIn.

Real-Time Vehicle Tracking with Neo4j, Databricks Lakebase and OpenStreetMap

This book builds a real-time fleet operations dashboard using three database systems:

1. Neo4j Aura for the road network graph.
2. Databricks Lakebase for live vehicle positions.
3. Databricks Lakehouse for historical analytics.

Ten simulated vehicles move around a city following real road connections loaded from OpenStreetMap. Two Streamlit dashboards show live positions and analytics.

The primary demo uses the London Borough of Merton. Additional example configurations are provided for San Francisco and Singapore. The whole system is driven by a single YAML configuration file.

How the Code Is Organized

All runnable code lives in the code/ directory alongside config.yaml.

The chapter markdown files live in src/.

Notebooks are numbered to match the chapters – 02_road_network.ipynb corresponds to Chapter 2, and so on.

Chapter 1: Architecture and Setup

What We’re Building

Real-time vehicle tracking sits at the intersection of three distinct data problems:

1. Graph problem – what’s the fastest route between two points on an actual road network and which zones are reachable from where?
2. Operational problem – where are my vehicles right now and where have they been?
3. Analytical problem – which areas of the city are seeing the most activity and which roads carry the most traffic?

Each of those problems has a natural home in a different kind of database. That’s the core idea behind this book. We’re not going to use one database and make it do everything. We’re going to use three and let each one do what it does best.

The system we’ll build is a fleet operations dashboard for the London Borough of Merton – a compact urban borough in South London with a dense road network that’s large enough to be interesting but small enough to run on free-tier accounts. Ten simulated vehicles move around the borough following real road connections loaded from OpenStreetMap. A live Streamlit dashboard shows their positions, trails and the shortest path between any two zones. A separate analytics dashboard shows zone activity over time and which named roads carry the most traffic.

We’ll also show how to adapt the entire system to a different city by changing a single configuration file. Additional example configuration files are provided for San Francisco and Singapore.

The Three-System Architecture

The architecture has three layers, each serving a different purpose.

Neo4j Aura is the graph layer. It holds the road network for Merton – 3,203 intersections and 7,278 road segments loaded from OpenStreetMap via the OSMnx library. Aura answers the questions that a relational database handles with difficulty:

• What’s the shortest path between two points along drivable roads?
• Which zones border which other zones?
• Which intersections are the most connected hubs of the network?

The native spatial index on intersection nodes makes nearest-neighbor lookups fast enough to run on every map refresh.

Databricks Lakebase is the operational layer. It’s a fully managed Postgres database, hosted inside Databricks, that accepts the high-frequency position writes from our vehicle simulator. Every two seconds, ten vehicles each write their current coordinates to a vehicle_positions table. Lakebase handles this with standard psycopg2 connectivity, foreign key constraints and BIGSERIAL auto-increment IDs. The token-based authentication enforces a natural session boundary, as the system runs for an hour at a time, which is the right behavior for a demo.

Databricks Lakehouse is the analytics layer. Position data flows from Lakebase into a Delta table, where we run aggregations, such as zone demand over time, position volume trends and the cross-system join that answers “which named roads carry the most traffic?” by combining Lakebase position data with Aura road names.

The relationship between these systems is as follows:

• Aura is where the structure lives – the road graph that neither Lakebase nor Lakehouse knows anything about.
• Lakebase is where data is written, in real time, by the simulator.
• Lakehouse is where we analyze the history that Lakebase accumulates.

Each system has a clear role and none of the roles overlap.

What Aura Adds

A natural question is: what does Aura add? We already have a Postgres database. Can’t we just store road network data there too? We could store it there. But querying it could be difficult. The shortest path between two points requires traversing a graph – following edges from node to node, keeping track of visited nodes and finding the minimum-cost sequence. In SQL this means recursive CTEs, which are slow for deep traversals. In Cypher, Aura’s query language, it’s a single function call. For example:

MATCH path = shortestPath((start)-[:ROAD*..300]->(end))
RETURN length(path) AS hops

The zone adjacency queries are similarly clean. “Which zones can be reached within two hops from Morden?” is a two-line Cypher query. In SQL it would require multiple self-joins or a recursive CTE that grows in complexity with each additional hop.

Also, the road network is a graph. Intersections are nodes. Roads are edges. The connectivity between them is the point. Storing that as rows and columns in a relational table is possible but goes against the natural structure of the data.

The Configuration File

Every city-specific value in the system lives in a single YAML file called config.yaml. The zone definitions, vehicle assignments, map coordinates and OpenStreetMap place name all come from this file. Switching from London to San Francisco or Singapore means copying a different config file into place and re-running the notebooks.

There are three example configs:

1. config.yaml – the London Borough of Merton (the default)
2. config_sf.yaml – five neighborhoods in southern San Francisco
3. config_sg.yaml – five planning areas in central Singapore

The supporting code in config_validator.py validates the config file on load and raises clear errors if anything is missing or malformed.

What You’ll Need

To follow along you’ll need accounts and access to these services:

1. Neo4j Aura – the free tier is sufficient. Create an account at Get Started for Free and note your URI, username and password.
2. Databricks – a free trial account gives us access to both Lakebase and Lakehouse. Create an account at Databricks Free Edition. Create a Lakebase project named vehicle-tracker and a SQL warehouse before running the notebooks.
3. Python – we’ll use Python 3.12 throughout. The notebooks run in a local Jupyter environment. A virtual environment is highly recommended. For example:

python3 -m venv ~/vehicle-tracker-env
source ~/vehicle-tracker-env/bin/activate

All dependencies are installed using %pip install cells at the top of each notebook, with pinned versions for stability.

Environment Variables

Credentials are passed to notebooks and the Streamlit applications using environment variables. Set these before starting Jupyter:

# Neo4j Aura
export NEO4J_URI="neo4j+s://xxxx.databases.neo4j.io"
export NEO4J_USERNAME="your-username"
export NEO4J_PASSWORD="your-password"

# Databricks Lakebase
export LAKEBASE_HOST="ep-xxxx.database.eu-west-1.cloud.databricks.com"
export LAKEBASE_USER="your-databricks-email"
export LAKEBASE_TOKEN="your-oauth-token"
export LAKEBASE_DBNAME="databricks_postgres"

# Databricks Lakehouse
export DATABRICKS_SERVER_HOSTNAME="dbc-xxxx.cloud.databricks.com"
export DATABRICKS_HTTP_PATH="/sql/1.0/warehouses/xxxx"
export DATABRICKS_TOKEN="your-personal-access-token"

Note that LAKEBASE_TOKEN expires after one hour. This is by design – it’s Databricks’s authentication model for OAuth-based access and it gives us a natural session boundary. When it expires, we need to generate a fresh token from the Lakebase Connect dialog in the Databricks UI and update the LAKEBASE_TOKEN environment variable.

For longer-running sessions, Chapter 4 explains how to set up and use a native Postgres role with a permanent password instead.

Chapter Overview

Here’s what each chapter covers:

Chapter 2: The Road Network walks through loading the Merton road network from OpenStreetMap into Aura using OSMnx. We’ll cover the data cleaning required to handle the quirks of OSM data, such as list-valued properties, duplicate edges and missing speed limits. We’ll also describe the Aura schema that makes spatial queries efficient.

Chapter 3: Zones and Graph Queries defines the five zones that divide the borough, assigns each intersection to its zone and builds the zone adjacency graph. We’ll show the Cypher queries that make the zone topology useful, such as nearest neighbors, multi-hop reachability and complex intersection counts.

Chapter 4: The Operational Layer sets up the Lakebase tables that hold live vehicle data. We’ll cover the table design, the indexes that make time-series queries fast and the credential options for connecting from Python.

Chapter 5: The Simulator writes a vehicle simulator that loads the road network from Aura, places ten vehicles in their home zones and moves each one along Breadth-First Search (BFS)-computed shortest paths. The simulator runs as a background process and writes position updates to Lakebase every two seconds.

Chapter 6: The Streamlit Apps builds two Streamlit dashboards. The first one shows vehicles moving on a live map with trail history and shortest path queries. The second one connects to Lakehouse to show zone activity trends and road segment traffic, updated every 30 seconds.

Chapter 7: Bringing It All Together covers the full system running end-to-end and ideas for going further.

Let’s start by loading the road network.

Chapter 2: The Road Network

What We Need From the Road Network

The road network is the foundation of the entire system. Without it, we can’t route vehicles, compute shortest paths between zones or tell a vehicle where to go next. Everything the simulator and the Streamlit application do depends on having an accurate, connected graph of the roads in a city.

We specifically need:

• Every road intersection as a node, with its GPS coordinates.
• Every drivable road segment as a directed edge, with its name, road type, speed limit and length.
• A spatial index on intersection coordinates so we can quickly find the nearest intersection to any GPS position.
• A connected graph, with no isolated subgraphs, so every vehicle can reach every other zone via some sequence of road segments.

OpenStreetMap gives us the raw data. OSMnx gives us the tools to download it, clean it and convert it to a graph we can load into Aura.

The Aura Graph Model

The road network maps naturally to Aura’s property graph model. Here’s what we’re storing:

(:Intersection {node_id, lat, lon, street_count, location})
    -[:ROAD {osmid, name, highway, maxspeed, oneway, length_m}]->
(:Intersection)

Each Intersection node represents a road junction, which is a point where two or more roads meet. The location property stores an Aura point() object, which enables native spatial queries (nearest-neighbor lookup, distance calculation) without any additional libraries.

Each ROAD relationship represents a directed road segment between two intersections. A one-way street from A to B is stored as a single relationship A -> B. A two-way street is stored as two relationships: A -> B and B -> A. The highway property holds the OSM road classification (primary, residential, motorway and so on) and maxspeed holds the posted speed limit where OSM data are available.

Downloading the Road Network

For Merton, we use OSMnx’s graph_from_place() function, which geocodes the place name via Nominatim, finds the boundary polygon in OpenStreetMap and downloads all drivable road segments within it:

G = ox.graph_from_place("London Borough of Merton, UK", network_type="drive")

The network_type="drive" filter keeps only roads that cars can use, excluding footpaths, cycleways and pedestrian areas.

For alternate cities, such as San Francisco and Singapore, where neighborhood polygon boundaries are either unavailable or produce disconnected subgraphs, we use a different approach: download a regional OSM data file from Geofabrik and clip it to a bounding box using pyrosm. This is covered in notebook 00_prepare_osm.ipynb, which runs once before the road network notebook to prepare the clipped file.

The city config file (config.yaml) tells notebook 2 which approach to use:

city:
  osmnx_place: "London Borough of Merton, UK"  # Merton -- direct download
  # osm_file: "singapore-central.osm.pbf"      # Singapore -- local file

If osm_file is present, the notebook loads from the local clipped file. Otherwise it uses graph_from_place().

Cleaning the Data

OSMnx returns a MultiDiGraph, which is a directed graph where edges can have multiple attributes, some of which are lists rather than scalars. In the OSM data model, a single road segment can carry multiple OSM tag values when the underlying data are ambiguous or inconsistent.

Before loading into Aura, we need to clean three things:

1. List-valued properties. The osmid, name, highway and maxspeed fields can be Python lists rather than single values. We take the first element in each case:

def flatten(val):
    if isinstance(val, list):
        return val[0]
    return val

2. Speed limit strings. OSM stores maxspeed as a string ("50 mph" or just "50") rather than a number. We parse it to an integer, stripping the unit suffix:

def parse_maxspeed(val):
    try:
        return int(str(val).replace(" mph", "").strip())
    except (ValueError, AttributeError):
        return None

3. NaN values. Pandas uses float('nan') for missing values, but the Neo4j Python driver doesn’t accept NaN. We convert all NaN values to None before loading:

rows = df.where(pd.notnull(df), None).to_dict("records")

Deduplicating Edges

OSMnx assigns the same osmid to multiple edges in two cases:

1. Bidirectional roads, where the same OSM way appears as both A -> B and B -> A.
2. Segmented ways, where a single named road is split into multiple segments sharing one OSM ID.

The composite key osmid + u + v (where u and v are the source and target node IDs) is usually unique, but not always, as some split ways produce multiple segments with the same u, v and osmid but different lengths. We deduplicate by keeping the longest segment for each (osmid, u, v) triple:

edges_clean = (
    edges_clean
    .sort_values("length_m", ascending=False)
    .drop_duplicates(subset=["osmid", "u", "v"], keep="first")
)

This reduces Merton’s 7,317 raw edges to 7,278 clean edges with duplicates removed.

Loading Into Aura

We load nodes and edges in separate passes, using MERGE to make the operation idempotent. Running the notebook twice won’t create duplicate nodes.

Nodes. Each intersection becomes an Aura node with a uniqueness constraint on node_id:

UNWIND $rows AS row
MERGE (i:Intersection {node_id: row.node_id})
SET i.lat          = row.lat,
    i.lon          = row.lon,
    i.street_count = row.street_count,
    i.location     = point({latitude: row.lat, longitude: row.lon})

The location property stores a native Aura spatial point, which enables the point.distance() function used later for nearest-driver queries.

Edges. Each road segment becomes a ROAD relationship. The composite MERGE key uses osmid, u and v together to uniquely identify each directed edge:

UNWIND $rows AS row
MATCH (a:Intersection {node_id: row.u})
MATCH (b:Intersection {node_id: row.v})
MERGE (a)-[r:ROAD {osmid: row.osmid, u: row.u, v: row.v}]->(b)
SET r.name     = row.name,
    r.highway  = row.highway,
    r.maxspeed = row.maxspeed,
    r.oneway   = row.oneway,
    r.length_m = row.length_m

We load in batches of 500 rows at a time, using a progress bar to track progress. For Merton this load is quite fast. For San Francisco and Singapore it takes longer.

Clearing Before Reload

When switching cities, we need to clear the existing graph before loading a new one. The clear step runs before the constraint and index setup:

MATCH (n)
CALL (n) { DETACH DELETE n } IN TRANSACTIONS OF 1000 ROWS

The Spatial Index

The POINT INDEX on location is created before loading nodes:

CREATE POINT INDEX intersection_location IF NOT EXISTS
FOR (i:Intersection) ON (i.location)

Creating it before the data load means the index is populated incrementally as nodes are inserted.

Verifying the Result

After loading, we verify the counts match expectations and inspect the longest named roads:

Intersections : 3,203
Roads         : 7,278

Longest named roads:
  Home Park Road                 residential  20.0 mph   1013.41 m
  Home Park Road                 residential  20.0 mph   1013.41 m
  Croydon Road                   primary      30.0 mph   982.52 m
  Croydon Road                   primary      30.0 mph   982.52 m
  Croydon Road                   primary      30.0 mph   865.69 m

The duplicate entries reflect different directed edges sharing the same display attributes. For example, two segments of a road traveling in opposite directions. The graph data in Aura is correct.

For Merton, we expect 3,203 intersections and 7,278 roads after deduplication. Home Park Road, at over a kilometre, is a residential street. Croydon Road, at 30 mph, is a local connector across parts of the borough.

Alternate Cities

For San Francisco and Singapore, the road network is loaded from a pre-clipped OSM file rather than using the Overpass API. The 00_prepare_osm.ipynb notebook handles downloading the regional file from Geofabrik and clipping it to the bounding box defined in the city config. You can regenerate these files at any time by running that notebook with the appropriate config.

The clipped files are included in the GitHub repo, so you won’t need to run the prepare notebook.

San Francisco’s graph is larger than Merton’s because the area covered is larger and its road network is denser. Singapore’s graph is similar in size to Merton. Both are fully connected, with a single strongly connected component, which means every vehicle can reach every other zone via directed road segments.

Gotchas

OSMnx version compatibility. OSMnx 2.1.1 requires Shapely 2.1.x. Earlier Shapely versions (2.0.x) were built against NumPy 1.x and fail with TypeError when OSMnx calls union_all() internally. The fix is to pin Shapely to 2.1.2 or later.

List-valued edge properties. OSMnx can return osmid, name, highway and maxspeed as Python lists rather than scalars for some edges. Always flatten before loading. The flatten() helper takes the first element of any list and returns non-list values unchanged.

NaN vs None. The Neo4j Python driver doesn’t accept float('nan'). Call df.where(pd.notnull(df), None) before converting to records.

Edge deduplication. The composite key osmid + u + v is not always unique. Sort by length descending and drop duplicates before loading.

Cypher 25 syntax. Use CALL (r) { DELETE r } IN TRANSACTIONS not the older CALL { WITH r DELETE r } form, which is deprecated and produces warnings.

Speed limits in alternate cities. OSM stores speed limits as numbers without units. For UK cities the values are in mph. For other countries they’re in km/h, but the field name is the same.

pbf format. Geofabrik distributes OSM data in Protocol Buffer format (.osm.pbf). This is a binary format that OSMnx’s graph_from_xml() function can’t read directly, as it expects plain OSM XML. Use pyrosm to read .pbf files.

nx.compose() produces disconnected subgraphs. Composing multiple neighborhood polygon graphs with nx.compose() can produce isolated subgraphs if the constituent polygons don’t share road edges at their boundaries. This causes shortestPath() to return no result between nodes in different subgraphs. Use pyrosm with a bounding box to get a single connected graph instead.

Not all place names have OSM polygon boundaries. ox.graph_from_place() requires Nominatim to return a polygon boundary for the place name. Neighborhood names in particular often return only a point. Test with ox.geocode_to_gdf(place) and check that geom_type is Polygon or MultiPolygon before using a place name in your config.

nx.compose() node count is misleading. len(G.nodes) on the first graph in a compose chain returns only that graph’s node count. After composing multiple graphs, Aura receives the full union of all node IDs. The final Aura intersection count is correct; the intermediate len(G.nodes) is not.

Chapter 3: Zones and Graph Queries

Why Zones?

The road network we loaded in Chapter 2 is a graph of thousands of intersections connected by thousands of directed road segments. That’s a detailed and accurate model of Merton’s road network, but it’s not enough for a vehicle tracking system on its own.

A dispatcher needs to think in terms of areas, not individual intersections. “Send the nearest vehicle in Colliers Wood to the pickup in Mitcham” is a meaningful instruction. “Send the vehicle at node 65304090 to node 229523558” is not. We need to group the road network into named zones that the simulator, the dispatcher logic and the dashboard can all reason about.

We also need to know which zones border which other zones. The simulator uses this to decide where to send a vehicle next, with a 70% chance of staying in or near the home zone and a 30% chance of crossing into any adjacent zone. Without an explicit zone adjacency graph, the simulator would have to compute this at runtime from intersection coordinates, which is slower and more error-prone.

The Zone Model

We define five logical zones for Merton, each as a simple bounding box:

These are logical simplified zones for the demo, not official neighborhood boundary polygons. Merton’s Local Plan recognizes a richer set of neighborhoods including South Wimbledon, Wimbledon Park, Merton Park and Lower Morden, none of which are represented here. The bounding boxes are a deliberate simplification that keeps the configuration transparent and easy to adjust.

Intersections that fall outside all five boxes – boundary edge cases from floating-point coordinates – are assigned to an Unknown zone automatically. In practice this is just one intersection for Merton.

All zone definitions live in config.yaml. Switching to a different city means changing the config file; the notebook code itself is city-agnostic.

Adding Zones to Aura

We extend the graph model from Chapter 2 with two new elements:

(:Zone {name, lat_min, lat_max, lon_min, lon_max})
(:Intersection)-[:IN_ZONE]->(:Zone)
(:Zone)-[:ADJACENT_TO]->(:Zone)

Each Zone node stores its bounding box coordinates. The IN_ZONE relationship connects each intersection to its zone. The ADJACENT_TO relationship connects zones that share a boundary, added manually based on geography, stored bidirectionally.

Assigning Intersections to Zones

The zone assignment query runs entirely in Cypher. For each intersection, it finds the zone whose bounding box contains the intersection’s coordinates and creates an IN_ZONE relationship:

MATCH (i:Intersection)
MATCH (z:Zone)
WHERE i.lat >= z.lat_min AND i.lat < z.lat_max
  AND i.lon >= z.lon_min AND i.lon < z.lon_max
MERGE (i)-[:IN_ZONE]->(z)
RETURN count(*) AS assigned

Any intersection not matched by this query goes to the Unknown zone:

MATCH (i:Intersection)
WHERE NOT (i)-[:IN_ZONE]->()
MATCH (z:Zone {name: "Unknown"})
MERGE (i)-[:IN_ZONE]->(z)

For Merton, this assigns nearly all intersections to named zones and 1 to Unknown.

Zone Adjacency

The adjacency relationships define which zones border which others. For Merton, Wimbledon acts as the hub, as it borders both Raynes Park to the west and Colliers Wood to the east. The remaining chain runs Colliers Wood -> Mitcham -> Morden:

Wimbledon
   |    \
Raynes    Colliers Wood
Park          |
            Mitcham
              |
            Morden

We store adjacency bidirectionally and a single pair [Wimbledon, Colliers Wood] in the config generates two ADJACENT_TO relationships in Aura:

UNWIND $pairs AS pair
MATCH (a:Zone {name: pair.a})
MATCH (b:Zone {name: pair.b})
MERGE (a)-[:ADJACENT_TO]->(b)
MERGE (b)-[:ADJACENT_TO]->(a)

Verifying the Zone Graph

After loading, we verify both the intersection counts per zone and the adjacency structure:

Intersections per zone:
  Wimbledon       839
  Raynes Park     761
  Mitcham         723
  Colliers Wood   544
  Morden          335
  Unknown           1
  Total         3,203

Zone adjacency:
  Colliers Wood  -> Mitcham, Wimbledon
  Mitcham        -> Colliers Wood, Morden
  Morden         -> Mitcham
  Raynes Park    -> Wimbledon
  Wimbledon      -> Colliers Wood, Raynes Park

Wimbledon has the most intersections because it’s the largest zone by area. Morden has the fewest but it’s still more than enough for the simulator to route vehicles meaningfully.

Graph Queries on the Zone Topology

With zones and adjacency loaded, we can ask questions that would be challenging in a relational database.

Which zones border Wimbledon?

MATCH (z:Zone {name: 'Wimbledon'})-[:ADJACENT_TO]->(neighbour:Zone)
RETURN neighbour.name AS zone
ORDER BY zone

Result: Colliers Wood, Raynes Park.

Which zones can a vehicle reach within two hops from Morden?

MATCH (z:Zone {name: 'Morden'})-[:ADJACENT_TO*1..2]->(reachable:Zone)
WHERE reachable.name <> 'Morden'
RETURN DISTINCT reachable.name AS zone
ORDER BY zone

Result: Colliers Wood, Mitcham, Raynes Park, Wimbledon. All four other zones are reachable within two zone-hops, which means the zone graph is fully connected and a vehicle starting anywhere can reach any other zone.

How many complex intersections are in Wimbledon?

MATCH (i:Intersection)-[:IN_ZONE]->(z:Zone {name: 'Wimbledon'})
WHERE i.street_count >= 3
RETURN count(i) AS complex_intersections

Result: 635. About 76% of Wimbledon’s 839 intersections connect three or more roads, which reflects the dense residential street grid in this part of London.

Shortest Path Between Zones

The most powerful query the zone graph enables is shortest path between two named zones. The Streamlit application uses this for the “Find shortest path” feature – the user selects two zones, the app finds the nearest intersection to each zone’s centre point and then asks Aura for the shortest directed path between them along ROAD relationships:

MATCH (start:Intersection {node_id: $start_id}),
      (end:Intersection {node_id: $end_id})
MATCH path = shortestPath((start)-[:ROAD*..300]->(end))
RETURN [node IN nodes(path) | [node.lat, node.lon]] AS coords,
       length(path) AS hops

The *..300 hop limit prevents the query from running indefinitely on disconnected graphs. For Merton’s well-connected network, shortest paths are typically 20-100 hops. The result is a sequence of coordinates that the Streamlit application draws as a line on the map.

Finding the nearest intersection to a zone centre uses Aura’s spatial index:

MATCH (i:Intersection)
RETURN i.node_id AS node_id
ORDER BY point.distance(
    i.location,
    point({latitude: $lat, longitude: $lon})
) ASC
LIMIT 1

This runs in milliseconds because the POINT INDEX from Chapter 2 makes the spatial lookup efficient.

Why Store Zones in Aura?

We could store zone assignments in Postgres alongside the vehicle positions. The simulator could look up each vehicle’s zone by querying the Lakebase vehicles table. Why put zones in Aura instead?

Two reasons. First, the adjacency queries are natural graph traversals. “Zones reachable within two hops” is a recursive query that SQL handles with CTEs, which can be verbose and slow for deeper traversals. In Cypher it’s a single line with a variable-length path pattern.

Second, the shortest path query crosses zone boundaries. The zone context, which intersections belong to which zone, enriches the result without requiring a join to a separate database. Everything the path query needs is in the same graph.

Zone-Awareness in the Simulator

The simulator uses zone membership in two ways. At startup, it reads zone membership counts from Aura to confirm the graph loaded correctly:

Zone distribution: {'Wimbledon': 839, 'Raynes Park': 761, ... }

At runtime, it determines the current_zone of each vehicle by checking which zone the vehicle’s current intersection belongs to. This is achieved with an in-memory dictionary built from Aura data at startup, not a live Aura query per tick, so it adds no latency to the simulation loop.

The zone adjacency graph from the config drives the routing bias: 70% of the time a vehicle’s next destination is chosen from its current zone or an adjacent zone and 30% of the time from anywhere in the borough. This creates realistic clustering and vehicles tend to stay in their home area but occasionally make longer cross-borough runs.

Gotchas

Bounding box zones are simplified. The zone bounding boxes are logical demo zones, not official neighborhood boundaries. For Merton, the Local Plan recognizes a richer set of neighborhoods. For Singapore, the URA planning areas have irregular polygon boundaries. For San Francisco, the city publishes official neighborhood boundary maps. The YAML zones are a deliberate simplification for the demo.

Switching cities requires a full Aura wipe. ROAD relationships, Intersection nodes and Zone nodes all need to be cleared before loading a new city. Clearing only ROAD relationships leaves orphaned Intersection nodes from the previous city, which then get mixed into the zone assignment for the new city.

Chapter 4: The Operational Layer

What the Operational Layer Does

Aura holds the road network, which is the structure of the city. But while vehicles are moving, we need somewhere to record what’s actually happening: where each vehicle is right now, where it’s been and what state it’s in. This is the operational layer.

The operational layer needs to handle high-frequency writes. Ten vehicles writing their position every two seconds means 300 writes per minute, sustained for as long as the simulator runs. It also needs to serve the Streamlit application, which reads the latest position of every vehicle on every refresh. These are classic OLTP characteristics: small, frequent reads and writes, low latency and strong consistency.

Databricks Lakebase is a managed Postgres database hosted inside Databricks. It handles the operational workload exactly as a production Postgres instance would, using standard psycopg2 connectivity from Python. Credentials are obtained from the Lakebase Connect dialog in the Databricks UI.

Creating the Lakebase Project

Before running this notebook, you’ll need a Lakebase project named vehicle-tracker in your Databricks workspace. The project name is deliberately generic – it’s city-agnostic, so the same project works whether you’re running the Merton, San Francisco or Singapore config. When you switch cities, you re-run this notebook to drop and recreate the tables; the project itself stays the same.

Create the project from the Lakebase section of the Databricks UI.

Setting Up Credentials

Lakebase supports two connection methods.

Default Using OAuth Token

The OAuth token expires after one hour. This is intentional – it’s Databricks’s authentication model and it enforces a natural session boundary. For a demo, this is actually useful as the system stops writing after an hour unless you actively renew it. When the token expires, generate a fresh one from the Lakebase Connect dialog in the Databricks UI and update LAKEBASE_TOKEN before reconnecting.

Native Postgres Password

For longer-running sessions, Lakebase supports native Postgres password authentication. Enable it from the Lakebase UI: Settings -> Database connections -> check “Allow Password (Native Postgres roles)”.

Then create a dedicated application role in the Lakebase SQL editor:

-- 1. Create the application role
CREATE ROLE vehicle_tracker
    LOGIN
    PASSWORD 'your-strong-password';

-- 2. Allow access to the public schema
GRANT USAGE
    ON SCHEMA public
    TO vehicle_tracker;

-- 3. Grant CRUD access to existing tables
GRANT SELECT, INSERT, UPDATE, DELETE
    ON ALL TABLES IN SCHEMA public
    TO vehicle_tracker;

-- 4. Grant sequence access to existing sequences
GRANT USAGE, SELECT
    ON ALL SEQUENCES IN SCHEMA public
    TO vehicle_tracker;

-- 5. Identify the role that creates the database objects
SELECT current_user;

-- 6. Grant CRUD access to future tables created by that role
ALTER DEFAULT PRIVILEGES FOR ROLE <your-databricks-username>
    IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE
    ON TABLES
    TO vehicle_tracker;

-- 7. Grant sequence access to future sequences
ALTER DEFAULT PRIVILEGES FOR ROLE <your-databricks-username>
    IN SCHEMA public
    GRANT USAGE, SELECT
    ON SEQUENCES
    TO vehicle_tracker;

Run SELECT current_user; first (step 5), note the result and substitute it into steps 6 and 7. Steps 6 and 7 are important, as without them, tables recreated when you switch cities (by re-running this notebook) won’t automatically get the right permissions.

Replace LAKEBASE_TOKEN with LAKEBASE_PASSWORD in all connection code and set:

export LAKEBASE_USER="vehicle_tracker"
export LAKEBASE_PASSWORD="your-strong-password"

The password doesn’t expire, so you won’t need to refresh it between sessions.

The Three Tables

The operational layer uses three tables. Their design reflects the different access patterns each one serves.

vehicles

CREATE TABLE vehicles (
    vehicle_id  TEXT PRIMARY KEY,
    driver_name TEXT NOT NULL,
    zone        TEXT NOT NULL,
    status      TEXT NOT NULL DEFAULT 'idle',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
)

One row per vehicle. This table changes slowly and the simulator updates status between idle and en_route as vehicles start and finish routes, but the vehicle IDs, driver names and home zones don’t change during a run. The Streamlit application reads this table to populate the driver list in the nearest-driver panel.

vehicle_positions

CREATE TABLE vehicle_positions (
    position_id  BIGSERIAL PRIMARY KEY,
    vehicle_id   TEXT NOT NULL REFERENCES vehicles(vehicle_id),
    lat          DOUBLE PRECISION NOT NULL,
    lon          DOUBLE PRECISION NOT NULL,
    speed_kmh    DOUBLE PRECISION,
    current_zone TEXT,
    recorded_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
)

One row per position update. This is the high-frequency table and the simulator inserts a new row for every vehicle on every tick. The BIGSERIAL primary key auto-increments and recorded_at defaults to the current timestamp. The current_zone column records which zone the vehicle is in at the time of the update, written by the simulator from its in-memory zone lookup. The Streamlit application reads the latest position per vehicle from this table on every refresh.

trips

CREATE TABLE trips (
    trip_id      TEXT PRIMARY KEY,
    vehicle_id   TEXT REFERENCES vehicles(vehicle_id),
    pickup_lat   DOUBLE PRECISION NOT NULL,
    pickup_lon   DOUBLE PRECISION NOT NULL,
    dropoff_lat  DOUBLE PRECISION NOT NULL,
    dropoff_lon  DOUBLE PRECISION NOT NULL,
    pickup_zone  TEXT,
    dropoff_zone TEXT,
    status       TEXT NOT NULL DEFAULT 'requested',
    requested_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    started_at   TIMESTAMPTZ,
    completed_at TIMESTAMPTZ
)

One row per trip, tracking the full lifecycle from request to completion. The simulator doesn’t write trips in this demo and the table is included in the schema because it’s what a production system would need and it’s a natural extension point for readers who want to add trip dispatch logic.

Indexes

Three indexes support the primary access patterns:

-- Latest positions per vehicle (used by every Streamlit map refresh)
CREATE INDEX idx_vehicle_positions_vehicle_id
ON vehicle_positions (vehicle_id, recorded_at DESC);

-- Vehicle lookup by status and zone (used by nearest-driver query)
CREATE INDEX idx_vehicles_status_zone
ON vehicles (status, zone);

-- Trip lookup by status (used by dispatch logic)
CREATE INDEX idx_trips_status
ON trips (status);

The composite index on vehicle_positions(vehicle_id, recorded_at DESC) is the most important, as it makes the “latest position per vehicle” query fast regardless of how many rows the table contains. Without it, every map refresh would scan the entire table.

Seeding Vehicles

The vehicles are seeded from the city config. Ten vehicles, two per zone, with names chosen to reflect the international character of each city’s driver population:

vehicles = [
    (v["id"], v["driver"], v["zone"])
    for v in cfg["vehicles"]
]
cursor.executemany("""
    INSERT INTO vehicles (vehicle_id, driver_name, zone)
    VALUES (%s, %s, %s)
""", vehicles)

• For Merton, the zones are Wimbledon, Raynes Park, Colliers Wood, Mitcham and Morden.
• For San Francisco, the zones are Noe Valley, Castro, Glen Park, Bernal Heights and Visitacion Valley.
• For Singapore, the zones are Toa Payoh, Bishan, Ang Mo Kio, Serangoon and Novena.

Connecting to Lakebase

The psycopg2 connection requires SSL and uses port 5432:

conn = psycopg2.connect(
    host     = os.environ["LAKEBASE_HOST"],
    user     = os.environ["LAKEBASE_USER"],
    password = os.environ["LAKEBASE_TOKEN"],
    dbname   = os.environ["LAKEBASE_DBNAME"],
    sslmode  = "require",
    port     = 5432
)
conn.autocommit = True

autocommit = True is required for DDL statements (CREATE TABLE, DROP TABLE, CREATE INDEX). Without it, DDL runs inside a transaction and some statements fail silently.

Verifying the Setup

After seeding, we verify the table row counts and vehicle distribution:

vehicles             10 rows
vehicle_positions     0 rows
trips                 0 rows

Vehicles per zone:
  Colliers Wood   2
  Mitcham         2
  Morden          2
  Raynes Park     2
  Wimbledon       2

Zero rows in vehicle_positions is correct at this stage – the simulator hasn’t run yet. After Chapter 5, this table will have thousands of rows.

Gotchas

Token expiry. The OAuth token expires after one hour. If you’re running the simulator for longer sessions, either refresh the token or set up native Postgres password authentication as described above.

Drop order. When re-running the notebook to recreate tables, the drop order matters because of foreign key constraints. Drop trips first, then vehicle_positions, then vehicles. Reversing the order fails with a constraint violation.

autocommit. Set conn.autocommit = True immediately after connecting. If you forget, DDL statements may appear to succeed but then silently roll back when the connection closes.

ALTER DEFAULT PRIVILEGES. If you switch to native password authentication and later re-run this notebook to recreate tables (when switching cities), the new tables won’t automatically inherit the permissions unless you ran the ALTER DEFAULT PRIVILEGES steps. This is the most common gotcha with Postgres role setup – existing grants cover existing tables; default privileges cover future ones.

Free tier daily limit. Databricks free accounts have a “free daily limit” on usage. If you hit this limit, the connection will be refused. Wait until the next day or contact Databricks support.

Chapter 5: The Simulator

What the Simulator Does

The simulator is the engine that gives life to the system. It loads the road network from Aura into memory, places ten vehicles at their home intersections and then moves each one along the road graph, writing a GPS position to Lakebase every two seconds.

The simulator has four responsibilities:

1. Load the road graph from Aura once at startup into an in-memory adjacency structure.
2. Compute an initial route for each vehicle using Breadth-First Search (BFS).
3. Move each vehicle one intersection at a time along its route, writing a position record to Lakebase at each tick.
4. Assign a new destination when a vehicle reaches the end of its current route.

It runs as a background subprocess, started from the Jupyter notebook and continues independently while you use the Streamlit dashboard.

Running as a Background Process

The simulator uses the %%writefile pattern – the Jupyter cell writes the simulator source code to simulator.py on disk and then a second cell launches it as a subprocess:

proc = subprocess.Popen(
    [sys.executable, "-u", "simulator.py"],
    stdout=subprocess.PIPE,
    stderr=subprocess.STDOUT,
    text=True,
    env={**os.environ, "PYTHONUNBUFFERED": "1"}
)

Two details are important here:

1. sys.executable uses the virtual environment’s Python interpreter; using the bare python command would pick up the system Python, which doesn’t have neo4j or psycopg2 installed.
2. -u (unbuffered) combined with PYTHONUNBUFFERED=1 forces Python to flush output immediately rather than buffering it, which means we can see the simulator’s startup messages in the notebook.

After launch, a sentinel loop reads lines from the subprocess until it sees “Simulator running”:

for line in proc.stdout:
    print(line, end="")
    if "Simulator running" in line:
        break
print("Simulator is running in the background.")

Once the sentinel fires, the loop exits and the simulator continues independently in the background. The notebook cell completes, leaving the simulator process alive.

Loading the Road Graph

At startup, the simulator connects to Aura and loads the entire road graph into memory:

with driver.session() as session:
    result = session.run("""
        MATCH (a:Intersection)-[r:ROAD]->(b:Intersection)
        RETURN a.node_id AS u, b.node_id AS v, r.length_m AS length_m
    """)
    for rec in result:
        graph[rec["u"]].append((rec["v"], rec["length_m"]))

The result is a plain Python dictionary: {node_id: [(neighbour_id, length_m), ...]}. With Merton’s edges, this takes a second or two and uses negligible memory. All routing from this point runs entirely in this in-memory structure and no further Aura queries happen during the simulation loop.

Loading the full graph into memory rather than querying Aura per tick is a deliberate design choice. An Aura query per tick would add latency and load to Aura unnecessarily. The road network doesn’t change between ticks, so there’s no reason to re-read it.

BFS Routing

Each vehicle follows a route computed by breadth-first search. BFS finds the shortest path in terms of number of hops (intersections traversed), not physical distance. For a vehicle tracking demo, hop count is close enough to physical distance to produce realistic-looking movement.

The routing function enforces a minimum path length of 20 hops. Shorter routes would cause vehicles to reach their destination almost immediately and spend most of their time idle, which makes for a less interesting demo. If BFS can’t find a path of 20 or more hops in the given direction, it falls back to the longest path it found and if it can’t find any path at all (which can happen in graphs with isolated components), it uses a very short local fallback:

def bfs_route(graph, start, min_hops=20):
    queue = deque([(start, [start])])
    best = None
    while queue:
        node, path = queue.popleft()
        if len(path) >= min_hops:
            return path
        if best is None or len(path) > len(best):
            best = path
        for neighbour, _ in graph.get(node, []):
            if neighbour not in path:
                queue.append((neighbour, path + [neighbour]))
    return best or [start]

Zone Bias

Vehicles don’t route randomly across the entire borough. They have a home zone and 70% of the time their next destination is chosen from intersections in their home zone or an adjacent zone. The remaining 30% of the time, the destination is any intersection in the borough. This creates realistic clustering, as vehicles tend to work their home area, with occasional longer cross-borough runs.

The zone adjacency from the config drives this. The zone_adjacency() helper function from config_validator.py returns a dictionary mapping each zone name to a list of that zone and its neighbors:

ZONE_ADJACENCY = zone_adjacency(cfg)
# {'Wimbledon': ['Wimbledon', 'Raynes Park', 'Colliers Wood'], ...}

When assigning a destination, the simulator picks a zone with 70/30 weighting, then chooses a random intersection from that zone’s node list.

The Main Loop

The simulator runs a simple tick loop. Each tick:

1. Checks for a stop signal (discussed below).
2. Reconnects to Lakebase if the connection dropped.
3. Advances each vehicle one step along its route.
4. Writes a position record to Lakebase.
5. Assigns a new destination if the vehicle has reached the end of its route.
6. Sleeps for approximately two seconds, with a small random jitter.

The tick jitter (±0.3 seconds, randomized per tick) prevents all ten vehicles from writing simultaneously, which would create brief spikes in Lakebase write load. Staggered writes produce a smoother stream of position records.

tick_sleep = TICK_SECONDS + random.uniform(-TICK_JITTER, TICK_JITTER)
time.sleep(tick_sleep)

Writing Positions to Lakebase

Each position write is a single SQL INSERT:

cursor.execute("""
    INSERT INTO vehicle_positions
        (vehicle_id, lat, lon, speed_kmh, current_zone)
    VALUES (%s, %s, %s, %s, %s)
""", (vehicle_id, lat, lon, speed_kmh, current_zone))

The current_zone is determined from the in-memory zone lookup and not an Aura query. At startup, the simulator builds a dictionary mapping each node_id to its zone name, loaded from Aura:

result = session.run("""
    MATCH (i:Intersection)-[:IN_ZONE]->(z:Zone)
    RETURN i.node_id AS node_id, z.name AS zone
""")
node_zone = {rec["node_id"]: rec["zone"] for rec in result}

The current_zone value is then just node_zone.get(current_node_id, "Unknown") on every tick.

Lakebase Reconnection

The simulator handles Lakebase disconnections gracefully. The OAuth token expires after one hour and the connection will drop when it does. Rather than crashing, the simulator reconnects at the start of each tick:

def pg_reconnect(conn):
    try:
        conn.cursor().execute("SELECT 1")
        return conn
    except Exception:
        return psycopg2.connect(...)

If the reconnection itself fails (for example, because the token has expired and hasn’t been refreshed), the simulator logs the error and retries on the next tick. Vehicles continue to route in memory; only the Lakebase writes are paused until connectivity is restored.

Stopping the Simulator

The simulator checks for a stop signal at the top of each tick. The Streamlit analytics dashboard’s “Stop Simulator” button writes a small flag file called simulator.stop to disk. The simulator detects it, deletes it and exits cleanly:

if os.path.exists("simulator.stop"):
    os.remove("simulator.stop")
    print("\nStop signal received -- shutting down.")
    raise KeyboardInterrupt

This is a simple inter-process communication pattern – no sockets, no message queues, just a file on the shared filesystem. It works because both the Streamlit application and the simulator run in the same directory. The KeyboardInterrupt triggers the existing clean shutdown code, which resets all vehicle statuses to idle in Lakebase before the process exits.

Alternatively, the simulator can be stopped from the Jupyter notebook by interrupting the kernel or running:

proc.terminate()

Dead-End Detection

Some intersections in the road graph are dead ends as they have no outgoing edges or all their outgoing edges lead back to nodes already visited in the current route. When BFS gets stuck at a dead end, it returns the shortest path it found rather than an empty list. The simulator detects a one-node path (just the current intersection, with no next step) and assigns a new destination immediately:

if len(state["path"]) == 1:
    # Dead end -- assign new destination
    state["path"] = assign_destination(vehicle_id, current_node)

Dead ends are more common in the San Francisco and Singapore graphs, where some streets connect to motorway on-ramps or service roads with no exit back into the local network.

Startup Output

When the simulator starts, it prints a summary of the zone distribution and the initial route assignment for each vehicle:

Simulator started (PID 12313)
Using Python: /Users/veryfatboy/vehicle-tracker-env/bin/python3.12
Connecting to Neo4j Aura...
Loaded 3,203 nodes and 7,278 directed edges
Zone distribution: {'Wimbledon': 839, 'Raynes Park': 761, 'Colliers Wood': 544, 'Mitcham': 723, 'Morden': 335, 'Unknown': 1}
Neo4j Aura connection closed after graph load.

Connecting to Lakebase...

Placing vehicles and computing initial routes...
  V001: route 32 hops -> Colliers Wood
  V002: route 36 hops -> Colliers Wood
  V003: route 27 hops -> Wimbledon
  V004: route 38 hops -> Raynes Park
  V005: route 32 hops -> Wimbledon
  V006: route 51 hops -> Wimbledon
  V007: route 34 hops -> Mitcham
  V008: route 62 hops -> Colliers Wood
  V009: route 67 hops -> Mitcham
  V010: route 38 hops -> Morden

Simulator running. Tick every ~2s.

Simulator is running in the background.

The hop count shows how long each vehicle’s initial route is. Routes of 20-80 hops are typical for Merton. Occasionally a vehicle gets a very long route (100+ hops) if BFS finds a winding path through a dense part of the network, which is normal and produces interesting movement on the map.

Verifying the Simulator

A cell at the end of the notebook connects directly to Lakebase and checks that position records are being written:

Total position records : 7,010

Latest position per vehicle:
  Vehicle           Lat        Lon  Zone            Recorded at
  V001         51.41006   -0.19241  Wimbledon       2026-09-03 ...
  V002         51.42684   -0.19065  Wimbledon       2026-09-03 ...
  V003         51.41134   -0.17311  Raynes Park     2026-09-03 ...
  V004         51.41389   -0.18370  Raynes Park     2026-09-03 ...
  V005         51.39998   -0.19312  Colliers Wood   2026-09-03 ...
  V006         51.39934   -0.18377  Colliers Wood   2026-09-03 ...
  V007         51.40229   -0.14242  Mitcham         2026-09-03 ...
  V008         51.41002   -0.16028  Raynes Park     2026-09-03 ...
  V009         51.39037   -0.21811  Morden          2026-09-03 ...
  V010         51.38796   -0.20437  Morden          2026-09-03 ...

The total record count grows continuously as the simulator runs – ten vehicles writing every two seconds. The latest position per vehicle confirms each is active and reporting from the correct zone.

Gotchas

sys.executable vs python. Always use sys.executable in the Popen call. The bare python or python3 command uses the system Python, which won’t have the project’s dependencies installed.

Output buffering. Without -u and PYTHONUNBUFFERED=1, the subprocess buffers its output and nothing appears in the notebook. Both flags are required.

readline() deadlock. If you loop on proc.stdout.readline() without a sentinel, the loop blocks indefinitely once the simulator is running (because the simulator is producing output, not closing stdout). Always break on a sentinel string.

Stop flag file location. The simulator.stop flag file must be created in the same directory from which the simulator is running. If the Streamlit analytics application and the simulator are started from different directories, the button won’t work. Run everything from the project root.

Fallback routes. A “fallback route (short path)” message at startup means BFS couldn’t find a 20-hop path from that vehicle’s starting position. This happens most often in sparse zones or zones with many dead ends. The vehicle will still move; it just won’t travel as far before needing a new destination.

Chapter 6: The Streamlit Apps

Two Dashboards

The system has two Streamlit dashboards. The vehicle tracker (app.py, port 8501) shows vehicles moving on a live map with trail history, nearest-driver lookup and shortest path queries between zones. The analytics dashboard (analytics_app.py, port 8502) shows zone activity over time and the busiest road segments, updated every 30 seconds from a Lakehouse Delta table.

Run them alongside the simulator:

streamlit run app.py
streamlit run analytics_app.py --server.port 8502

Both read from config.yaml at startup, so switching cities means updating the config and restarting both apps.

The Vehicle Tracker

Auto-Refresh

The vehicle tracker refreshes every three seconds. streamlit-autorefresh handles this with a single call at the top of the script:

st_autorefresh(interval=3000, key="map_refresh")

Every three seconds the entire script re-runs from top to bottom, re-querying Lakebase for the latest positions and redrawing the map. Streamlit’s session state preserves the user’s selected zones and the last shortest path across refreshes.

Connections

Both the Neo4j driver and the Lakebase connection are cached with @st.cache_resource. This means they’re created once when the application starts and reused on every refresh, rather than opening a new connection every three seconds:

@st.cache_resource
def get_neo4j_driver():
    return GraphDatabase.driver(
        os.environ["NEO4J_URI"],
        auth=(os.environ["NEO4J_USERNAME"], os.environ["NEO4J_PASSWORD"])
    )

The application checks whether the connection is alive before using it and reconnects if needed. This handles the case where the Lakebase OAuth token expires mid-session and the next refresh detects the dropped connection and reconnects automatically.

The Map

The map uses pydeck with a CARTO Voyager basemap – no Mapbox token required:

map_style = "https://basemaps.cartocdn.com/gl/voyager-gl-style/style.json"

Three pydeck layers sit on top of the basemap:

1. IconLayer. One car icon per vehicle at its current position. The icons are PNG files loaded from a CDN. SVG icons don’t work with pydeck’s IconLayer, as they render silently as nothing. The icon size is fixed regardless of zoom level, which means vehicles remain visible when zoomed out.
2. PathLayer (trails). A line showing each vehicle’s last 20 positions. The trail fades naturally as old positions are replaced by new ones. The color matches the vehicle’s home zone color, making it easy to see which zone a vehicle started from.
3. PathLayer (shortest path). A black line showing the road-network shortest path between two selected zones, when requested. This layer is only present when the user has clicked “Find shortest path”. It persists across refreshes – stored in st.session_state – so the path stays on screen while vehicles continue moving around it.

Stale Data Warning

If no position records have been written in the last 30 seconds, the application shows a warning banner:

age = (datetime.now(timezone.utc) - latest_ts).total_seconds()
if age > STALE_SECONDS:
    st.warning(f"No position updates in {int(age)}s. Is the simulator running?")

The map still shows the last known positions – useful for seeing where vehicles were when the simulator stopped. This behavior differs from the analytics dashboard, which calls st.stop() on stale data since there’s nothing useful to show without fresh positions.

Nearest Driver

The nearest-driver feature answers the question “which vehicle is currently closest to this zone?” using haversine distance from the zone center:

def haversine(lat1, lon1, lat2, lon2):
    R = 6371000
    phi1, phi2 = radians(lat1), radians(lat2)
    dphi = radians(lat2 - lat1)
    dlambda = radians(lon2 - lon1)
    a = sin(dphi/2)**2 + cos(phi1)*cos(phi2)*sin(dlambda/2)**2
    return 2 * R * asin(sqrt(a))

The sidebar lets the user select a zone and the application queries the latest position of every vehicle from Lakebase, computes the haversine distance from each vehicle to the selected zone’s center and returns the closest one. The zone centers are verified road-network coordinates loaded from config and they map to real intersections in the road graph.

Shortest Path

The shortest path feature lets the user pick a source and destination zone and find the road-network path between them. Clicking “Find shortest path” triggers a two-step Aura query: first find the nearest intersection to each zone center, then run shortestPath() between them:

result = session.run("""
    MATCH (s:Intersection {node_id: $start}),
          (e:Intersection {node_id: $end})
    MATCH path = shortestPath((s)-[:ROAD*..300]->(e))
    RETURN [n IN nodes(path) | [n.lat, n.lon]] AS coords,
           length(path) AS hops
""", start=start_id, end=end_id)

The result is a list of coordinate pairs that pydeck draws as a black line on the map. The path and its metadata (number of hops and approximate distance) persist in st.session_state so they stay visible across the 3-second refresh cycle.

A key implementation detail is that pydeck re-renders layers when their data changes, but it also re-renders when its key parameter changes. We set the key to include the selected zone names:

key=f"map_{st.session_state.path_from}_{st.session_state.path_to}"

This forces a full re-render whenever the user changes the selected zones, which clears the old path immediately.

Zone Activity Chart

The sidebar shows a bar chart of position updates per zone over the last 10 minutes. This uses the current_zone column in vehicle_positions and not the home zone from the vehicles table. The distinction matters: a vehicle that started in Wimbledon but is currently in Colliers Wood contributes to Colliers Wood’s activity count, not Wimbledon’s.

SELECT current_zone, COUNT(*) AS updates
FROM vehicle_positions
WHERE recorded_at >= NOW() - INTERVAL '10 minutes'
  AND current_zone IS NOT NULL
GROUP BY current_zone
ORDER BY updates DESC

The Analytics Dashboard

The analytics dashboard connects to three systems:

1. Aura for road names in the cross-system join.
2. Lakebase for live position data.
3. Lakehouse for Delta table storage and SQL analytics.

All three connections are from a local Jupyter notebook process using standard Python connectors.

Incremental Sync

On each 30-second refresh, the dashboard reads new position records from Lakebase and appends them to a Delta table in Lakehouse. It tracks the last position_id it synced in st.session_state and only reads records with a higher ID:

SELECT vp.position_id, vp.vehicle_id, v.zone AS home_zone,
       vp.current_zone, vp.lat, vp.lon, vp.recorded_at
FROM vehicle_positions vp
JOIN vehicles v ON v.vehicle_id = vp.vehicle_id
WHERE vp.position_id > %s
  AND vp.recorded_at >= NOW() - INTERVAL '60 minutes'
ORDER BY vp.position_id

The first refresh loads the full hour of history; subsequent refreshes only load the new records since the last sync. This makes refreshes fast even when the Delta table contains tens of thousands of rows.

Simulator Check

Before syncing, the dashboard checks whether the simulator is producing fresh data:

def simulator_is_running(lb_conn):
    cursor.execute("""
        SELECT COUNT(*) FROM vehicle_positions
        WHERE recorded_at >= NOW() - INTERVAL '30 seconds'
    """)
    return cursor.fetchone()[0] > 0

If no positions have been written in the last 30 seconds, the dashboard shows a warning and calls st.stop(), halting the render. The auto-refresh continues firing, so the dashboard recovers automatically when the simulator starts again.

Zone Activity Over Time

The first chart shows position update counts per zone per minute over the last hour:

SELECT current_zone AS zone,
       DATE_TRUNC('minute', recorded_at) AS minute,
       COUNT(*) AS updates
FROM vehicle_positions_delta
WHERE current_zone IS NOT NULL
GROUP BY current_zone, DATE_TRUNC('minute', recorded_at)
ORDER BY minute, zone

This query runs on Lakehouse against the Delta table, not against Lakebase directly. For a large dataset, Lakehouse columnar storage and parallel execution make this faster than running the equivalent query on Lakebase.

Top Road Segments – The Cross-System Join

The second chart is the most interesting query in the system. It answers “which named roads carry the most vehicle traffic?” To answer this requires data from two different databases.

Lakebase has the position records (lat, lon per vehicle per tick). Aura has the road names (what named road does each intersection belong to). Neither system alone can answer the question.

The join runs in Python using pandas. The dashboard loads intersection coordinates and road names from Aura once at startup (cached for an hour) and position coordinates from Lakehouse on each refresh. It rounds both sets of coordinates to three decimal places (~100m precision) and joins on the rounded values:

pos_df["lat_r"]  = pos_df["lat"].round(3)
pos_df["lon_r"]  = pos_df["lon"].round(3)
road_df["lat_r"] = road_df["lat"].round(3)
road_df["lon_r"] = road_df["lon"].round(3)

joined = pos_df.merge(
    road_df[["road_name", "highway", "lat_r", "lon_r"]],
    on=["lat_r", "lon_r"],
    how="inner"
)

The result is a count of vehicle passes per named road – Morden Road, London Road, and so on, colored by highway type. Primary roads tend to dominate because the BFS simulator routes vehicles along main roads when finding shortest paths.

Simulator Control

The sidebar has two control buttons:

1. Stop Simulator. Writes simulator.stop to disk. The simulator checks for this file at the top of each tick, deletes it and exits cleanly via KeyboardInterrupt. This triggers the existing shutdown code, which resets all vehicle statuses to idle in Lakebase before the process exits.
2. Reset Delta Table. Clears the last_position_id counter in session state and marks the Delta table as needing recreation. On the next refresh, the dashboard drops the existing Delta table, recreates it and loads the full hour of history from scratch. Useful when you restart the simulator or switch cities.

Production Path: Lakehouse Sync

In this demo, position data flows from Lakebase to Lakehouse via the analytics dashboard’s incremental sync. In production, Databricks provides a native CDC feature called Lakebase Change Data Feed that replicates Lakebase tables into Unity Catalog Delta tables automatically, with no application-level sync code needed.

Lakehouse Sync requires a workspace admin to enable it from the Databricks workspace Previews page. It’s currently in Public Preview and isn’t available on free-tier Databricks accounts. The developer template walks through the setup.

Gotchas

use_container_width deprecated. Streamlit deprecated use_container_width in favor of width='stretch' in recent versions. Use st.plotly_chart(fig, width='stretch') not use_container_width=True.

pydeck IconLayer needs PNG not SVG. SVG icons render silently as nothing in pydeck’s IconLayer. Use a PNG URL. The car icon used in this demo comes from a CDN and is loaded fresh on each render.

CARTO basemap, no Mapbox token. The CARTO Voyager basemap shows roads, labels and satellite imagery without requiring a Mapbox account or API key.

@st.cache_resource and schema changes. @st.cache_resource caches the database connection across the entire Streamlit session. If you drop and recreate the Lakebase tables (by re-running 04_lakebase.ipynb), the cached connection still points at the old session. Restart Streamlit to pick up the new schema.

Shortest path and pydeck key. Without a unique key on the pydeck chart, changing the selected zones doesn’t clear the old path from the map. Set the key to include the zone names: key=f"map_{from_zone}_{to_zone}".

Aggressive Aura health checks. Calling driver.verify_connectivity() on every render creates a new connection to Aura on every 3-second refresh. Check instead whether the driver object is None and reconnect only when necessary.

current_zone vs home zone. The zone activity chart must read current_zone from vehicle_positions, not zone from the vehicles table. The home zone is static; current zone reflects where the vehicle actually is right now.

Chapter 7: Bringing It All Together

The Full System

With all six chapters complete, the system looks like this:

• Neo4j Aura. Holds the road network with thousands of intersections and thousands of road segments for Merton, loaded once from OpenStreetMap and unchanged for the life of the demo. It answers graph questions: shortest path between zones, zone adjacency, nearest intersection to a GPS coordinate.

• Databricks Lakebase. Holds the live operational data with vehicle positions written every two seconds by the simulator, vehicle statuses and trip records. It answers OLTP questions: where is vehicle V003 right now, which vehicles are currently idle, how many positions have been written in the last 10 minutes.

• Databricks Lakehouse. Holds the analytical history with position data synced from Lakebase into a Delta table, aggregated by zone and road segment. It answers analytical questions: which zones have been busiest in the last hour, which roads carry the most vehicle traffic.

None of these systems knows about the others. Aura knows nothing about Lakebase positions. Lakebase knows nothing about road names. Lakehouse knows nothing about zone topology. The intelligence sits in the application layer – the simulator, the Streamlit applications and the analytics notebook – which orchestrates queries across all three and combines the results.

Running the System

The order of operations matters. Work through the notebooks in sequence:

1. 00_prepare_osm.ipynb – For new cities only or if you wish to rerun the data generation for San Francisco or Singapore. Skip for Merton.
2. 02_road_network.ipynb – Loads the road network into Aura.
3. 03_zones.ipynb – Assigns intersections to zones and builds the adjacency graph.
4. 04_lakebase.ipynb – Creates the Lakebase tables and seeds the vehicles.
5. 05_simulator.ipynb – Starts the simulator in the background.
6. 06_analytics.ipynb – Optional, for running four analytics queries interactively.
7. In a terminal: streamlit run app.py.
8. In a second terminal: streamlit run analytics_app.py --server.port 8502.

Switching cities. Copy config_sf.yaml or config_sg.yaml to config.yaml and re-run notebooks 2 through 5. Notebook 2 clears the existing graph before loading the new city. Both Streamlit applications pick up the new city automatically on restart.

Adding a New City

The YAML config system makes it straightforward to adapt the project to any city covered by OpenStreetMap. The process for adding a new city is:

Choose an area. Pick a part of a city that’s large enough to have interesting routing (at least a few thousand intersections) but small enough to fit within Neo4j Aura’s free-tier limits.

Test the place name. For cities where a single administrative boundary works with OSMnx, test it first:

import osmnx as ox
gdf = ox.geocode_to_gdf("Your Place Name, Country")
print(gdf.geometry.iloc[0].geom_type)  # should be Polygon or MultiPolygon

If Nominatim returns a polygon, use osmnx_place as a single string in your config. If it returns a point or fails, use the Geofabrik approach.

For the Geofabrik approach. Download the regional .osm.pbf file from Geofabrik, add the four osm_* fields to your config and run 00_prepare_osm.ipynb to clip the file to your bounding box. Check that the clipped graph is fully connected (one strongly connected component) before proceeding.

Define zones. Choose five zone names, define bounding boxes that tile your area without overlapping and set center coordinates within each box. Describe them in the config comments as “logical simplified zones” rather than official boundaries.

Check adjacency. Review the zone topology against a map. A simple connected chain is the safest choice, as it avoids questions about which zones are “really” adjacent. Have a domain expert check the adjacency if possible.

Run the pipeline. Check the intersection counts per zone. If any zone has very few intersections (under 50), its bounding box may be misaligned or covering an area with few drivable roads. Adjust the box.

What Each System Does That the Others Can’t

It’s worth noting why three systems are better than one for this use case.

Why not just Aura? Neo4j Aura is purpose-built for graph traversals and spatial queries – exactly what we use it for. Road networks, zone adjacency and shortest path computation are natural fits for a graph database. The native spatial index on intersection nodes makes nearest-neighbor lookups fast regardless of how many intersections the graph contains. Also, a shortest path query that would require a recursive CTE in SQL is a single function call in Cypher.

Why not just Lakebase? Lakebase is purpose-built for operational data – high-frequency writes, transactional consistency and low-latency row reads. It handles the live vehicle positions exactly as we’d want a production Postgres database to. Foreign key constraints, BIGSERIAL auto-increment and standard SQL all work exactly as expected.

Why not just Lakehouse? Lakehouse is excellent for large-scale analytics and historical queries. It’s designed for aggregating millions of position records by zone, identifying the busiest road segments over time and running SQL across large Delta tables. The columnar storage and parallel execution make these queries fast at scale, and the Delta table format adds reliability through ACID transactions and time-travel capability.

The three-system architecture isn’t complexity for its own sake. Each system does what it’s genuinely good at and the results are combined at the application layer.

Going Further

The system as built is a working demo. Here are some directions for making it more realistic.

Speed-aware movement. Vehicles currently move one intersection per tick regardless of road speed or distance. Loading maxspeed and length_m from the ROAD relationships would allow the simulator to calculate the time each edge takes to traverse and advance multiple intersections per tick on fast roads. A vehicle on the A3 (50 mph) would visibly move faster than one on a residential street (20 mph).

Trip lifecycle. The trips table is in the schema but not used. Adding a full dispatch cycle – idle -> dispatched -> carrying passenger -> idle – would make the system much more realistic. Each trip would have a pickup and dropoff zone, a vehicle assignment and timestamps for each state transition.

Lakehouse Sync. In the demo, position data moves from Lakebase to Lakehouse via the analytics dashboard’s incremental sync. In production, Databricks’s Lakebase Change Data Feed replicates Lakebase tables into Unity Catalog Delta tables automatically via CDC. This requires enabling the feature from the workspace Previews page and is available on paid Databricks accounts. See the developer template.

Actual neighborhood boundaries. The zone bounding boxes are logical approximations. For Merton, ONS and Ordnance Survey provide ward and postcode boundary polygons. For Singapore, OneMap provides URA planning area polygons. For San Francisco, the city publishes official neighborhood boundary GeoJSON. Replacing the bounding boxes with real polygon boundaries would make zone assignment geographically accurate.

H3 indexing. Uber’s H3 library provides a hexagonal grid system that covers the globe at multiple resolutions. Indexing intersections by H3 cell would make the nearest-driver query more efficient at scale and is the approach used in production ride-hailing systems.

Kafka integration. Replacing the simulator’s direct Lakebase writes with a Kafka producer and adding a consumer that writes to Lakebase would make the architecture more production-ready. It would also decouple the simulator from Lakebase, as the simulator could run even when Lakebase is temporarily unavailable, with positions queued in Kafka until connectivity is restored.

Free Books

The SingleStore Cookbook: Recipes for Multi-Model, Machine Learning and AI Data Engineering

Read Online ↗

A hands-on cookbook covering SingleStore’s multi-model capabilities, from time series and geospatial data through vector search, machine learning pipelines and AI-powered applications. The recipes draw on first-hand experience building applications with the platform and are organized into four parts:

1. Multi-Model
2. Streaming and Big Data Pipelines
3. Machine Learning
4. AI and Agentic Frameworks

Seven Vector Databases in Seven Days

Read Online ↗

A practical guide that takes one vector database per day and pairs each with a use case chosen to showcase that database’s strengths. Databases covered:

1. PostgreSQL and pgvector - Semantic job search
2. MongoDB Atlas - Recipe finder
3. Pinecone - E-commerce search
4. Weaviate - Research paper discovery
5. Neo4j - Fraud detection
6. Snowflake - Customer support analytics
7. Databricks - RAG over internal documents

Each chapter is self-contained, comes with a Jupyter notebook and gives an assessment of when you’d look elsewhere.

Generative AI: A Manager’s Guide

Read Online ↗

A practical guide for managers, directors and executives who need to make decisions about AI in their organizations, not the engineers building it, but the people responsible for making it work well. The book uses a single central metaphor, the Digital Intern, to frame what AI is genuinely good at, where it falls short and what managing it actually requires. It covers governance, risk, board-level accountability, business case building and the organizational change of moving from pilot to embedded capability.

Seven Ways to Do Vector Search in Python

Read Online ↗

A practitioner’s guide that benchmarks seven Python libraries against the same dataset, measuring recall and latency consistently so you can compare like-for-like. Libraries covered:

1. FAISS
2. Voyager
3. Scikit-learn NearestNeighbors
4. PyNNDescent
5. USearch
6. Chroma
7. LanceDB

Each chapter covers one library, explains what it’s genuinely good at and when you’d reach for something else.

Real-Time Vehicle Tracking with Neo4j, Databricks Lakebase and OpenStreetMap

Read Online ↗

A fleet operations demo that puts ten simulated vehicles onto real road networks loaded from OpenStreetMap. The architecture:

• Neo4j Aura holds the road network graph
• Databricks Lakebase stores live vehicle positions
• Databricks Lakehouse handles historical analytics

Two Streamlit dashboards display live positions and trend data. The primary demo uses the London Borough of Merton, with additional configurations for San Francisco and Singapore.

Real-Time Supply Chain Routing with Neo4j, Snowflake Postgres and Confluent Kafka

In progress.