Logistik

Studi Kasus Logistik dan Buku Resep SQL

Aktifkan IoT Query sebelum menggunakan data untuk membangun analitik komprehensif. Jika Anda belum memilikinya, hubungi kami untuk detail aktivasi - [email protected]

Logistik adalah ekosistem kompleks yang melibatkan koordinasi transportasi, operasi gudang, inventaris, dan pelaksanaan pengiriman. Mengintegrasikan telematika ke dalam proses logistik memungkinkan perusahaan mengumpulkan data real-time tentang kendaraan, pengemudi, rute, dan kondisi kargo, yang secara signifikan meningkatkan pengambilan keputusan dan efisiensi operasional.

Navixy IoT Query, dengan kapabilitas ingest data dan analitik deret waktu yang kuat, mendukung transformasi digital operasi logistik dengan memungkinkan visibilitas mendalam ke setiap tahap siklus hidup. Kapabilitas ingest telematika yang kuat menyediakan visibilitas komprehensif ke operasi ini. Data GPS real-time, diagnostik data sensor, geofencing, dan analitik sensor memungkinkan operator logistik mendigitalkan alur kerja, mengotomatisasi kontrol, dan membuat keputusan yang terinformasi.

Fase Siklus Hidup	Tujuan	Kasus Penggunaan / Resep yang Dicover
Manajemen rute	Optimalkan perutean kendaraan, pastikan pengiriman efisien, dan kurangi keterlambatan	Jumlah Perjalanan per Hari Hitungan Jarak Tempuh per Kendaraan per Hari (7 Hari Terakhir)
Pemantauan Kargo	Pastikan kondisi transportasi yang tepat untuk barang sensitif	Peristiwa Pelanggaran Suhu (dan Kelembapan) dalam 7 Hari Terakhir
Operasi Kendaraan	Lacak pemanfaatan armada, pastikan pemeliharaan, dan kurangi waktu menganggur	Ringkasan Jam Mesin per Kendaraan / Pengemudi / Hari (7 Hari Terakhir) Analisis Waktu Menganggur Kendaraan Pelacakan Aset Tanpa Pergerakan
Keamanan & Keselamatan Rute	Deteksi penyalahgunaan, aktivitas tidak sah, dan pelanggaran keselamatan	Deteksi Penyimpangan Rute - Pemberhentian Tidak Sah (24 Jam Terakhir) Deteksi Penggunaan di Luar Jam
Manajemen Kepatuhan	Pantau perilaku pengemudi, tegakkan kebijakan dan kepatuhan operasional	Ringkasan Jam Mesin per Kendaraan / Pengemudi / Hari (7 Hari Terakhir) Deteksi Penggunaan di Luar Jam
Analisis Pasca-Pengiriman	Evaluasi efisiensi operasional dan kinerja historis	Laporan Log Peristiwa Kendaraan Hitungan Jarak Tempuh per Kendaraan per Hari (7 Hari Terakhir) Jumlah Perjalanan per Hari Pelacakan Aset Tanpa Pergerakan

Pelacakan Aset Tanpa Pergerakan

Kasus ini mengidentifikasi aset (misalnya, kendaraan atau trailer) yang tidak mengubah GPS bandingkan koordinat minimum dan maksimum selama periode. Jika kedua nilai jatuh dalam rentang yang sangat sempit (ambang toleransi, mis. ±0,01 derajat), kami menandai aset sebagai tidak bergerak. Query juga menggabungkan dengan tabel objects dan vehicles di raw_business_data untuk mengambil label aset yang bermakna untuk keluaran hasil.

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;

Analisis Waktu Menganggur Kendaraan

Kasus ini berfokus pada menganalisis berapa lama kendaraan tidak beroperasi akibat perawatan, kerusakan, atau tidak aktif. Metrik waktu menganggur sangat penting bagi operasi logistik untuk memantau kesehatan armada, mengurangi waktu menganggur, dan meningkatkan pemanfaatan serta efisiensi penjadwalan secara keseluruhan.

Inti dari analisis waktu menganggur terletak pada pemanfaatan tabel vehicle_service_tasks dari raw_business_data, yang mencatat baik peristiwa pemeliharaan terencana dan tidak terencana. Setiap tugas berisi start_date dan end_date, yang merepresentasikan periode waktu menganggur. Dengan memfilter untuk tugas layanan yang selesai, kita dapat menghitung durasi pasti setiap kendaraan tidak beroperasi.

Query menghitung total waktu menganggur per kendaraan dengan menjumlahkan durasi semua tugas layanan (dalam jam). Ini juga memungkinkan pemecahan berdasarkan pemeliharaan terencana vs tidak terencana dengan menggunakan flag is_unplanned. Untuk membuat hasil lebih dapat ditindaklanjuti, query bergabung dengan tabel vehicles untuk memasukkan label kendaraan, nomor registrasi, dan informasi model.

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;

Deteksi Penyimpangan Rute

Kasus ini mengidentifikasi kejadian di mana kendaraan menyimpang dari rute yang ditugaskan atau diharapkan — khususnya zona geofenced atau koridor pengiriman. Melacak penyimpangan tersebut membantu memastikan kepatuhan rute, mengurangi keterlambatan, mendeteksi perilaku berkendara berisiko, dan menjaga SLA pengiriman.

Logika ini membandingkan posisi GPS aktual kendaraan dari tracking_data_core (dalam skema raw_telematics_data) terhadap zona geografis yang telah ditentukan dari tabel zones di raw_business_data. Zona-zona ini mewakili rute yang ditugaskan atau segmen rute. Dengan menggunakan perbandingan geometris melalui ST_DWithin, kami menentukan apakah sebuah titik berada di dalam atau di luar area rute yang diberi buffer.

Query menggabungkan setiap posisi GPS dengan setiap zona rute yang diketahui menggunakan sebuah CROSS JOIN, kemudian menerapkan ST_DWithin() untuk memeriksa apakah kendaraan berada dalam koridor yang diizinkan. Kami mengisolasi baris di mana kendaraan di luar semua rute geofenced dan menandainya sebagai penyimpangan. Keluaran akhir mencantumkan penyimpangan ini, termasuk perangkat, cap waktu, label kendaraan, dan seberapa jauh titik tersebut dari pusat zona terdekat.

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;

Ringkasan Jam Mesin per Kendaraan / Pengemudi / Hari (7 Hari Terakhir)

Kasus ini mengukur berapa lama mesin aktif untuk setiap kendaraan secara harian, memungkinkan manajer armada melacak pemanfaatan, mengidentifikasi penggunaan berlebih atau kurang, dan mengkorelasikan aktivitas dengan penugasan pengemudi. Ketika terkait dengan pengemudi, ini juga mendukung validasi jam kerja dan analisis kinerja.

Tabel states di raw_telematics_data merekam indikator status mesin deret waktu, biasanya dengan state_name seperti 'ignition' dan nilai 1 (nyala) atau 0 (mati). Untuk menghitung jam mesin, kita menemukan semua transisi berpenanda waktu untuk setiap perangkat dan menghitung durasi saat mesin menyala (1).

Untuk mengaitkan aktivitas mesin ke baik kendaraan dan pengemudi, kami menggunakan tabel objects, vehicles, dan driver_history dari raw_business_data. Kami mengasosiasikan setiap catatan status ke pengemudi saat ini pada objek tersebut (melalui riwayat penugasan pengemudi) dan ke kendaraan yang sesuai. Kemudian kami mengelompokkan data berdasarkan hari, kendaraan, dan pengemudi, menjumlahkan total waktu mesin aktif (dalam jam).

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;

Peristiwa Pelanggaran Suhu (dan Kelembapan) dalam 7 Hari Terakhir

Kasus ini mengidentifikasi pembacaan sensor — seperti suhu atau kelembapan — yang melebihi ambang kritis selama transportasi. Memantau pelanggaran semacam itu penting bagi industri yang mengangkut barang mudah rusak (mis. makanan, farmasi) untuk memastikan kepatuhan dengan persyaratan rantai dingin dan mencegah kebusukan.

Query ini mengekstrak data input sensor dari tabel inputs dalam skema raw_telematics_data. Setiap baris merepresentasikan pembacaan sensor (mis. suhu, kelembapan) yang direkam pada cap waktu tertentu oleh sebuah perangkat. Kami memfilter catatan ini untuk memasukkan hanya yang dari 7 hari terakhir.

Logika penyaringan utama didasarkan pada pola nama sensor dan perbandingan nilai numeriknya terhadap ambang (mis. >25°C untuk suhu, >80% untuk kelembapan). Karena value disimpan sebagai teks, kami meng-cast-nya ke numerik sebelum menerapkan kondisi ambang. Untuk memperkaya hasil, kami bergabung dengan tabel objects untuk mengambil label kendaraan atau aset, yang meningkatkan keterbacaan bagi manajer armada. WITH recent_sensor_data AS (

i.value::float AS value
   SELECT
       i.device_id,
       i.device_time,
       i.sensor_name,
       WHERE i.device_time >= now() - interval '1 hour'
   FROM raw_telematics_data.inputs i
   sensor_meta AS (
),
sd.input_label,
   SELECT
       sd.device_id,
       sd.sensor_id,
       sd.sensor_type,
       sd.calibration_data
       FROM raw_business_data.sensor_description sd
   joined_data AS (
),
rsd.*,
   SELECT
       sm.sensor_id,
       sm.sensor_type,
       sm.calibration_data
       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 (
),
jd.device_id,
   SELECT
       jd.device_time,
       jd.sensor_name,
       jd.value,
       jd.sensor_id,
       jd.sensor_type,
       CASE
       WHEN cd_low.cal_value IS NOT NULL AND cd_high.cal_value IS NOT NULL THEN
           WHEN cd_high.cal_value = cd_low.cal_value THEN cd_low.cal_volume
               WHEN cd_low.cal_value IS NOT NULL AND cd_high.cal_value IS NOT NULL THEN
                   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
   (p->>'in')::float  AS cal_value,
   LEFT JOIN LATERAL (
       SELECT
           (p->>'out')::float AS cal_volume
           FROM jsonb_array_elements(jd.calibration_data) AS p
       WHERE (p->>'in')::float <= jd.value
       ORDER BY (p->>'in')::float DESC
       ) cd_low ON TRUE
       LIMIT 1
   WHERE (p->>'in')::float >= jd.value
   LEFT JOIN LATERAL (
       SELECT
           (p->>'out')::float AS cal_volume
           FROM jsonb_array_elements(jd.calibration_data) AS p
       WHERE (p->>'in')::float <= jd.value
       ORDER BY (p->>'in')::float ASC
       ) cd_high ON TRUE
       LIMIT 1
   violations AS (
),
cd.device_id,
   SELECT
       cd.device_time,
       cd.sensor_name,
       cd.calibrated_value,
       WHEN cd.sensor_type = 'temperature' AND cd.calibrated_value > 25 THEN 'High Temperature'
       WHEN cd_low.cal_value IS NOT NULL AND cd_high.cal_value IS NOT NULL THEN
           WHEN cd.sensor_type = 'temperature' AND cd.calibrated_value < 0 THEN 'Low Temperature'
           WHEN cd.sensor_type = 'humidity' AND cd.calibrated_value > 80 THEN 'High Humidity'
           ELSE NULL
           END AS violation_type
       FROM calibrated_data cd
   WHERE cd.sensor_type IN ('temperature', 'humidity')
   vio.device_time,
)
SELECT
   v.vehicle_label,
   o.object_label,
   v.registration_number,
   v.model,
   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;
Pemberhentian Tidak Sah (24 Jam Terakhir)

Kasus ini mengidentifikasi

pemberhentian yang tidak sah atau tidak direncanakan yang dilakukan oleh kendaraan dalam 24 jam terakhir. Ini membantu mendeteksi potensi pelanggaran rute pengiriman, istirahat tidak sah, atau waktu menganggur yang dapat memengaruhi efisiensi bahan bakar dan kinerja SLA. Query menganalisis

titik lokasi dengan kecepatan rendah atau nol menggunakan 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 kecepatan turun di bawah 3 km/jam untuk durasi lebih dari 2 menit . Dengan menggunakan fungsi LAG dan LEAD, query memsegmentasi periode kecepatan rendah ini untuk menentukan cap waktu mulai dan akhir pemberhentian.Untuk mendeteksi

pemberhentian tidak sah , query memfilter lokasi yang berada dalamzona geofenced yang dikenal (tabel zones) menggunakan ST_DWithin dari PostGIS. Hanya pemberhentian di luar buffer zona mana pun yang dilaporkan. Hasil mencakup ID kendaraan, label objek, registrasi, cap waktu, durasi, dan koordinat untuk setiap pemberhentian. WITH speed_data AS (

td.speed / 100.0 AS speed_kph,
    SELECT
        td.device_id,
        td.device_time,
        td.latitude / 1e7 AS lat,
        td.longitude / 1e7 AS lon
        WHERE td.device_time >= now() - interval '1 day'
    FROM raw_telematics_data.tracking_data_core td
    low_speed_points AS (
),
LAG(device_time) OVER (PARTITION BY device_id ORDER BY device_time) AS prev_time,
    SELECT
        *,
        LAG(speed_kph) OVER (PARTITION BY device_id ORDER BY device_time) AS prev_speed
        FROM speed_data
    stops_marked AS (
),
SELECT *,
    WHEN speed_kph < 3 AND (prev_speed >= 3 OR prev_speed IS NULL) THEN 1
        WHEN cd_low.cal_value IS NOT NULL AND cd_high.cal_value IS NOT NULL THEN 
            ELSE 0 
            END AS stop_start, 
        WHEN speed_kph >= 3 AND prev_speed < 3 THEN 1
        WHEN cd_low.cal_value IS NOT NULL AND cd_high.cal_value IS NOT NULL THEN 
            END AS stop_end 
            END AS stop_start, 
        FROM low_speed_points
    stop_segments AS (
),
device_time AS stop_start_time,
    SELECT
        device_id,
        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 (
),
ss.*,
    SELECT
        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,
            WHERE z.zone_id IS NULL  -- not inside any known zone
            ST_SetSRID(ST_MakePoint(z.circle_center_longitude, z.circle_center_latitude), 4326)::geography,
            z.radius
        )
    AND EXTRACT(EPOCH FROM (ss.stop_end_time - ss.stop_start_time)) > 120  -- minimum 2 minutes
      with_metadata AS (
),
us.*,
    SELECT
        FROM unauthorized_stops us
        o.object_label,
        v.vehicle_label,
        v.registration_number
    JOIN raw_business_data.objects o ON o.device_id = us.device_id
    stop_start_time,
    LEFT JOIN raw_business_data.vehicles v ON v.object_id = o.object_id
)
SELECT
    vehicle_label,
    registration_number,
    object_label,
    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;
Deteksi Penggunaan di Luar Jam

Kasus ini mengidentifikasi kejadian di mana kendaraan dioperasikan

di luar jam kerja normal — yang didefinisikan di sini sebagai Senin sampai Jumat, 09:00–18:00 . Deteksi semacam ini penting untuk menandaipenggunaan tidak sah , mengidentifikasi potensipenyalahgunaan kendaraan , dan meningkatkankeamanan aset Logika dibangun pada tabel tracking_data_core dari raw_telematics_data, yang mencatat peristiwa GPS berpenanda waktu per perangkat. Kami mengambil.

hari dalam minggu lokal jam penggunaan dan dari setiap entri device_time dan memfilter catatan di luar jendela kerja yang ditentukan (yaitu, sebelum pukul 9 pagi, setelah pukul 6 sore, atau kapan pun pada akhir pekan). Untuk memberikan kejelasan, kami memperkaya data GPS dengan metadata objek dan kendaraan dari raw_business_data (mis. label kendaraan, registrasi, ID objek). Untuk ringkasan yang lebih bermakna, kami secara opsional mengagregasi penggunaan untuk menghitung

berapa banyak kejadian di luar jam yang terjadi per kendaraan dan kapan terjadi. Ini dapat membantu mengidentifikasi pola atau pelanggar berulang. WITH gps_events AS (

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

Kasus ini mengukur

berapa banyak perjalanan yang diselesaikan setiap kendaraan setiap hari dan seberapa jauh mereka berjalan, membantu tim logistik menilai penggunaan kendaraan , mengoptimalkan rute, dan mendeteksi anomali seperti perjalanan yang tidak lengkap atau penggunaan yang tidak dilaporkan.Untuk mendefinisikan sebuah

perjalanan , kami menggunakan perubahan dalamstatus pergerakan kendaraan — yaitu, transisi dari berhenti ke bergerak dan kembali berhenti. Menggunakan nilai kecepatan dari tabel tracking_data_core, query memsegmentasi data berdasarkan transisi ini. Sebuah perjalanan diidentifikasi sebagai periode gerakan kontinu di mana kecepatan tetap di atas ambang (mis. >5 km/jam). Setiap perjalanan mencakup:

A

• A timestamp dan lokasi mulai (titik bergerak pertama)

• Sebuah timestamp dan lokasi akhir (titik bergerak terakhir sebelum berhenti)

• The Jarak Haversine antara lokasi awal dan akhir

Kami menghitung jumlah perjalanan dan total jarak per hari per kendaraan, secara opsional diperkaya dengan label kendaraan dari tabel vehicles.

WITH base_points AS (
    SELECT
        td.device_id,
        td.device_time,
        td.longitude / 1e7 AS lon
        td.longitude / 1e7 AS lon,
        td.latitude / 1e7 AS lat,
        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
    off_hour_events AS (
),
trip_segments AS (
    SELECT
        *,
        WHEN cd_low.cal_value IS NOT NULL AND cd_high.cal_value IS NOT NULL THEN 
            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,
        -- Perkiraan jarak menggunakan rumus Haversine (dalam km)
        111 * SQRT(POWER(tp.end_lat - tp.start_lat, 2) + POWER((tp.end_lon - tp.start_lon) * COS(RADIANS(tp.start_lat)), 2))::numeric 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;

Jumlah Jarak Tempuh per Kendaraan per Hari (7 Hari Terakhir)

Kasus ini menghitung jarak tempuh harian (dalam kilometer) untuk setiap kendaraan selama 7 hari terakhir. Ini penting untuk melacak pemanfaatan kendaraan, memantau efisiensi bahan bakar, merencanakan pemeliharaan, dan mendeteksi penggunaan yang kurang atau berlebih.

Kami mengekstrak semua rekaman GPS dari tracking_data_core untuk 7 hari terakhir. Setiap titik GPS memiliki timestamp, latitude, dan longitude. Untuk setiap kendaraan dan setiap hari, kami:

1. Mengurutkan titik GPS secara kronologis per perangkat.

2. Menghitung jarak antara titik berturut-turut menggunakan rumus Haversine.

3. Menjumlahkan jarak per hari per perangkat untuk mendapatkan total jarak tempuh.

Pendekatan ini memberikan akurasi tinggi tanpa bergantung pada sensor odometer eksternal. Secara opsional, query bergabung dengan objects dan vehicles untuk memperkaya hasil dengan metadata aset.

WITH gps_points AS (
    SELECT
        td.device_id,
        td.device_time,
        td.device_time::date AS trip_day,
        td.longitude / 1e7 AS lon
        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
    off_hour_events AS (
),
distances AS (
    SELECT
        device_id,
        trip_day,
        -- Aproksimasi rumus Haversine dalam 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 -- mengecualikan outlier
)
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;

Laporan Log Peristiwa Kendaraan

Kasus ini memberikan laporan komprehensif dari semua peristiwa terkait kendaraan (mis. kontak pengapian, pintu terbuka, pengereman keras, dll.) di seluruh armada. Ini mencakup jenis peristiwa, timestamp, dan konteks kendaraan, memungkinkan tim operasional mengaudit perilaku, melacak aktivitas abnormal, atau memicu peringatan dan analitik.

Sumber utama adalah tabel states dari skema raw_telematics_data. Setiap baris mencakup: device_id (sumber peristiwa), device_time (timestamp), state_name (label peristiwa), dan value (status atau pengukuran).

Untuk membuat laporan yang berguna:

1. Kami mengekstrak semua rekaman dari 7 hari terakhir.

2. Mengelompokkan mereka berdasarkan jenis peristiwa, kendaraan, dan tanggal untuk menyediakan jumlah seberapa sering setiap peristiwa terjadi dan kapan itu terjadi.

3. Memperkaya hasil dengan metadata kendaraan (vehicle_label, registration_number, object_label) melalui objects dan vehicles.

Ini memberikan garis waktu peristiwa harian di seluruh armada - penting untuk diagnostik, analisis perilaku, dan pemeliharaan proaktif.

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;