106 lines
5.3 KiB
SQL
106 lines
5.3 KiB
SQL
-- ============================================================
|
|
-- WebGIS Database Schema
|
|
-- Supports: Points (SPBU, Mosque, Poor Population),
|
|
-- Lines (Roads), Polygons (Land Parcels)
|
|
-- ============================================================
|
|
|
|
-- CREATE DATABASE IF NOT EXISTS webgis_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
-- USE webgis_db;
|
|
|
|
-- ------------------------------------------------------------
|
|
-- TABLE: points
|
|
-- Stores all point-type features (SPBU, Mosque, Poor Population)
|
|
-- ------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS points (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
category ENUM('spbu','mosque','poor') NOT NULL COMMENT 'spbu=Gas Station, mosque=Mosque, poor=Poor Population',
|
|
subtype VARCHAR(100) DEFAULT NULL COMMENT 'For SPBU: 24hours or not24hours',
|
|
latitude DECIMAL(10,8) NOT NULL,
|
|
longitude DECIMAL(11,8) NOT NULL,
|
|
description TEXT DEFAULT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
tanggal_lahir DATE DEFAULT NULL,
|
|
pendidikan VARCHAR(100) DEFAULT NULL,
|
|
pekerjaan VARCHAR(100) DEFAULT NULL,
|
|
jumlah_tanggungan INT DEFAULT NULL,
|
|
riwayat_penyakit TEXT DEFAULT NULL,
|
|
alamat TEXT DEFAULT NULL,
|
|
status_verifikasi ENUM('Menunggu Verifikasi','Layak','Tidak Layak','Perlu Verifikasi Ulang') DEFAULT 'Menunggu Verifikasi'
|
|
) ENGINE=InnoDB;
|
|
|
|
-- ------------------------------------------------------------
|
|
-- TABLE: laporan_masyarakat
|
|
-- Stores reports submitted by citizens about poor populations
|
|
-- ------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS laporan_masyarakat (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama_pelapor VARCHAR(100) NOT NULL,
|
|
no_hp VARCHAR(20) DEFAULT NULL,
|
|
nama_warga VARCHAR(100) NOT NULL,
|
|
alamat TEXT DEFAULT NULL,
|
|
latitude DECIMAL(10,8) DEFAULT NULL,
|
|
longitude DECIMAL(11,8) DEFAULT NULL,
|
|
keterangan TEXT DEFAULT NULL,
|
|
status ENUM('Menunggu Verifikasi','Disetujui','Ditolak') DEFAULT 'Menunggu Verifikasi',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB;
|
|
|
|
-- ------------------------------------------------------------
|
|
-- TABLE: bantuan
|
|
-- Stores aid distribution records for poor populations
|
|
-- ------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS bantuan (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
point_id INT NOT NULL,
|
|
jenis_bantuan VARCHAR(100) NOT NULL,
|
|
nominal DECIMAL(12,2) DEFAULT NULL,
|
|
tanggal_bantuan DATE DEFAULT NULL,
|
|
instansi_pemberi VARCHAR(100) DEFAULT NULL,
|
|
keterangan TEXT DEFAULT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (point_id) REFERENCES points (id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB;
|
|
|
|
-- ------------------------------------------------------------
|
|
-- TABLE: roads
|
|
-- Stores road polylines with GeoJSON geometry
|
|
-- ------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS roads (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
road_type ENUM('national','provincial','district') NOT NULL,
|
|
length_m DECIMAL(12,2) NOT NULL DEFAULT 0 COMMENT 'Length in meters, auto-calculated by frontend',
|
|
geojson LONGTEXT NOT NULL COMMENT 'GeoJSON LineString geometry',
|
|
description TEXT DEFAULT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB;
|
|
|
|
-- ------------------------------------------------------------
|
|
-- TABLE: parcels
|
|
-- Stores land parcel polygons with GeoJSON geometry
|
|
-- ------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS parcels (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
owner_name VARCHAR(255) NOT NULL,
|
|
ownership_type ENUM('SHM','HGB','HGU','HP') NOT NULL,
|
|
area_m2 DECIMAL(15,4) NOT NULL DEFAULT 0 COMMENT 'Area in square meters, auto-calculated by frontend',
|
|
geojson LONGTEXT NOT NULL COMMENT 'GeoJSON Polygon geometry',
|
|
description TEXT DEFAULT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB;
|
|
|
|
-- ------------------------------------------------------------
|
|
-- Sample seed data (optional)
|
|
-- ------------------------------------------------------------
|
|
INSERT INTO points (name, category, subtype, latitude, longitude, description) VALUES
|
|
('SPBU Jl. Sudirman', 'spbu', '24hours', -6.9175, 107.6191, 'SPBU di pusat kota'),
|
|
('SPBU Jl. Asia Afrika', 'spbu', 'not24hours', -6.9214, 107.6062, 'SPBU selatan kota'),
|
|
('Masjid Agung', 'mosque', NULL, -6.9200, 107.6100, 'Masjid Agung Bandung'),
|
|
('Masjid Al-Ikhlas', 'mosque', NULL, -6.9150, 107.6250, 'Masjid Kelurahan'),
|
|
('Lokasi Kemiskinan A', 'poor', NULL, -6.9300, 107.6050, 'Kawasan padat penduduk'),
|
|
('Lokasi Kemiskinan B', 'poor', NULL, -6.9350, 107.6150, 'RW 07 Kebon Jeruk');
|