123 lines
5.4 KiB
SQL
123 lines
5.4 KiB
SQL
-- ============================================================
|
|
-- DATABASE LENGKAP: webgis_p3
|
|
-- WebGIS Poverty Mapping + Sistem Login (3 Role)
|
|
-- Import file ini ke Laragon phpMyAdmin
|
|
-- ============================================================
|
|
|
|
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) ────────────────────────
|
|
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 ──────────────────────────────────────
|
|
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,
|
|
jumlah_kk INT NOT NULL DEFAULT 1,
|
|
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)
|
|
CREATE TABLE IF NOT EXISTS data_masjid (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama_masjid VARCHAR(200) NOT NULL,
|
|
jenis ENUM('Masjid','Mushola','Gereja','Pura','Vihara','Klenteng') NOT NULL DEFAULT 'Masjid',
|
|
pic_masjid VARCHAR(200) NOT NULL,
|
|
alamat TEXT NOT NULL,
|
|
radius_m INT NOT NULL DEFAULT 500,
|
|
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 Users (Sistem Login 3 Role) ─────────────────────
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(100) NOT NULL UNIQUE,
|
|
nama_lengkap VARCHAR(200) NOT NULL,
|
|
role ENUM('admin','petugas','pengurus','walikota') NOT NULL,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
aktif TINYINT(1) NOT NULL DEFAULT 1,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX idx_role (role),
|
|
INDEX idx_aktif (aktif)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ── Akun Demo ─────────────────────────────────────────────
|
|
-- Password semua akun: "password" (bcrypt via PHP PASSWORD_DEFAULT)
|
|
INSERT INTO users (username, nama_lengkap, role, password_hash) VALUES
|
|
(
|
|
'admin',
|
|
'Administrator Sistem',
|
|
'admin',
|
|
'$2y$10$mhMxyKlOSSTQxfmWXEncfe1AF56CJ8fb.v0h0CYZ.ELQQd8iP5LrG'
|
|
),
|
|
(
|
|
'petugas',
|
|
'Petugas Lapangan',
|
|
'petugas',
|
|
'$2y$10$mhMxyKlOSSTQxfmWXEncfe1AF56CJ8fb.v0h0CYZ.ELQQd8iP5LrG'
|
|
),
|
|
(
|
|
'pengurus',
|
|
'Pengurus Rumah Ibadah',
|
|
'pengurus',
|
|
'$2y$10$mhMxyKlOSSTQxfmWXEncfe1AF56CJ8fb.v0h0CYZ.ELQQd8iP5LrG'
|
|
),
|
|
(
|
|
'walikota',
|
|
'Walikota Pontianak',
|
|
'walikota',
|
|
'$2y$10$mhMxyKlOSSTQxfmWXEncfe1AF56CJ8fb.v0h0CYZ.ELQQd8iP5LrG'
|
|
);
|