# Leasing {% hint style="warning" %} Aktifkan **IoT Query** sebelum memanfaatkan data untuk membangun analitik yang komprehensif. Jika Anda belum memilikinya, hubungi kami untuk detail aktivasi - {% endhint %} Perusahaan leasing (terutama bank dan penyedia sewa‑armada) mempertahankan kepemilikan kendaraan atau peralatan sementara klien hanya menyewa penggunaannya, sehingga mereka menanggung risiko terkait aset sepanjang kontrak.  Untuk melindungi nilai residual, menegakkan batasan kontraktual (jarak tempuh, wilayah, pemeliharaan), dan menyederhanakan kewajiban layanan penuh, mereka mengandalkan Navixy. Data GPS real‑time, diagnostik berbasis sensor, dan analitik perilaku memungkinkan mereka memverifikasi kondisi penggunaan, mengotomatiskan penjadwalan layanan, mendeteksi masalah mekanis dini, menghitung penalti atau biaya kilometer berlebih, dan bila perlu, melumpuhkan atau merebut kembali aset—semua ini mengamankan investasi mereka, mengurangi biaya operasional, dan meningkatkan transparansi bagi pelanggan sepanjang siklus leasing. Navixy **IoT Query** akan membantu mengorganisir segala jenis analitik pada setiap tahap kontrak leasing. Sebuah kontrak leasing melewati beberapa fase yang dapat diprediksi: Onboarding & Asset Setup → Operational Phase → Risk & Compliance Oversight Resep SQL berikut dalam buku Anda secara kolektif memantau setiap tonggak penting di seluruh siklus tersebut: | Fase Siklus Hidup | Tujuan & Tonggak | Kasus Penggunaan / Resep yang Dicakup | | ----------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Onboarding & Penyiapan Aset | • Mendaftarkan kendaraan, mengaktifkan asuransi dan kredensial pengemudi. • Mengimpor aset ke portal klien dengan visibilitas yang benar. | Peringatan Kedaluwarsa Registrasi/Asuransi – tanggal dasar ditangkap. Kedaluwarsa Surat Izin Mengemudi – memvalidasi pengemudi sebelum pelepasan. | | Perencanaan Pemeliharaan Preventif | • Menetapkan jadwal layanan berkala berbasis jarak tempuh dan waktu. • Menjamin pergantian ban musiman. | Inspeksi Rutin berdasarkan Interval – tugas berbasis kalender. Layanan berdasarkan Ambang Kilometer – aturan servis minor/major berbasis km. Pemantauan Jam Mesin – layanan berbasis jam untuk mesin. | | Batas Penggunaan Terikat Kontrak | • Menegakkan alokasi jarak tempuh dan batasan finansial. • Mendeteksi penggunaan berlebih lebih awal untuk menghindari kejutan di akhir masa. | Batas Kilometer & Penalti – penegakan kilometre tahunan / total kontrak. | | Perilaku Pengemudi & Aset Real‑time | • Melindungi nilai aset; melatih pengemudi. • Mendeteksi penyalahgunaan yang membatalkan cakupan “layanan penuh”. | Rem Keras. Percepatan Keras. Tikungan Mendadak/Cornering. | | Pengawasan Risiko & Kepatuhan | • Menjaga aset tetap di dalam batas geografis dan kontraktual. • Mempertahankan hak untuk menonaktifkan atau merebut kembali. | Keluar Geofence (Perbatasan Negara) – peringatan instan saat pelanggaran wilayah. Deteksi Pengapian & Idle – pelacakan pemborosan bahan bakar / penyalahgunaan. | ### Template dashboard Meskipun resep SQL di bawah memberikan kontrol penuh atas analitik leasing, Anda dapat memulai lebih cepat dengan dashboard pra‑buat yang memvisualisasikan metrik kritis di seluruh siklus leasing. Template ini menghilangkan kebutuhan membangun query dan visualisasi dari awal. Impor, sesuaikan parameter, dan mulai memantau kepatuhan, risiko, dan perlindungan aset segera. Template ini menangani alur kerja kunci leasing: pelacakan kedaluwarsa registrasi dan asuransi, pemantauan Surat Izin Mengemudi, deteksi rem dan percepatan keras dengan klasifikasi tingkat keparahan, analisis waktu idle, dan pemantauan aktivitas perangkat.
Impor konfigurasi ke [Dashboard Studio](https://marketplace.navixy.com/shop/dashboard-studio/), sesuaikan ambang untuk kontrak Anda (batas kilometer, tingkat keparahan perilaku, parameter deteksi idle), dan terapkan ruang kerja pemantauan lengkap. Ini sangat berguna ketika tim membutuhkan dashboard operasional untuk pengawasan kepatuhan dan risiko sehari‑hari tanpa menulis SQL. **Prasyarat:** * IoT Query diaktifkan di lingkungan Anda * Dashboard Studio terpasang dan dapat diakses * Minimal 72 jam data pelacakan * Tabel skema standar terisi: `tracking_data_core`, `states`, `objects`, `vehicles`, `employees` **Konfigurasi setelah impor:** Setelah mengimpor template, sesuaikan dengan kontrak leasing spesifik dan ambang operasional Anda: 1. Tinjau rentang waktu default 72 jam dan sesuaikan jika ketersediaan data Anda berbeda. 2. Tetapkan ambang keparahan untuk kejadian mengemudi dalam parameter query (default: 60+ km/h/s untuk peringatan, 80+ km/h/s untuk peringatan kritis). 3. Konfigurasikan parameter deteksi idle (default: kecepatan di bawah 5 km/h, durasi minimum 5 menit). 4. Perbarui label zona geofence dalam query penyeberangan perbatasan jika memantau pembatasan wilayah. 5. Gunakan pemilih waktu global untuk menganalisis periode historis atau fokus pada aktivitas terbaru. **Template JSON:** {% file src="" %} {% code lineNumbers="true" expandable="true" %} ```json { "id": null, "uid": "hello-world", "tags": [ "example", "getting-started" ], "time": { "to": "now", "from": "now-72h" }, "links": [], "style": "dark", "title": "Leasing Dashboard", "panels": [ { "id": 10, "type": "text", "title": "Deteksi Pengapian & Idle", "gridPos": { "h": 4, "w": 24, "x": 0, "y": 158 }, "options": { "mode": "markdown", "content": " " }, "x-navixy": { "sql": { "statement": "" }, "dataset": { "shape": "table", "columns": {} } } }, { "id": 11, "type": "piechart", "title": "Pengemudi dengan tanggal kadaluwarsa terdekat", "gridPos": { "h": 12, "w": 12, "x": 12, "y": 0 }, "options": { "pieType": "donut" }, "x-navixy": { "sql": { "params": {}, "statement": "SELECT \r\n CASE \r\n WHEN (DATE(e.driver_license_valid_till) - CURRENT_DATE)::INTEGER < 0 THEN 'Expired'\r\n ELSE 'Others'\r\n END AS category,\r\n COUNT(*) AS value\r\nFROM raw_business_data.employees e\r\nWHERE e.driver_license_valid_till IS NOT NULL\r\nGROUP BY category\r\nORDER BY category" }, "verify": { "max_rows": 1000 }, "dataset": { "shape": "pie", "columns": { "value": { "type": "integer" }, "category": { "type": "string" } } } } }, { "id": 12, "type": "piechart", "title": "Kendaraan dengan tanggal kedaluwarsa terdekat", "gridPos": { "h": 12, "w": 12, "x": 0, "y": 0 }, "options": { "pieType": "donut" }, "x-navixy": { "sql": { "params": {}, "statement": "WITH expiry_data AS (\r\n SELECT \r\n CASE \r\n WHEN v.free_insurance_valid_till_date IS NOT NULL\r\n AND (v.liability_insurance_valid_till IS NULL \r\n OR v.free_insurance_valid_till_date <= v.liability_insurance_valid_till)\r\n THEN v.free_insurance_valid_till_date\r\n WHEN v.liability_insurance_valid_till IS NOT NULL\r\n THEN v.liability_insurance_valid_till\r\n ELSE NULL\r\n END AS nearest_expiry_date\r\n FROM raw_business_data.vehicles v\r\n),\r\ncategorized_data AS (\r\n SELECT \r\n CASE \r\n WHEN (DATE(nearest_expiry_date) - CURRENT_DATE)::INTEGER < 0 THEN 'Expired'\r\n WHEN (DATE(nearest_expiry_date) - CURRENT_DATE)::INTEGER >= 0 \r\n AND (DATE(nearest_expiry_date) - CURRENT_DATE)::INTEGER < 30 THEN 'Expires within 30 days'\r\n ELSE 'Others'\r\n END AS category\r\n FROM expiry_data\r\n WHERE nearest_expiry_date IS NOT NULL\r\n)\r\nSELECT \r\n category,\r\n COUNT(*) AS value\r\nFROM categorized_data\r\nGROUP BY category\r\nORDER BY \r\n CASE category\r\n WHEN 'Expired' THEN 1\r\n WHEN 'Expires within 30 days' THEN 2\r\n WHEN 'Others' THEN 3\r\n END" }, "verify": { "max_rows": 1000 }, "dataset": { "shape": "pie", "columns": { "value": { "type": "integer" }, "category": { "type": "string" } } } } }, { "id": 13, "type": "barchart", "title": "Kejadian Rem Keras", "gridPos": { "h": 11, "w": 24, "x": 0, "y": 12 }, "options": { "textMode": "auto" }, "x-navixy": { "sql": { "params": {}, "statement": "WITH spd AS (\r\n SELECT\r\n device_id,\r\n device_time,\r\n speed/100.0 AS kmh,\r\n LAG(speed/100.0) OVER (PARTITION BY device_id ORDER BY device_time) AS prev_kmh,\r\n EXTRACT(EPOCH FROM (device_time - LAG(device_time) OVER (PARTITION BY device_id ORDER BY device_time))) AS dt_sec\r\n FROM\r\n raw_telematics_data.tracking_data_core\r\n WHERE 1=1\r\n ),\r\n decels AS (\r\n SELECT\r\n device_id,\r\n device_time,\r\n (prev_kmh - kmh) / NULLIF(dt_sec, 0) AS decel_kmh_per_sec\r\n FROM\r\n spd\r\n WHERE\r\n prev_kmh IS NOT NULL\r\n ),\r\n events_with_severity AS (\r\n SELECT \r\n DATE(d.device_time) AS category,\r\n CASE \r\n WHEN d.decel_kmh_per_sec >= 80 THEN 'Critical'\r\n WHEN d.decel_kmh_per_sec >= 60 THEN 'Warning'\r\n ELSE 'Normal'\r\n END AS series\r\n FROM decels d\r\n WHERE d.decel_kmh_per_sec >= 60\r\n )\r\n SELECT \r\n category,\r\n COUNT(*) AS value,\r\n series\r\n FROM events_with_severity\r\n GROUP BY category, series\r\n ORDER BY series" }, "verify": { "max_rows": 1000 }, "dataset": { "shape": "category_value", "columns": { "value": { "type": "integer" }, "series": { "type": "string" }, "category": { "type": "date" } } }, "visualization": { "sortOrder": "none", "colorPalette": "vibrant" } } }, { "id": 14, "type": "barchart", "title": "Kejadian Percepatan Keras", "gridPos": { "h": 17, "w": 24, "x": 0, "y": 41 }, "options": { "orientation": "vertical" }, "x-navixy": { "sql": { "params": {}, "statement": "WITH spd AS (\r\n SELECT\r\n device_id,\r\n device_time,\r\n speed/100.0 AS kmh,\r\n LAG(speed/100.0) OVER (PARTITION BY device_id ORDER BY device_time) AS prev_kmh,\r\n EXTRACT(EPOCH FROM (device_time - LAG(device_time) OVER (PARTITION BY device_id ORDER BY device_time))) AS dt_sec\r\n FROM\r\n raw_telematics_data.tracking_data_core\r\n ),\r\n accels AS (\r\n SELECT\r\n device_id,\r\n device_time,\r\n (kmh - prev_kmh) / NULLIF(dt_sec, 0) AS accel_kmh_per_sec\r\n FROM\r\n spd\r\n WHERE\r\n prev_kmh IS NOT NULL\r\n ),\r\n events_with_severity AS (\r\n SELECT \r\n DATE(a.device_time) AS category,\r\n CASE \r\n WHEN a.accel_kmh_per_sec >= 80 THEN 'Critical'\r\n WHEN a.accel_kmh_per_sec >= 60 THEN 'Warning'\r\n ELSE 'Normal'\r\n END AS series\r\n FROM accels a\r\n WHERE a.accel_kmh_per_sec >= 60\r\n )\r\n SELECT \r\n category,\r\n COUNT(*) AS value,\r\n series\r\n FROM events_with_severity\r\n GROUP BY category, series\r\n ORDER BY series" }, "verify": { "max_rows": 1000 }, "dataset": { "shape": "category_value", "columns": { "value": { "type": "integer" }, "series": { "type": "string" }, "category": { "type": "date" } } }, "visualization": { "colorPalette": "vibrant" } } }, { "id": 15, "type": "barchart", "title": "Tikungan Mendadak / Cornering", "gridPos": { "h": 13, "w": 24, "x": 0, "y": 23 }, "options": { "orientation": "vertical" }, "x-navixy": { "sql": { "params": {}, "statement": "WITH pts AS (\r\n SELECT\r\n device_id,\r\n device_time,\r\n latitude/1e7::numeric AS lat,\r\n longitude/1e7::numeric AS lon,\r\n LAG(latitude/1e7::numeric) OVER (PARTITION BY device_id ORDER BY device_time) AS prev_lat,\r\n LAG(longitude/1e7::numeric) OVER (PARTITION BY device_id ORDER BY device_time) AS prev_lon,\r\n speed/100.0 AS kmh,\r\n LAG(speed/100.0) OVER (PARTITION BY device_id ORDER BY device_time) AS prev_kmh\r\n FROM\r\n raw_telematics_data.tracking_data_core\r\n ),\r\n bearing AS (\r\n SELECT *,\r\n atan2(\r\n sin(radians(lon-prev_lon))*cos(radians(lat)),\r\n cos(radians(prev_lat))*sin(radians(lat)) -\r\n sin(radians(prev_lat))*cos(radians(lat))*cos(radians(lon-prev_lon))\r\n ) * 180/pi() AS heading_change\r\n FROM pts\r\n WHERE prev_lat IS NOT NULL AND prev_lon IS NOT NULL\r\n ),\r\n events_with_severity AS (\r\n SELECT \r\n DATE(b.device_time) AS category,\r\n CASE \r\n WHEN ABS(b.heading_change) >= 50 AND b.kmh >= 30 THEN 'Critical'\r\n WHEN ABS(b.heading_change) >= 30 AND b.kmh >= 30 THEN 'Warning'\r\n ELSE 'Normal'\r\n END AS series\r\n FROM bearing b\r\n )\r\n SELECT \r\n category,\r\n COUNT(*) AS value,\r\n series\r\n FROM events_with_severity\r\n GROUP BY category, series\r\n ORDER BY category, series" }, "verify": { "max_rows": 1000 }, "dataset": { "shape": "category_value", "columns": { "value": { "type": "integer" }, "series": { "type": "string" }, "category": { "type": "date" } } }, "visualization": { "stacking": "percent", "colorPalette": "classic" } } }, { "id": 16, "type": "stat", "title": "Total Kejadian Idle", "gridPos": { "h": 5, "w": 8, "x": 0, "y": 36 }, "options": { "textMode": "auto" }, "x-navixy": { "sql": { "params": {}, "statement": "WITH ign AS (\r\n SELECT device_id,\r\n device_time,\r\n value::int AS ign_on\r\n FROM raw_telematics_data.states\r\n WHERE state_name = 'ignition'\r\n ),\r\n spd AS (\r\n SELECT device_id, device_time, speed/100.0 AS kmh\r\n FROM raw_telematics_data.tracking_data_core\r\n ),\r\n merged AS (\r\n SELECT i.device_id,\r\n i.device_time,\r\n i.ign_on,\r\n s.kmh,\r\n LEAD(i.device_time) OVER (PARTITION BY i.device_id ORDER BY i.device_time) AS next_time\r\n FROM ign i\r\n LEFT JOIN spd s ON s.device_id = i.device_id AND s.device_time = i.device_time\r\n )\r\n SELECT COUNT(*) AS value\r\n FROM merged m\r\n WHERE m.ign_on = 1 \r\n AND (m.kmh IS NULL OR m.kmh < 5) \r\n AND m.next_time IS NOT NULL\r\n AND EXTRACT(EPOCH FROM (m.next_time - m.device_time))/60 >= 5" }, "verify": { "max_rows": 1000 }, "dataset": { "shape": "kpi", "columns": {} } } }, { "id": 9, "type": "timeseries", "title": "Pesan Seiring Waktu", "gridPos": { "h": 13, "w": 24, "x": 0, "y": 83 }, "options": { "legend": { "calcs": [], "placement": "bottom", "showLegend": true, "displayMode": "list" }, "tooltip": { "mode": "single", "sort": "none" } }, "targets": [], "x-navixy": { "sql": { "params": {}, "statement": "WITH bounds AS (\nSELECT date_trunc('hour', NOW() - INTERVAL '24 hours') AS start_ts, date_trunc('hour', NOW()) AS end_ts ), hours AS (\nSELECT generate_series( (\nSELECT start_ts\nFROM bounds), (\nSELECT end_ts\nFROM bounds), INTERVAL '1 hour' ) AS bucket ), counts AS (\nSELECT date_trunc('hour', t.device_time) AS bucket, COUNT(*) AS messages, COUNT(DISTINCT t.device_id) AS unique_devices\nFROM raw_telematics_data.tracking_data_core t --\n JOIN raw_business_data.objects o\n ON o.device_id = t.device_id --\n AND o.client_id = 398286 -- uncomment & set if you want a specific client\nWHERE t.device_time >= (\nSELECT start_ts\nFROM bounds)\n AND t.device_time < (\nSELECT end_ts\nFROM bounds) + INTERVAL '1 hour'\nGROUP BY 1 )\nSELECT h.bucket AS time, COALESCE(c.messages, 0) AS messages, COALESCE(c.unique_devices, 0) AS unique_devices\nFROM hours h LEFT\n JOIN counts c USING (bucket)\nORDER BY time;" }, "verify": { "max_rows": 1000 }, "dataset": { "shape": "time_value", "columns": {} }, "visualization": { "lineStyle": "solid", "colorPalette": "modern", "interpolation": "smooth", "legendPosition": "top" } }, "datasource": null, "fieldConfig": { "defaults": { "unit": "short", "color": { "mode": "palette-classic" }, "custom": { "stacking": { "mode": "none", "group": "A" }, "drawStyle": "line", "lineWidth": 1, "spanNulls": false, "showPoints": "auto", "fillOpacity": 10, "gradientMode": "none", "axisPlacement": "auto", "lineInterpolation": "linear" }, "mappings": [], "thresholds": { "mode": "absolute", "steps": [ { "color": "green", "value": null } ] } }, "overrides": [] } }, { "id": 1, "type": "kpi", "title": "Jumlah Sampel", "gridPos": { "h": 5, "w": 6, "x": 18, "y": 128 }, "options": { "textMode": "auto", "colorMode": "value", "graphMode": "none", "justifyMode": "auto", "orientation": "auto" }, "targets": [], "x-navixy": { "sql": { "params": { "tenant_id": { "type": "uuid" } }, "statement": "SELECT *\nFROM raw_telematics_data.tracking_data_core LIMIT 10;" }, "verify": { "max_rows": 1 }, "dataset": { "shape": "kpi", "columns": { "value": { "type": "number" } } } }, "datasource": null, "description": "", "fieldConfig": { "defaults": { "unit": "short", "color": { "mode": "thresholds" }, "thresholds": { "mode": "absolute", "steps": [ { "color": "green", "value": null } ] } }, "overrides": [] } }, { "id": 2, "type": "barchart", "title": "Data Sampel berdasarkan Kategori", "gridPos": { "h": 15, "w": 18, "x": 0, "y": 130 }, "options": { "valueMode": "color", "displayMode": "gradient", "orientation": "horizontal", "showUnfilled": true }, "targets": [], "x-navixy": { "sql": { "params": {}, "statement": "SELECT o.object_label, COUNT(*) AS msgs_24h\nFROM raw_telematics_data.tracking_data_core AS t\n JOIN raw_business_data.objects AS o\n ON o.device_id = t.device_id\nWHERE t.device_time >= NOW() - INTERVAL '24 hours'\nGROUP BY o.client_id, o.object_label, t.device_id\nORDER BY msgs_24h DESC LIMIT 20;" }, "verify": { "max_rows": 10 }, "dataset": { "shape": "category_value", "columns": {} }, "visualization": { "orientation": "vertical" } }, "datasource": null, "fieldConfig": { "defaults": { "unit": "short", "color": { "mode": "palette-classic" }, "custom": { "hideFrom": { "viz": false, "legend": false, "tooltip": false } }, "mappings": [], "thresholds": { "mode": "absolute", "steps": [ { "color": "green", "value": null } ] } }, "overrides": [] } }, { "id": 3, "type": "table", "title": "Pesan per hari", "gridPos": { "h": 9, "w": 12, "x": 4, "y": 148 }, "options": { "sortBy": [], "showHeader": true }, "targets": [], "x-navixy": { "sql": { "params": { "__to": null, "__from": null }, "statement": "SELECT TO_CHAR(date_trunc('day', t.device_time), 'Mon DD, YYYY') AS \"Date\", \n COUNT(*) AS \"Messages\"\nFROM raw_telematics_data.tracking_data_core AS t\nWHERE t.device_time >= ${__from}\n AND t.device_time < ${__to}\nGROUP BY 1\nORDER BY 1;" }, "verify": { "max_rows": 10 }, "dataset": { "shape": "table", "columns": {} }, "visualization": { "pageSize": 5 } }, "datasource": null, "fieldConfig": { "defaults": { "custom": { "align": "auto", "displayMode": "auto" }, "mappings": [], "thresholds": { "mode": "absolute", "steps": [ { "color": "green", "value": null } ] } }, "overrides": [] } }, { "id": 6, "type": "kpi", "title": "Total Waktu Idle", "gridPos": { "h": 5, "w": 8, "x": 8, "y": 36 }, "options": { "textMode": "auto" }, "x-navixy": { "sql": { "params": {}, "statement": "WITH ign AS (\n SELECT device_id,\n device_time,\n value::int AS ign_on\n FROM raw_telematics_data.states\n WHERE state_name = 'ignition'\n ),\n spd AS (\n SELECT device_id, device_time, speed/100 AS kmh\n FROM raw_telematics_data.tracking_data_core\n ),\n merged AS (\n SELECT i.device_id,\n i.device_time,\n i.ign_on,\n s.kmh,\n LEAD(i.device_time) OVER (PARTITION BY i.device_id ORDER BY i.device_time) AS next_time\n FROM ign i\n LEFT JOIN spd s ON s.device_id = i.device_id AND s.device_time = i.device_time\n )\n SELECT round(COALESCE(SUM(EXTRACT(EPOCH FROM (m.next_time - m.device_time))/60), 0), 0) AS value\n FROM merged m\n WHERE m.ign_on = 1 \n AND (m.kmh IS NULL OR m.kmh < 5) \n AND m.next_time IS NOT NULL\n AND EXTRACT(EPOCH FROM (m.next_time - m.device_time))/60 >= 5" }, "verify": { "max_rows": 1000 }, "dataset": { "shape": "kpi", "columns": { "value": { "type": "number" } } } } }, { "id": 7, "type": "piechart", "title": "Perangkat aktif teratas dalam 24 jam terakhir", "gridPos": { "h": 9, "w": 12, "x": 0, "y": 121 }, "options": { "pieType": "donut" }, "x-navixy": { "sql": { "params": {}, "statement": "SELECT o.object_label, COUNT(*) AS msgs_24h\nFROM raw_telematics_data.tracking_data_core AS t\n JOIN raw_business_data.objects AS o\n ON o.device_id = t.device_id\nWHERE t.device_time >= NOW() - INTERVAL '24 hours'\nGROUP BY o.client_id, o.object_label, t.device_id\nORDER BY msgs_24h DESC LIMIT 20;" }, "verify": { "max_rows": 1000 }, "dataset": { "shape": "pie", "columns": {} } } }, { "id": 8, "type": "table", "title": "Pergerakan Kendaraan Baru‑baru ini", "gridPos": { "h": 25, "w": 24, "x": 0, "y": 96 }, "options": { "showHeader": true }, "x-navixy": { "sql": { "params": {}, "statement": "SELECT\n to_char(t.device_time, 'YYYY-MM-DD HH24:MI:SS TZ') AS \"Timestamp\",\n t.device_id AS \"Device ID\",\n t.speed AS \"Speed\",\n round(t.latitude::numeric / 10000000, 6) AS \"Latitude (°)\",\n round(t.longitude::numeric / 10000000, 6) AS \"Longitude (°)\"\nFROM raw_telematics_data.tracking_data_core t\nWHERE t.device_time >= NOW() - INTERVAL '3 days'\n AND t.speed > 0\nORDER BY t.device_time DESC;" }, "verify": { "max_rows": 3 }, "dataset": { "shape": "table", "columns": { "Speed": { "type": "integer" }, "Device ID": { "type": "integer" }, "Timestamp": { "type": "string" }, "Latitude (°)": { "type": "number" }, "Longitude (°)": { "type": "number" } } }, "visualization": { "pageSize": 25, "sortable": true, "showHeader": true, "showTotals": false, "rowHighlighting": "hover" } } }, { "id": 19, "type": "stat", "title": "Rata‑rata Durasi Idle", "gridPos": { "h": 5, "w": 8, "x": 16, "y": 36 }, "options": { "textMode": "auto" }, "x-navixy": { "sql": { "params": {}, "statement": "WITH ign AS (\r\n SELECT device_id,\r\n device_time,\r\n value::int AS ign_on\r\n FROM raw_telematics_data.states\r\n WHERE state_name = 'ignition'\r\n ),\r\n spd AS (\r\n SELECT device_id, device_time, speed/100.0 AS kmh\r\n FROM raw_telematics_data.tracking_data_core\r\n ),\r\n merged AS (\r\n SELECT i.device_id,\r\n i.device_time,\r\n i.ign_on,\r\n s.kmh,\r\n LEAD(i.device_time) OVER (PARTITION BY i.device_id ORDER BY i.device_time) AS next_time\r\n FROM ign i\r\n LEFT JOIN spd s ON s.device_id = i.device_id AND s.device_time = i.device_time\r\n )\r\n SELECT round(COALESCE(AVG(EXTRACT(EPOCH FROM (m.next_time - m.device_time))/60), 0),0) AS value\r\n FROM merged m\r\n WHERE m.ign_on = 1 \r\n AND (m.kmh IS NULL OR m.kmh < 5) \r\n AND m.next_time IS NOT NULL\r\n AND EXTRACT(EPOCH FROM (m.next_time - m.device_time))/60 >= 5" }, "verify": { "max_rows": 1000 }, "dataset": { "shape": "kpi", "columns": {} } } } ], "refresh": "30s", "version": 1, "editable": true, "timezone": "browser", "x-navixy": { "execution": { "dialect": "postgresql", "endpoint": "/api/v1/sql/run", "max_rows": 1000, "read_only": true, "timeout_ms": 5000, "allowed_schemas": [ "demo_data" ] }, "parameters": { "bindings": { "to": "${__to}", "from": "${__from}", "tenant_id": "${var_tenant}" } }, "schemaVersion": "1.0.0" }, "templating": { "list": [ { "name": "var_tenant", "type": "constant", "label": "Tenant", "query": "demo-tenant-id", "current": { "text": "Demo Tenant", "value": "demo-tenant-id" }, "options": [ { "text": "Demo Tenant", "value": "demo-tenant-id", "selected": true } ] } ], "enable": true }, "timepicker": { "now": true, "enable": true, "hidden": false, "collapse": false, "time_options": [ "5m", "15m", "1h", "6h", "12h", "24h" ], "refresh_intervals": [ "5s", "10s", "30s", "1m", "5m", "15m", "30m", "1h" ] }, "annotations": { "list": [ { "hide": true, "name": "Annotations & Alerts", "type": "dashboard", "enable": true, "target": { "tags": [], "type": "dashboard", "limit": 100, "matchAny": false }, "builtIn": 1, "iconColor": "rgba(0, 211, 255, 1)", "datasource": { "uid": "-- Dashboard --", "type": "dashboard" } } ] }, "description": "Contoh dashboard sederhana untuk memulai", "graphTooltip": 1, "schemaVersion": 38 } ``` {% endcode %} Untuk mempelajari lebih lanjut tentang aplikasi dashboard IoT Querie, lihat [Dashboard Studio](https://www.navixy.com/docs/analytics/id/dashboard-studio). Untuk bantuan pengaturan, hubungi . ## **Peringatan Kedaluwarsa Registrasi / Asuransi** Bank harus melacak kedaluwarsa registrasi dan asuransi yang akan datang karena mereka bertanggung jawab atas inspeksi teknis, registrasi, dan asuransi. Peringatan tepat waktu mencegah denda dan waktu henti kendaraan. {% code expandable="true" %} ```sql SELECT v.vehicle_id, v.vehicle_label, v.registration_number, v.free_insurance_valid_till_date, v.liability_insurance_valid_till FROM raw_business_data.vehicles v WHERE v.free_insurance_valid_till_date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '30 days' OR v.liability_insurance_valid_till BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '30 days'; ``` {% endcode %} ## **Kedaluwarsa Surat Izin Mengemudi** Meskipun tidak selalu wajib, menawarkan peringatan kedaluwarsa SIM secara proaktif adalah layanan bernilai tambah. Peringatan dini memungkinkan klien memperbarui lisensi sebelum kedaluwarsa. Harap dicatat Anda {% code expandable="true" %} ```sql SELECT e.employee_id, e.first_name || ' ' || e.last_name AS driver_name, e.driver_license_number, e.driver_license_valid_till FROM raw_business_data.employees e WHERE e.driver_license_valid_till BETWEEN CURRENT_DATE AND CURRENT_DATE + (30 * INTERVAL '1 day'); ``` {% endcode %} ## Keluar Geofence (Perbatasan Negara) Kontrak mungkin membatasi pergerakan kendaraan ke wilayah tertentu (mis. Serbia). Keluar dari zona tersebut harus segera memberi peringatan kepada bank sehingga dapat bertindak (mis. menghubungi klien, melumpuhkan aset). Query SQL ini dirancang untuk memantau dan mengidentifikasi ketika sebuah perangkat keluar dari zona geografis yang telah ditetapkan berlabel "Tallaght Depot Geofences." Proses dimulai dengan mengumpulkan dan mengurutkan titik geografis yang mendefinisikan batas zona. Untuk memastikan batas membentuk poligon yang valid, titik pertama ditambahkan kembali ke akhir daftar, secara efektif menutup bentuknya. Set himpunan titik tertutup ini kemudian digunakan untuk membuat poligon yang mewakili zona geografis, yang dikonversi menjadi objek geography untuk analisis spasial. Query kemudian mengambil data pelacakan perangkat dalam rentang waktu yang ditentukan, mengonversi nilai lintang dan bujur mentah menjadi titik geografis. Query menghitung apakah setiap titik perangkat berada di dalam atau di luar zona yang telah ditetapkan menggunakan fungsi ST\_Contains, yang memeriksa keterkapsulan spasial. Parameter yang dihitung pos menunjukkan 'inside' jika titik berada di dalam zona dan 'outside' jika sebaliknya. Akhirnya, query memfilter hasil ini untuk mendeteksi transisi di mana perangkat berpindah dari di dalam zona ke di luar, menggunakan fungsi jendela untuk membandingkan posisi saat ini dengan posisi sebelumnya. Logika ini membantu dalam memantau pergerakan perangkat dan mendeteksi kejadian keluar dari area geografis tertentu. Pastikan Anda menambahkan nilai yang benar untuk parameter: `z.zone_label = 'your_zone_label'.` {% code expandable="true" %} ```sql WITH zone AS ( SELECT z.zone_id, ST_MakePolygon(ST_MakeLine(ARRAY_AGG(ST_MakePoint(g.longitude, g.latitude) ORDER BY g.number)))::geography AS geog FROM raw_business_data.zones z JOIN raw_business_data.geofence_points g ON g.zone_id = z.zone_id WHERE z.zone_label = 'your_zone_label' GROUP BY z.zone_id ), pts AS ( SELECT device_id, device_time, ST_SetSRID(ST_MakePoint(longitude/1e7::numeric, latitude/1e7::numeric), 4326)::geography AS geog FROM raw_telematics_data.tracking_data_core WHERE device_time BETWEEN '2025-07-27 00:00:00' AND '2025-07-28 23:59:59' ), states AS ( SELECT p.*, CASE WHEN ST_Contains(z.geog::geometry, p.geog::geometry) THEN 'inside' ELSE 'outside' END AS pos FROM pts p CROSS JOIN zone z ), filtered_states AS ( SELECT device_id, device_time, pos, LAG(pos) OVER (PARTITION BY device_id ORDER BY device_time) AS prev_pos FROM states ) SELECT device_id, device_time, pos FROM filtered_states WHERE prev_pos = 'inside' AND pos = 'outside'; ``` {% endcode %} ## **Inspeksi Rutin berdasarkan Interval Waktu** Beberapa tugas pemeliharaan berulang pada jadwal waktu tetap. Sistem harus menandai kendaraan yang pemeriksaan/cek berikutnya jatuh tempo dalam interval yang ditentukan. {% code expandable="true" %} ```sql WITH t AS ( SELECT vehicle_id, description, start_date, date_repeat_interval, make_interval(days => date_repeat_interval) AS repeat_interval FROM raw_business_data.vehicle_service_tasks WHERE date_repeat_interval IS NOT NULL ) SELECT vehicle_id, description, start_date, date_repeat_interval, start_date + repeat_interval * floor( extract(epoch from (current_date::timestamp - start_date)) / extract(epoch from repeat_interval) ) AS last_due, start_date + repeat_interval * ( floor( extract(epoch from (current_date::timestamp - start_date)) / extract(epoch from repeat_interval) ) + 1 ) AS next_due FROM t WHERE ( start_date + repeat_interval * ( floor( extract(epoch from (current_date::timestamp - start_date)) / extract(epoch from repeat_interval) ) + 1 ) ) BETWEEN current_date AND (current_date + interval '30 days'); ``` {% endcode %} ## **Layanan berdasarkan Ambang Kilometer (Minor/Major)** Servis minor dan major dipicu oleh jarak tempuh sejak kejadian servis terakhir. Saat kilometer terakumulasi melebihi ambang, servis yang sesuai harus dijadwalkan. Harap perhatikan `kolom vst.description harus berisi komentar / deskripsi yang relevan untuk digunakan sebagai filter dalam kode SQL di bawah.` {% code expandable="true" %} ```sql SELECT v.vehicle_id, v.vehicle_label, km.km_since_service, vst.mileage_limit FROM raw_business_data.vehicles v JOIN LATERAL ( SELECT MAX(vst.completion_date) AS last_service_date FROM raw_business_data.vehicle_service_tasks vst WHERE vst.vehicle_id = v.vehicle_id AND (vst.description ILIKE '%minor%' OR vst.description ILIKE '%major%') AND vst.completion_date IS NOT NULL ) ls ON TRUE JOIN raw_business_data.objects o ON o.object_id = v.vehicle_id JOIN LATERAL ( SELECT SUM(t.track_distance_meters) / 1000.0 AS km_since_service FROM business_data.tracks t WHERE t.device_id = o.device_id AND t.track_start_time > ls.last_service_date ) km ON TRUE JOIN raw_business_data.vehicle_service_tasks vst ON vst.vehicle_id = v.vehicle_id AND vst.completion_date = ls.last_service_date AND (vst.description ILIKE '%minor%' OR vst.description ILIKE '%major%') ``` {% endcode %} ## **Batas Kilometer & Penalti** Kontrak leasing sering menetapkan batas kilometer (misalnya, 25.000 km/tahun). Jika batas dilampaui, klausul penalti berlaku. Sistem harus membandingkan jarak tempuh aktual selama periode kontrak dengan batas yang disepakati dan menghitung biaya. {% code expandable="true" %} ```sql WITH driven AS ( SELECT o.object_id, DATE_TRUNC('year', t.track_start_time) AS year, SUM(t.track_distance_meters) / 1000.0 AS km_year FROM business_data.tracks t JOIN raw_business_data.objects o ON o.device_id = t.device_id WHERE t.track_start_time >= '2023-01-01'::date AND t.track_start_time < '2024-01-01'::date GROUP BY o.object_id, DATE_TRUNC('year', t.track_start_time) ), limits AS ( SELECT object_id, 10000 AS km_limit, 0.5 AS penalty_rate FROM raw_business_data.objects ) SELECT d.object_id, d.year, d.km_year, l.km_limit, GREATEST(d.km_year - l.km_limit, 0) AS km_over, GREATEST(d.km_year - l.km_limit, 0) * l.penalty_rate AS penalty_amount FROM driven d JOIN limits l ON d.object_id = l.object_id; ``` {% endcode %} ## **Pemantauan Jam Mesin** Untuk mesin dan peralatan pertanian, jam operasi—bukan jarak tempuh—menentukan pemeliharaan dan penagihan. Data jam mesin (mis., dari CAN-Bus) harus dimonitor dan dirangkum. {% code expandable="true" %} ```sql WITH last_service AS ( SELECT vst.vehicle_id, MAX(vst.completion_date) AS last_service_date, MAX(vst.completion_engine_hours) AS last_service_engine_hours FROM raw_business_data.vehicle_service_tasks vst GROUP BY vst.vehicle_id ), engine_hours_since_service AS ( SELECT v.vehicle_id, SUM(t.track_duration_seconds) / 3600.0 AS engine_hours_since_service FROM raw_business_data.vehicles v JOIN raw_business_data.objects o ON o.object_id = v.object_id JOIN business_data.tracks t ON t.device_id = o.device_id JOIN last_service ls ON ls.vehicle_id = v.vehicle_id WHERE t.track_start_time > ls.last_service_date GROUP BY v.vehicle_id ) SELECT v.vehicle_id, v.vehicle_label, ls.last_service_engine_hours, ehs.engine_hours_since_service, (COALESCE(ehs.engine_hours_since_service,0) + COALESCE(ls.last_service_engine_hours,0)) AS current_engine_hours, vst.engine_hours_limit FROM raw_business_data.vehicles v JOIN last_service ls ON ls.vehicle_id = v.vehicle_id LEFT JOIN engine_hours_since_service ehs ON ehs.vehicle_id = v.vehicle_id JOIN raw_business_data.vehicle_service_tasks vst ON vst.vehicle_id = v.vehicle_id AND vst.completion_date = ls.last_service_date ``` {% endcode %} ## **Peristiwa Pengereman Keras** Perilaku mengemudi memengaruhi keausan dan kepatuhan kontrak. Mendeteksi pengereman keras membantu pihak bank mengaitkan keausan rem/ban dini ke penyalahgunaan pengemudi dan, jika perlu, mengalihkan biaya. Query SQL di bawah pertama menghitung kecepatan dalam kilometer per jam dan selisih waktu antara titik data berurutan untuk setiap perangkat. Menggunakan informasi ini, query kemudian menghitung laju perlambatan dalam kilometer per jam per detik. Akhirnya, query memfilter dan mengembalikan baris di mana laju perlambatan adalah 20 km/h per detik atau lebih tinggi, yang menunjukkan peristiwa perlambatan signifikan. {% code expandable="true" %} ```sql WITH spd AS ( SELECT device_id, device_time, speed/100.0 AS kmh, LAG(speed/100.0) OVER (PARTITION BY device_id ORDER BY device_time) AS prev_kmh, EXTRACT(EPOCH FROM (device_time - LAG(device_time) OVER (PARTITION BY device_id ORDER BY device_time))) AS dt_sec FROM raw_telematics_data.tracking_data_core WHERE device_time BETWEEN '2025-07-24 00:00:00' AND '2025-07-24 23:59:59' ), decels AS ( SELECT device_id, device_time, (prev_kmh - kmh) / NULLIF(dt_sec, 0) AS decel_kmh_per_sec FROM spd WHERE prev_kmh IS NOT NULL ) SELECT * FROM decels WHERE decel_kmh_per_sec >= 20; ``` {% endcode %} ## **Peristiwa Akselerasi Keras** Akselerasi agresif meningkatkan keausan pada ban, transmisi, drivetrain, dan dudukan mesin. Mengidentifikasi peristiwa ini mendukung pembinaan dan potensi pemulihan biaya. Query SQL di bawah dirancang untuk mengidentifikasi peristiwa akselerasi signifikan dari dataset data pelacakan. Pertama menghitung kecepatan dalam kilometer per jam dan selisih waktu antara titik data berurutan untuk setiap perangkat. Menggunakan informasi ini, query kemudian menghitung laju percepatan dalam kilometer per jam per detik. Akhirnya, query memfilter dan mengembalikan baris di mana laju percepatan memenuhi atau melebihi ambang yang ditentukan, menunjukkan peristiwa akselerasi signifikan. {% code expandable="true" %} ```sql WITH spd AS ( SELECT device_id, device_time, speed/100.0 AS kmh, LAG(speed/100.0) OVER (PARTITION BY device_id ORDER BY device_time) AS prev_kmh, EXTRACT(EPOCH FROM (device_time - LAG(device_time) OVER (PARTITION BY device_id ORDER BY device_time))) AS dt_sec FROM raw_telematics_data.tracking_data_core WHERE device_time BETWEEN '2025-07-28 00:00:00' AND '2025-07-28 23:59:59' ) SELECT device_id, device_time, (kmh - prev_kmh) / NULLIF(dt_sec, 0) AS accel_kmh_per_sec FROM spd WHERE prev_kmh IS NOT NULL AND (kmh - prev_kmh) / NULLIF(dt_sec, 0) >= 20; ``` {% endcode %} ## **Belokan Mendadak / Memotong Tikungan** Belokan tajam yang dikombinasikan dengan perubahan kecepatan yang mendadak menunjukkan pengemudian berisiko. Melacak perilaku semacam itu membantu mendeteksi penggunaan kendaraan yang tidak semestinya. Query SQL ini dirancang untuk mengidentifikasi perubahan arah dan kecepatan yang signifikan dari data pelacakan selama periode waktu yang ditentukan. Pertama mengonversi nilai latitude dan longitude mentah menjadi derajat desimal dan menghitung kecepatan dalam kilometer per jam. Menggunakan fungsi LAG, query mengambil data lokasi dan kecepatan sebelumnya untuk setiap perangkat, yang memungkinkan perhitungan perubahan dari waktu ke waktu. Query kemudian menghitung perubahan heading dalam derajat menggunakan fungsi trigonometri untuk menentukan bearing antara titik berurutan. Ia juga menghitung perubahan kecepatan antara titik-titik ini. Akhirnya, query memfilter hasil untuk hanya menyertakan baris di mana perubahan heading absolut adalah 10 derajat atau lebih dan perubahan kecepatan absolut adalah 5 km/h atau lebih, mengidentifikasi manuver signifikan atau peristiwa dalam data pelacakan. {% code expandable="true" %} ```sql WITH pts AS ( SELECT device_id, device_time, latitude/1e7::numeric AS lat, longitude/1e7::numeric AS lon, LAG(latitude/1e7::numeric) OVER (PARTITION BY device_id ORDER BY device_time) AS prev_lat, LAG(longitude/1e7::numeric) OVER (PARTITION BY device_id ORDER BY device_time) AS prev_lon, speed/100.0 AS kmh, LAG(speed/100.0) OVER (PARTITION BY device_id ORDER BY device_time) AS prev_kmh FROM raw_telematics_data.tracking_data_core WHERE device_time BETWEEN '2025-07-28 00:00:00' AND '2025-07-28 23:59:59' ), bearing AS ( SELECT *, atan2( sin(radians(lon-prev_lon))*cos(radians(lat)), cos(radians(prev_lat))*sin(radians(lat)) - sin(radians(prev_lat))*cos(radians(lat))*cos(radians(lon-prev_lon)) ) * 180/pi() AS heading_change, (kmh - prev_kmh) AS delta_speed FROM pts ) SELECT * FROM bearing WHERE abs(heading_change) >= 10 AND abs(delta_speed) >= 5; ``` {% endcode %} ## **Deteksi Pengapian & Menganggur** Mengukur waktu menganggur (pengapian menyala, kecepatan rendah/tidak ada) membantu mengurangi pemborosan bahan bakar dan mengidentifikasi penyalahgunaan. Periode menganggur yang panjang harus dilaporkan dan dikelola. {% hint style="info" %} [Dashboard Studio](https://www.navixy.com/docs/analytics/id/dashboard-studio) dan alat serupa tidak mendukung substitusi variabel (`:variable` sintaks). Gantilah semua parameter dengan nilai literal sebelum menjalankan query ini. Lihat contoh di bawah untuk format yang benar. {% endhint %} {% code expandable="true" %} ```sql WITH ign AS ( SELECT device_id, device_time, value::int AS ign_on FROM raw_telematics_data.states WHERE state_name = 'ignition' AND device_time BETWEEN :from_ts::timestamptz AND :to_ts::timestamptz -- Untuk Dashboard Studio, gantilah :from_ts dan :to_ts dengan cap waktu literal: -- TIMESTAMPTZ '2024-01-01 00:00:00+00' AND TIMESTAMPTZ '2024-01-07 00:00:00+00' ), spd AS ( SELECT device_id, device_time, speed / 100.0 AS kmh FROM raw_telematics_data.tracking_data_core WHERE device_time BETWEEN :from_ts AND :to_ts -- Untuk Dashboard Studio, gantilah :from_ts dan :to_ts dengan cap waktu literal: -- TIMESTAMPTZ '2024-01-01 00:00:00+00' AND TIMESTAMPTZ '2024-01-07 00:00:00+00' ), merged AS ( SELECT i.device_id, i.device_time, i.ign_on, s.kmh, LEAD(i.device_time) OVER ( PARTITION BY i.device_id ORDER BY i.device_time ) AS next_time FROM ign i LEFT JOIN spd s USING (device_id, device_time) ) SELECT device_id, device_time AS idle_start, next_time AS idle_end, EXTRACT(EPOCH FROM (next_time - device_time)) / 60.0 AS idle_minutes FROM merged WHERE ign_on = 1 AND kmh < :idle_speed -- Untuk Dashboard Studio, gantilah :idle_speed dengan nilai numerik, mis., 5 AND next_time IS NOT NULL AND (next_time - device_time) >= (:idle_min::int * INTERVAL '1 minute'); -- Untuk Dashboard Studio, gantilah :idle_min dengan nilai numerik, mis., (5 * INTERVAL '1 minute') ``` {% endcode %}