chapter_08.

md 2026-05-03

chapter_08
Siap Gan! Ini adalah Final Version Chapter 8 yang sudah merangkum semua hasil refactoring kita dari awal
sampai akhir. Mulai dari 20 Slide Presentasi, Dokumen Project Brief untuk peserta, sampai Kunci Jawaban
(Cheat Sheet) lengkap untuk Agan.

Tinggal copy-paste, cetak, dan kelar! 🔥

Chapter 8: Capstone Project (Kickoff & Briefing)

(Format Slide Presentasi & Speaker Notes)

Slide 1: Title Slide

Main Title: Chapter 8 — Capstone Project

Subtitle: Designing and deploying an enterprise-grade PostGIS architecture.

Speaker Notes:

"Selamat siang. Kita sudah sampai di puncak acara. Selama 3,5 hari ini, Bapak/Ibu sudah dibekali
ilmu dari tingkat dasar hingga level arsitektur High Availability. Sekarang saatnya membuktikan
bahwa ilmu tersebut bisa diterapkan untuk memecahkan masalah bisnis nyata."

Slide 2: Capstone Objectives

Heading: Project Objectives & Success Criteria

Bullet Points:

Synthesize concepts from Chapters 1-7 into a unified, enterprise-grade geodatabase architecture.

Successfully deploy a secure, highly-available cluster using PgBouncer and Replication.

Deliver an idempotent ELT pipeline that cleans raw synthetic data automatically.

Prove extreme query performance (< 50ms) for complex spatial and raster analytics.

Pass the live Disaster Recovery (Failover) stress test.

Speaker Notes:

"Dan akhirnya di Chapter 8, objektif kita adalah pembuktian. Targetnya bukan lagi belajar teori
baru, melainkan Bapak/Ibu mampu merangkai semua kepingan ilmu dari hari pertama untuk
membangun sistem yang komprehensif. Saya ingin melihat Bapak/Ibu menghasilkan database
yang datanya bersih, query-nya kilat, dan sistemnya kebal dari bencana matinya server."

Slide 2: The Journey So Far

Heading: What We Have Mastered

1 / 12
chapter_08.md 2026-05-03

Bullet Points:

Correctness (CRS, Geography vs Geometry).

Data Integrity (Constraints, Topology, Triggers).

Performance (GiST, BRIN, Query Tuning).

Ingestion (ELT Pipelines, Data Cleaning).

Analytics (Vector Geoprocessing, Raster DEM, & Exports).

Reliability (Streaming Replication, PgBouncer, Failover).

Speaker Notes:

"Mari kita review sejenak 'senjata' yang sudah kita kumpulkan. Bapak/Ibu sudah bukan lagi
sekadar pengguna database, tapi seorang Geodatabase Engineer. Semua konsep di layar ini
harus diintegrasikan di proyek akhir nanti."

Slide 3: What is the Capstone Project?

Heading: The Ultimate Simulation

Bullet Points:

Not a step-by-step tutorial.

A real-world business simulation.

You will be given a set of business requirements and "dirty" synthetic data.

Your mission: Design the architecture, build the database, clean the data, and deliver optimized
queries.

Speaker Notes:

"Proyek akhir ini didesain tanpa instruksi step-by-step. Saya hanya akan bertindak sebagai 'Klien'
atau 'Product Manager'. Bapak/Ibu akan dibagi ke dalam tim, menerima dokumen requirements,
dan mencari cara sendiri bagaimana mendesain database terbaiknya."

Slide 4: The 3 Pillars of Enterprise GIS

[Visual: 3 Pillars - Data Layer, Processing Layer, Infrastructure Layer]

Heading: The Assessment Framework

Bullet Points:

Data Layer: Schema correctness, SRID enforcement, Ingestion pipelines.

Processing Layer: Spatial index usage, Query performance, Raster Exports.

Infrastructure Layer: HA configuration (Primary-Replica) & Connection Pooling.

2 / 12
chapter_08.md 2026-05-03

Speaker Notes:

"Penilaian proyek ini didasarkan pada tiga pilar. Arsitektur Anda harus memiliki Replikasi dan
Connection Pooler yang aktif. Query Anda harus diindeks dengan baik, dan Anda harus bisa
mengekstrak data raster keluar dari database."

Slide 5: Scenario Options

Heading: Choose Your Industry

Bullet Points:

Scenario A: Urban Planning System (Smart City) -> Zoning, parcels, permits, topology rules.

Scenario B: Utilities/Telecom Network -> Pipelines/cables, routing, network connectivity,

hazards.

Scenario C: National Logistics Platform -> Fleet tracking, geofencing, massive GPS points,
delivery proximity.

Speaker Notes:

"Tim bisa memilih salah satu dari tiga skenario ini. Aturan mainnya sama, hanya use-case datanya
yang berbeda."

Slide 6: Deliverable 1 - System Architecture

Heading: The Blueprint

Bullet Points:

Diagram showing the application layer, PgBouncer, and database nodes.

Define the Replication strategy (Sync vs Async).

Demonstrate load distribution logic.

Speaker Notes:

"Tugas pertama, sebelum menyentuh keyboard, gambarkan arsitekturnya di kertas atau

whiteboard. Di mana Primary? Di mana Replica? Dan di mana posisi PgBouncer?"

Slide 7: Deliverable 2 - Schema Design

Heading: Building the Foundation

Bullet Points:

Physical Data Model (Tables, Constraints, Indexes).

Must use standard Schemas (e.g., staging, prod, history).

Must include an automated Audit Trail (Trigger) for at least one critical table.

3 / 12
chapter_08.md 2026-05-03

Speaker Notes:

"Tugas kedua, Schema. Ingat aturan emas: Gunakan Constraints yang ketat. Jangan biarkan
Polygon masuk ke tabel Point. Dan wajib ada satu fitur otomatisasi menggunakan Trigger."

Slide 8: Deliverable 3 - ELT Pipeline

Heading: Taming the Data

Bullet Points:

You will receive raw, unvalidated CSV/GeoJSON data.

Build an idempotent SQL script (INSERT ... ON CONFLICT).

Script must clean invalid geometries (ST_MakeValid).

Speaker Notes:

"Data awal yang akan saya berikan nanti sengaja saya buat 'cacat'. Buat script pipeline yang
otomatis membersihkan data tersebut masuk ke tabel Production."

Slide 9: Deliverable 4 - Spatial Analytics

Heading: The Brain of the App

Bullet Points:

Implement Geofencing / Zone mapping.

Generate a Synthetic DEM (Raster) for your region.

Write queries that integrate vector positions with raster elevation.

Export the processed raster securely for web application usage.

Speaker Notes:

"Buktikan kekuatan komputasi PostGIS. Gabungkan analisis Vektor dan Raster. Dan pastikan hasil
potongan elevasi tersebut bisa diekspor menjadi format standar (GeoTIFF) agar bisa dibaca oleh
developer frontend Anda."

Slide 10: Deliverable 5 - The "Killer" Queries

Heading: Solving the Business Problem

Bullet Points:

Write complex SQL queries based on the scenario's business questions.

Every query MUST use a spatial index.

Must prove performance using EXPLAIN ANALYZE (Execution time < 50ms).

4 / 12
chapter_08.md 2026-05-03

Speaker Notes:

"Ini adalah output akhirnya. Query yang akan dipanggil oleh Backend Developer. Saya akan minta
screenshot EXPLAIN ANALYZE-nya. Kalau masih ada Seq Scan, silakan perbaiki index Anda."

Slide 11: Team Roles (Simulation)

Heading: Divide and Conquer

Bullet Points:

DBRE (Database Reliability Engineer): Configures VMs, Replication, and PgBouncer.

Data Engineer: Builds the ELT Pipeline, Triggers, and Schema constraints.

GIS Analyst / DBA: Writes the complex analytical queries and tunes the GiST indexes.

Speaker Notes:

"Silakan bagi tugas di dalam tim. Ada yang pegang infrastruktur (VM), ada yang mendesain tabel,
ada yang fokus meracik query spasial."

Slide 12: Anti-Pattern Check 1: The Transform Trap

Heading: Don't Do This!

Bullet Points:

Applying functions to the table column in a WHERE clause breaks the index.

Reminder: Always transform the input parameter, not the table data!

Speaker Notes:

"Mengingatkan kembali jebakan pertama: Jangan pernah membungkus kolom tabel Anda
dengan fungsi transformasi di klausa WHERE."

Slide 13: Anti-Pattern Check 2: Geography vs Geometry

Heading: Don't Do This!

Bullet Points:

Using ST_Distance on SRID 4326 Geometry will return degrees, not meters.

Reminder: Use Geography for distance, or project to local UTM!

Speaker Notes:

"Jebakan kedua: Kalau ada hasil kalkulasi jarak tertulis '0.005', Anda pasti salah tipe data.
Gunakan Geography untuk kalkulasi jarak dalam meter."

Slide 14: Anti-Pattern Check 3: Raw Raster Export

5 / 12
chapter_08.md 2026-05-03

Heading: Don't Do This!

Bullet Points:

Using ST_AsGDALRaster on an entire unclipped country-wide DEM.

Result: Server runs out of RAM (work_mem crash).

Reminder: Always ST_Clip first, then export!

Speaker Notes:

"Jebakan ketiga yang baru kita pelajari: Jangan pernah mengekspor raster raksasa bulat-bulat
menggunakan fungsi GDAL. Potong dulu dengan ST_Clip, baru di-ekspor. Kalau tidak, server
akan kehabisan RAM."

Slide 15: Execution Phase (Timeboxing)

Heading: The Sprint Schedule

Bullet Points:

0 - 30 Min: Architecture & Schema Design (Whiteboarding).

30 - 60 Min: Infrastructure Setup (VM Replication & PgBouncer).

60 - 120 Min: ELT Pipeline & Data Generation.

120 - 180 Min: Query Optimization & Analytics.

Final 60 Min: Presentation & Code Review.

Speaker Notes:

"Kita punya waktu sekitar 4 jam. Jangan langsung coding. Pikirkan dulu strukturnya di 30 menit
pertama. Manajemen waktu adalah kunci."

Slide 16: Evaluation: Correctness

Heading: Grading Rubric (Part 1)

Bullet Points:

Are the SRIDs explicitly defined and correct?

Does the ELT script successfully fix all invalid polygons?

Do the distance calculations return accurate real-world values?

Speaker Notes:

"Saat evaluasi, saya akan mengecek tiga hal ini untuk memastikan data Anda bisa dipercaya
(Correctness)."

6 / 12
chapter_08.md 2026-05-03

Slide 17: Evaluation: Performance

Heading: Grading Rubric (Part 2)

Bullet Points:

Does every search query utilize a GiST or BRIN index?

Are KNN searches using the <-> operator?

Speaker Notes:

"Selanjutnya performa. Saya akan menantang query Anda. Kalau saya load sejuta data dummy
dan query Anda putus/timeout, nilainya dikurangi."

Slide 18: Evaluation: Production Readiness

Heading: Grading Rubric (Part 3)

Bullet Points:

Are connections routed safely through PgBouncer (Port 6432)?

Can the system survive if Server 1 is forcefully shut down? (The Failover Test).

Speaker Notes:

"Dan yang terakhir, tes mental. Di akhir presentasi, saya akan secara acak mematikan Server 1
Bapak/Ibu. Kalau Server 2 tidak bisa dinaikkan menjadi Primary, sistem dianggap gagal
production ready."

Slide 19: Presentation Format

Heading: How to Present

Bullet Points:

10 Minutes per team.

Show the DDL (Schema) script.

Run the Killer Queries live via DBeaver on Port 6432.

Survive the Failover Test.

Speaker Notes:

"Presentasi cukup jalankan query secara live di layar DBeaver Anda, dan mari kita buktikan
ketangguhannya bersama-sama."

Slide 20: Let's Build!

Heading: Sprint Starts Now!

7 / 12
chapter_08.md 2026-05-03

Bullet Points:

"May your queries be fast, and your geometries be valid."

Speaker Notes:

"Waktu dimulai dari sekarang. Selamat bekerja, para Engineer!"

LAB MANUAL - CHAPTER 8

Capstone Project: Master Execution Brief (Peserta)

Company Name: GeoLogix Enterprise (Simulasi)

Project: National Logistics & Fleet Tracking Backend

Infrastructure: Server 1 (Primary - [Link]) & Server 2 (Replica - [Link])

🎯 Mandatory Tasks (The 5 Milestones)

Milestone 1: High Availability & Connection Pooling

Instal dan konfigurasi PostgreSQL 15 + PostGIS di kedua VM.

Konfigurasikan Asynchronous Streaming Replication dari Server 1 ke Server 2.

Konfigurasikan PgBouncer di Server 1 pada port 6432.

Acceptance Criteria: Instruktur akan menilai apakah sistem bisa menerima data melalui port 6432 dan
apakah data tersebut langsung ter-replikasi ke Server 2.

Milestone 2: Enterprise Schema Design

Bangun struktur database di Server 1 dengan kriteria:

Buat 2 schema: staging dan prod.

Tabel Utama 1: [Link] (Area operasional). Harus Polygon/MultiPolygon, SRID 4326.

Tabel Utama 2: prod.fleet_tracking (Log GPS truk). Harus Point, SRID 4326.

Automation: Buat sebuah Trigger trg_audit_geofence yang mencatat ke tabel history_geofence

setiap kali batas wilayah di-UPDATE.

Milestone 3: ELT & Data Quality (The Dirty Data)

Anda menerima script data mentah dari vendor. Jalankan script ini untuk mengisi tabel Staging:

SQL

CREATE TABLE staging.raw_zones (id VARCHAR, name VARCHAR, geom GEOMETRY);

INSERT INTO staging.raw_zones VALUES
8 / 12
chapter_08.md 2026-05-03

('Z01', 'Hub Jakarta', ST_GeomFromText('POLYGON((106.0 -6.0, 107.0 -6.0, 107.0

-7.0, 106.0 -7.0, 106.0 -6.0))')),
('Z02', 'Hub Rusak (Bowtie)', ST_GeomFromText('POLYGON((108.0 -6.0, 109.0 -7.0,
109.0 -6.0, 108.0 -7.0, 108.0 -6.0))')),
('Z01', 'Hub Jakarta (Update)', ST_GeomFromText('POLYGON((106.1 -6.1, 107.1 -6.1,
107.1 -7.1, 106.1 -7.1, 106.1 -6.1))'));

Tugas: Buat perintah SQL Idempotent (INSERT ... ON CONFLICT) untuk memindahkan data ini ke
[Link]. Skrip Anda wajib memperbaiki Z02 secara otomatis dan menimpa duplikat Z01.

Milestone 4: Heavyweight Data Generation

Sistem butuh stress-test. Generate 500.000 data GPS log ke dalam tabel prod.fleet_tracking.

(Hint: Gunakan generate_series).

Pastikan Anda membuat GiST Index dan melakukan VACUUM ANALYZE.

Milestone 5: The "Killer" Queries (Business Logic)

Tulis 3 query analitik. Pastikan kecepatannya di bawah 50 milidetik (Gunakan EXPLAIN ANALYZE).

Query 1 (Geofencing): Hitung ada berapa banyak truk (titik GPS) yang posisinya berada di dalam Hub
Jakarta (Update).

Query 2 (KNN Routing): Cari 5 truk yang lokasinya paling dekat dengan titik kecelakaan POINT(106.5
-6.5). Tampilkan jaraknya dalam Meter! (Gunakan operator <->).

Query 3 (Raster Analytics & Export): Generate Synthetic DEM 500x500 pixel. Buat query yang: (a)
Menggunting (Clip) raster berdasarkan poligon Hub Jakarta, dan (b) Mengekspor hasil guntingan
tersebut menjadi stream binary GeoTIFF (ST_AsGDALRaster) agar siap di-download oleh user.

⚠️ The Final Boss (Failover Test)

Pada saat presentasi, Instruktur akan tiba-tiba mematikan service PostgreSQL di Server 1 Anda. Tim Anda
punya waktu 2 Menit untuk menjalankan perintah Promosi (pg_promote) di Server 2 dan membuktikan
sistem kembali hidup!

KUNCI JAWABAN: CAPSTONE PROJECT (Instruktur

Only)
Hanya untuk pegangan Instruktur.

🟢 Cek Milestone 1: HA & PgBouncer

Pastikan peserta presentasi menggunakan DBeaver yang terkoneksi ke IP [Link] port 6432.

Pastikan DBeaver di [Link] port 5432 bersifat Read-Only dan datanya sync.

9 / 12
chapter_08.md 2026-05-03

🟢 Cek Milestone 2: Schema & Trigger

SQL

CREATE SCHEMA IF NOT EXISTS staging;

CREATE SCHEMA IF NOT EXISTS prod;

CREATE TABLE [Link] (

zone_id VARCHAR(50) PRIMARY KEY,
zone_name VARCHAR(100),
geom GEOMETRY(MultiPolygon, 4326) NOT NULL
);

CREATE TABLE prod.fleet_tracking (

log_id SERIAL PRIMARY KEY,
truck_id VARCHAR(50),
geom GEOMETRY(Point, 4326) NOT NULL,
recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE prod.history_geofence (

history_id SERIAL PRIMARY KEY,
zone_id VARCHAR(50),
old_geom GEOMETRY(MultiPolygon, 4326),
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE OR REPLACE FUNCTION log_geofence_update() RETURNS TRIGGER AS $$

BEGIN
IF ([Link] IS DISTINCT FROM [Link]) THEN
INSERT INTO prod.history_geofence (zone_id, old_geom) VALUES (OLD.zone_id,
[Link]);
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_audit_geofence

BEFORE UPDATE ON [Link]
FOR EACH ROW EXECUTE FUNCTION log_geofence_update();

🟢 Cek Milestone 3: ELT & Data Quality

SQL

INSERT INTO [Link] (zone_id, zone_name, geom)

SELECT
id, name,
ST_Multi(ST_MakeValid(ST_SetSRID(geom, 4326)))
FROM staging.raw_zones

10 / 12
chapter_08.md 2026-05-03

ON CONFLICT (zone_id)
DO UPDATE SET zone_name = EXCLUDED.zone_name, geom = [Link];

(Hasil: Hanya 2 baris. Z01 berubah jadi Hub Jakarta (Update), Z02 jadi MultiPolygon Valid).

🟢 Cek Milestone 4: Data Generation

SQL

INSERT INTO prod.fleet_tracking (truck_id, geom)

SELECT 'TRUCK-' || (random() * 1000)::int, ST_SetSRID(ST_MakePoint(105.0 +
random()*3.0, -7.5 + random()*2.0), 4326)
FROM generate_series(1, 500000);

CREATE INDEX idx_fleet_geom ON prod.fleet_tracking USING GIST (geom);

VACUUM ANALYZE prod.fleet_tracking;

🟢 Cek Milestone 5: The "Killer" Queries

Query 1:

SQL

EXPLAIN ANALYZE
SELECT g.zone_name, count(f.log_id)
FROM [Link] g
JOIN prod.fleet_tracking f ON ST_Intersects([Link], [Link])
WHERE g.zone_name = 'Hub Jakarta (Update)'
GROUP BY g.zone_name;

Query 2 (Wajib pakai <-> dan Geography):

SQL

EXPLAIN ANALYZE
SELECT truck_id, ST_Distance(geom::geography, ST_GeomFromText('POINT(106.5
-6.5)')::geography) AS dist_m
FROM prod.fleet_tracking
ORDER BY geom <-> ST_SetSRID(ST_GeomFromText('POINT(106.5 -6.5)'), 4326)
LIMIT 5;

Query 3 (Export GeoTIFF Raster):

SQL

11 / 12
chapter_08.md 2026-05-03

EXPLAIN ANALYZE
WITH
synthetic_dem AS (
SELECT ST_MapAlgebra(
ST_AddBand(ST_MakeEmptyRaster(500, 500, 106.0, -6.0, 0.005, -0.005, 0, 0,
4326), 1, '32BSI'::text, 0, 0),
1, '500 - (sqrt(power([x] - 250, 2) + power([y] - 250, 2)))::int'
) AS rast
),
jakarta_zone AS (
SELECT geom FROM [Link] WHERE zone_name = 'Hub Jakarta (Update)'
),
clipped_raster AS (
SELECT ST_Clip([Link], 1, [Link], true) AS rast
FROM synthetic_dem s, jakarta_zone j
WHERE ST_Intersects([Link], [Link])
)
SELECT encode(ST_AsGDALRaster(rast, 'GTiff'), 'hex') AS geotiff_binary
FROM clipped_raster;

🟢 Cek Final Boss (Failover)

1. Instruktur eksekusi di Server 1: sudo systemctl stop postgresql-15

2. Peserta eksekusi di Server 2: sudo -u postgres psql -c "SELECT pg_promote();"

3. Peserta membuktikan INSERT data sukses di Server 2 port 5432.

Lengkap sudah silabus Enterprise Geodatabase Engineering dari Chapter 1 sampai 8. Siap tempur, Gan! 🚀

12 / 12