chapter_03_lab.
md 2026-05-01
chapter_03_lab
LAB MANUAL - CHAPTER 3
Advanced Data Modeling & Integrity (Smart City Case Study)
Lab Objective: Peserta akan membangun skema database infrastruktur cerdas yang terdiri dari tabel aset
(Points) dan tabel wilayah (Polygons), menerapkan constraints spasial, dan membuat sistem history otomatis
menggunakan Triggers.
Step 1: Schema Design with Strict Constraints
Kita akan membuat dua tabel: districts (Wilayah) dan sensors (Aset). Kita akan mengunci agar tabel
wilayah hanya bisa menerima Poligon dan tabel sensor hanya menerima Titik.
1. Jalankan DDL berikut di DBeaver:
SQL
-- Tabel Wilayah (Distrik)
CREATE TABLE districts (
id SERIAL PRIMARY KEY,
district_name VARCHAR(100) NOT NULL,
geom GEOMETRY(MultiPolygon, 4326) NOT NULL
);
-- Tabel Sensor (Aset)
CREATE TABLE sensors (
id SERIAL PRIMARY KEY,
sensor_type VARCHAR(50),
district_id INT REFERENCES districts(id), -- Relational Constraint
geom GEOMETRY(Point, 4326) NOT NULL -- Strict Constraint: Harus Point &
SRID 4326
);
Step 2: Testing Constraint Enforcement (Anti-Error Check)
Mari kita buktikan bahwa database akan menolak jika kita mencoba memasukkan data yang salah tipe.
1. Test 1: Masukkan data LineString ke tabel Point (Akan FAIL):
SQL
INSERT INTO sensors (sensor_type, geom)
1/4
chapter_03_lab.md 2026-05-01
VALUES ('CCTV', ST_GeomFromText('LINESTRING(106.8 -6.1, 106.9 -6.2)', 4326));
❌ Expected Error: "Column has geometry type Point but Point but geometry column has type
LineString".
Step 3: Advanced Geometry Cleaning (The "Repair" Workflow)
Dalam dunia nyata, kita sering menerima data kotor dari vendor. Mari kita simulasi cara membersihkannya.
1. Masukkan data poligon yang "rusak" (Self-Intersecting/Bentuk Bowtie):
SQL
CREATE TABLE raw_data_land (
id SERIAL PRIMARY KEY,
owner_name VARCHAR(100),
geom GEOMETRY
);
INSERT INTO raw_data_land (owner_name, geom)
VALUES ('Bapak Budi', ST_GeomFromText('POLYGON((0 0, 10 10, 10 0, 0 10, 0
0))'));
2. Cek validitasnya:
SQL
SELECT owner_name, ST_IsValid(geom), ST_IsValidReason(geom)
FROM raw_data_land;
3. Perbaiki dan pindahkan ke tabel bersih:
SQL
INSERT INTO districts (district_name, geom)
SELECT owner_name, ST_Multi(ST_MakeValid(geom)) -- Gunakan ST_Multi karena
perbaikan bisa menghasilkan banyak poligon
FROM raw_data_land
WHERE owner_name = 'Bapak Budi';
Step 4: Building an Automated Spatial Audit Trail
Ini adalah porsi beratnya. Kita ingin setiap kali lokasi sensor dipindahkan (UPDATE), koordinat lamanya dicatat
otomatis ke tabel sensor_history.
2/4
chapter_03_lab.md 2026-05-01
1. Buat tabel History:
SQL
CREATE TABLE sensor_history (
history_id SERIAL PRIMARY KEY,
sensor_id INT,
old_geom GEOMETRY(Point, 4326),
change_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
2. Buat Fungsi Trigger:
SQL
CREATE OR REPLACE FUNCTION log_sensor_movement()
RETURNS TRIGGER AS $$
BEGIN
IF ([Link] IS DISTINCT FROM [Link]) THEN
INSERT INTO sensor_history (sensor_id, old_geom)
VALUES ([Link], [Link]);
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
3. Pasang Trigger ke Tabel Sensors:
SQL
CREATE TRIGGER trg_sensor_movement
BEFORE UPDATE ON sensors
FOR EACH ROW
EXECUTE FUNCTION log_sensor_movement();
Step 5: Final Test Project (Integration)
Sekarang, mari kita jalankan skenario lengkap.
1. Insert Distrik:
SQL
INSERT INTO districts (district_name, geom)
VALUES ('Kebayoran', ST_Multi(ST_GeomFromText('POLYGON((106.7 6.2, 106.8 6.2,
3/4
chapter_03_lab.md 2026-05-01
106.8 6.3, 106.7 6.3, 106.7 6.2))', 4326)));
2. Insert Sensor:
SQL
INSERT INTO sensors (sensor_type, district_id, geom)
VALUES ( 'AQMS',
-- Ambil ID secara dinamis menggunakan Subquery
(SELECT id FROM districts WHERE district_name = 'Kebayoran' LIMIT 1),
ST_GeomFromText('POINT(106.75 6.25)', 4326)
);
3. Update Lokasi Sensor (Pindah):
SQL
UPDATE sensors SET geom = ST_GeomFromText('POINT(106.76 6.26)', 4326)
WHERE sensor_type = 'AQMS';
4. Cek History:
SQL
SELECT * FROM sensor_history;
✅ Expected Output: Anda akan melihat record baru yang berisi koordinat lama sensor sebelum
dipindahkan.
[End of Lab 3]
4/4