Optimalisasi Database Spasial menggunakan PostGIS untuk Analisis Gerak Penduduk dan Perencanaan Transportasi

Pengelolaan data gerak penduduk (mobility data) telah menjadi kebutuhan kritis bagi perencana kota, operator transportasi umum, dan peneliti kebijakan publik. Volume data yang besar, heterogenitas sumber (GPS, kartu tap-in/tap-out, CDR), serta kebutuhan analisis real‑time menuntun arsitektur database yang handal. Optimalisasi Database Spasial menggunakan PostGIS menawarkan fondasi yang kokoh untuk mengatasi tantangan tersebut melalui indeks spasial canggih, partisi tabel berbasis waktu, dan materialized view yang mempercepat agregasi jalur.

Mengapa PostGIS Cocok untuk Data Gerak Penduduk

PostGIS memperluas PostgreSQL dengan tipe data geometri dan raster, fungsi analisis spasial, serta dukungan indeks R‑Tree (GiST) dan SP‑GiST. Keuntungan utamanya meliputi:

• Integrasi SQL penuh – query spasial dapat digabungkan dengan analisis statistik, window functions, dan CTE tanpa keluar dari engine database.
• Skalabilitas horizontal – partisi tabel (range partitioning) memungkinkan pemisahan data per hari, minggu, atau zona administrasi.
• Ekstensi ekosistem – kompatibel dengan pgRouting untuk analisis jaringan jalan, TimescaleDB untuk deret waktu, dan Foreign Data Wrapper untuk mengakses data eksternal.

Arsitektur Data: Skema, Partisi, dan Indeks

1. Desain Skema Berbasis Kontrak

Gunakan skema terpisah untuk mentah (raw), bersih (clean), dan analitik (analytics). Tabel inti trips menyimpan kolom:

CREATE TABLE raw.trips (
    trip_id        BIGSERIAL PRIMARY KEY,
    user_id        BIGINT NOT NULL,
    start_ts       TIMESTAMPTZ NOT NULL,
    end_ts         TIMESTAMPTZ NOT NULL,
    geom_start     GEOGRAPHY(POINT,4326),
    geom_end       GEOGRAPHY(POINT,4326),
    mode           SMALLINT,          -- 1=walk,2=bike,3=transit,4=car
    source         TEXT               -- gps, smartcard, cdr
);

Kolom geom_start dan geom_end menggunakan tipe GEOGRAPHY agar perhitungan jarak otomatis dalam meter dan mendukung indeks esfir.

2. Partisi Temporal Otomatis

PostgreSQL 14+ mendukung partisi deklaratif. Partisi harian mengurangi ukuran indeks per partisi dan memungkinkan partition pruning saat query rentang waktu.

CREATE TABLE clean.trips (
    LIKE raw.trips INCLUDING ALL
) PARTITION BY RANGE (start_ts);

-- Contoh partisi harian
CREATE TABLE clean.trips_2024_01_01 PARTITION OF clean.trips
    FOR VALUES FROM ('2024-01-01') TO ('2024-01-02');
-- Ulangi untuk setiap hari atau gunakan pg_partman untuk otomatisasi.

3. Indeks Hibrida Spasial‑Temporal

Gabungkan indeks GiST pada kolom geografis dengan indeks B‑Tree pada timestamp:

CREATE INDEX idx_trips_geom_start_gist ON clean.trips USING GIST (geom_start);
CREATE INDEX idx_trips_start_ts_btree ON clean.trips (start_ts);
-- Indeks komposit untuk filter spatio‑temporal bersamaan
CREATE INDEX idx_trips_spatiotemporal ON clean.trips USING GIST (geom_start, start_ts);

Indeks komposit memungkinkan planner menggunakan index-only scan saat query memfilter area dan rentang waktu sekaligus.

Materialized View untuk Agregasi Cepat

Analisis pola pergerakan harian (origin‑destination matrix, heatmap kepadatan) sering dijalankan berulang. Materialized view menyimpan hasil agregasi dan direfresh secara terjadwal atau triggger berbasis pg_cron.

CREATE MATERIALIZED VIEW analytics.od_matrix_daily AS
SELECT
    date_trunc('day', start_ts)::date AS trip_date,
    ST_SnapToGrid(geom_start, 0.001) AS origin_grid,
    ST_SnapToGrid(geom_end, 0.001)   AS dest_grid,
    COUNT(*) AS trip_count,
    AVG(ST_Distance(geom_start, geom_end)) AS avg_distance_m
FROM clean.trips
GROUP BY trip_date, origin_grid, dest_grid;

CREATE UNIQUE INDEX idx_od_matrix_daily_pk ON analytics.od_matrix_daily (trip_date, origin_grid, dest_grid);

Refresh harian:

REFRESH MATERIALIZED VIEW CONCURRENTLY analytics.od_matrix_daily;

Optimasi Query Lanjutan

Window Functions untuk Deret Pergerakan

Menghitung kecepatan rata‑rata per pengguna dalam jendela waktu 15 menit:

SELECT
    user_id,
    start_ts,
    ST_Distance(geom_start, LAG(geom_end) OVER (PARTITION BY user_id ORDER BY start_ts)) / 
    EXTRACT(EPOCH FROM (start_ts - LAG(end_ts) OVER (PARTITION BY user_id ORDER BY start_ts))) AS speed_mps
FROM clean.trips
WHERE start_ts BETWEEN '2024-06-01' AND '2024-06-02';

Approximate Query Processing (AQP) dengan HyperLogLog

Untuk estimasi jumlah unik pengguna di area tertentu tanpa full scan, gunakan ekstensi postgresql-hll:

SELECT hll_cardinality(hll_add_agg(hll_hash_bigint(user_id)))
FROM clean.trips
WHERE geom_start && ST_MakeEnvelope(106.7, -6.3, 106.9, -6.1, 4326)
  AND start_ts BETWEEN '2024-06-01' AND '2024-06-07';

Integrasi dengan pgRouting dan WebGIS

Setelah matriks OD tersedia, pgRouting dapat menghitung rute optimal, alternatif transportasi publik, dan simulasi skenario penutupan jalan. Hasilnya disajikan melalui layanan OGC (WMS/WFS) atau API GeoJSON yang dikonsumsi oleh frontend Leaflet/Mapbox. Contoh endpoint FastAPI:

@app.get("/od/{date}")
async def get_od(date: str):
    sql = """SELECT origin_grid, dest_grid, trip_count
             FROM analytics.od_matrix_daily
             WHERE trip_date = $1"""
    rows = await pool.fetch(sql, date)
    return [dict(r) for r in rows]

[[internal-link:related-article]]

Keamanan Data Lokasi Sensitif

Data gerak penduduk mengandung informasi pribadi. Implementasikan Row Level Security (RLS) dan generalisasi geometri sebelum ekspor:

ALTER TABLE clean.trips ENABLE ROW LEVEL SECURITY;
CREATE POLICY analyst_access ON clean.trips
    FOR SELECT TO analyst_role
    USING (source = 'aggregated');

-- Generalisasi 100 meter untuk publik
CREATE VIEW public.trips_anonymized AS
SELECT
    trip_id,
    start_ts,
    ST_SnapToGrid(geom_start, 0.001) AS geom_start,
    ST_SnapToGrid(geom_end, 0.001)   AS geom_end,
    mode
FROM clean.trips;

Observabilitas dan SLO

Tetapkan Service Level Objective (SLO) latency p95 < 300 ms untuk query OD harian. Gunakan pg_stat_statements dan pg_stat_user_tables untuk memantau hit ratio indeks, ukuran partisi, dan frekuensi vacuum. Otomatiskan peringatan via Prometheus + Grafana.

Studi Kasus Singkat: Kota Bandung

Pemerintah Kota Bandung mengadopsi arsitektur di atas untuk 12 juta record per bulan. Hasilnya:

• Waktu query matriks OD turun dari 12 detik menjadi 0,4 detik (peningkatan 96 %).
• Ukuran indeks total dikurangi 38 % berkat partisi harian dan kompresi TOAST.
• Tim perencanaan transportasi dapat menjalankan simulasi skenario penutupan jalan dalam hitungan menit, mendukung keputusan kebijakan berbasis bukti.

Checklist Implementasi Praktis

1. Definisikan skema raw, clean, analytics dengan kontrak kolom yang jelas.
2. Aktifkan partisi temporal (harian/bulanan) dan gunakan pg_partman untuk otomatisasi.
3. Buat indeks GiST pada kolom geografis dan indeks komposit spatio‑temporal.
4. Bangun materialized view untuk agregasi OD, heatmap, dan statistik kecepatan.
5. Terapkan RLS, generalisasi, dan audit logging untuk kepatuhan privasi.
6. Pasang monitoring SLO, alerting, dan rencana pemulihan bencana (point‑in‑time recovery).

FAQ

Apakah partisi harian selalu optimal?

Tergantung volume harian. Jika < 500 ribu baris per hari, partisi mingguan bisa mengurangi overhead metadata.

Bagaimana cara menangani data GPS yang bersifat bruit?

Lakukan pembersihan di lapisan clean menggunakan ST_SnapToGrid, filter kecepatan tidak realistis, dan interpolasi jalur dengan ST_MakeLine setelah ordering by timestamp.

Apakah materialized view mendukung refresh inkremental?

PostgreSQL 15+ mendukung REFRESH MATERIALIZED VIEW CONCURRENTLY tetapi masih full refresh. Untuk inkremental, pertimbangkan trigger yang mengupdate tabel ringkasan atau gunakan TimescaleDB continuous aggregates.

Bagaimana mengintegrasikan data kartu tap-in/tap-out yang tidak memiliki koordinat?

Gabungkan dengan tabel master halte (berisi geometri) melalui JOIN pada stop_id sebelum memasukkan ke clean.trips.

Dengan mengikuti panduan ini, tim data spasial dapat membangun pipeline yang scalable, aman, dan siap mendukung analisis gerak penduduk serta perencanaan transportasi yang responsif. [[internal-link:related-article]]