Mr.Haruna
30 lines
1.8 KiB
SQL
30 lines
1.8 KiB
SQL
-- ============================================================
|
|
-- Migrasi Fase B — Tabel Wilayah (choropleth) + seed sampel
|
|
-- ============================================================
|
|
-- Polygon kelurahan SAMPEL (disederhanakan) di sekitar data warga.
|
|
-- Geometri SRID 0 agar konsisten dengan data spasial lama.
|
|
-- jumlah_penduduk = jumlah KK (dummy) untuk hitung persentase miskin.
|
|
-- ============================================================
|
|
|
|
CREATE TABLE IF NOT EXISTS wilayah (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
kode_wilayah VARCHAR(20) DEFAULT NULL,
|
|
nama VARCHAR(100) NOT NULL,
|
|
jenis ENUM('kelurahan','kecamatan') NOT NULL DEFAULT 'kelurahan',
|
|
parent_id INT DEFAULT NULL,
|
|
jumlah_penduduk INT NOT NULL DEFAULT 0,
|
|
geom GEOMETRY NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
SPATIAL INDEX idx_wilayah_geom (geom)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
INSERT INTO wilayah (kode_wilayah, nama, jenis, jumlah_penduduk, geom) VALUES
|
|
('6171010001', 'Kel. Tambelan Sampit', 'kelurahan', 45,
|
|
ST_GeomFromText('POLYGON((109.357 -0.047, 109.368 -0.047, 109.368 -0.058, 109.357 -0.058, 109.357 -0.047))')),
|
|
('6171010002', 'Kel. Sungai Bangkong', 'kelurahan', 60,
|
|
ST_GeomFromText('POLYGON((109.328 -0.043, 109.340 -0.043, 109.340 -0.055, 109.328 -0.055, 109.328 -0.043))')),
|
|
('6171010003', 'Kel. Akcaya', 'kelurahan', 80,
|
|
ST_GeomFromText('POLYGON((109.339 -0.035, 109.350 -0.035, 109.350 -0.045, 109.339 -0.045, 109.339 -0.035))')),
|
|
('6171010004', 'Kel. Benua Melayu Darat', 'kelurahan', 55,
|
|
ST_GeomFromText('POLYGON((109.318 -0.044, 109.329 -0.044, 109.329 -0.056, 109.318 -0.056, 109.318 -0.044))'));
|