Live Fleet Dispatch with Streaming SQL

Fleet dispatch depends on knowing where every vehicle is right now — not where it was two minutes ago. RisingWave, a PostgreSQL-compatible streaming database, processes GPS telemetry and job assignment events in real time so dispatchers always work from a live picture of fleet availability and location.

Why Live Dispatch Visibility Matters

A fleet operations center managing hundreds of vehicles faces a fundamental data latency problem. GPS positions, delivery completions, fuel stops, and mechanical alerts all arrive as a continuous stream of events. Routing engines need fresh vehicle locations to assign the nearest available driver. Dispatchers need to know the moment a vehicle breaks down or a driver goes off-schedule so they can reassign work before delivery windows close.

Traditional fleet management systems refresh dashboards on polling intervals — typically every 30 to 60 seconds. That sounds fast, but at highway speeds a vehicle travels half a mile between refreshes. In an urban environment with tight delivery windows and dynamic traffic, even a one-minute-old location can cause a dispatcher to route a driver into a conflict or miss an opportunity to rebalance workloads.

The downstream cost of stale dispatch data compounds quickly. A missed reassignment leads to a failed delivery. A failed delivery triggers a re-attempt, increasing fuel costs and driver hours. Customer satisfaction metrics drop. In regulated industries like pharmaceutical distribution or temperature-controlled food logistics, a missed time window may have compliance consequences beyond the commercial impact.

How Streaming SQL Solves the Dispatch Problem

RisingWave ingests vehicle telemetry from a Kafka topic and maintains continuously updated materialized views of fleet state. Because RisingWave uses incremental computation, each new GPS ping updates only the rows affected — the entire fleet table is not recomputed on every event. This makes it practical to maintain sub-second-fresh views even for large fleets.

Window functions like HOP and SESSION are especially useful for fleet analytics: HOP windows calculate rolling average speeds over overlapping time intervals, and SESSION windows detect when a vehicle has been stationary for an unusual length of time. Temporal joins correlate live vehicle positions against route plans and job assignments stored in a PostgreSQL operational database, giving dispatchers a unified view without requiring a separate ETL pipeline.

Building It Step by Step

Step 1: Create the Data Source

-- Live GPS telemetry from vehicle telematics units
CREATE SOURCE vehicle_telemetry (
    vehicle_id      VARCHAR,
    driver_id       VARCHAR,
    latitude        DOUBLE PRECISION,
    longitude       DOUBLE PRECISION,
    speed_kmh       DOUBLE PRECISION,
    heading         SMALLINT,
    fuel_pct        SMALLINT,
    engine_on       BOOLEAN,
    odometer_km     DOUBLE PRECISION,
    event_ts        TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'fleet.telemetry',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;

-- Job assignment events
CREATE SOURCE job_events (
    job_id          VARCHAR,
    vehicle_id      VARCHAR,
    event_type      VARCHAR,   -- ASSIGNED, STARTED, ARRIVED, COMPLETED, FAILED
    location_lat    DOUBLE PRECISION,
    location_lon    DOUBLE PRECISION,
    scheduled_ts    TIMESTAMPTZ,
    event_ts        TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'fleet.jobs',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;

Step 2: Build the Core Materialized View

-- Live fleet position — latest ping per vehicle
CREATE MATERIALIZED VIEW fleet_live_position AS
SELECT DISTINCT ON (vehicle_id)
    vehicle_id,
    driver_id,
    latitude,
    longitude,
    speed_kmh,
    heading,
    fuel_pct,
    engine_on,
    event_ts            AS last_seen_ts,
    NOW() - event_ts    AS staleness
FROM vehicle_telemetry
ORDER BY vehicle_id, event_ts DESC;

-- Active job status — latest event per job
CREATE MATERIALIZED VIEW active_jobs AS
SELECT DISTINCT ON (job_id)
    job_id,
    vehicle_id,
    event_type          AS current_status,
    scheduled_ts,
    event_ts            AS last_updated_ts,
    EXTRACT(EPOCH FROM (event_ts - scheduled_ts)) / 60.0 AS schedule_variance_min
FROM job_events
WHERE event_type NOT IN ('COMPLETED', 'FAILED')
ORDER BY job_id, event_ts DESC;

Step 3: Add Alerts and Aggregations

-- Vehicles stationary for more than 15 minutes while a job is active
CREATE MATERIALIZED VIEW stalled_vehicle_alerts AS
SELECT
    p.vehicle_id,
    p.driver_id,
    p.latitude,
    p.longitude,
    p.last_seen_ts,
    EXTRACT(EPOCH FROM (NOW() - p.last_seen_ts)) / 60.0 AS stationary_minutes,
    j.job_id,
    j.scheduled_ts
FROM fleet_live_position p
JOIN active_jobs j ON j.vehicle_id = p.vehicle_id
WHERE p.speed_kmh < 2
  AND EXTRACT(EPOCH FROM (NOW() - p.last_seen_ts)) / 60.0 > 15;

-- Rolling 10-minute average speed per vehicle (HOP window, 10-min window, 1-min hop)
CREATE MATERIALIZED VIEW vehicle_speed_rolling AS
SELECT
    window_start,
    window_end,
    vehicle_id,
    AVG(speed_kmh)  AS avg_speed_kmh,
    MAX(speed_kmh)  AS max_speed_kmh,
    COUNT(*)        AS ping_count
FROM HOP(vehicle_telemetry, event_ts, INTERVAL '1 MINUTE', INTERVAL '10 MINUTES')
GROUP BY window_start, window_end, vehicle_id;

-- Hourly delivery completion rate
CREATE MATERIALIZED VIEW hourly_completion_rate AS
SELECT
    window_start,
    window_end,
    COUNT(*) FILTER (WHERE event_type = 'COMPLETED') AS completed,
    COUNT(*) FILTER (WHERE event_type = 'FAILED')    AS failed,
    COUNT(*)                                          AS total_events,
    ROUND(
        100.0 * COUNT(*) FILTER (WHERE event_type = 'COMPLETED') /
        NULLIF(COUNT(*) FILTER (WHERE event_type IN ('COMPLETED', 'FAILED')), 0),
        1
    ) AS completion_rate_pct
FROM TUMBLE(job_events, event_ts, INTERVAL '1 HOUR')
WHERE event_type IN ('COMPLETED', 'FAILED')
GROUP BY window_start, window_end;

Step 4: Sink Results Downstream

-- Stream stalled vehicle alerts to ops notification system
CREATE SINK stalled_vehicle_sink
FROM stalled_vehicle_alerts
WITH (
    connector = 'kafka',
    topic = 'ops.alerts.stalled_vehicles',
    properties.bootstrap.server = 'kafka:9092'
) FORMAT PLAIN ENCODE JSON;

-- Write live fleet positions to a JDBC sink for the dispatch dashboard
CREATE SINK fleet_position_sink
FROM fleet_live_position
WITH (
    connector = 'jdbc',
    jdbc.url = 'jdbc:postgresql://dispatch-db:5432/fleet',
    table.name = 'fleet_live_position'
) FORMAT PLAIN ENCODE JSON;

How This Compares to Traditional Approaches

FAQ

What is live fleet dispatch?

Live fleet dispatch is the real-time management of vehicle assignments and routing based on continuously updated vehicle locations, availability, and job statuses. Effective dispatch minimizes empty miles, meets delivery time windows, and responds instantly to exceptions like breakdowns or traffic delays.

How does RisingWave handle the volume of GPS telemetry?

Modern telematics units emit a ping every few seconds per vehicle. For a fleet of 1,000 vehicles at 10-second intervals, that is 100 events per second. RisingWave's incremental computation model handles this efficiently — each ping updates only the relevant row in the materialized view rather than triggering a full table scan.

Can I integrate RisingWave with my existing stack?

Yes. RisingWave connects to Kafka, PostgreSQL via CDC, MySQL via CDC, and writes to downstream systems via JDBC or Kafka sinks. Its PostgreSQL-compatible interface means routing engines, BI dashboards, and alerting tools can query live fleet state using standard SQL.

How do I handle GPS signal loss or delayed pings?

You can add a staleness column (as shown in the fleet_live_position view) and filter dispatchers' views to surface vehicles whose last ping exceeds a threshold. SESSION window functions can also detect communication gaps and trigger connectivity alerts.

Key Takeaways

• Polling-based dashboards leave dispatchers working with stale data; streaming SQL surfaces vehicle positions and job status in sub-second time.
• RisingWave's HOP and SESSION window functions enable rolling speed analysis and stationary detection without custom application code.
• Temporal joins correlate live telemetry against job assignments stored in PostgreSQL, giving dispatchers a unified operational view.
• Alerts and metrics flow downstream through Kafka and JDBC sinks, fitting seamlessly into existing operations center infrastructure.

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