132 lines
6.4 KiB
SQL
132 lines
6.4 KiB
SQL
CREATE DATABASE IF NOT EXISTS db_poverty_mapping;
|
|
USE db_poverty_mapping;
|
|
|
|
CREATE TABLE IF NOT EXISTS data_parsil (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama_parsil VARCHAR(200) NOT NULL,
|
|
fungsi_parsil VARCHAR(150) NOT NULL DEFAULT 'Pemukiman',
|
|
luas_m2 DECIMAL(12,2) NOT NULL DEFAULT 0.00,
|
|
geojson LONGTEXT NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP()
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
USE db_poverty_mapping;
|
|
|
|
-- 1. TABEL PENGGUNA
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama_lengkap VARCHAR(150) NOT NULL,
|
|
username VARCHAR(80) NOT NULL UNIQUE,
|
|
email VARCHAR(150) NOT NULL UNIQUE,
|
|
password VARCHAR(255) NOT NULL,
|
|
role ENUM('admin','relawan','pengurus','pemerintah') NOT NULL DEFAULT 'relawan',
|
|
foto VARCHAR(255) DEFAULT NULL,
|
|
aktif TINYINT(1) NOT NULL DEFAULT 1,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
last_login TIMESTAMP NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
-- Akun Default Admin (Password: admin123)
|
|
INSERT INTO users (nama_lengkap, username, email, password, role) VALUES
|
|
('Administrator', 'admin', 'admin@webgis.id', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin')
|
|
ON DUPLICATE KEY UPDATE id=id;
|
|
|
|
-- 2. TABEL RUMAH IBADAH & RUMAH MISKIN
|
|
CREATE TABLE IF NOT EXISTS rumah_ibadah (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama_ibadah VARCHAR(200) NOT NULL,
|
|
kategori ENUM('Masjid','Musholla','Gereja','Pura','Vihara') NOT NULL,
|
|
alamat VARCHAR(255) DEFAULT NULL,
|
|
nama_pengurus VARCHAR(150) DEFAULT NULL,
|
|
no_telp VARCHAR(25) DEFAULT NULL,
|
|
kuota_bantuan INT NOT NULL DEFAULT 0,
|
|
radius_m INT NOT NULL DEFAULT 300,
|
|
lat DECIMAL(10,8) NOT NULL,
|
|
lng DECIMAL(11,8) NOT NULL,
|
|
created_by INT DEFAULT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS rumah_miskin (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama_kepala_keluarga VARCHAR(150) NOT NULL,
|
|
nik VARCHAR(20) NOT NULL UNIQUE,
|
|
tanggal_lahir DATE DEFAULT NULL,
|
|
jumlah_tanggungan INT NOT NULL DEFAULT 0,
|
|
pendapatan_bulanan DECIMAL(12,2) NOT NULL DEFAULT 0.00,
|
|
kondisi_rumah ENUM('Layak','Tidak Layak','Sangat Tidak Layak') NOT NULL DEFAULT 'Tidak Layak',
|
|
lat DECIMAL(10,8) NOT NULL,
|
|
lng DECIMAL(11,8) NOT NULL,
|
|
prioritas TINYINT(1) NOT NULL DEFAULT 0,
|
|
status_bantuan ENUM('Blank Spot','Terjangkau') NOT NULL DEFAULT 'Blank Spot',
|
|
created_by INT DEFAULT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
-- 3. TABEL BENCANA & BANTUAN TERSALUR
|
|
CREATE TABLE IF NOT EXISTS bencana (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama_bencana VARCHAR(150) NOT NULL,
|
|
jenis_bencana VARCHAR(100) NOT NULL,
|
|
tanggal_kejadian DATE NOT NULL,
|
|
lokasi_terdampak TEXT NOT NULL,
|
|
keterangan TEXT DEFAULT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS bantuan_tersalur (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
warga_id INT NOT NULL,
|
|
ibadah_id INT DEFAULT NULL,
|
|
bencana_id INT DEFAULT NULL,
|
|
jenis_bantuan VARCHAR(150) NOT NULL,
|
|
jumlah VARCHAR(100) DEFAULT NULL,
|
|
tanggal DATE NOT NULL,
|
|
petugas VARCHAR(150) DEFAULT NULL,
|
|
keterangan TEXT DEFAULT NULL,
|
|
disalurkan_oleh INT DEFAULT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (warga_id) REFERENCES rumah_miskin(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (ibadah_id) REFERENCES rumah_ibadah(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (bencana_id) REFERENCES bencana(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (disalurkan_oleh) REFERENCES users(id) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
-- 4. TABEL DATA JALAN, DATA POINT & GEOMETRI SIGMA
|
|
CREATE TABLE IF NOT EXISTS data_jalan (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama_jalan VARCHAR(200) NOT NULL,
|
|
status_jalan ENUM('Jalan Nasional','Jalan Provinsi','Jalan Kabupaten') NOT NULL,
|
|
panjang_meter DECIMAL(12,2) NOT NULL DEFAULT 0.00,
|
|
geojson LONGTEXT NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP()
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS data_point (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama_fasilitas VARCHAR(200) NOT NULL,
|
|
jenis_fasilitas VARCHAR(100) NOT NULL,
|
|
geojson LONGTEXT NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP()
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS geometri_sigma (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama_objek VARCHAR(200) NOT NULL,
|
|
keterangan TEXT DEFAULT NULL,
|
|
apakah_terdampak ENUM('Ya','Tidak') NOT NULL DEFAULT 'Tidak',
|
|
tipe_geometri ENUM('marker','polyline','polygon') NOT NULL,
|
|
koordinat_json LONGTEXT NOT NULL,
|
|
warna_layer VARCHAR(50) NOT NULL DEFAULT 'blue',
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP()
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
-- INDEX OPTIMALISASI
|
|
CREATE INDEX idx_miskin_coords ON rumah_miskin (lat, lng);
|
|
CREATE INDEX idx_ibadah_coords ON rumah_ibadah (lat, lng); |