81 lines
3.1 KiB
SQL
81 lines
3.1 KiB
SQL
-- ============================================================
|
|
-- WebGIS - Sistem Informasi Geografis Pengentasan Kemiskinan
|
|
-- Database Schema
|
|
-- ============================================================
|
|
|
|
CREATE DATABASE IF NOT EXISTS webgis CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
USE webgis;
|
|
|
|
-- Tabel Points (SPBU / Titik Umum)
|
|
DROP TABLE IF EXISTS points;
|
|
CREATE TABLE points (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama VARCHAR(150) NOT NULL,
|
|
no_wa VARCHAR(30) NOT NULL,
|
|
buka_24_jam ENUM('Ya','Tidak') NOT NULL DEFAULT 'Tidak',
|
|
latitude DECIMAL(18,15) NOT NULL,
|
|
longitude DECIMAL(18,15) NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB;
|
|
|
|
-- Tabel Jalan
|
|
DROP TABLE IF EXISTS jalan;
|
|
CREATE TABLE 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(14,2) NOT NULL DEFAULT 0,
|
|
koordinat LONGTEXT NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB;
|
|
|
|
-- Tabel Polygon
|
|
DROP TABLE IF EXISTS polygon;
|
|
CREATE TABLE polygon (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama_parsil VARCHAR(200) NOT NULL,
|
|
status_kepemilikan ENUM('SHM','HGB','HGU','HP') NOT NULL,
|
|
luas_tanah DECIMAL(18,4) NOT NULL DEFAULT 0,
|
|
koordinat LONGTEXT NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB;
|
|
|
|
-- Tabel Rumah Ibadah
|
|
DROP TABLE IF EXISTS rumah_ibadah;
|
|
CREATE TABLE rumah_ibadah (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama VARCHAR(200) NOT NULL,
|
|
jenis ENUM('Masjid','Gereja','Pura','Vihara','Klenteng','Lainnya') NOT NULL DEFAULT 'Masjid',
|
|
alamat TEXT,
|
|
latitude DECIMAL(18,15) NOT NULL,
|
|
longitude DECIMAL(18,15) NOT NULL,
|
|
radius INT NOT NULL DEFAULT 300,
|
|
kontak VARCHAR(50),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB;
|
|
|
|
-- Tabel Penduduk Miskin
|
|
DROP TABLE IF EXISTS penduduk_miskin;
|
|
CREATE TABLE penduduk_miskin (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama VARCHAR(200) NOT NULL,
|
|
nik VARCHAR(20),
|
|
keterangan TEXT,
|
|
latitude DECIMAL(18,15) NOT NULL,
|
|
longitude DECIMAL(18,15) NOT NULL,
|
|
status_jangkauan ENUM('Dalam Jangkauan','Luar Jangkauan') NOT NULL DEFAULT 'Luar Jangkauan',
|
|
rumah_ibadah_id INT DEFAULT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (rumah_ibadah_id) REFERENCES rumah_ibadah(id) ON DELETE SET NULL
|
|
) ENGINE=InnoDB;
|
|
|
|
-- Sample data untuk testing
|
|
INSERT INTO rumah_ibadah (nama, jenis, alamat, latitude, longitude, radius) VALUES
|
|
('Masjid Raya Pontianak', 'Masjid', 'Jl. Tanjungpura, Pontianak', -0.0263, 109.3425, 500),
|
|
('Gereja Katedral Pontianak', 'Gereja', 'Jl. Pattimura, Pontianak', -0.0571, 109.3283, 400);
|
|
|
|
INSERT INTO penduduk_miskin (nama, nik, keterangan, latitude, longitude, status_jangkauan, rumah_ibadah_id) VALUES
|
|
('Ahmad Fauzi', '6171010101010001', 'Buruh harian lepas', -0.0280, 109.3430, 'Dalam Jangkauan', 1),
|
|
('Sari Dewi', '6171010101010002', 'Ibu rumah tangga tidak bekerja', -0.0900, 109.3900, 'Luar Jangkauan', NULL),
|
|
('Budi Santoso', '6171010101010003', 'Pekerja informal', -0.0600, 109.3280, 'Dalam Jangkauan', 2);
|