Real-Time Route Optimization with Streaming Data

Route optimization that runs once at the start of a shift becomes stale within minutes. Real-time route optimization means continuously monitoring fleet positions, traffic conditions, and delivery outcomes — and triggering rerouting recommendations the moment conditions change. With RisingWave, a PostgreSQL-compatible streaming database, you can build the data layer that powers this using SQL alone.

Why Static Route Plans Fail

A route plan computed at 6 AM reflects the world as it was at 6 AM. By 8 AM, several things will have changed: a driver is running 20 minutes behind, a customer called to reschedule, road construction has added 15 minutes to a corridor, and two parcels were undeliverable at first attempt. Every one of these changes creates an opportunity to improve the plan — but only if the optimization engine has access to current state.

Traditional systems batch-process updates on a schedule: re-optimize every 30 minutes, or only when a dispatcher manually triggers a replan. This misses the continuously changing reality of fleet operations, where conditions evolve faster than batch cycles can respond.

A streaming data layer changes this dynamic. RisingWave maintains always-current views of vehicle positions, traffic anomalies, stop completion status, and capacity utilization. A connected optimization engine can query this layer at any time and receive a precise snapshot of fleet state, enabling near-continuous rerouting decisions.

How Streaming Route Data Works

The architecture separates concerns cleanly:

RisingWave maintains real-time state: vehicle positions, remaining stops, traffic flags, and capacity metrics
Optimization engine (external solver or custom logic) reads from RisingWave views and produces updated route plans
Feedback loop: completed stops and driver acknowledgments flow back into Kafka, updating RisingWave state

RisingWave does not run the optimization algorithm itself — it provides the live data substrate that makes continuous optimization practical. The materialized views act as a real-time snapshot API that the optimizer can query without touching raw Kafka streams.

Step-by-Step Tutorial

Step 1: Set Up the Data Source

Define Kafka sources for vehicle telemetry and traffic events, plus a table for the current route plan:

-- Vehicle telemetry
CREATE SOURCE vehicle_telemetry (
    vehicle_id   VARCHAR,
    route_id     VARCHAR,
    event_time   TIMESTAMPTZ,
    latitude     DOUBLE PRECISION,
    longitude    DOUBLE PRECISION,
    speed_kmh    DOUBLE PRECISION,
    load_pct     DOUBLE PRECISION,  -- current cargo load percentage
    status       VARCHAR
)
WITH (
    connector    = 'kafka',
    topic        = 'vehicle-telemetry',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
)
FORMAT PLAIN ENCODE JSON;

-- Traffic and road events
CREATE SOURCE traffic_events (
    segment_id   VARCHAR,
    event_time   TIMESTAMPTZ,
    event_type   VARCHAR,  -- 'CONGESTION','INCIDENT','CLOSURE','CLEARED'
    severity     INTEGER,  -- 1 (minor) to 5 (road closed)
    delay_min    INTEGER,
    latitude     DOUBLE PRECISION,
    longitude    DOUBLE PRECISION,
    segment_lat2 DOUBLE PRECISION,
    segment_lon2 DOUBLE PRECISION
)
WITH (
    connector    = 'kafka',
    topic        = 'traffic-events',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
)
FORMAT PLAIN ENCODE JSON;

-- Current route plans (updated by optimizer)
CREATE TABLE route_plans (
    route_id          VARCHAR PRIMARY KEY,
    vehicle_id        VARCHAR,
    stops             JSONB,    -- ordered list of stop coordinates and windows
    total_distance_km DOUBLE PRECISION,
    planned_duration  INTERVAL,
    created_at        TIMESTAMPTZ,
    version           INTEGER
);

-- Stop completion events
CREATE SOURCE stop_events (
    stop_id      VARCHAR,
    route_id     VARCHAR,
    vehicle_id   VARCHAR,
    event_time   TIMESTAMPTZ,
    event_type   VARCHAR,  -- 'ARRIVED','DEPARTED','DELIVERED','FAILED'
    stop_seq     INTEGER
)
WITH (
    connector    = 'kafka',
    topic        = 'stop-events',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
)
FORMAT PLAIN ENCODE JSON;

Step 2: Build the Core Materialized View

Maintain a real-time fleet state view that the optimizer can query to understand current vehicle positions and remaining work:

CREATE MATERIALIZED VIEW fleet_real_time_state AS
SELECT
    v.vehicle_id,
    v.route_id,
    v.latitude           AS current_lat,
    v.longitude          AS current_lon,
    v.speed_kmh,
    v.load_pct,
    v.status,
    v.event_time         AS last_ping,
    -- Count remaining stops (not yet completed)
    (
        SELECT COUNT(*)
        FROM stop_events se
        WHERE se.route_id = v.route_id
          AND se.event_type NOT IN ('DELIVERED', 'FAILED')
    )                    AS stops_remaining,
    -- Count completed stops
    (
        SELECT COUNT(*)
        FROM stop_events se
        WHERE se.route_id = v.route_id
          AND se.event_type IN ('DELIVERED', 'FAILED')
    )                    AS stops_completed
FROM (
    SELECT DISTINCT ON (vehicle_id)
        vehicle_id, route_id, latitude, longitude,
        speed_kmh, load_pct, status, event_time
    FROM vehicle_telemetry
    ORDER BY vehicle_id, event_time DESC
) v;

Build a traffic impact view showing which route segments currently have active disruptions:

CREATE MATERIALIZED VIEW active_traffic_disruptions AS
SELECT
    segment_id,
    event_type,
    severity,
    delay_min,
    latitude     AS seg_lat1,
    longitude    AS seg_lon1,
    segment_lat2 AS seg_lat2,
    segment_lon2 AS seg_lon2,
    event_time   AS reported_at,
    NOW() - event_time AS age
FROM (
    SELECT DISTINCT ON (segment_id)
        segment_id, event_type, severity, delay_min,
        latitude, longitude, segment_lat2, segment_lon2, event_time
    FROM traffic_events
    ORDER BY segment_id, event_time DESC
) latest
WHERE event_type != 'CLEARED'
  AND NOW() - event_time < INTERVAL '2 hours';

Step 3: Add Rerouting Trigger Signals

Create a view that surfaces vehicles whose routes pass through disrupted segments — the primary signal for triggering a replan:

CREATE MATERIALIZED VIEW rerouting_candidates AS
SELECT
    f.vehicle_id,
    f.route_id,
    f.current_lat,
    f.current_lon,
    f.stops_remaining,
    t.segment_id          AS affected_segment,
    t.severity,
    t.delay_min           AS expected_delay_min,
    t.reported_at
FROM fleet_real_time_state f
-- Spatial proximity: flag vehicles within ~10km of a disruption
CROSS JOIN active_traffic_disruptions t
WHERE f.stops_remaining > 0
  AND 111.0 * SQRT(
      POWER(f.current_lat - t.seg_lat1, 2) +
      POWER(f.current_lon - t.seg_lon1, 2) * POWER(COS(RADIANS(f.current_lat)), 2)
  ) < 10.0
  AND t.severity >= 3;

-- Vehicle utilization for load balancing opportunities
CREATE MATERIALIZED VIEW vehicle_load_balance AS
SELECT
    window_start,
    window_end,
    vehicle_id,
    AVG(load_pct)        AS avg_load_pct,
    MIN(load_pct)        AS min_load_pct,
    MAX(load_pct)        AS max_load_pct,
    COUNT(*)             AS ping_count
FROM TUMBLE(vehicle_telemetry, event_time, INTERVAL '15 minutes')
GROUP BY vehicle_id, window_start, window_end;

The optimizer queries rerouting_candidates to get a ranked list of vehicles needing attention, fetches current state from fleet_real_time_state, and writes updated plans back to route_plans.

How This Compares to Traditional Approaches

Aspect	Scheduled Reoptimization	Event-Driven Callbacks	RisingWave Streaming Layer
State freshness for optimizer	Stale (age of last batch)	Depends on event design	Sub-second
Traffic integration	Manual feed, delayed	Requires bespoke connectors	Kafka source, automatic
Stop completion visibility	Batch upload	Webhook per event	Continuous stream
Query interface for optimizer	Custom API or DB queries	Custom API	Standard PostgreSQL SQL
Load balancing signals	Manual or end-of-day	Not typically available	Continuous window aggregates
Complexity	Medium	High	Medium

FAQ

Does RisingWave run the route optimization algorithm itself?

No — RisingWave is the real-time data layer, not the solver. It maintains current fleet state in materialized views that your optimization engine queries. The solver (a VRP solver, custom heuristic, or ML model) reads from RisingWave, computes updated routes, and writes results back to the route_plans table.

How do I handle the case where a reoptimized route is rejected by the driver?

Model driver acknowledgment as a stop event with type REJECTED. RisingWave ingests this from Kafka, which causes the route's state in fleet_real_time_state to reflect the rejection. Your optimizer sees the current state on the next query cycle and can produce an alternative plan.

Can RisingWave write route updates back to Kafka for downstream consumption?

Yes. RisingWave supports a Kafka sink. You can define a sink that publishes rows from a materialized view (or directly from a table after an INSERT) to a Kafka topic. Mobile driver apps, customer notification systems, and dispatch consoles can all consume updated routes from that topic.

Key Takeaways

RisingWave acts as a real-time data substrate for fleet optimization — maintaining always-current materialized views of vehicle positions, traffic disruptions, stop completions, and load metrics that any optimizer can query instantly.
The rerouting_candidates view provides a ready-made signal layer: vehicles near severe traffic events with remaining stops bubble up automatically, without polling raw event streams.
Separating the streaming data layer (RisingWave) from the optimization logic means each component can evolve independently — you can swap optimization algorithms without changing your data infrastructure.

Ready to try this yourself? Get started with RisingWave in minutes. Join our Slack community.