> For the complete documentation index, see [llms.txt](https://navixy.com/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://navixy.com/docs/analytics/id/example-queries/logistics.md).

# Logistik

Templat kueri SQL untuk analitik logistik dan transportasi, mencakup optimasi rute, pemantauan muatan, pemanfaatan armada, dan kinerja pengiriman

{% hint style="warning" %}
Aktifkan **IoT Query** sebelum memanfaatkan data untuk membangun analitik yang komprehensif. Jika Anda belum memilikinya, hubungi kami untuk detail aktivasi - <iotquery@navixy.com>
{% endhint %}

Logistik adalah ekosistem kompleks yang melibatkan koordinasi transportasi, operasi gudang, inventaris, dan pelaksanaan pengiriman. Mengintegrasikan telematika ke dalam proses logistik memungkinkan perusahaan mengumpulkan data waktu nyata tentang Kendaraan, Supir, rute, dan kondisi muatan, yang secara signifikan meningkatkan pengambilan keputusan dan efisiensi operasional.

Navixy **IoT Query**, dengan kemampuan penyerapan data dan analitik deret waktu yang andal, mendukung transformasi digital operasi logistik dengan memungkinkan visibilitas mendalam ke setiap tahap siklus hidup. Kemampuan penyerapan telematika yang andal, memberikan visibilitas komprehensif terhadap operasi ini. Data GPS waktu nyata, diagnostik data sensor, geofencing, dan analitik sensor memungkinkan operator logistik mendigitalkan alur kerja, mengotomatiskan kontrol, dan membuat keputusan yang tepat.

| Fase siklus hidup               | Tujuan                                                                                     | Kasus penggunaan / resep yang dicakup                                                                                                                |
| ------------------------------- | ------------------------------------------------------------------------------------------ | ---------------------------------------------------------------------------------------------------------------------------------------------------- |
| **Manajemen rute**              | Optimalkan perutean kendaraan, pastikan pengiriman yang efisien, dan kurangi keterlambatan | Jumlah perjalanan per hari Jumlah 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                                                                                    |
| **Operasional kendaraan**       | Lacak pemanfaatan armada, pastikan pemeliharaan, dan kurangi waktu henti                   | Ringkasan jam mesin per kendaraan / supir / hari (7 hari terakhir) Analisis waktu henti kendaraan Pelacakan aset tanpa gerakan                       |
| **Keamanan & keselamatan rute** | Deteksi penyalahgunaan, aktivitas tidak sah, dan pelanggaran Keselamatan                   | Deteksi penyimpangan Rute- Perhentian tidak sah (24 jam terakhir) Deteksi penggunaan di luar jam kerja                                               |
| **Manajemen kepatuhan**         | Pantau perilaku Supir, tegakkan kebijakan dan kepatuhan operasional                        | Ringkasan Jam mesin per kendaraan / Supir / hari (7 hari terakhir) Deteksi penggunaan di luar jam kerja                                              |
| **Analisis pascapengiriman**    | Evaluasi 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 Gerakan |

## **Pelacakan Aset tanpa Gerakan** <a href="#asset-tracking-without-movement" id="asset-tracking-without-movement"></a>

Kasus ini mengidentifikasi Aset (misalnya, Kendaraan atau trailer) yang belum mengubah GPS mereka dibandingkan **koordinat minimum dan maksimum** selama periode tersebut. Jika kedua nilai jatuh dalam rentang yang sangat sempit (ambang toleransi, misalnya ±0,01 derajat), kami menandai aset sebagai tidak bergerak. Kueri juga bergabung dengan tabel objek dan Kendaraan dalam raw\_business\_data untuk mengambil label Aset yang bermakna untuk keluaran hasil.

{% code expandable="true" %}

```sql
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'
    Kelompok berdasarkan td.device_id
),
stationary_devices AS (
    SELECT
        device_id
    DARI gps_bounds
    WHERE location_records > 10 -- kecualikan perangkat dengan data yang sangat minim
	AND((max_lat - min_lat) <= 2000 -- ~10 meter
		ATAU
      	(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.Kendaraan v ON v.Pelacak_id = o.Pelacak_id
ORDER BY gb.location_records DESC;
```

{% endcode %}

## **Analisis waktu henti kendaraan** <a href="#vehicle-downtime-analysis" id="vehicle-downtime-analysis"></a>

Kasus ini berfokus pada analisis 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 keseluruhan serta efisiensi Penjadwalan.

Inti analisis waktu henti terletak pada pemanfaatan tabel vehicle\_service\_tasks dari raw\_business\_data, yang mencatat keduanya **peristiwa Pemeliharaan terencana dan tidak terencana**. Setiap Tugas berisi start\_date dan end\_date, yang merepresentasikan periode waktu henti. Dengan memfilter untuk **Tugas layanan yang selesai**, kami dapat menghitung durasi pasti setiap kendaraan tidak beroperasi.

Query ini menghitung total waktu henti per kendaraan dengan menjumlahkan durasi seluruh tugas servisnya (dalam jam). Query ini juga memungkinkan perincian berdasarkan Pemeliharaan terencana versus tidak terencana dengan menggunakan flag is\_unplanned. Agar hasilnya lebih dapat ditindaklanjuti, query ini bergabung dengan tabel Kendaraan untuk menyertakan label kendaraan, nomor registrasi, dan informasi model.

{% code expandable="true" %}

```sql
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
    DARI 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.Kendaraan v ON v.vehicle_id = dd.vehicle_id
Kelompok BY v.vehicle_id, v.vehicle_label, v.registration_number, v.model
ORDER BY total_downtime_hours DESC;
```

{% endcode %}

## **Deteksi penyimpangan Rute** <a href="#route-deviation-detection" id="route-deviation-detection"></a>

Kasus ini mengidentifikasi instance ketika Kendaraan menyimpang dari Rute yang ditetapkan atau diharapkan — khususnya zona geofence atau koridor pengiriman. Melacak penyimpangan seperti itu membantu memastikan kepatuhan Rute, mengurangi keterlambatan, mendeteksi perilaku Mengemudi yang berisiko, dan mempertahankan SLA pengiriman.

Logika ini membandingkan posisi GPS aktual kendaraan dari tracking\_data\_core (di skema raw\_telematics\_data) dengan zona geografis yang telah ditentukan dari tabel zones di raw\_business\_data. Zona-zona ini merepresentasikan Rute yang ditetapkan atau segmen Rute. Dengan menggunakan perbandingan geometri melalui ST\_DWithin, kami menentukan apakah sebuah titik berada di dalam atau di luar area Rute yang dibuffer.

Query ini menggabungkan setiap posisi GPS dengan setiap zona Rute yang diketahui menggunakan gabungan spasial **CROSS JOIN**, lalu menerapkan ST\_DWithin() untuk memeriksa apakah kendaraan berada di dalam koridor yang diizinkan. Kami mengisolasi baris ketika kendaraan berada **di luar semua Rute yang diberi geofence** dan menandainya sebagai penyimpangan. Hasil akhir mencantumkan penyimpangan ini, termasuk perangkat, stempel waktu, label kendaraan, dan seberapa jauh titik tersebut dari pusat zona terdekat.

{% code expandable="true" %}

```sql
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 Rute_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'
),
dievaluasi sebagai (
    SELECT
        device_id,
        device_time,
        ID objek,
        pelacak_label,
        zone_id,
        label zona,
        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,
    pelacak_label,
    label zona,
    device_time,
    deviation_distance_meters
FROM deviations_only
ORDER BY device_time DESC;
```

{% endcode %}

## **Ringkasan Jam mesin per kendaraan / Supir / hari (7 hari terakhir)** <a href="#engine-hours-summary-per-vehicle-driver-day-last-7-days" id="engine-hours-summary-per-vehicle-driver-day-last-7-days"></a>

Kasus ini mengukur berapa lama mesin aktif untuk setiap kendaraan secara harian, sehingga manajer Armada dapat melacak **utilisasi**, mengidentifikasi **penggunaan berlebihan atau kurang**, dan mengorelasikan aktivitas dengan penugasan supir. Ketika dikaitkan dengan supir, ini juga mendukung **validasi jam kerja** dan **analisis kinerja**.

Tabel states dalam raw\_telematics\_data merekam **indikator status mesin deret waktu**, biasanya dengan state\_name seperti 'pengapian' dan value 1 (menyala) atau 0 (mati). Untuk menghitung jam mesin, kami menemukan semua transisi bertanda waktu untuk setiap perangkat dan menghitung durasi saat mesin menyala (1).

Untuk mengaitkan aktivitas mesin ke keduanya **Kendaraan dan Supir**, kami menggunakan tabel pelacak, Kendaraan, dan riwayat Supir dari raw\_business\_data. Kami mengaitkan setiap catatan status ke Supir saat ini pada Pelacak tersebut (melalui riwayat penugasan Supir) dan ke Kendaraan yang sesuai. Lalu, kami membuat Kelompok data berdasarkan hari, Kendaraan, dan Supir, dengan menjumlahkan total waktu mesin aktif (dalam jam).

{% code expandable="true" %}

```sql
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  -- perpindahan dari ON ke 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.Kendaraan v ON v.Pelacak_id = o.Pelacak_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
       DI MANA d.object_id = ewo.object_id
         AND d.changed_datetime <= ewo.engine_on_time
       ORDER BY d.changed_datetime DESC
       LIMIT 1
   ) dh ON benar
)
SELECT
   hari aktivitas,
   label kendaraan,
   nomor registrasi,
   pelacak_label,
   e.first_name || ' ' || e.last_name AS nama_supir,
   SUM(engine_hours) AS total_engine_hours
DARI assigned_drivers ad
LEFT JOIN raw_business_data.employees e ON ad.employee_id = e.employee_id
Kelompokkan berdasarkan activity_day, vehicle_label, registration_number, object_label, driver_name
ORDER BY activity_day DESC, vehicle_label;

```

{% endcode %}

## **Peristiwa pelanggaran suhu (dan kelembapan) dalam 7 hari terakhir** <a href="#temperature-and-humidity-violation-events-in-the-last-7-days" id="temperature-and-humidity-violation-events-in-the-last-7-days"></a>

Kasus ini mengidentifikasi pembacaan sensor — seperti **suhu atau kelembapan** — yang melampaui ambang batas kritis selama transportasi. Pemantauan pelanggaran semacam itu sangat penting bagi industri yang mengangkut barang mudah rusak (misalnya, makanan, farmasi) untuk memastikan kepatuhan terhadap persyaratan rantai dingin dan mencegah kerusakan.

Kueri ini mengekstrak **data masukan sensor** dari tabel inputs pada skema raw\_telematics\_data. Setiap baris merepresentasikan pembacaan sensor (misalnya, suhu, kelembapan) yang direkam pada stempel waktu tertentu oleh perangkat. Kami memfilter catatan ini untuk hanya menyertakan yang dari **7 hari terakhir**.

Logika pemfilteran utama didasarkan pada **pola nama sensor** dan perbandingan **nilai numerik mereka terhadap ambang batas** (misalnya, >25°C untuk suhu, >80% untuk kelembapan). Karena value disimpan sebagai teks, kami mengonversinya ke numerik sebelum menerapkan kondisi ambang batas. Untuk memperkaya hasil, kami melakukan join dengan tabel objects untuk mengambil label kendaraan atau Aset, yang meningkatkan kemudahan interpretasi bagi manajer Armada.

{% code expandable="true" %}

```sql
WITH recent_sensor_data AS (
   SELECT
       i.device_id,
       i.device_time,
       i.sensor_name,
       i.value::float AS value
   FROM raw_telematics_data.inputs i
   WHERE i.device_time >= now() - interval '1 hour'
),
sensor_meta AS (
   SELECT
       sd.device_id,
       sd.input_label,
       sd.sensor_id,
       sd.sensor_type,
       sd.calibration_data
   FROM raw_business_data.sensor_description sd
),
joined_data AS (
   SELECT
       rsd.*,
       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 (
   SELECT
       jd.device_id,
       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
               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
           (p->>'in')::float  AS cal_value,
           (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
       LIMIT 1
   ) cd_low ON TRUE
   LEFT JOIN LATERAL (
       SELECT
           (p->>'in')::float  AS cal_value,
           (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
       LIMIT 1
   ) cd_high ON TRUE
),
violations AS (
   SELECT
       cd.device_id,
       cd.device_time,
       cd.sensor_name,
       cd.calibrated_value,
       CASE
           WHEN cd.sensor_type = 'temperature' AND cd.calibrated_value > 25 THEN 'High Temperature'
           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')
)
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.Kendaraan v
  ON v.object_id = o.object_id
ORDER BY vio.device_time DESC;

```

{% endcode %}

## **Perhentian tidak sah (24 jam terakhir)** <a href="#unauthorized-stops-last-24-hours" id="unauthorized-stops-last-24-hours"></a>

Kasus ini mengidentifikasi **Perhentian tanpa izin atau tidak direncanakan** dilakukan oleh kendaraan dalam 24 jam terakhir. Ini membantu mendeteksi potensi pelanggaran rute pengiriman, jeda tanpa izin, atau waktu menganggur yang dapat memengaruhi efisiensi bahan bakar dan kinerja SLA.

Kueri menganalisis **titik lokasi dengan kecepatan rendah atau nol** Menggunakan kueri yang menggunakan tabel tracking\_data\_core dari raw\_telematics\_data untuk mengekstrak data lokasi deret waktu dan kecepatan. Sebuah perhentian terdeteksi ketika **kecepatan turun di bawah 3 km/jam** selama **lebih dari 2 menit**. Dengan menggunakan fungsi LAG dan LEAD, kueri ini membagi periode berkecepatan rendah ini untuk menentukan stempel waktu mulai dan akhir perhentian.

Untuk mendeteksi **perhentian tanpa izin**, memfilter lokasi yang berada dalam zona geofence yang dikenal **zona geofence** (tabel zones) menggunakan ST\_DWithin milik PostGIS. Hanya Perhentian **di luar penyangga zona mana pun** Dilaporkan. Hasilnya mencakup ID kendaraan, label Pelacak, registrasi, stempel waktu, durasi, dan koordinat untuk setiap pemberhentian.

{% code expandable="true" %}

```sql
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
),
perhentian_ditandai SEBAGAI (
    SELECT *,
        CASE 
            WHEN speed_kph < 3 AND (prev_speed >= 3 OR prev_speed IS NULL) THEN 1 
            LAINNYA 0 
        END AS stop_start,
        CASE 
            KETIKA speed_kph >= 3 DAN prev_speed < 3 MAKA 1 
            LAINNYA 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,
        lintang,
        lon
    Dari Perhentian yang ditandai
    WHERE stop_start = 1
),
Perhentian tanpa izin 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 mana pun yang diketahui
      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
    DARI unauthorized_Perhentian us
    JOIN raw_business_data.objects o ON o.device_id = us.device_id
    LEFT JOIN raw_business_data.Kendaraan v ON v.Pelacak_id = o.Pelacak_id
)
SELECT
    label kendaraan,
    nomor registrasi,
    pelacak_label,
    waktu mulai berhenti,
    waktu akhir perhentian,
    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;
```

{% endcode %}

## **Deteksi penggunaan di luar jam operasional** <a href="#off-hour-usage-detection" id="off-hour-usage-detection"></a>

Kasus ini mengidentifikasi kejadian ketika Kendaraan dioperasikan **di luar jam operasional normal** — yang didefinisikan di sini sebagai **Senin sampai Jumat, 09:00–18:00**. Deteksi seperti itu sangat penting untuk menandai **penggunaan tanpa izin**, mengidentifikasi potensi **penyalahgunaan kendaraan**, dan meningkatkan **Keamanan Aset**.

Logika ini dibangun di atas tabel tracking\_data\_core dari raw\_telematics\_data, yang mencatat kejadian GPS berstempel waktu per perangkat. Kami menurunkan lokal **hari dalam pekan** dan **jam penggunaan** dari setiap entri device\_time dan memfilter rekaman **di luar jendela bisnis yang ditentukan** (yaitu, sebelum pukul 9 pagi, setelah pukul 6 sore, atau kapan saja pada akhir pekan).

Untuk memberikan kejelasan, kami memperkaya data GPS dengan metadata Pelacak dan kendaraan dari raw\_business\_data (misalnya, label kendaraan, registrasi, ID Pelacak). Untuk ringkasan yang lebih bermakna, kami secara opsional mengagregasi penggunaan untuk menghitung **berapa banyak kejadian di luar jam kerja** yang terjadi per kendaraan dan kapan terjadinya. Ini dapat membantu mengidentifikasi pola atau pelanggar berulang.

{% code expandable="true" %}

```sql
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)  -- 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 nama_supir,
        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.Kendaraan v ON v.Pelacak_id = o.Pelacak_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
    pelacak_label,
    label kendaraan,
    nomor registrasi,
    nama_supir,
    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;
```

{% endcode %}

## **Jumlah perjalanan per hari** <a href="#trip-counts-per-day" id="trip-counts-per-day"></a>

Kasus ini mengukur **berapa banyak perjalanan** apa yang diselesaikan setiap kendaraan setiap hari dan seberapa jauh mereka menempuh 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 pada **status gerakan kendaraan** — yaitu, transisi dari berhenti ke bergerak dan kembali ke berhenti. Dengan menggunakan nilai kecepatan dari tabel tracking\_data\_core, kueri membagi data berdasarkan transisi ini. Sebuah perjalanan diidentifikasi sebagai sebuah **periode gerakan berkelanjutan** di mana kecepatan tetap di atas ambang batas (misalnya, >5 km/jam).

Setiap perjalanan mencakup:

* Sebuah **waktu mulai dan lokasi** (titik bergerak pertama)
* Sebuah **waktu akhir dan lokasi** (titik bergerak terakhir sebelum berhenti)
* Itu **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 kendaraan.

{% code expandable="true" %}

```sql
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))::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.Kendaraan v ON v.Pelacak_id = o.Pelacak_id
Kelompokkan berdasarkan v.vehicle_label, v.registration_number, tm.device_id, tm.trip_day
ORDER BY tm.trip_day DESC, v.vehicle_label;
```

{% endcode %}

## **Jumlah jarak tempuh per kendaraan per hari (7 hari terakhir)** <a href="#mileage-count-per-vehicle-per-day-last-7-days" id="mileage-count-per-vehicle-per-day-last-7-days"></a>

Kasus ini menghitung **jarak tempuh harian** (dalam kilometer) untuk setiap kendaraan selama 7 hari terakhir. Ini sangat penting untuk pelacakan **pemanfaatan kendaraan**, pemantauan **efisiensi bahan bakar**, perencanaan **Pemeliharaan**, dan mendeteksi penggunaan yang kurang atau berlebihan.

Kami mengekstrak semua rekaman GPS dari tracking\_data\_core selama 7 hari terakhir. Setiap titik GPS memiliki stempel waktu, lintang, dan bujur. Untuk setiap kendaraan dan setiap hari, kami:

1. **Urutkan titik GPS secara kronologis** per perangkat.
2. **Hitung jarak antara titik-titik berurutan** menggunakan rumus Haversine.
3. **Jumlahkan jarak tempuh per hari per perangkat** untuk mendapatkan jarak tempuh total.

Pendekatan ini memberikan akurasi tinggi tanpa bergantung pada sensor odometer eksternal. Secara opsional, kueri digabungkan dengan objek dan Kendaraan untuk memperkaya hasil dengan metadata Aset.

{% code expandable="true" %}

```sql
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'
),
jarak AS (
    SELECT
        device_id,
        hari perjalanan,
        -- 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 -- kecualikan pencilan
)
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 jarak_tempuh_km
FROM distances d
JOIN raw_business_data.objects o ON o.device_id = d.device_id
LEFT JOIN raw_business_data.Kendaraan v ON v.Pelacak_id = o.Pelacak_id
Kelompok berdasarkan v.vehicle_label, v.registration_number, o.object_label, d.device_id, d.trip_day
ORDER BY d.trip_day DESC, v.vehicle_label;
```

{% endcode %}

## **Laporan log peristiwa kendaraan** <a href="#vehicle-event-log-report" id="vehicle-event-log-report"></a>

Kasus ini memberikan laporan komprehensif dari semua **peristiwa terkait kendaraan** (misalnya, Pengapian, pintu terbuka, Pengemudian kasar, dll.) di seluruh Armada. Ini mencakup **jenis peristiwa**, **stempel waktu**, dan **konteks kendaraan**, sehingga tim operasional dapat mengaudit perilaku, melacak aktivitas abnormal, atau memicu Aturan peringatan dan analitik.

Sumber utamanya adalah tabel states dari skema raw\_telematics\_data. Setiap baris mencakup: device\_id (sumber kejadian), device\_time (stempel waktu), state\_name (label kejadian), dan value (status atau pengukuran).

Untuk membuat laporan yang dapat digunakan:

1. Kami mengekstrak semua rekaman dari 7 hari terakhir.
2. Kelompokkan berdasarkan **jenis peristiwa**, **kendaraan**, dan **tanggal** untuk menyediakan sebuah **jumlah seberapa sering setiap peristiwa terjadi** dan **ketika itu terjadi**.
3. Perkaya hasil dengan metadata Kendaraan (vehicle\_label, registration\_number, object\_label) melalui Pelacak dan Kendaraan.

Ini memberikan sebuah **Waktu kejadian harian** di seluruh Armada - penting untuk diagnostik, analisis perilaku, dan Pemeliharaan proaktif.

{% code expandable="true" %}

```sql
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.Kendaraan v ON v.Pelacak_id = o.Pelacak_id
),
event_summary AS (
    SELECT
        hari kejadian,
        nama status,
        label kendaraan,
        nomor registrasi,
        pelacak_label,
        COUNT(*) AS event_count,
        MIN(device_time) AS first_occurred,
        MAX(device_time) AS last_occurred
    DARI with_vehicle_info
    Kelompok BY event_day, state_name, vehicle_label, registration_number, object_label
)
SELECT
    hari kejadian,
    label kendaraan,
    nomor registrasi,
    pelacak_label,
    state_name AS event_type,
    jumlah kejadian,
    pertama_terjadi,
    last_occurred
FROM event_summary
ORDER BY event_day DESC, vehicle_label, state_name;
```

{% endcode %}


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://navixy.com/docs/analytics/id/example-queries/logistics.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.