Real-time fleet telematics and geofencing with streaming SQL means every GPS ping, engine reading, fuel rate, tire pressure sample, and ELD duty status change is evaluated the moment it arrives: geofence entries and exits, speeding, overheating, fault codes, idle waste, hours-of-service limits, slow tire leaks, stalled vehicles, and rerouting triggers all surface within seconds instead of at the next 30-second poll or in tomorrow's report. This guide builds the full fleet data layer on RisingWave, a PostgreSQL-compatible streaming database, starting with a verified geofencing tutorial and extending it to dispatch, routing, fuel, hours of service, engine diagnostics, and tire maintenance.
Why Fleet Data Belongs in a Streaming Database
A fleet management system that polls a PostGIS database every 30 seconds misses the truck that entered a restricted zone at second 15. A dispatcher working from a dashboard refreshed every minute is looking at a position half a mile old at highway speed. An operator with fifty long-haul trucks does not learn about a driver speeding for two hours, a fault code thrown forty minutes ago, or a route deviation until the batch report runs. A driver 45 minutes from the 11-hour driving limit gets assigned a new load. A slow tire leak that TPMS has been reporting for three hours becomes a blowout on the interstate.
Telematics data is high volume and needs correlation: engine faults joined to location, hours of service joined to current speed, position joined to geofences, fuel rate joined to idle state. Time series databases store the samples; the joins and windowed aggregations that turn samples into alerts need either precomputation or expensive query-time work. RisingWave maintains them as incrementally updated materialized views, so only the affected vehicle's rows change on each event, and serves them over the PostgreSQL protocol to dispatch dashboards or through Kafka sinks to notification, maintenance, and routing systems. RisingWave is open source under Apache 2.0, written in Rust, and checkpoints state to S3.
Tutorial: Geofencing with the Haversine Formula
Start with two reference tables, devices and geofences, and a GPS event table. In production the GPS table is a Kafka source; the views below are unchanged. The scenario is a logistics fleet in San Francisco with a depot, a downtown restricted area, a delivery zone, and a corporate campus.
CREATE TABLE geo_devices (
device_id VARCHAR PRIMARY KEY,
device_name VARCHAR,
device_type VARCHAR, -- vehicle, drone, asset
owner VARCHAR
);
CREATE TABLE geo_geofences (
fence_id INT PRIMARY KEY,
fence_name VARCHAR,
center_lat DOUBLE PRECISION,
center_lon DOUBLE PRECISION,
radius_meters DOUBLE PRECISION,
fence_type VARCHAR -- depot, restricted, delivery_zone, campus
);
INSERT INTO geo_devices VALUES
('veh-001', 'Delivery Truck Alpha', 'vehicle', 'LogiCo Fleet'),
('veh-002', 'Delivery Truck Beta', 'vehicle', 'LogiCo Fleet'),
('veh-003', 'Service Van Gamma', 'vehicle', 'LogiCo Fleet'),
('drn-001', 'Inspection Drone 1', 'drone', 'Site Ops'),
('ast-001', 'High-Value Asset A', 'asset', 'Warehouse Team');
INSERT INTO geo_geofences VALUES
(1, 'Main Depot', 37.7749, -122.4194, 500.0, 'depot'),
(2, 'Downtown Restricted', 37.7858, -122.4065, 300.0, 'restricted'),
(3, 'Zone A Delivery', 37.7680, -122.4312, 400.0, 'delivery_zone'),
(4, 'Corporate Campus', 37.7920, -122.3900, 600.0, 'campus');
CREATE TABLE geo_gps_events (
event_id BIGINT,
device_id VARCHAR,
latitude DOUBLE PRECISION,
longitude DOUBLE PRECISION,
speed_kmh DOUBLE PRECISION,
heading DOUBLE PRECISION,
event_ts TIMESTAMPTZ
);
The Haversine formula gives great-circle distance in meters and is more accurate than Euclidean distance for anything beyond a few kilometers. Evaluating every ping against every fence is a cross join; for a handful of fences that is fine, and the scaling section below covers thousands.
CREATE MATERIALIZED VIEW geo_distance_to_fence AS
SELECT g.event_id, g.device_id, d.device_name, d.device_type, g.latitude, g.longitude, g.speed_kmh, g.event_ts,
f.fence_id, f.fence_name, f.fence_type, f.radius_meters,
ROUND((6371000.0 * 2.0 * ASIN(SQRT(
POWER(SIN((RADIANS(g.latitude) - RADIANS(f.center_lat)) / 2.0), 2)
+ COS(RADIANS(g.latitude)) * COS(RADIANS(f.center_lat))
* POWER(SIN((RADIANS(g.longitude) - RADIANS(f.center_lon)) / 2.0), 2))))::NUMERIC, 1) AS distance_to_center_m
FROM geo_gps_events g
JOIN geo_devices d ON g.device_id = d.device_id
CROSS JOIN geo_geofences f;
CREATE MATERIALIZED VIEW geo_device_status AS
SELECT DISTINCT ON (device_id, fence_id)
device_id, device_name, device_type, fence_id, fence_name, fence_type,
latitude, longitude, speed_kmh, distance_to_center_m, radius_meters,
distance_to_center_m <= radius_meters AS is_inside,
event_ts AS last_seen
FROM geo_distance_to_fence
ORDER BY device_id, fence_id, event_ts DESC;
Query geo_device_status and each device has one row per fence: High-Value Asset A is 67.3 meters from the depot center and inside; the drone is at the exact center of the campus; Delivery Truck Beta is inside the delivery zone. Alerts are a filter over that status:
CREATE MATERIALIZED VIEW geo_fence_violations AS
SELECT device_id, device_name, device_type, fence_id, fence_name, fence_type, latitude, longitude, distance_to_center_m, last_seen,
CASE WHEN fence_type = 'restricted' AND is_inside THEN 'UNAUTHORIZED_ENTRY'
WHEN fence_type = 'depot' AND NOT is_inside THEN 'LEFT_DEPOT' END AS alert_type
FROM geo_device_status
WHERE (fence_type = 'restricted' AND is_inside) OR (fence_type = 'depot' AND NOT is_inside);
CREATE MATERIALIZED VIEW geo_geofence_summary AS
SELECT fence_id, fence_name, fence_type, radius_meters,
COUNT(*) FILTER (WHERE is_inside) AS devices_inside,
COUNT(*) FILTER (WHERE NOT is_inside) AS devices_outside
FROM geo_device_status
GROUP BY fence_id, fence_name, fence_type, radius_meters;
Insert a ping placing Delivery Truck Alpha at the depot center and its LEFT_DEPOT row disappears within seconds; the three vehicles out on deliveries remain, correctly. The latest position per device, independent of fences, is the map layer for the dispatch dashboard:
CREATE MATERIALIZED VIEW geo_latest_positions AS
SELECT DISTINCT ON (device_id) device_id, latitude, longitude, speed_kmh, heading, event_ts
FROM geo_gps_events
ORDER BY device_id, event_ts DESC;
Production details matter. Consumer GPS is accurate to 3 to 5 meters, so add a buffer of 10 to 20 meters around the boundary (inside when distance is at most radius minus 10, outside when it exceeds radius plus 10) to stop a parked vehicle oscillating between states. For thousands of fences, pre-filter with a bounding box on latitude and longitude per fence, or compute a geohash cell at the producer and only fan out to fences in the same or adjacent cells. Polygon fences need a point-in-polygon check at the enrichment layer that tags each ping with the relevant fence_id before RisingWave. Dwell time inside a customer site is a tumbling window over pings inside the fence, and more than two hours at a site is an alert in its own right.
Fleet Telematics Sources
One telemetry stream from the J1939 CAN bus carries position, speed, engine, and fuel fields. ELD duty status, TPMS and axle sensors, job assignments, traffic, and fuel card transactions are separate streams.
CREATE SOURCE truck_telemetry (
vin VARCHAR,
driver_id VARCHAR,
route_id VARCHAR,
latitude DOUBLE PRECISION,
longitude DOUBLE PRECISION,
speed_mph DOUBLE PRECISION,
heading_deg INT,
engine_rpm INT,
engine_load_pct INT,
torque_nm DOUBLE PRECISION,
coolant_temp_c DOUBLE PRECISION,
oil_pressure_kpa DOUBLE PRECISION,
fuel_rate_lph DOUBLE PRECISION, -- J1939 FuelRate PGN
fuel_level_pct DOUBLE PRECISION,
odometer_km DOUBLE PRECISION,
load_weight_kg DOUBLE PRECISION,
dtc_code VARCHAR, -- SAE J1939 or OBD-II code, NULL if none
dtc_severity INT, -- 1 info, 2 warning, 3 critical
ignition_on BOOLEAN,
idle_engine_on BOOLEAN, -- speed under 1 mph with engine on
harsh_brake BOOLEAN,
harsh_accel BOOLEAN,
event_ts TIMESTAMPTZ
) WITH (connector = 'kafka', topic = 'fleet.telemetry', properties.bootstrap.server = 'kafka:9092', scan.startup.mode = 'latest')
FORMAT PLAIN ENCODE JSON;
CREATE SOURCE eld_duty_events (
event_id VARCHAR,
driver_id VARCHAR,
vin VARCHAR,
duty_status VARCHAR, -- 'DRIVING', 'ON_DUTY_NOT_DRIVING', 'SLEEPER_BERTH', 'OFF_DUTY'
event_time TIMESTAMPTZ
) WITH (connector = 'kafka', topic = 'fleet.eld.events', properties.bootstrap.server = 'kafka:9092', scan.startup.mode = 'latest')
FORMAT PLAIN ENCODE JSON;
CREATE SOURCE tpms_axle_readings (
vin VARCHAR,
axle_position VARCHAR, -- 'FRONT_LEFT', 'REAR_LEFT_INNER', ...
tire_pressure_kpa DOUBLE PRECISION,
tire_temp_c DOUBLE PRECISION,
axle_load_kg DOUBLE PRECISION,
vibration_hz DOUBLE PRECISION,
event_time TIMESTAMPTZ
) WITH (connector = 'kafka', topic = 'fleet.tpms.readings', properties.bootstrap.server = 'kafka:9092', scan.startup.mode = 'latest')
FORMAT PLAIN ENCODE JSON;
CREATE SOURCE job_events (
job_id VARCHAR,
vin VARCHAR,
route_id VARCHAR,
event_type VARCHAR, -- 'ASSIGNED', 'STARTED', 'ARRIVED', 'DELIVERED', 'FAILED'
stop_seq INTEGER,
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;
CREATE SOURCE traffic_events (
segment_id VARCHAR,
event_type VARCHAR, -- 'CONGESTION', 'INCIDENT', 'CLOSURE', 'CLEARED'
severity INTEGER, -- 1 minor to 5 closed
delay_min INTEGER,
latitude DOUBLE PRECISION,
longitude DOUBLE PRECISION,
event_time TIMESTAMPTZ
) WITH (connector = 'kafka', topic = 'traffic-events', properties.bootstrap.server = 'kafka:9092', scan.startup.mode = 'latest')
FORMAT PLAIN ENCODE JSON;
CREATE SOURCE fuel_transactions (
transaction_id VARCHAR,
vin VARCHAR,
driver_id VARCHAR,
fuel_liters DOUBLE PRECISION,
cost_usd DOUBLE PRECISION,
transaction_ts TIMESTAMPTZ
) WITH (connector = 'kafka', topic = 'fleet.fuel_transactions', properties.bootstrap.server = 'kafka:9092', scan.startup.mode = 'latest')
FORMAT PLAIN ENCODE JSON;
Live Dispatch: Positions, Jobs, Stalls, and Completion Rate
The dispatch picture is the latest ping per vehicle, the latest event per job, and the join between them. A vehicle below 2 mph for 15 minutes while a job is active is stalled; a job behind its schedule by more than a threshold is late before the customer calls.
CREATE MATERIALIZED VIEW fleet_live_position AS
SELECT DISTINCT ON (vin)
vin, driver_id, route_id, latitude, longitude, speed_mph, heading_deg, fuel_level_pct, ignition_on,
dtc_code, coolant_temp_c, event_ts AS last_seen, NOW() - event_ts AS staleness
FROM truck_telemetry
ORDER BY vin, event_ts DESC;
CREATE MATERIALIZED VIEW active_jobs AS
SELECT DISTINCT ON (job_id) job_id, vin, route_id, event_type AS current_status, stop_seq, scheduled_ts, event_ts AS last_updated,
EXTRACT(EPOCH FROM (event_ts - scheduled_ts)) / 60.0 AS schedule_variance_min
FROM job_events
WHERE event_type NOT IN ('DELIVERED', 'FAILED')
ORDER BY job_id, event_ts DESC;
CREATE MATERIALIZED VIEW stalled_vehicle_alerts AS
SELECT p.vin, p.driver_id, p.latitude, p.longitude, p.last_seen, j.job_id, j.scheduled_ts,
EXTRACT(EPOCH FROM (NOW() - p.last_seen)) / 60.0 AS minutes_since_movement
FROM fleet_live_position p
JOIN active_jobs j ON j.vin = p.vin
WHERE p.speed_mph < 2 AND p.ignition_on AND NOW() - p.last_seen > INTERVAL '15 MINUTES';
CREATE MATERIALIZED VIEW hourly_completion_rate AS
SELECT window_start, window_end,
COUNT(*) FILTER (WHERE event_type = 'DELIVERED') AS completed,
COUNT(*) FILTER (WHERE event_type = 'FAILED') AS failed,
ROUND(100.0 * COUNT(*) FILTER (WHERE event_type = 'DELIVERED') / NULLIF(COUNT(*), 0), 1) AS completion_rate_pct
FROM TUMBLE(job_events, event_ts, INTERVAL '1 HOUR')
WHERE event_type IN ('DELIVERED', 'FAILED')
GROUP BY window_start, window_end;
A truly stationary vehicle keeps sending pings at speed zero, so in production the stall check uses a session window over position changes or a LAG on coordinates rather than ping age. fleet_live_position sinks to the dispatch database as an upsert, and the dispatcher's map is never more than a ping old.
Continuous Route Optimization Signals
A route plan computed at 6 AM is stale by 8 AM: a driver is 20 minutes behind, a customer rescheduled, construction added 15 minutes to a corridor, two parcels failed first attempt. RisingWave does not run the solver; it maintains the live state the solver reads, and it produces the trigger that says which vehicles need a replan now.
CREATE MATERIALIZED VIEW route_progress AS
SELECT route_id, vin,
COUNT(*) FILTER (WHERE event_type IN ('DELIVERED', 'FAILED')) AS stops_completed,
COUNT(*) FILTER (WHERE event_type NOT IN ('DELIVERED', 'FAILED')) AS stops_open
FROM (SELECT DISTINCT ON (job_id) job_id, route_id, vin, event_type FROM job_events ORDER BY job_id, event_ts DESC) latest
GROUP BY route_id, vin;
CREATE MATERIALIZED VIEW active_traffic_disruptions AS
SELECT segment_id, event_type, severity, delay_min, latitude, longitude, event_time AS reported_at
FROM (SELECT DISTINCT ON (segment_id) * FROM traffic_events ORDER BY segment_id, event_time DESC) latest
WHERE event_type <> 'CLEARED' AND event_time > NOW() - INTERVAL '2 HOURS';
CREATE MATERIALIZED VIEW rerouting_candidates AS
SELECT p.vin, p.route_id, p.latitude, p.longitude, r.stops_open,
t.segment_id AS affected_segment, t.severity, t.delay_min AS expected_delay_min, t.reported_at
FROM fleet_live_position p
JOIN route_progress r ON r.route_id = p.route_id
CROSS JOIN active_traffic_disruptions t
WHERE r.stops_open > 0
AND t.severity >= 3
AND 111.0 * SQRT(POWER(p.latitude - t.latitude, 2) + POWER((p.longitude - t.longitude) * COS(RADIANS(p.latitude)), 2)) < 10.0;
The optimizer polls rerouting_candidates, reads vehicle state and remaining stops, writes a new plan version to its plan table, and the driver's acknowledgment flows back through job_events. The equirectangular distance approximation is adequate for a 10 km proximity filter; use Haversine where the result is the output rather than a filter.
Fuel: Consumption, Idle Waste, Efficiency Benchmarks, and Reconciliation
Fuel is 25 to 40 percent of cost per mile. A driver at 75 mph instead of 65 burns 15 to 20 percent more, and idle burns fuel for nothing. Hourly consumption from the J1939 fuel rate, sampled roughly every ten seconds, gives liters consumed, idle liters, distance, and efficiency per truck; a 24-hour fleet benchmark gives percentiles to compare against; fuel card transactions reconcile the estimate.
CREATE MATERIALIZED VIEW truck_fuel_hourly AS
SELECT vin, driver_id, window_start, window_end,
SUM(fuel_rate_lph) / 360.0 AS est_fuel_consumed_l, -- 10-second samples
SUM(fuel_rate_lph) FILTER (WHERE idle_engine_on) / 360.0 AS idle_fuel_l,
MAX(odometer_km) - MIN(odometer_km) AS distance_km,
ROUND(((MAX(odometer_km) - MIN(odometer_km)) / NULLIF(SUM(fuel_rate_lph) / 360.0, 0))::NUMERIC, 3) AS km_per_liter,
AVG(speed_mph) FILTER (WHERE speed_mph > 0) AS avg_moving_speed_mph,
COUNT(*) FILTER (WHERE speed_mph > 70) AS overspeed_samples,
AVG(engine_rpm) FILTER (WHERE speed_mph > 0) AS avg_moving_rpm,
COUNT(*) FILTER (WHERE idle_engine_on) AS idle_samples,
ROUND(COUNT(*) FILTER (WHERE idle_engine_on) * 100.0 / NULLIF(COUNT(*), 0), 2) AS idle_time_pct,
COUNT(*) FILTER (WHERE harsh_brake) AS harsh_brakes,
COUNT(*) FILTER (WHERE harsh_accel) AS harsh_accels
FROM TUMBLE(truck_telemetry, event_ts, INTERVAL '1 HOUR')
WHERE ignition_on
GROUP BY vin, driver_id, window_start, window_end;
CREATE MATERIALIZED VIEW fleet_fuel_benchmark_24h AS
SELECT window_start, window_end,
AVG(km_per_liter) AS fleet_avg_km_per_l,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY km_per_liter) AS p25_km_per_l,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY km_per_liter) AS p75_km_per_l,
AVG(idle_time_pct) AS fleet_avg_idle_pct,
SUM(est_fuel_consumed_l) AS fleet_total_fuel_l,
SUM(idle_fuel_l) AS fleet_total_idle_fuel_l
FROM TUMBLE(truck_fuel_hourly, window_end, INTERVAL '24 HOURS')
WHERE distance_km > 20
GROUP BY window_start, window_end;
CREATE MATERIALIZED VIEW fuel_alerts AS
SELECT vin, driver_id, window_end AS alert_ts, 'EXCESSIVE_IDLE' AS alert_type, idle_samples / 6.0 AS metric
FROM truck_fuel_hourly
WHERE idle_time_pct > 30.0 AND idle_samples > 180
UNION ALL
SELECT h.vin, h.driver_id, h.window_end, 'LOW_FUEL_EFFICIENCY', ROUND((h.km_per_liter / NULLIF(b.fleet_avg_km_per_l, 0))::NUMERIC, 2)
FROM truck_fuel_hourly h
JOIN fleet_fuel_benchmark_24h b ON h.window_end > b.window_start AND h.window_end <= b.window_end
WHERE h.km_per_liter < b.fleet_avg_km_per_l * 0.70 AND h.distance_km > 50;
CREATE MATERIALIZED VIEW fuel_reconciliation_daily AS
SELECT t.vin, t.window_start::DATE AS day,
SUM(t.est_fuel_consumed_l) AS telematics_est_l,
SUM(f.fuel_liters) AS card_actual_l,
SUM(f.cost_usd) AS fuel_cost_usd,
ABS(SUM(t.est_fuel_consumed_l) - SUM(f.fuel_liters)) / NULLIF(SUM(f.fuel_liters), 0) * 100 AS variance_pct
FROM truck_fuel_hourly t
LEFT JOIN fuel_transactions f ON t.vin = f.vin AND f.transaction_ts::DATE = t.window_start::DATE
GROUP BY t.vin, t.window_start::DATE;
A dispatcher who can call a driver the moment idle time passes 30 minutes changes behavior that a weekly coaching report never does. A large variance between telematics and fuel card purchases points at a sensor problem or at fuel that did not go into the truck.
Hours of Service Compliance
Federal hours-of-service rules set an 11-hour driving limit, a 14-hour on-duty window, a 30-minute break requirement, and 60 or 70-hour weekly limits, and violations bring fines and out-of-service orders. Electronic logging devices publish every duty status change; the compliance view turns consecutive changes into durations with LAG and accumulates them within each regulatory window, so dispatch sees that a driver has 45 minutes left before assigning a load.
CREATE MATERIALIZED VIEW duty_segments AS
SELECT driver_id, vin, duty_status, event_time AS segment_start,
LEAD(event_time) OVER (PARTITION BY driver_id ORDER BY event_time) AS segment_end,
EXTRACT(EPOCH FROM (LEAD(event_time) OVER (PARTITION BY driver_id ORDER BY event_time) - event_time)) / 3600.0 AS segment_hours
FROM eld_duty_events;
CREATE MATERIALIZED VIEW driver_hos_14h AS
SELECT driver_id,
SUM(segment_hours) FILTER (WHERE duty_status = 'DRIVING') AS driving_hours,
SUM(segment_hours) FILTER (WHERE duty_status IN ('DRIVING', 'ON_DUTY_NOT_DRIVING')) AS on_duty_hours,
MAX(segment_start) FILTER (WHERE duty_status IN ('OFF_DUTY', 'SLEEPER_BERTH') AND segment_hours >= 0.5) AS last_break_start,
MIN(segment_start) FILTER (WHERE duty_status IN ('DRIVING', 'ON_DUTY_NOT_DRIVING')) AS duty_window_start
FROM duty_segments
WHERE segment_end IS NOT NULL AND segment_start > NOW() - INTERVAL '14 HOURS'
GROUP BY driver_id;
CREATE MATERIALIZED VIEW driver_hos_8day AS
SELECT driver_id, SUM(segment_hours) FILTER (WHERE duty_status IN ('DRIVING', 'ON_DUTY_NOT_DRIVING')) AS on_duty_hours_8d
FROM duty_segments
WHERE segment_end IS NOT NULL AND segment_start > NOW() - INTERVAL '8 DAYS'
GROUP BY driver_id;
CREATE MATERIALIZED VIEW hos_compliance_alerts AS
SELECT d.driver_id, d.driving_hours, d.on_duty_hours, w.on_duty_hours_8d, d.last_break_start,
CASE WHEN d.driving_hours >= 11 THEN 'AT_DRIVING_LIMIT'
WHEN d.driving_hours >= 10 THEN 'APPROACHING_11H_LIMIT'
WHEN d.on_duty_hours >= 13 THEN 'APPROACHING_14H_WINDOW'
WHEN w.on_duty_hours_8d >= 65 THEN 'APPROACHING_70H_LIMIT'
WHEN d.driving_hours >= 8 AND (d.last_break_start IS NULL
OR d.last_break_start < NOW() - INTERVAL '8 HOURS') THEN 'BREAK_REQUIRED' END AS compliance_status
FROM driver_hos_14h d
LEFT JOIN driver_hos_8day w USING (driver_id)
WHERE d.driving_hours >= 8 OR d.on_duty_hours >= 13 OR w.on_duty_hours_8d >= 65;
The current open segment (no LEAD yet) is the driver's present status and its duration is NOW() minus its start; include it by unioning the latest event per driver into the sums. The raw ELD stream in Kafka is the immutable audit log; the views are derived state, which is how regulators expect it.
Engine Diagnostics and Overheating
Engine failure is preceded by hours or days of degrading readings. Five-minute rolling engine health per truck, an hour of active fault codes, and critical alerts for overheating, low oil pressure, and severe codes give maintenance a window to schedule rather than tow.
CREATE MATERIALIZED VIEW engine_health_5m AS
SELECT vin, window_start, window_end,
AVG(engine_rpm) AS avg_rpm, MAX(engine_rpm) AS max_rpm,
AVG(coolant_temp_c) AS avg_coolant_temp, MAX(coolant_temp_c) AS max_coolant_temp,
AVG(oil_pressure_kpa) AS avg_oil_pressure, MIN(oil_pressure_kpa) AS min_oil_pressure,
AVG(torque_nm) AS avg_torque,
COUNT(dtc_code) AS dtc_event_count, MAX(dtc_severity) AS max_dtc_severity
FROM TUMBLE(truck_telemetry, event_ts, INTERVAL '5 MINUTES')
WHERE ignition_on
GROUP BY vin, window_start, window_end;
CREATE MATERIALIZED VIEW active_dtc_summary AS
SELECT vin, dtc_code, dtc_severity, COUNT(*) AS occurrence_count, MIN(event_ts) AS first_seen, MAX(event_ts) AS last_seen,
CASE WHEN dtc_code LIKE 'P0%' THEN 'POWERTRAIN' WHEN dtc_code LIKE 'P1%' THEN 'MANUFACTURER' WHEN dtc_code LIKE 'U%' THEN 'NETWORK' ELSE 'OTHER' END AS fault_category
FROM truck_telemetry
WHERE dtc_code IS NOT NULL AND dtc_code <> '' AND event_ts >= NOW() - INTERVAL '1 HOUR'
GROUP BY vin, dtc_code, dtc_severity;
CREATE MATERIALIZED VIEW engine_critical_alerts AS
SELECT vin, window_end AS alert_ts, avg_coolant_temp, min_oil_pressure, max_dtc_severity, dtc_event_count,
CASE WHEN avg_coolant_temp > 105 THEN 'OVERHEAT'
WHEN min_oil_pressure < 140 THEN 'LOW_OIL_PRESSURE'
ELSE 'CRITICAL_DTC' END AS alert_type
FROM engine_health_5m
WHERE avg_coolant_temp > 105 OR min_oil_pressure < 140 OR max_dtc_severity >= 3;
CREATE MATERIALIZED VIEW speed_violations AS
SELECT vin, driver_id, window_start, window_end, AVG(speed_mph) AS avg_speed_mph, MAX(speed_mph) AS max_speed_mph
FROM TUMBLE(truck_telemetry, event_ts, INTERVAL '5 MINUTES')
WHERE ignition_on
GROUP BY vin, driver_id, window_start, window_end
HAVING AVG(speed_mph) > 70.0;
Joining engine_critical_alerts to fleet_live_position gives the alert a location, which is what the dispatcher needs to route the truck to the nearest service point. Correlating active_dtc_summary across the fleet by code and vehicle model, from a CDC asset table, is how warranty patterns are found while the claims are still valid.
Predictive Tire Maintenance
Blowouts are among the most preventable and costly incidents on the road. Tire pressure monitoring and axle load sensors stream continuously; a 30-minute window per wheel position tracks minimum pressure (slow leaks that a point reading misses), temperature (brake drag, bearing trouble, under-inflation), and vibration (tread separation), and comparing axle loads across positions catches load imbalance that accelerates wear.
CREATE MATERIALIZED VIEW tire_health_30m AS
SELECT vin, axle_position, window_start, window_end,
AVG(tire_pressure_kpa) AS avg_pressure_kpa, MIN(tire_pressure_kpa) AS min_pressure_kpa,
MAX(tire_pressure_kpa) - MIN(tire_pressure_kpa) AS pressure_range_kpa,
AVG(tire_temp_c) AS avg_temp_c, MAX(tire_temp_c) AS max_temp_c,
AVG(axle_load_kg) AS avg_axle_load_kg,
AVG(vibration_hz) AS avg_vibration_hz, MAX(vibration_hz) AS max_vibration_hz,
COUNT(*) AS reading_count
FROM TUMBLE(tpms_axle_readings, event_time, INTERVAL '30 MINUTES')
GROUP BY vin, axle_position, window_start, window_end;
CREATE MATERIALIZED VIEW axle_load_balance AS
SELECT vin, window_start, window_end,
MAX(avg_axle_load_kg) - MIN(avg_axle_load_kg) AS load_imbalance_kg,
COUNT(DISTINCT axle_position) AS positions_reporting
FROM tire_health_30m
GROUP BY vin, window_start, window_end
HAVING COUNT(DISTINCT axle_position) >= 4;
CREATE MATERIALIZED VIEW tire_maintenance_alerts AS
SELECT vin, axle_position, window_end AS alert_ts, min_pressure_kpa, pressure_range_kpa, max_temp_c, max_vibration_hz,
CASE WHEN min_pressure_kpa < 620 THEN 'LOW_PRESSURE_CRITICAL'
WHEN pressure_range_kpa > 40 THEN 'PRESSURE_LOSS_DETECTED'
WHEN max_temp_c > 90 THEN 'THERMAL_ANOMALY'
ELSE 'VIBRATION_ALERT' END AS alert_type
FROM tire_health_30m
WHERE min_pressure_kpa < 620 OR pressure_range_kpa > 40 OR max_temp_c > 90 OR max_vibration_hz > 150;
A pressure drop of 40 kPa within 30 minutes on one wheel while the others hold steady is a puncture, and the alert reaches dispatch with hours to spare before the next terminal stop. A hot tire paired with a normal pressure is usually a dragging brake on that wheel end.
Routing Alerts and Serving Dispatch
Geofence, stall, engine, fuel, hours-of-service, and tire alerts each sink to the team that acts on them. The live position view sinks to the dispatch database for the map.
CREATE SINK geofence_alert_sink FROM geo_fence_violations
WITH (connector = 'kafka', topic = 'alerts.fleet.geofencing', properties.bootstrap.server = 'kafka:9092') FORMAT PLAIN ENCODE JSON;
CREATE SINK fleet_critical_alerts AS
SELECT vin, alert_ts, alert_type, avg_coolant_temp::VARCHAR AS detail FROM engine_critical_alerts
UNION ALL
SELECT vin, alert_ts, alert_type, metric::VARCHAR FROM fuel_alerts
UNION ALL
SELECT vin, alert_ts, alert_type, min_pressure_kpa::VARCHAR FROM tire_maintenance_alerts
UNION ALL
SELECT vin, last_seen, 'STALLED_VEHICLE', job_id FROM stalled_vehicle_alerts
WITH (connector = 'kafka', topic = 'alerts.fleet.critical', properties.bootstrap.server = 'kafka:9092') FORMAT PLAIN ENCODE JSON;
CREATE SINK hos_alerts_sink FROM hos_compliance_alerts
WITH (connector = 'kafka', topic = 'fleet.hos.alerts', properties.bootstrap.server = 'kafka:9092') FORMAT UPSERT ENCODE JSON;
CREATE SINK rerouting_sink FROM rerouting_candidates
WITH (connector = 'kafka', topic = 'fleet.rerouting.candidates', properties.bootstrap.server = 'kafka:9092') FORMAT PLAIN ENCODE JSON;
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', type = 'upsert', primary_key = 'vin');
At 1,000 vehicles transmitting every five seconds, 720,000 events an hour, the pipeline is well within range, because every view updates only the affected vehicle's rows and Kafka sources and sinks are processed exactly once across restarts.
Polling and Batch vs Streaming SQL for Fleets
| Concern | Polling or nightly report | Streaming SQL (RisingWave) |
|---|---|---|
| Geofence entry | Caught at the next 30 to 60 second poll | Evaluated on the GPS event, sub-second |
| Dispatch map | A minute old | Latest ping per vehicle, upserted to the dashboard |
| Stalled vehicle | Noticed when the delivery fails | Position plus active job, 15-minute check |
| Rerouting | Replan every 30 minutes or manual | Candidates on each traffic event near a vehicle with open stops |
| Idle fuel waste | Weekly coaching report | Hourly idle percent, call the driver within the hour |
| Hours of service | Dashboard refresh, violation after the fact | Durations accumulated per duty change, warning at 10 hours |
| Engine faults | Read from the warehouse daily | Five-minute health plus active codes, located on the map |
| Tire leaks | Visual check at the terminal | 30-minute pressure range per wheel, alert before the blowout |
Frequently Asked Questions
How do I handle polygon geofences? Do the point-in-polygon check at the enrichment layer before Kafka and tag each ping with the fence IDs it falls in, or approximate the polygon with a bounding box pre-filter plus a circular check. RisingWave then maintains status, dwell, and alerts the same way.
How are GPS gaps distinguished from stationary vehicles?
Use staleness in fleet_live_position for gaps and a session window or LAG over coordinates for stationary. A vehicle with fresh pings and zero movement is parked; a vehicle with no pings is dark, which is its own alert.
Can hours of service be computed exactly rather than estimated?
Yes, the duty_segments pattern computes real durations from consecutive status changes with LEAD. Add the open segment per driver using the latest event and NOW() to include the current status.
How accurate is fuel consumption from the J1939 fuel rate?
Within a few percent when sampling is regular. The hourly estimate assumes ten-second samples; for irregular intervals, integrate fuel rate over the actual gap using LAG on the timestamp. The fuel card reconciliation view tells you how far off you are.
Can the route optimizer run inside RisingWave? No, and it should not. RisingWave maintains the live state and the rerouting triggers; a solver reads them over PostgreSQL, writes a new plan version, and the plan flows back as reference data.
