89 lines
4.1 KiB
SQL
89 lines
4.1 KiB
SQL
-- ============================================================
|
||
-- DATABASE: webgis_p3
|
||
-- Pertemuan 3 – Layer Groups and Layers Control
|
||
-- Data SPBU dengan 2 Layer Group: Buka 24 Jam & Tidak 24 Jam
|
||
-- ============================================================
|
||
|
||
CREATE DATABASE IF NOT EXISTS webgis_p3
|
||
CHARACTER SET utf8mb4
|
||
COLLATE utf8mb4_unicode_ci;
|
||
|
||
USE webgis_p3;
|
||
|
||
-- ---- Tabel Data Jalan (Polyline) ----
|
||
CREATE TABLE IF NOT EXISTS data_jalan (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
nama_jalan VARCHAR(200) NOT NULL,
|
||
status ENUM('Nasional','Provinsi','Kabupaten') NOT NULL DEFAULT 'Kabupaten',
|
||
panjang_m DOUBLE NOT NULL DEFAULT 0,
|
||
geojson LONGTEXT NOT NULL,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ---- Tabel Data Parsil Tanah (Polygon) ----
|
||
CREATE TABLE IF NOT EXISTS data_parsil (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
no_parsil VARCHAR(100) NOT NULL,
|
||
pemilik VARCHAR(200) NOT NULL,
|
||
status ENUM('SHM','HGB','HGU','HP') NOT NULL DEFAULT 'SHM',
|
||
luas_m2 DOUBLE NOT NULL DEFAULT 0,
|
||
geojson LONGTEXT NOT NULL,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ---- Tabel Data SPBU (Point/Marker) ----
|
||
-- buka_24_jam: 'Ya' → Layer Group "SPBU 24 Jam"
|
||
-- 'Tidak' → Layer Group "SPBU Tidak 24 Jam"
|
||
CREATE TABLE IF NOT EXISTS data_spbu (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
nama_spbu VARCHAR(200) NOT NULL,
|
||
no_wa VARCHAR(20) NOT NULL,
|
||
buka_24_jam ENUM('Ya','Tidak') NOT NULL DEFAULT 'Tidak',
|
||
latitude DOUBLE NOT NULL,
|
||
longitude DOUBLE NOT NULL,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ---- Data Contoh SPBU (opsional, untuk testing) ----
|
||
INSERT INTO data_spbu (nama_spbu, no_wa, buka_24_jam, latitude, longitude) VALUES
|
||
('SPBU Jl. Ahmad Yani', '0812-1111-0001', 'Ya', -0.0400, 109.3200),
|
||
('SPBU Jl. Gajah Mada', '0812-1111-0002', 'Ya', -0.0520, 109.3450),
|
||
('SPBU Jl. Diponegoro', '0812-1111-0003', 'Tidak', -0.0610, 109.3600),
|
||
('SPBU Jl. Sutan Syahrir', '0812-1111-0004', 'Tidak', -0.0480, 109.3700),
|
||
('SPBU Jl. Kom. Yos Sudarso', '0812-1111-0005', 'Ya', -0.0350, 109.3550);
|
||
|
||
-- ============================================================
|
||
-- Tabel Data Kemiskinan (Point/Marker)
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS data_kemiskinan (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
nama_kk VARCHAR(200) NOT NULL, -- Nama kepala keluarga
|
||
jumlah_kk INT NOT NULL DEFAULT 1, -- Jumlah anggota dalam KK
|
||
alamat TEXT NOT NULL,
|
||
latitude DOUBLE NOT NULL,
|
||
longitude DOUBLE NOT NULL,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- ============================================================
|
||
-- Tabel Data Rumah Ibadah (Point/Marker + Radius Buffer)
|
||
-- jenis: Masjid, Gereja, Pura, Vihara, Klenteng
|
||
-- radius_m: radius lingkaran buffer dalam meter (100 – 5000)
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS data_masjid (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
nama_masjid VARCHAR(200) NOT NULL,
|
||
jenis ENUM('Masjid','Gereja','Pura','Vihara','Klenteng') NOT NULL DEFAULT 'Masjid',
|
||
pic_masjid VARCHAR(200) NOT NULL, -- Person In Charge
|
||
alamat TEXT NOT NULL,
|
||
radius_m INT NOT NULL DEFAULT 500, -- Radius buffer (100-5000 m)
|
||
latitude DOUBLE NOT NULL,
|
||
longitude DOUBLE NOT NULL,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|