Logistics

Logistics Case Study and SQL Recipe Book

Enable IoT Query before utilizing data for building comprehensive analytics. If you don't have it yet, contact us for activation details - [email protected]

Logistics is a complex ecosystem involving the coordination of transport, warehouse operations, inventory, and delivery execution. Integrating telematics into logistics processes allows companies to collect real-time data on vehicles, drivers, routes, and cargo conditions, which significantly improves decision-making and operational efficiency.

Navixy IoT Query, with its robust data ingestion and time-series analytics capabilities, supports the digital transformation of logistics operations by enabling deep visibility into each lifecycle stage. Its robust telematics ingestion capabilities, provides comprehensive visibility into these operations. Real-time GPS data, sensor data diagnostics, geofencing, and sensor analytics allow logistics operators to digitize workflows, automate controls, and make informed decisions.

Lifecycle Phase	Goals	Covered Use Cases / Recipes
Route management	Optimize vehicle routing, ensure efficient dispatching, and reduce delays	Trip Counts per Day Mileage Count per Vehicle per Day (Last 7 Days)
Cargo Monitoring	Ensure proper transport conditions for sensitive goods	Temperature (and Humidity) Violation Events in the Last 7 Days
Vehicle Operation	Track fleet utilization, ensure maintenance, and reduce downtime	Engine Hours Summary per Vehicle / Driver / Day (Last 7 Days) Vehicle Downtime Analysis Asset Tracking Without Movement
Route Security & Safety	Detect misuse, unauthorized activity, and safety violations	Route Deviation Detection- Unauthorized Stops (Last 24 Hours) Off-Hour Usage Detection
Compliance Management	Monitor driver behavior, enforce policies and operational compliance	Engine Hours Summary per Vehicle / Driver / Day (Last 7 Days) Off-Hour Usage Detection
Post-Delivery Analysis	Evaluate operational efficiency and historical performance	Vehicle Event Log Report Mileage Count per Vehicle per Day (Last 7 Days) Trip Counts per Day Asset Tracking Without Movement

Asset Tracking Without Movement

This case identifies assets (e.g., vehicles or trailers) that have not changed their GPS compare the minimum and maximum coordinates during the period. If both values fall within a very narrow range (a tolerance threshold, e.g., ±0.01 degrees), we flag the asset as non-moving. The query also joins with the objects and vehicles tables in raw_business_data to retrieve meaningful asset labels for the result output.

WITH gps_bounds AS (
    SELECT
        td.device_id,
        MIN(td.latitude) AS min_lat,
        MAX(td.latitude) AS max_lat,
        MIN(td.longitude) AS min_lon,
        MAX(td.longitude) AS max_lon,
        COUNT(*) AS location_records
    FROM raw_telematics_data.tracking_data_core td
    WHERE td.device_time >= now() - interval '48 hours'
    GROUP BY td.device_id
),
stationary_devices AS (
    SELECT
        device_id
    FROM gps_bounds
    WHERE location_records > 10 -- exclude devices with very sparse data
	AND((max_lat - min_lat) <= 2000 -- ~10 meters
		OR
      	(max_lon - min_lon) <= 1000  -- ~10 meters
    		)
SELECT
    v.vehicle_id,
    v.vehicle_label,
    o.object_id,
    o.object_label,
    sd.device_id,
    gb.min_lat / 1e7 AS latitude,
    gb.min_lon / 1e7 AS longitude,
    gb.location_records
FROM stationary_devices sd
JOIN gps_bounds gb ON sd.device_id = gb.device_id
JOIN raw_business_data.objects o ON o.device_id = sd.device_id
LEFT JOIN raw_business_data.vehicles v ON v.object_id = o.object_id
ORDER BY gb.location_records DESC;

Vehicle Downtime Analysis

This case focuses on analyzing how long vehicles are non-operational due to maintenance, breakdowns, or inactivity. Downtime metrics are crucial for logistics operations to monitor fleet health, reduce idle time, and improve overall utilization and scheduling efficiency.

The core of the downtime analysis lies in leveraging the vehicle_service_tasks table from raw_business_data, which logs both planned and unplanned maintenance events. Each task contains a start_date and an end_date, representing the downtime period. By filtering for completed service tasks, we can compute the exact duration each vehicle was out of operation.

The query calculates total downtime per vehicle by summing up the durations of all its service tasks (in hours). It also allows breakdown by planned vs unplanned maintenance by using the is_unplanned flag. To make the results more actionable, it joins with the vehicles table to include vehicle labels, registration numbers, and model information.

WITH downtime_durations AS (
    SELECT
        vst.vehicle_id,
        vst.is_unplanned,
        vst.start_date,
        vst.end_date,
        EXTRACT(EPOCH FROM (vst.end_date - vst.start_date))/3600 AS downtime_hours
    FROM raw_business_data.vehicle_service_tasks vst
    WHERE vst.status = 'done'
      AND vst.start_date IS NOT NULL
      AND vst.end_date IS NOT NULL
)
SELECT
    v.vehicle_id,
    v.vehicle_label,
    v.registration_number,
    v.model,
    COUNT(dd.*) AS total_service_events,
    SUM(dd.downtime_hours) AS total_downtime_hours,
    SUM(dd.downtime_hours) FILTER (WHERE dd.is_unplanned = TRUE) AS unplanned_downtime_hours,
    SUM(dd.downtime_hours) FILTER (WHERE dd.is_unplanned = FALSE) AS planned_downtime_hours
FROM downtime_durations dd
JOIN raw_business_data.vehicles v ON v.vehicle_id = dd.vehicle_id
GROUP BY v.vehicle_id, v.vehicle_label, v.registration_number, v.model
ORDER BY total_downtime_hours DESC;

Route Deviation Detection

This case identifies instances where vehicles deviate from their assigned or expected routes — particularly geofenced zones or delivery corridors. Tracking such deviations helps in ensuring route compliance, reducing delays, detecting risky driving behavior, and maintaining delivery SLAs.

This logic compares the vehicle’s actual GPS positions from tracking_data_core (in the raw_telematics_data schema) against predefined geographic zones from the zones table in raw_business_data. These zones represent assigned routes or route segments. Using geometric comparisons via ST_DWithin, we determine whether a point is inside or outside the buffered route area.

The query joins each GPS position with every known route zone using a spatial CROSS JOIN, then applies ST_DWithin() to check if the vehicle was within the permitted corridor. We isolate the rows where the vehicle was outside all geofenced routes and flag them as deviations. The final output lists these deviations, including the device, timestamp, vehicle label, and how far the point was from the nearest zone center.

WITH positions AS (
    SELECT
        td.device_id,
        td.device_time,
        o.object_id,
        o.object_label,
        z.zone_id,
        z.zone_label,
        ST_SetSRID(ST_MakePoint(td.longitude / 1e7, td.latitude / 1e7), 4326)::geography AS gps_point,
        ST_Buffer(
            ST_SetSRID(ST_MakePoint(z.circle_center_longitude, z.circle_center_latitude), 4326)::geography,
            z.radius
        ) AS route_buffer
    FROM raw_telematics_data.tracking_data_core td
    JOIN raw_business_data.objects o ON td.device_id = o.device_id
    CROSS JOIN raw_business_data.zones z
    WHERE td.device_time >= now() - interval '2 days'
),
evaluated AS (
    SELECT
        device_id,
        device_time,
        object_id,
        object_label,
        zone_id,
        zone_label,
        NOT ST_DWithin(gps_point, route_buffer, 0) AS is_deviation,
        ST_Distance(gps_point, route_buffer) AS deviation_distance_meters
    FROM positions
),
deviations_only AS (
    SELECT *
    FROM evaluated
    WHERE is_deviation = true
)
SELECT
    device_id,
    object_label,
    zone_label,
    device_time,
    deviation_distance_meters
FROM deviations_only
ORDER BY device_time DESC;

Engine Hours Summary per Vehicle / Driver / Day (Last 7 Days)

This case measures how long engines were active for each vehicle on a daily basis, allowing fleet managers to track utilization, identify overuse or underuse, and correlate activity with driver assignments. When tied to drivers, it also supports labor hour validation and performance analysis.

The states table in raw_telematics_data records time-series engine state indicators, typically with a state_name like 'ignition' and a value of 1 (on) or 0 (off). To calculate engine hours, we find all timestamped transitions for each device and compute durations where the engine was on (1).

To tie engine activity to both vehicles and drivers, we use the objects, vehicles, and driver_history tables from raw_business_data. We associate each state record to the current driver on that object (via driver assignment history) and to the corresponding vehicle. We then group the data by day, vehicle, and driver, summing total active engine time (in hours).

WITH inputs_core AS (
   SELECT
       i.device_id,
       i.device_time,
       i.event_id,
       i.record_added_at,
       i.sensor_name,
       i.value
   FROM raw_telematics_data.inputs i
   WHERE i.device_time >= now() - interval '7 days'
),
clear_inputs AS (
   SELECT
       i.device_id,
       i.device_time,
       i.event_id,
       i.record_added_at,
       i.sensor_name,
       i.value,
       LAG(i.device_time) OVER (PARTITION BY i.device_id ORDER BY i.device_time) AS prev_time,
       LAG(i.value::int) OVER (PARTITION BY i.device_id ORDER BY i.device_time) AS prev_status
   FROM inputs_core i
   LEFT JOIN raw_business_data.sensor_description sd
       ON sd.input_label = i.sensor_name
      AND sd.device_id = i.device_id
   WHERE sd.sensor_type = 'engine'
     AND i.value = '1'
),
engine_on_periods AS (
   SELECT
       device_id,
       prev_time AS engine_on_time,
       device_time AS engine_off_time,
       device_time::date AS activity_day,
       EXTRACT(EPOCH FROM (device_time - prev_time)) / 3600 AS engine_hours
   FROM clear_inputs
   WHERE value::int = 0 AND prev_status = 1  -- transitions from ON to OFF
),
enriched_with_objects AS (
   SELECT
       eop.*,
       o.object_id,
       o.object_label,
       v.vehicle_id,
       v.vehicle_label,
       v.registration_number
   FROM engine_on_periods eop
   JOIN raw_business_data.objects o ON o.device_id = eop.device_id
   LEFT JOIN raw_business_data.vehicles v ON v.object_id = o.object_id
),
assigned_drivers AS (
   SELECT
       ewo.*,
       dh.new_employee_id as employee_id
   FROM enriched_with_objects ewo
   LEFT JOIN LATERAL (
       SELECT d.*
       FROM raw_business_data.driver_history d
       WHERE d.object_id = ewo.object_id
         AND d.changed_datetime <= ewo.engine_on_time
       ORDER BY d.changed_datetime DESC
       LIMIT 1
   ) dh ON true
)
SELECT
   activity_day,
   vehicle_label,
   registration_number,
   object_label,
   e.first_name || ' ' || e.last_name AS driver_name,
   SUM(engine_hours) AS total_engine_hours
FROM assigned_drivers ad
LEFT JOIN raw_business_data.employees e ON ad.employee_id = e.employee_id
GROUP BY activity_day, vehicle_label, registration_number, object_label, driver_name
ORDER BY activity_day DESC, vehicle_label;

Temperature (and Humidity) Violation Events in the Last 7 Days

This case identifies sensor readings — such as temperature or humidity — that exceed critical thresholds during transport. Monitoring such violations is vital for industries transporting perishable goods (e.g., food, pharmaceuticals) to ensure compliance with cold chain requirements and to prevent spoilage.

This query extracts sensor input data from the inputs table in the raw_telematics_data schema. Each row represents a sensor reading (e.g., temperature, humidity) recorded at a specific timestamp by a device. We filter these records to include only those from the last 7 days.

The main filtering logic is based on sensor name patterns and a comparison of their numeric values against thresholds (e.g., >25°C for temperature, >80% for humidity). Because value is stored as text, we cast it to numeric before applying the threshold conditions. To enrich the results, we join with the objects table to retrieve vehicle or asset labels, which improves interpretability for fleet managers.

WITH recent_sensor_data AS (
   SELECT
       i.device_id,
       i.device_time,
       i.sensor_name,
       i.value::float
   FROM raw_telematics_data.inputs i
   WHERE i.device_time >= now() - interval '1 hour'
),
calibration_data AS (
   SELECT
       sensor_id,
       value AS cal_value,
       volume AS cal_volume
   FROM raw_business_data.sensor_calibration_data
),
sensor_meta AS (
   SELECT
       sd.device_id,
       sd.input_label,
       sd.sensor_id
   FROM raw_business_data.sensor_description sd
),
joined_data AS (
   SELECT
       rsd.*,
       sm.sensor_id
   FROM recent_sensor_data rsd
   LEFT JOIN sensor_meta sm
       ON rsd.device_id = sm.device_id AND rsd.sensor_name = sm.input_label
),
calibrated_data AS (
   SELECT
       jd.device_id,
       jd.device_time,
       jd.sensor_name,
       jd.value,
       jd.sensor_id,
       CASE
           WHEN cd_low.cal_value IS NOT NULL AND cd_high.cal_value IS NOT NULL THEN
               CASE
                   WHEN cd_high.cal_value = cd_low.cal_value THEN cd_low.cal_volume
                   ELSE cd_low.cal_volume +
                       ((jd.value - cd_low.cal_value) / NULLIF(cd_high.cal_value - cd_low.cal_value, 0))
                       * (cd_high.cal_volume - cd_low.cal_volume)
               END
           ELSE jd.value
       END AS calibrated_value
   FROM joined_data jd
   LEFT JOIN LATERAL (
       SELECT * FROM calibration_data
       WHERE sensor_id = jd.sensor_id AND cal_value <= jd.value
       ORDER BY cal_value DESC LIMIT 1
   ) cd_low ON TRUE
   LEFT JOIN LATERAL (
       SELECT * FROM calibration_data
       WHERE sensor_id = jd.sensor_id AND cal_value >= jd.value
       ORDER BY cal_value ASC LIMIT 1
   ) cd_high ON TRUE
),
violations AS (
   SELECT
       cd.device_id,
       cd.device_time,
       cd.sensor_name,
       cd.calibrated_value,
       CASE
           WHEN sd.sensor_type = 'temperature' AND cd.calibrated_value > 25 THEN 'High Temperature'
           WHEN sd.sensor_type = 'temperature' AND cd.calibrated_value < 0 THEN 'Low Temperature'
           WHEN sd.sensor_type = 'humidity' AND cd.calibrated_value > 80 THEN 'High Humidity'
           ELSE NULL
       END AS violation_type
   FROM calibrated_data cd
   left join raw_business_data.sensor_description sd on sd.device_id = cd.device_id and sd.input_label = cd.sensor_name
   WHERE sensor_type in ('temperature', 'humidity')
   )
SELECT
   v.vehicle_label,
   o.object_label,
   v.registration_number,
   v.model,
   vio.device_time,
   vio.sensor_name,
   vio.calibrated_value,
   vio.violation_type
FROM violations vio
JOIN raw_business_data.objects o ON o.device_id = vio.device_id
LEFT JOIN raw_business_data.vehicles v ON v.object_id = o.object_id
ORDER BY vio.device_time DESC;

Unauthorized Stops (Last 24 Hours)

This case identifies unauthorized or unplanned stops made by vehicles within the past 24 hours. It helps in detecting potential violations of delivery routes, unauthorized breaks, or idle time that may affect fuel efficiency and SLA performance.

The query analyzes location points with low or zero speed using the The query uses the tracking_data_core table from raw_telematics_data to extract time-series location data and speed. A stop is detected when speed drops below 3 km/h for a duration of more than 2 minutes. Using LAG and LEAD functions, the query segments these low-speed periods to determine stop start and end timestamps.

To detect unauthorized stops, it filters out locations that fall within known geofenced zones (zones table) using PostGIS's ST_DWithin. Only stops outside of any zone buffer are reported. The result includes vehicle ID, object label, registration, timestamps, duration, and coordinates for each stop.

WITH speed_data AS (
    SELECT
        td.device_id,
        td.device_time,
        td.speed / 100.0 AS speed_kph,
        td.latitude / 1e7 AS lat,
        td.longitude / 1e7 AS lon
    FROM raw_telematics_data.tracking_data_core td
    WHERE td.device_time >= now() - interval '1 day'
),
low_speed_points AS (
    SELECT
        *,
        LAG(device_time) OVER (PARTITION BY device_id ORDER BY device_time) AS prev_time,
        LAG(speed_kph) OVER (PARTITION BY device_id ORDER BY device_time) AS prev_speed
    FROM speed_data
),
stops_marked AS (
    SELECT *,
        CASE 
            WHEN speed_kph < 3 AND (prev_speed >= 3 OR prev_speed IS NULL) THEN 1 
            ELSE 0 
        END AS stop_start,
        CASE 
            WHEN speed_kph >= 3 AND prev_speed < 3 THEN 1 
            ELSE 0 
        END AS stop_end
    FROM low_speed_points
),
stop_segments AS (
    SELECT
        device_id,
        device_time AS stop_start_time,
        LEAD(device_time) OVER (PARTITION BY device_id ORDER BY device_time) AS stop_end_time,
        lat,
        lon
    FROM stops_marked
    WHERE stop_start = 1
),
unauthorized_stops AS (
    SELECT
        ss.*,
        EXTRACT(EPOCH FROM (ss.stop_end_time - ss.stop_start_time))/60 AS stop_duration_min
    FROM stop_segments ss
    LEFT JOIN raw_business_data.zones z ON
        ST_DWithin(
            ST_SetSRID(ST_MakePoint(ss.lon, ss.lat), 4326)::geography,
            ST_SetSRID(ST_MakePoint(z.circle_center_longitude, z.circle_center_latitude), 4326)::geography,
            z.radius
        )
    WHERE z.zone_id IS NULL  -- not inside any known zone
      AND EXTRACT(EPOCH FROM (ss.stop_end_time - ss.stop_start_time)) > 120  -- minimum 2 minutes
),
with_metadata AS (
    SELECT
        us.*,
        o.object_label,
        v.vehicle_label,
        v.registration_number
    FROM unauthorized_stops us
    JOIN raw_business_data.objects o ON o.device_id = us.device_id
    LEFT JOIN raw_business_data.vehicles v ON v.object_id = o.object_id
)
SELECT
    vehicle_label,
    registration_number,
    object_label,
    stop_start_time,
    stop_end_time,
    ROUND(stop_duration_min, 1) AS stop_duration_minutes,
    ROUND(lat, 6) AS latitude,
    ROUND(lon, 6) AS longitude
FROM with_metadata
ORDER BY stop_start_time DESC;

Off-Hour Usage Detection

This case identifies instances where vehicles are operated outside normal business hours — defined here as Monday through Friday, 09:00–18:00. Such detections are essential for flagging unauthorized use, identifying potential vehicle misuse, and improving asset security.

The logic is built on the tracking_data_core table from raw_telematics_data, which logs timestamped GPS events per device. We derive local day of week and hour of use from each device_time entry and filter for records outside the defined business window (i.e., before 9 AM, after 6 PM, or any time on weekends).

To provide clarity, we enrich the GPS data with object and vehicle metadata from raw_business_data (e.g., vehicle label, registration, object ID). For more meaningful summaries, we optionally aggregate the usage to count how many off-hour events occurred per vehicle and when they happened. This can help in identifying patterns or repeat offenders.

WITH gps_events AS (
    SELECT
        td.device_id,
        td.device_time,
        EXTRACT(DOW FROM td.device_time) AS day_of_week, -- 0=Sunday, 6=Saturday
        EXTRACT(HOUR FROM td.device_time) AS hour_of_day,
        td.latitude / 1e7 AS latitude,
        td.longitude / 1e7 AS longitude
    FROM raw_telematics_data.tracking_data_core td
    WHERE td.device_time >= now() - interval '7 days'
),
off_hour_events AS (
    SELECT
        ge.*
    FROM gps_events ge
    WHERE 
        day_of_week IN (0, 6)  -- Saturday or Sunday
        OR hour_of_day < 9 
        OR hour_of_day >= 18
),
with_metadata AS (
    SELECT
        o.object_label,
        v.vehicle_label,
        v.registration_number,
        e.first_name || ' ' || e.last_name AS driver_name,
        o.device_id,
        o.object_id,
        o.create_datetime,
        ge.device_time,
        ge.latitude,
        ge.longitude,
        ge.day_of_week,
        ge.hour_of_day
    FROM off_hour_events ge
    JOIN raw_business_data.objects o ON ge.device_id = o.device_id
    LEFT JOIN raw_business_data.vehicles v ON v.object_id = o.object_id
    LEFT JOIN raw_business_data.driver_history dh ON dh.object_id = o.object_id 
        AND dh.changed_datetime <= ge.device_time
    LEFT JOIN raw_business_data.employees e ON e.employee_id = dh.new_employee_id
)
SELECT
    object_label,
    vehicle_label,
    registration_number,
    driver_name,
    device_time,
    TO_CHAR(device_time, 'Day') AS weekday,
    TO_CHAR(device_time, 'HH24:MI') AS time_of_event,
    ROUND(latitude::numeric, 6) AS lat,
    ROUND(longitude::numeric, 6) AS lon
FROM with_metadata
ORDER BY device_time DESC;

Trip counts per day

This case measures how many trips each vehicle completes daily and how far they travel, helping logistics teams assess vehicle usage, optimize routes, and detect anomalies like incomplete trips or unreported usage.

To define a trip, we use a change in vehicle movement state — i.e., transition from stopped to moving and back to stopped. Using the speed values from the tracking_data_core table, the query segments the data based on these transitions. A trip is identified as a continuous movement period where the speed stays above a threshold (e.g., >5 km/h).

Each trip includes:

• A start timestamp and location (first moving point)

• An end timestamp and location (last moving point before stopping)

• The Haversine distance between start and end locations

We calculate the trip count and total distance per day per vehicle, optionally enriched with vehicle labels from the vehicles table.

WITH base_points AS (
    SELECT
        td.device_id,
        td.device_time,
        td.latitude / 1e7 AS lat,
        td.longitude / 1e7 AS lon,
        td.speed / 100.0 AS speed_kph,
        LEAD(td.speed / 100.0) OVER (PARTITION BY td.device_id ORDER BY td.device_time) AS next_speed,
        LAG(td.speed / 100.0) OVER (PARTITION BY td.device_id ORDER BY td.device_time) AS prev_speed
    FROM raw_telematics_data.tracking_data_core td
    WHERE td.device_time >= now() - interval '7 days'
),
trip_segments AS (
    SELECT
        *,
        CASE 
            WHEN speed_kph >= 5 AND (prev_speed < 5 OR prev_speed IS NULL) THEN 'start'
            WHEN speed_kph < 5 AND prev_speed >= 5 THEN 'end'
        END AS trip_marker
    FROM base_points
),
trip_points AS (
    SELECT
        device_id,
        device_time AS trip_start_time,
        lat AS start_lat,
        lon AS start_lon,
        LEAD(device_time) OVER (PARTITION BY device_id ORDER BY device_time) AS trip_end_time,
        LEAD(lat) OVER (PARTITION BY device_id ORDER BY device_time) AS end_lat,
        LEAD(lon) OVER (PARTITION BY device_id ORDER BY device_time) AS end_lon
    FROM trip_segments
    WHERE trip_marker = 'start'
),
trip_metrics AS (
    SELECT
        tp.device_id,
        tp.trip_start_time,
        tp.trip_end_time,
        tp.start_lat,
        tp.start_lon,
        tp.end_lat,
        tp.end_lon,
        tp.trip_start_time::date AS trip_day,
        -- Approximate distance using Haversine formula (in km)
        111 * SQRT(POWER(tp.end_lat - tp.start_lat, 2) + POWER((tp.end_lon - tp.start_lon) * COS(RADIANS(tp.start_lat)), 2)) AS distance_km
    FROM trip_points tp
    WHERE tp.trip_end_time IS NOT NULL
)
SELECT
    v.vehicle_label,
    v.registration_number,
    tm.device_id,
    tm.trip_day,
    COUNT(*) AS trip_count,
    ROUND(SUM(tm.distance_km), 2) AS total_distance_km
FROM trip_metrics tm
JOIN raw_business_data.objects o ON o.device_id = tm.device_id
LEFT JOIN raw_business_data.vehicles v ON v.object_id = o.object_id
GROUP BY v.vehicle_label, v.registration_number, tm.device_id, tm.trip_day
ORDER BY tm.trip_day DESC, v.vehicle_label;

Mileage Count per Vehicle per Day (Last 7 Days)

This case calculates the daily mileage (in kilometers) for each vehicle over the last 7 days. It is fundamental for tracking vehicle utilization, monitoring fuel efficiency, planning maintenance, and detecting underuse or overuse.

We extract all GPS records from tracking_data_core for the past 7 days. Each GPS point has a timestamp, latitude, and longitude. For each vehicle and each day, we:

1. Sort GPS points chronologically per device.

2. Calculate distance between consecutive points using the Haversine formula.

3. Sum the distances per day per device to get total mileage.

This approach provides high accuracy without relying on external odometer sensors. Optionally, the query joins with objects and vehicles to enrich results with asset metadata.

WITH gps_points AS (
    SELECT
        td.device_id,
        td.device_time,
        td.device_time::date AS trip_day,
        td.latitude / 1e7 AS lat,
        td.longitude / 1e7 AS lon,
        LAG(td.latitude / 1e7) OVER (PARTITION BY td.device_id, td.device_time::date ORDER BY td.device_time) AS prev_lat,
        LAG(td.longitude / 1e7) OVER (PARTITION BY td.device_id, td.device_time::date ORDER BY td.device_time) AS prev_lon
    FROM raw_telematics_data.tracking_data_core td
    WHERE td.device_time >= now() - interval '7 days'
),
distances AS (
    SELECT
        device_id,
        trip_day,
        -- Haversine formula approximation in km
        111 * SQRT(POWER(lat - prev_lat, 2) + POWER((lon - prev_lon) * COS(RADIANS(lat)), 2)) AS segment_distance_km
    FROM gps_points
    WHERE prev_lat IS NOT NULL AND prev_lon IS NOT NULL
      AND ABS(lat - prev_lat) < 1 AND ABS(lon - prev_lon) < 1 -- exclude outliers
)
SELECT
    v.vehicle_label,
    v.registration_number,
    o.object_label,
    d.device_id,
    d.trip_day,
    ROUND(SUM(d.segment_distance_km)::numeric, 2) AS mileage_km
FROM distances d
JOIN raw_business_data.objects o ON o.device_id = d.device_id
LEFT JOIN raw_business_data.vehicles v ON v.object_id = o.object_id
GROUP BY v.vehicle_label, v.registration_number, o.object_label, d.device_id, d.trip_day
ORDER BY d.trip_day DESC, v.vehicle_label;

Vehicle Event Log Report

This case provides a comprehensive report of all vehicle-related events (e.g., ignition, door open, harsh braking, etc.) across the fleet. It includes event type, timestamp, and vehicle context, allowing operations teams to audit behavior, track abnormal activity, or drive alerts and analytics.

The primary source is the states table from the raw_telematics_data schema. Each row includes: device_id (source of the event), device_time (timestamp), state_name (event label), and value (status or measurement).

To create a usable report:

1. We extract all records from the last 7 days.

2. Group them by event type, vehicle, and date to provide a count of how often each event occurred and when it occurred.

3. Enrich the results with vehicle metadata (vehicle_label, registration_number, object_label) via objects and vehicles.

This gives a daily event timeline across the fleet - essential for diagnostics, behavioral analysis, and proactive maintenance.

WITH raw_events AS (
    SELECT
        s.device_id,
        s.device_time,
        s.device_time::date AS event_day,
        s.state_name,
        s.value
    FROM raw_telematics_data.states s
    WHERE s.device_time >= now() - interval '7 days'
),
with_vehicle_info AS (
    SELECT
        re.device_id,
        re.event_day,
        re.device_time,
        re.state_name,
        re.value,
        v.vehicle_label,
        v.registration_number,
        o.object_label
    FROM raw_events re
    JOIN raw_business_data.objects o ON o.device_id = re.device_id
    LEFT JOIN raw_business_data.vehicles v ON v.object_id = o.object_id
),
event_summary AS (
    SELECT
        event_day,
        state_name,
        vehicle_label,
        registration_number,
        object_label,
        COUNT(*) AS event_count,
        MIN(device_time) AS first_occurred,
        MAX(device_time) AS last_occurred
    FROM with_vehicle_info
    GROUP BY event_day, state_name, vehicle_label, registration_number, object_label
)
SELECT
    event_day,
    vehicle_label,
    registration_number,
    object_label,
    state_name AS event_type,
    event_count,
    first_occurred,
    last_occurred
FROM event_summary
ORDER BY event_day DESC, vehicle_label, state_name;