Logistik
Studi Kasus Logistik dan Buku Resep SQL
Aktifkan DataHub 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 DataHub, 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 ini memberikan visibilitas komprehensif ke operasi tersebut. Data GPS real-time, diagnostik data sensor, geofencing, dan analitik sensor memungkinkan operator logistik mendigitalkan alur kerja, mengotomatisasi kontrol, dan membuat keputusan yang berdasarkan informasi.
Manajemen rute
Mengoptimalkan perutean kendaraan, memastikan pengiriman yang efisien, dan mengurangi keterlambatan
Jumlah Perjalanan per Hari Jumlah Jarak Tempuh per Kendaraan per Hari (7 Hari Terakhir)
Pemantauan Kargo
Memastikan kondisi transportasi yang tepat untuk barang sensitif
Peristiwa Pelanggaran Suhu (dan Kelembapan) dalam 7 Hari Terakhir
Operasi Kendaraan
Melacak pemanfaatan armada, memastikan pemeliharaan, dan mengurangi waktu henti
Ringkasan Jam Mesin per Kendaraan / Pengemudi / Hari (7 Hari Terakhir) Analisis Waktu Henti Kendaraan Pelacakan Aset Tanpa Pergerakan
Keamanan & Keselamatan Rute
Mendeteksi penyalahgunaan, aktivitas tidak berwenang, dan pelanggaran keselamatan
Deteksi Penyimpangan Rute- Berhenti Tidak Sah (24 Jam Terakhir) Deteksi Penggunaan di Luar Jam
Manajemen Kepatuhan
Memantau perilaku pengemudi, menegakkan kebijakan dan kepatuhan operasional
Ringkasan Jam Mesin per Kendaraan / Pengemudi / Hari (7 Hari Terakhir) Deteksi Penggunaan di Luar Jam
Analisis Pasca-Pengiriman
Mengevaluasi efisiensi operasional dan kinerja historis
Laporan Log Peristiwa Kendaraan Jumlah 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 (mis. kendaraan atau trailer) yang tidak mengubah GPS mereka bandingkan koordinat minimum dan maksimum selama periode. Jika kedua nilai berada 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 -- kecualikan perangkat dengan data sangat jarang
AND((max_lat - min_lat) <= 2000 -- ~10 meter
OR
(max_lon - min_lon) <= 1000 -- ~10 meter
)
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 Henti Kendaraan
Kasus ini berfokus pada menganalisis berapa lama kendaraan tidak beroperasi karena pemeliharaan, kerusakan, atau tidak aktif. Metrik waktu henti sangat penting bagi operasi logistik untuk memantau kesehatan armada, mengurangi waktu menganggur, dan meningkatkan pemanfaatan serta efisiensi penjadwalan secara keseluruhan.
Inti dari analisis waktu henti terletak pada memanfaatkan tabel vehicle_service_tasks dari raw_business_data, yang mencatat baik peristiwa pemeliharaan terencana maupun tidak terencana. Setiap tugas berisi start_date dan end_date, yang merepresentasikan periode waktu henti. Dengan memfilter untuk tugas layanan yang selesai, kita dapat menghitung durasi tepat setiap kendaraan keluar dari operasi.
Query menghitung total waktu henti per kendaraan dengan menjumlahkan durasi semua tugas layanan yang dimilikinya (dalam jam). Ini juga memungkinkan pemisahan antara pemeliharaan terencana vs tidak terencana dengan menggunakan flag is_unplanned. Untuk membuat hasil lebih dapat ditindaklanjuti, query bergabung dengan tabel vehicles untuk menyertakan 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 — terutama zona geofenced atau koridor pengiriman. Melacak penyimpangan semacam itu membantu memastikan kepatuhan rute, mengurangi keterlambatan, mendeteksi perilaku mengemudi berisiko, dan mempertahankan SLA pengiriman.
Logika ini membandingkan posisi GPS aktual kendaraan dari tracking_data_core (dalam skema raw_telematics_data) terhadap zona geografis yang telah ditetapkan dari tabel zones di raw_business_data. Zona-zona ini merepresentasikan rute yang ditugaskan atau segmen rute. Dengan menggunakan perbandingan geometris melalui ST_DWithin, kita menentukan apakah sebuah titik berada di dalam atau di luar area koridor yang dibuffer.
Query menggabungkan setiap posisi GPS dengan setiap zona rute yang diketahui menggunakan sebuah CROSS JOIN, lalu menerapkan ST_DWithin() untuk memeriksa apakah kendaraan berada dalam koridor yang diperbolehkan. 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 dikaitkan dengan pengemudi, ini juga mendukung validasi jam kerja dan analisis kinerja.
Tabel states dalam raw_telematics_data mencatat 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 yang bertimestamp untuk setiap perangkat dan menghitung durasi saat mesin menyala (1).
Untuk mengaitkan aktivitas mesin ke baik kendaraan maupun pengemudi, kita 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 -- transisi dari MENYALA ke MATI
),
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 yang mudah rusak (mis. makanan, farmasi) untuk memastikan kepatuhan terhadap persyaratan rantai dingin dan mencegah pembusukan.
Query ini mengekstrak data input sensor dari tabel inputs dalam skema raw_telematics_data. Setiap baris merepresentasikan pembacaan sensor (mis. suhu, kelembapan) yang dicatat pada cap waktu tertentu oleh sebuah perangkat. Kami memfilter catatan ini untuk hanya menyertakan 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 mengkonversinya ke numerik sebelum menerapkan kondisi ambang. Untuk memperkaya hasil, kami bergabung dengan tabel objects untuk mengambil label kendaraan atau aset, yang meningkatkan interpretabilitas bagi manajer armada.
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;
Berhenti Tidak Sah (24 Jam Terakhir)
Kasus ini mengidentifikasi berhenti 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 Query menggunakan tabel tracking_data_core dari raw_telematics_data untuk mengekstrak data lokasi deret-waktu dan kecepatan. Sebuah berhenti terdeteksi ketika kecepatan turun di bawah 3 km/h selama durasi lebih dari 2 menit. Dengan menggunakan fungsi LAG dan LEAD, query memsegmentasi periode kecepatan rendah ini untuk menentukan cap waktu mulai dan akhir berhenti.
Untuk mendeteksi berhenti yang tidak sah, query memfilter lokasi yang berada dalam zona geofenced yang dikenal (tabel zones) menggunakan ST_DWithin dari PostGIS. Hanya berhenti di luar buffer zona mana pun yang dilaporkan. Hasil mencakup ID kendaraan, label objek, registrasi, cap waktu, durasi, dan koordinat untuk setiap berhenti.
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 -- tidak berada di dalam zona yang dikenal mana pun
AND EXTRACT(EPOCH FROM (ss.stop_end_time - ss.stop_start_time)) > 120 -- minimal 2 menit
),
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;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 menandai penggunaan tidak sah, mengidentifikasi potensi penyalahgunaan kendaraan, dan meningkatkan keamanan aset.
Logika dibangun pada tabel tracking_data_core dari raw_telematics_data, yang mencatat peristiwa GPS bertimestamp per perangkat. Kami menurunkan lokal hari dalam minggu dan jam penggunaan dari setiap entri device_time dan memfilter untuk catatan di luar jendela jam kerja yang ditetapkan (yaitu, sebelum pukul 9, setelah pukul 18, atau kapan saja 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 (
SELECT
td.device_id,
td.device_time,
EXTRACT(DOW FROM td.device_time) AS day_of_week, -- 0=Minggu, 6=Sabtu
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) -- Sabtu atau Minggu
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;Jumlah perjalanan per hari
Kasus ini mengukur berapa banyak perjalanan yang diselesaikan setiap kendaraan setiap hari dan sejauh mana mereka melakukan perjalanan, 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 dalam status gerakan kendaraan — yaitu, transisi dari berhenti ke bergerak dan kembali ke berhenti. Menggunakan nilai kecepatan dari tabel tracking_data_core, query memsegmentasi data berdasarkan transisi ini. Perjalanan diidentifikasi sebagai periode gerakan kontinu di mana kecepatan tetap di atas ambang (mis. >5 km/h).
Setiap perjalanan mencakup:
Sebuah cap waktu dan lokasi mulai (titik bergerak pertama)
Sebuah cap waktu dan lokasi akhir (titik bergerak terakhir sebelum berhenti)
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.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;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 catatan GPS dari tracking_data_core untuk 7 hari terakhir. Setiap titik GPS memiliki cap waktu, lintang, dan bujur. Untuk setiap kendaraan dan setiap hari, kami:
Mengurutkan titik GPS secara kronologis per perangkat.
Menghitung jarak antara titik berturut-turut menggunakan rumus Haversine.
Menjumlahkan jarak per hari per perangkat untuk mendapatkan total jarak tempuh.
Pendekatan ini memberikan akurasi tinggi tanpa mengandalkan sensor odometer eksternal. Opsional, kueri 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.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;Laporan Log Kejadian Kendaraan
Kasus ini memberikan laporan komprehensif dari semua kejadian terkait kendaraan (mis. pengapian, pintu terbuka, pengereman keras, dll.) di seluruh armada. Ini mencakup jenis kejadian, cap waktu, dan konteks kendaraan, memungkinkan tim operasional mengaudit perilaku, melacak aktivitas abnormal, atau menghasilkan peringatan dan analitik.
Sumber utama adalah tabel states dari skema raw_telematics_data. Setiap baris mencakup: device_id (sumber kejadian), device_time (cap waktu), state_name (label kejadian), dan value (status atau pengukuran).
Untuk membuat laporan yang dapat digunakan:
Kami mengekstrak semua catatan dari 7 hari terakhir.
Mengelompokkannya berdasarkan jenis kejadian, kendaraan, dan tanggal untuk menyediakan jumlah seberapa sering tiap kejadian terjadi dan kapan terjadi.
Memperkaya hasil dengan metadata kendaraan (vehicle_label, registration_number, object_label) melalui objects dan vehicles.
Ini memberikan garis waktu kejadian 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;Last updated
Was this helpful?