Lapisan Silver

Segera hadir!

Arsitektur lapisan Silver yang dijelaskan dalam dokumen ini saat ini sedang dalam pengembangan. Meskipun kemampuan transformasi inti sudah berfungsi, sistem konfigurasi dan detail implementasinya mungkin berkembang sebelum rilis final. Jika Anda tertarik untuk mendapatkan akses awal atau memiliki pertanyaan tentang fungsionalitas ini, harap hubungi [email protected].

Lapisan Silver mengubah data telematik mentah dan informasi bisnis menjadi entitas yang ternormalisasi dan siap-kueri dengan metrik dan struktur yang telah ditetapkan. Lapisan Bronze berisi semua yang ditangkap dari perangkat dan sistem—titik individu, peristiwa, dan nilai field yang berguna untuk verifikasi dan pemecahan masalah. Lapisan Silver memproses data mentah ini menjadi entitas bermakna seperti perjalanan, kunjungan zona, dan status operasional melalui transformasi yang dapat dikonfigurasi yang membersihkan, menstandarkan, dan mengagregasi data menjadi objek analitis yang mudah dipahami.

💡 Lapisan Silver secara singkat: Bronze adalah semua yang dikumpulkan, Silver adalah apa yang dapat Anda gunakan.

Lapisan perantara ini menghilangkan pekerjaan ETL manual yang berulang dan mempersiapkan data untuk analitik praktis. Operator armada mendapatkan jawaban atas pertanyaan umum tanpa pemrosesan data yang luas, sementara integrator memperoleh fondasi yang stabil untuk membangun fungsionalitas yang dapat diskalakan.

Arsitektur dan kapabilitas

Lapisan Silver mengatur data terproses ke dalam dua skema berbeda yang mencerminkan sumber transformasi dan pola akses yang berbeda. Kedua skema beroperasi pada tingkat lapisan Silver dari arsitektur medallion, ditempatkan di atas skema lapisan Bronze (raw_business_data dan raw_telematics_data) dan di bawah lapisan Gold.

Struktur skema

Lapisan Silver menggunakan pendekatan skema dinamis di mana struktur basis data terbentuk secara otomatis berdasarkan transformasi aktif. Berbeda dengan lapisan Bronze dengan definisi skemanya yang tetap, skema lapisan Silver hanya berisi tabel yang sesuai dengan transformasi yang dikonfigurasi dan dideploy. Ini berarti tabel yang tersedia dan strukturnya bergantung pada transformasi mana yang saat ini aktif di IoT Query instansi.

Data lapisan Silver diorganisasikan ke dalam dua skema PostgreSQL:

• processed_common_data: Berisi transformasi yang dikembangkan dan dipelihara oleh Navixy. Skema ini dibagikan di antara semua klien, menyediakan entitas analitis standar yang menangani kasus penggunaan telematik umum. Tabel muncul dalam skema ini saat Navixy mengembangkan dan mendeploy transformasi baru untuk memenuhi kebutuhan analitis yang luas.

• processed_custom_data: Berisi transformasi spesifik-klien yang dibuat untuk memenuhi kebutuhan bisnis unik. Setiap klien memiliki instance terisolasi dari skema ini, memungkinkan entitas analitis kustom tanpa memengaruhi klien lain. Tabel dalam skema ini sesuai dengan transformasi yang dikonfigurasi khusus untuk organisasi Anda.

Kedua skema beroperasi melalui konfigurasi transformasi berbasis JSON. Saat sebuah transformasi dikonfigurasi dan diaktifkan, sistem secara otomatis membuat struktur tabel yang sesuai dalam skema yang tepat. Ketika transformasi dihapus atau dinonaktifkan, tabelnya dapat diarsipkan atau dihapus berdasarkan kebijakan retensi data.

Pembentukan dinamis ini menyebabkan dokumentasi lapisan Silver tidak menyediakan deskripsi skema tetap seperti yang dilakukan lapisan Bronze. Sebagai gantinya, tabel yang tersedia dan strukturnya mencerminkan transformasi spesifik yang dikonfigurasi untuk instance IoT Query Anda. Untuk memahami data apa yang tersedia di lapisan Silver Anda, tinjau dokumentasi transformasi untuk entitas yang telah dideploy ke instance Anda.

Arsitektur pemrosesan

Transformasi lapisan Silver beroperasi melalui arsitektur yang digerakkan oleh konfigurasi yang memisahkan logika bisnis dari orkestrasi. Setiap transformasi didefinisikan oleh konfigurasi JSON yang menetapkan logika pemrosesan SQL, parameter, penjadwalan, dan perilaku perhitungan ulang. Apache Airflow mengelola siklus hidup eksekusi, menerapkan konfigurasi ini untuk memproses data lapisan Bronze menjadi entitas lapisan Silver.

Struktur konfigurasi JSON tetap identik untuk transformasi umum dan kustom, memastikan pola pemrosesan yang konsisten di seluruh entitas lapisan Silver. Pendekatan konfigurasi terpadu ini memungkinkan deployment transformasi yang fleksibel sambil mempertahankan eksekusi dan kontrol versi yang terstandarisasi.

Untuk informasi rinci tentang sistem konfigurasi JSON, lihat Configuration JSON bagian.

Kelengkapan data

Entitas lapisan Silver dipelihara secara otomatis melalui proses terjadwal yang ditentukan dalam konfigurasi transformasi. Saat Anda melakukan kueri pada data lapisan Silver, perhatikan karakteristik pemrosesan ini:

• Pembaruan terjadwal: Setiap transformasi memproses data lapisan Bronze baru sesuai jadwal yang dikonfigurasikan. Pembaruan biasanya terjadi setiap jam atau setiap beberapa jam tergantung pada kompleksitas transformasi.

• Jendela pemrosesan: Transformasi beroperasi pada jendela berbasis waktu untuk memproses segmen data yang dapat dikelola secara efisien daripada keseluruhan dataset.

• Dampak perhitungan ulang: Ketika perubahan konfigurasi memicu perhitungan ulang, data terkini mungkin menunjukkan ketidakkonsistenan singkat selama jendela pemrosesan.

• Perilaku spesifik skema: Transformasi di processed_common_data memperbarui secara bersamaan untuk semua klien yang berbagi skema tersebut. Transformasi di processed_custom_data berjalan secara mandiri per klien, memungkinkan penjadwalan dan logika pemrosesan yang disesuaikan.

Configuration JSON

Bagian ini menjelaskan arsitektur konfigurasi yang saat ini sedang dikembangkan. Struktur JSON dan spesifikasi parameter yang ditunjukkan di sini merepresentasikan pendekatan implementasi yang direncanakan. Detail implementasi akhir mungkin berbeda seiring kemajuan pengembangan.

Lapisan Silver beroperasi pada arsitektur yang digerakkan oleh konfigurasi di mana transformasi didefinisikan oleh spesifikasi JSON. Setiap konfigurasi berisi logika pemrosesan, parameter transformasi, aturan penjadwalan, dan kebijakan perhitungan ulang yang menentukan bagaimana data lapisan Bronze menjadi entitas lapisan Silver.

Struktur JSON

Sebuah konfigurasi transformasi terdiri dari empat bagian:

• version (string): Versi konfigurasi mengikuti semantic versioning

• metadata (object): Informasi dasar termasuk nama, deskripsi, timestamp pembuatan, dan identifier pembuat

• sql_template (object): Spesifikasi logika pemrosesan termasuk jalur file SQL, definisi tabel target, dan parameter transformasi

• target (object): Lokasi keluaran yang menentukan skema dan tabel

• scheduler (object): Kontrol eksekusi termasuk jadwal cron, status aktif, dan konfigurasi backfill

Skema konfigurasi

{
  "version": "<semantic_version>",
  "metadata": {
    "name": "<transformation_name>",
    "description": "<transformation_description>",
    "created_at": "<iso_8601_timestamp>",
    "created_by": "<creator_identifier>"
  },
  "sql_template": {
    "sql_file": "<path_to_primary_sql_script>",
    "sql_file_1": "<path_to_additional_sql_script>",
    "table_name": "<schema.table_for_source_data>",
    "parameters": {
      "<parameter_name_1>": "<parameter_value_1>",
      "<parameter_name_2>": "<parameter_value_2>"
    }
  },
  "target": {
    "schema": "<output_schema>",
    "table": "<output_table>"
  },
  "scheduler": {
    "cron_expression": "<cron_schedule>",
    "enabled": <boolean>,
    "backfill": {
      "backfill_allowed": <boolean>,
      "start_date": "<iso_8601_date>",
      "end_date": "<iso_8601_date>"
    }
  }
}

Parameterisasi skrip SQL

Skrip SQL transformasi merujuk parameter konfigurasi menggunakan placeholder yang diawali titik dua. Sistem menggantikan nilai aktual dari konfigurasi saat mengeksekusi skrip dan menyediakan parameter jendela waktu standar secara otomatis:

• :window_start - Awal jendela waktu pemrosesan (timestamp ISO-8601)

• :window_end - Akhir jendela waktu pemrosesan (timestamp ISO-8601)

Parameter kustom didefinisikan dalam sql_template.parameters bagian dan mengendalikan logika spesifik transformasi seperti ambang kualitas, aturan bisnis, dan metode perhitungan.

Contoh SQL dengan parameter:

SELECT device_id, device_time, speed
FROM raw_telematics_data.tracking_data_core
WHERE device_time >= :window_start
  AND device_time < :window_end
  AND satellites >= :min_satellites
  AND speed >= :min_speed_kmh;

Versi konfigurasi

Ketika ada perubahan parameter konfigurasi apa pun, versi baru dibuat. Setiap versi merepresentasikan satu set aturan pemrosesan spesifik yang aktif selama periode waktu tertentu, memungkinkan pelacakan bagaimana logika transformasi berkembang.

Pemicu pembuatan versi:

• Setiap parameter dalam sql_template.parameters berubah

• Jalur file skrip SQL diubah

• Skema atau tabel target berubah

• Pengaturan scheduler atau backfill disesuaikan

Penerapan versi: Ketika versi konfigurasi baru dibuat dan diterapkan, sistem memproses data berdasarkan mode perhitungan ulang yang dipilih.

Mode perhitungan ulang

Sistem konfigurasi mendukung tiga mode perhitungan ulang yang mengontrol bagaimana perubahan parameter memengaruhi data historis dan masa depan. Mode ini memberikan fleksibilitas dalam menyeimbangkan kebutuhan konsistensi data dengan efisiensi pemrosesan.

Perhitungan ulang maju-saja

Mode maju-saja menerapkan parameter konfigurasi baru hanya pada data yang diproses setelah perubahan versi. Data historis tetap tidak berubah, mempertahankan nilai yang dihitung dengan parameter sebelumnya.

Kapan digunakan: Penyesuaian parameter minor yang tidak mengubah definisi entitas secara fundamental, menguji parameter baru sebelum perhitungan ulang penuh, atau mengelola biaya komputasi dengan menghindari pemrosesan ulang historis.

Perilaku: Jika Anda mengubah min_speed_kmh dari 3 menjadi 5 pada 8 Desember, hanya perjalanan yang diproses sejak 8 Desember yang menggunakan ambang baru. Perjalanan yang dihitung sebelum 8 Desember mempertahankan nilai aslinya.

Perhitungan ulang seluruh riwayat

Mode perhitungan ulang penuh memproses semua data historis dalam rentang tanggal backfill yang dikonfigurasikan menggunakan parameter baru. Sistem menggantikan semua entitas yang ada dengan nilai yang dihitung ulang.

Kapan digunakan: Perubahan mendasar pada definisi entitas atau algoritma deteksi, memperbaiki kesalahan sistematis dalam perhitungan sebelumnya, atau menstandarkan semua data historis sesuai aturan bisnis saat ini.

Perilaku: Mengubah logika deteksi perjalanan memerlukan perhitungan ulang semua perjalanan untuk memastikan definisi entitas yang konsisten di seluruh rentang waktu.

Perhitungan ulang parsial

Mode perhitungan ulang parsial memproses jendela waktu terbatas dari data historis, biasanya beberapa hari atau minggu terakhir.

Kapan digunakan: Memperbaiki masalah kualitas data baru-baru ini, memperbarui parameter yang terutama memengaruhi pola operasional terkini, atau menerapkan perubahan dengan dampak historis terbatas.

Konfigurasi: Tentukan sebuah backfill_days parameter (mis. 7 untuk minggu terakhir) baik dalam konfigurasi atau saat memicu perhitungan ulang secara manual. Sistem memperbarui catatan yang ada dalam jendela waktu yang ditentukan.

Transformasi yang tersedia

Lapisan Silver saat ini menyediakan dua kelompok transformasi yang menunjukkan pendekatan berbasis konfigurasi dan berfungsi sebagai template untuk mengembangkan entitas kustom.

Perjalanan

Ini adalah transformasi pelacakan pergerakan yang mengidentifikasi segmen pergerakan kontinu dari data pelacakan mentah dan menghitung metrik perjalanan yang komprehensif.

Referensi cepat:

• Tujuan: Mengonversi data lokasi tingkat-titik menjadi analitik tingkat-perjalanan

• Tabel utama: business_data.tracks

• Metrik utama: Jarak, durasi, statistik kecepatan, batas geografis

• Data sumber: raw_telematics_data.tracking_data_core, raw_telematics_data.states

Tabel: business_data.tracks

Tabel tracks menyimpan informasi teragregasi tentang segmen pergerakan kontinu dengan metrik yang telah dihitung sebelumnya dan konteks geografis.

Kunci utama: track_id (identifikasi unik auto-increment)

Deskripsi field:

Jarak Haversine track_id field mengidentifikasi secara unik setiap segmen perjalanan. Field device_id mengacu pada perangkat pelacakan dari lapisan Bronze. Field track_start_time dan track_end_time mendefinisikan batas temporal perjalanan. Field track_duration menyediakan format durasi yang dapat dibaca manusia sementara track_duration_seconds memungkinkan perhitungan numerik. Field track_distance_meters berisi total jarak yang ditempuh. Field kecepatan (avg_speed, max_speed, min_speed) memberikan ringkasan statistik dalam kilometer per jam. Koordinat awal (latitude_start, longitude_start, altitude_start) dan koordinat akhir (latitude_end, longitude_end, altitude_end) mendefinisikan batas geografis. Field points_in_track menunjukkan kualitas data melalui jumlah titik. Field start_zone dan end_zone menghubungkan ke data referensi zona ketika perjalanan dimulai atau berakhir di dalam zona yang ditentukan.

Hubungan data:

-- Gabungkan dengan informasi perangkat
FROM business_data.tracks AS t
JOIN raw_business_data.devices AS d
  ON t.device_id = d.device_id

-- Gabungkan dengan rincian kendaraan
FROM business_data.tracks AS t
JOIN raw_business_data.devices AS d
  ON t.device_id = d.device_id
JOIN raw_business_data.vehicles AS v
  ON d.object_id = v.object_id

Algoritma deteksi perjalanan

Transformasi mengidentifikasi perjalanan menggunakan deteksi pergerakan yang menganalisis kecepatan, jarak, dan pola temporal. Sebuah perjalanan merepresentasikan segmen pergerakan kontinu yang dipisahkan dari perjalanan lain oleh periode parkir atau celah data.

Sistem memulai perjalanan baru ketika titik pelacakan pertama muncul untuk sebuah perangkat, ketika pergerakan dimulai setelah durasi parkir yang melebihi ambang yang dikonfigurasikan, ketika pergerakan dilanjutkan setelah celah data yang melebihi timeout yang dikonfigurasikan, atau ketika sebuah titik lokasi LBS (menara seluler) tunggal tercatat. Sistem mengakhiri perjalanan ketika pergerakan berhenti dan durasi parkir mencapai ambang yang dikonfigurasikan, atau ketika terjadi celah data yang melebihi timeout.

Klasifikasi pergerakan:

• Berjalan/bergerak: Kecepatan ≥ ambang yang dikonfigurasikan

• Parkir: Kecepatan < ambang DAN durasi ≥ durasi parkir yang dikonfigurasikan

• Celah data: Waktu antara titik ≥ timeout pemisahan yang dikonfigurasikan

Validasi kualitas: Perjalanan yang dihasilkan harus memenuhi ambang kualitas yang dapat dikonfigurasi untuk disertakan—minimal 2 titik pelacakan, kecepatan maksimum ≥ ambang yang dikonfigurasikan, total jarak ≥ ambang yang dikonfigurasikan, dan waktu mulai serta berakhir yang terdefinisi. Sistem memfilter data anomali termasuk kecepatan yang tidak realistis untuk titik LBS, koordinat nol, dan cakupan satelit yang tidak memadai.

Perhitungan metrik: Metrik perjalanan dihitung dari titik pelacakan yang tervalidasi. Jarak merepresentasikan panjang geometris total. Statistik kecepatan meliputi nilai rata-rata, maksimum, dan minimum dari kecepatan titik. Durasi adalah selisih waktu antara waktu akhir dan awal. Batas geografis menangkap koordinat titik pertama dan terakhir. Asosiasi zona mencocokkan zona awal dan akhir dari data referensi ketika perjalanan dimulai atau berakhir di dalam zona yang ditentukan.

Parameter konfigurasi

Parameter	Deskripsi	Unit
min_parking_seconds	Ambang durasi untuk deteksi parkir	detik
tracks_split_timeout_seconds	Celah maksimal antar titik sebelum memisahkan perjalanan	detik
min_distance_meters	Jarak minimum perjalanan untuk validasi kualitas	meter
min_speed_kmh	Ambang kecepatan minimum untuk deteksi pergerakan	km/h
max_lbs_speed_kmh	Kecepatan realistis maksimum untuk titik LBS	km/h
min_satellites	Jumlah satelit minimum untuk kualitas GPS	count

Contoh konfigurasi

{
  "version": "1.0.0",
  "metadata": {
    "name": "Movement Tracking Transformation",
    "description": "Processes raw tracking data into trip entities with quality validation and metric calculation",
    "created_at": "2025-01-15T00:00:00Z",
    "created_by": "system"
  },
  "sql_template": {
    "sql_file": "/opt/airflow/dags/repo/sql-file/tracks.sql",
    "table_name": "business_data.tracks",
    "parameters": {
      "min_parking_seconds": 300,
      "tracks_split_timeout_seconds": 1200,
      "min_distance_meters": 100,
      "min_speed_kmh": 3,
      "max_lbs_speed_kmh": 200,
      "min_satellites": 4
    }
  },
  "target": {
    "schema": "business_data",
    "table": "tracks"
  },
  "scheduler": {
    "cron_expression": "0 */8 * * *",
    "enabled": true,
    "backfill": {
      "backfill_allowed": true,
      "start_date": "2024-01-01",
      "end_date": "2025-12-31"
    }
  }
}

Contoh skrip SQL

SQL sederhana berikut menunjukkan penggunaan parameter dalam logika transformasi:

-- Deteksi perjalanan dengan parameter yang dapat dikonfigurasi
WITH tracking_with_gaps AS (
    SELECT 
        device_id,
        device_time,
        latitude,
        longitude,
        speed,
        satellites,
        LAG(device_time) OVER (PARTITION BY device_id ORDER BY device_time) as prev_time,
        LAG(speed) OVER (PARTITION BY device_id ORDER BY device_time) as prev_speed
    FROM raw_telematics_data.tracking_data_core
    WHERE device_time >= :window_start
      AND device_time < :window_end
      AND satellites >= :min_satellites
      AND latitude != 0 AND longitude != 0
),
trip_boundaries AS (
    WHEN speed_kph < 3 AND (prev_speed >= 3 OR prev_speed IS NULL) THEN 1
        WHEN cd_low.cal_value IS NOT NULL AND cd_high.cal_value IS NOT NULL THEN 
            WHEN prev_speed < :min_speed_kmh 
                 AND EXTRACT(EPOCH FROM (device_time - prev_time)) >= :min_parking_seconds
                 AND speed >= :min_speed_kmh
            THEN true
            WHEN EXTRACT(EPOCH FROM (device_time - prev_time)) >= :tracks_split_timeout_seconds
            THEN true
            LAINNYA false
        END as is_trip_start
    FROM tracking_with_gaps
)
INSERT INTO business_data.tracks (device_id, track_start_time, track_end_time)
SELECT device_id, MIN(device_time), MAX(device_time)
FROM trip_boundaries
WHERE speed >= :min_speed_kmh
GROUP BY device_id, SUM(is_trip_start::int) OVER (PARTITION BY device_id ORDER BY device_time);

Contoh kueri

Dapatkan semua perjalanan untuk perangkat tertentu:

SELECT 
    track_id,
    track_start_time,
    track_end_time,
    track_duration,
    track_distance_meters,
    avg_speed,
    start_zone,
    end_zone
FROM business_data.tracks
ORDER BY sensor_name
ORDER BY track_start_time DESC;

Hitung ringkasan jarak harian:

SELECT 
    DATE(track_start_time) as trip_date,
    COUNT(*) as total_trips,
    SUM(track_distance_meters) / 1000.0 as total_km,
    AVG(avg_speed) as avg_speed_kmh,
    SUM(track_duration_seconds) / 3600.0 as total_hours
FROM business_data.tracks
ORDER BY sensor_name
  AND track_start_time >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY DATE(track_start_time)
ORDER BY trip_date DESC;

Temukan perjalanan antara zona tertentu:

SELECT 
    t.track_id,
    t.track_start_time,
    t.track_duration,
    t.track_distance_meters / 1000.0 as distance_km,
    t.start_zone,
    t.end_zone,
    v.license_plate
FROM business_data.tracks AS t
JOIN raw_business_data.devices AS d
  ON t.device_id = d.device_id
JOIN raw_business_data.vehicles AS v
  ON d.object_id = v.object_id
WHERE t.start_zone = 'Distribution Center'
  AND t.end_zone = 'Customer Site A'
  AND t.track_start_time >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY t.track_start_time DESC;

Geofences

Transformasi Geofences menghitung terlebih dahulu batas geografis sebagai geometri PostGIS dan melacak ketika perangkat memasuki, tetap di dalam, dan keluar dari area yang ditentukan ini. Pemrosesan ini menghilangkan kebutuhan perhitungan spasial real-time selama kueri, secara signifikan meningkatkan performa untuk analitik berbasis lokasi.

Transformasi ini menunjukkan pemrosesan data spasial dan deteksi peristiwa dari aliran lokasi kontinu.

Referensi cepat:

• Tujuan: Hitung terlebih dahulu geometri geofence dan lacak keberadaan perangkat di area geografis

• Tabel utama: business_data.geofence_geometries, business_data.geofence_visits

• Metrik utama: Durasi kunjungan, waktu masuk/keluar, pemanfaatan geofence

• Manfaat performa: Kueri spasial 10-100x lebih cepat dibandingkan perhitungan geometri secara langsung

• Data sumber: raw_business_data.zones, raw_business_data.geofence_points, raw_telematics_data.tracking_data_core

Tabel: business_data.geofence_geometries

Tabel geofence_geometries menyimpan representasi geometris geofence yang dioptimalkan untuk kueri spasial yang efisien.

Kunci utama: geofence_id

Deskripsi field:

Jarak Haversine geofence_id field mengidentifikasi secara unik setiap geofence dan merujuk ke raw_business_data.zones.zone_id. Field geofence_type menunjukkan bentuk geofence (lingkaran, poligon, atau rute). Field geofence_label berisi nama geofence untuk tampilan dan referensi. Field address menyimpan deskripsi lokasi geofence. Field color menyimpan kode warna HEX untuk visualisasi. Field geofence_geom berisi representasi geografis untuk operasi spasial. Field created_at dan updated_at melacak perubahan temporal.

Spesifikasi tipe geofence:

• Lingkaran: Didefinisikan oleh titik pusat dan radius

• Poligon: Titik terurut yang membentuk bentuk tertutup

• Rute: Jalur garis dengan radius buffer

Perilaku sinkronisasi: Tabel secara otomatis disinkronkan ketika data sumber geofence berubah di lapisan Bronze.

Tabel: business_data.geofence_visits

Tabel geofence_visits mencatat riwayat keberadaan perangkat dalam geofence termasuk waktu masuk, waktu keluar, dan durasi kunjungan.

Kunci utama: Kunci komposit pada (device_id, geofence_id, enter_time)

Deskripsi field:

Jarak Haversine device_id field merujuk pada perangkat pelacakan. Field geofence_id mengacu pada geofence dari geofence_geometries. Field enter_time menandai ketika perangkat memasuki geofence. Field exit_time menandai ketika perangkat keluar (NULL untuk kunjungan yang sedang berlangsung). Field duration berisi panjang kunjungan yang dihitung.

Hubungan data:

-- Gabungkan dengan rincian geofence
FROM business_data.geofence_visits AS gv
JOIN business_data.geofence_geometries AS gg
  ON gv.geofence_id = gg.geofence_id

-- Gabungkan dengan informasi perangkat dan kendaraan
FROM business_data.geofence_visits AS gv
JOIN raw_business_data.devices AS d
  ON gv.device_id = d.device_id
JOIN raw_business_data.vehicles AS v
  ON d.object_id = v.object_id

Algoritma deteksi kunjungan

Transformasi melacak keberadaan perangkat dalam geofence dengan membandingkan titik pelacakan terhadap geometri geofence. Catatan kunjungan merekam ketika perangkat memasuki, tetap di dalam, dan keluar dari geofence yang ditentukan.

Deteksi masuk: Sistem mendeteksi masuk ketika titik pelacakan perangkat berada di dalam geometri geofence dan titik sebelumnya berada di luar geofence ini atau tidak ada titik sebelumnya.

Deteksi keluar: Sistem mendeteksi keluar ketika titik pelacakan perangkat berada di luar geometri geofence dan titik sebelumnya berada di dalam geofence.

Pengelompokan kunjungan: Pasangan masuk-keluar berurutan membentuk satu catatan kunjungan. Kunjungan terbuka (tidak terdeteksi keluar) menunjukkan NULL pada exit_time dan diperbarui ketika keluar terjadi dalam siklus pemrosesan berikutnya.

Perhitungan durasi: Durasi kunjungan dihitung sebagai selisih waktu antara peristiwa masuk dan keluar. Kunjungan terbuka menunjukkan durasi NULL sampai keluar terdeteksi.

Parameter konfigurasi

Parameter	Deskripsi	Unit
spatial_buffer_meters	Jarak buffer untuk deteksi batas geofence	meter
min_visit_duration_seconds	Durasi kunjungan minimum untuk dicatat	detik
max_visit_gap_seconds	Celah waktu maksimum sebelum dianggap kunjungan berakhir	detik

Contoh konfigurasi

{
  "version": "1.0.0",
  "metadata": {
    "name": "Geofences Transformation",
    "description": "Pre-computes geofence geometries and tracks device presence within defined geographic areas",
    "created_at": "2025-01-15T00:00:00Z",
    "created_by": "system"
  },
  "sql_template": {
    "sql_file": "/opt/airflow/dags/repo/sql-file/geofence_visits.sql",
    "table_name": "business_data.geofence_visits",
    "parameters": {
      "spatial_buffer_meters": 50,
      "min_visit_duration_seconds": 60,
      "max_visit_gap_seconds": 1800
    }
  },
  "target": {
    "schema": "business_data",
    "table": "geofence_visits"
  },
  "scheduler": {
    "cron_expression": "0 * * * *",
    "enabled": true,
    "backfill": {
      "backfill_allowed": true,
      "start_date": "2024-01-01",
      "end_date": "2025-12-31"
    }
  }
}

Contoh kueri

Dapatkan semua kunjungan ke geofence tertentu:

SELECT 
    gv.device_id,
    gv.enter_time,
    gv.exit_time,
    gv.duration,
    v.license_plate,
    gg.geofence_label
FROM business_data.geofence_visits AS gv
JOIN business_data.geofence_geometries AS gg
  ON gv.geofence_id = gg.geofence_id
JOIN raw_business_data.devices AS d
  ON gv.device_id = d.device_id
JOIN raw_business_data.vehicles AS v
  ON d.object_id = v.object_id
WHERE gg.geofence_label = 'Main Warehouse'
  AND gv.enter_time >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY gv.enter_time DESC;

Hitung statistik pemanfaatan geofence:

SELECT 
    gg.geofence_label,
    COUNT(*) as total_visits,
    COUNT(DISTINCT gv.device_id) as unique_devices,
    AVG(EXTRACT(EPOCH FROM gv.duration) / 60) as avg_duration_minutes,
    MAX(EXTRACT(EPOCH FROM gv.duration) / 60) as max_duration_minutes
FROM business_data.geofence_visits AS gv
JOIN business_data.geofence_geometries AS gg
  ON gv.geofence_id = gg.geofence_id
WHERE gv.enter_time >= CURRENT_DATE - INTERVAL '30 days'
  AND gv.exit_time IS NOT NULL
GROUP BY gg.geofence_label
ORDER BY total_visits DESC;

Temukan perangkat yang sedang hadir:

SELECT 
    gg.geofence_label,
    gv.device_id,
    v.license_plate,
    gv.enter_time,
    NOW() - gv.enter_time as time_in_geofence
FROM business_data.geofence_visits AS gv
JOIN business_data.geofence_geometries AS gg
  ON gv.geofence_id = gg.geofence_id
JOIN raw_business_data.devices AS d
  ON gv.device_id = d.device_id
JOIN raw_business_data.vehicles AS v
  ON d.object_id = v.object_id
WHERE gv.exit_time IS NULL
ORDER BY gg.geofence_label, gv.enter_time;

Analisis pola masuk/keluar geofence berdasarkan jam:

SELECT 
    gg.geofence_label,
    EXTRACT(HOUR FROM gv.enter_time) as entry_hour,
    COUNT(*) as entries,
    AVG(EXTRACT(EPOCH FROM gv.duration) / 60) as avg_visit_minutes
FROM business_data.geofence_visits AS gv
JOIN business_data.geofence_geometries AS gg
  ON gv.geofence_id = gg.geofence_id
WHERE gv.enter_time >= CURRENT_DATE - INTERVAL '30 days'
  AND gv.exit_time IS NOT NULL
GROUP BY gg.geofence_label, EXTRACT(HOUR FROM gv.enter_time)
ORDER BY gg.geofence_label, entry_hour;

Identifikasi perangkat dengan waktu tinggal terpanjang:

SELECT 
    gg.geofence_label,
    v.license_plate,
    gv.enter_time,
    gv.exit_time,
    gv.duration,
    EXTRACT(EPOCH FROM gv.duration) / 3600 as duration_hours
FROM business_data.geofence_visits AS gv
JOIN business_data.geofence_geometries AS gg
  ON gv.geofence_id = gg.geofence_id
JOIN raw_business_data.devices AS d
  ON gv.device_id = d.device_id
JOIN raw_business_data.vehicles AS v
  ON d.object_id = v.object_id
WHERE gv.exit_time IS NOT NULL
  AND gv.enter_time >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY gv.duration DESC
LIMIT 20;

Pengembangan entitas kustom

Lapisan Silver menunjukkan pola transformasi melalui transformasi yang tersedia, yang berfungsi sebagai template untuk mengembangkan entitas analitis kustom. Dengan menggunakan data lapisan Bronze, kapabilitas SQL, dan arsitektur konfigurasi, entitas kustom dapat dikembangkan untuk memenuhi kebutuhan bisnis tertentu.

Pendekatan pengembangan

Entitas Silver kustom mengikuti arsitektur yang digerakkan oleh konfigurasi yang dijelaskan dalam dokumen ini. Pendekatannya melibatkan mendefinisikan logika transformasi dalam skrip SQL dan membuat konfigurasi JSON yang menentukan parameter dan jadwal untuk eksekusi otomatis.

Kapabilitas utama: Mengagregasi beberapa titik data mentah menjadi objek analitis tunggal, menerapkan logika bisnis dan aturan validasi, menghitung metrik terlebih dahulu untuk mempercepat kueri, menjaga akurasi temporal melalui pemrosesan terjadwal, dan mengintegrasikan operasi spasial dengan konteks bisnis.

Jenis entitas kustom potensial

• Entitas operasional: Status operasional dan mode kerja spesifik perusahaan, pola shift dan pelacakan siklus tugas, metrik pemanfaatan aset, deteksi jendela pemeliharaan

• Entitas perilaku: Penilaian risiko kustom berdasarkan beberapa faktor, analisis dan klasifikasi pola berkendara, pemantauan kepatuhan dengan ambang batas yang dapat dikonfigurasi, agregasi indikator keselamatan

• Entitas kinerja: Metrik dan KPI spesifik industri, perhitungan efisiensi menggunakan rumus kustom, indikator optimasi sumber daya, pelacakan pencapaian tingkat layanan

• Entitas berbasis peristiwa: Deteksi peristiwa kustom dengan kondisi kompleks, agregasi dan pengenalan pola peringatan, identifikasi anomali menggunakan metode statistik, pelacakan pelanggaran ambang batas

Template konfigurasi

{
  "version": "1.0.0",
  "metadata": {
    "name": "<custom_entity_name>",
    "description": "<transformation_purpose_and_business_value>",
    "created_at": "<iso_8601_timestamp>",
    "created_by": "<developer_identifier>"
  },
  "sql_template": {
    "sql_file": "/opt/airflow/dags/repo/sql-file/<custom_entity>.sql",
    "table_name": "business_data.<custom_entity>",
    "parameters": {
      "<threshold_parameter>": <numeric_value>,
      "<time_window_parameter>": <numeric_value>,
      "<filter_enabled_parameter>": <boolean_value>
    }
  },
  "target": {
    "schema": "business_data",
    "table": "<custom_entity>"
  },
  "scheduler": {
    "cron_expression": "<cron_schedule>",
    "enabled": true,
    "backfill": {
      "backfill_allowed": true,
      "start_date": "<iso_8601_date>",
      "end_date": "<iso_8601_date>"
    }
  }
}

Panduan skrip SQL

Gunakan nilai yang diparameterkan:

WHERE value >= :custom_threshold
  AND duration_seconds <= :time_window_seconds
  AND (:quality_filter_enabled = false OR quality_score >= 0.8)

Manfaatkan jendela waktu standar:

FROM raw_telematics_data.tracking_data_core
WHERE device_time >= :window_start
  AND device_time < :window_end

Strukturkan pemrosesan dalam tahap:

WITH raw_data AS (
    -- Ekstrak dan saring data sumber
),
enriched_data AS (
    -- Gabungkan dengan data referensi dan hitung nilai antara
),
aggregated_entities AS (
    -- Agregasi ke dalam struktur entitas akhir
)
INSERT INTO business_data.custom_entity (...)
SELECT ... FROM aggregated_entities;

Sumber daya tambahan

Untuk pola kueri terperinci dan bekerja dengan data lapisan Silver, rujuk ke IoT Query SQL Recipe Book.

Jika Anda tertarik untuk akses awal atau memiliki pertanyaan tentang fungsionalitas ini, silakan hubungi [email protected].