azgrey
210 lines
7.5 KiB
SQL
210 lines
7.5 KiB
SQL
-- ============================================================
|
|
-- WebGIS Portal — Combined Database Schema
|
|
-- Covers: Project 01 (SPBU Map), 02 (WebGIS Jalan & Tanah),
|
|
-- 03 (Kepadatan Penduduk — reference), 04 (SPBU + Layer)
|
|
--
|
|
-- Usage:
|
|
-- mysql -u root -p < webgis-portal.sql
|
|
-- ============================================================
|
|
|
|
-- ============================================================
|
|
-- 1. spbu_map — used by Project 01 and Project 04
|
|
-- ============================================================
|
|
|
|
CREATE DATABASE IF NOT EXISTS spbu_map
|
|
CHARACTER SET utf8mb4
|
|
COLLATE utf8mb4_unicode_ci;
|
|
|
|
USE spbu_map;
|
|
|
|
DROP TABLE IF EXISTS spbu;
|
|
|
|
CREATE TABLE spbu (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
nama_spbu VARCHAR(200) NOT NULL,
|
|
nomor_wa VARCHAR(20) NOT NULL DEFAULT '',
|
|
buka_24_jam TINYINT(1) NOT NULL DEFAULT 0
|
|
COMMENT '1 = buka 24 jam, 0 = tidak buka 24 jam',
|
|
latitude DECIMAL(10, 7) NOT NULL,
|
|
longitude DECIMAL(10, 7) NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
ON UPDATE CURRENT_TIMESTAMP,
|
|
|
|
INDEX idx_buka (buka_24_jam),
|
|
INDEX idx_coords (latitude, longitude)
|
|
) ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8mb4
|
|
COLLATE = utf8mb4_unicode_ci;
|
|
|
|
-- Sample data — SPBU di area Pontianak
|
|
INSERT INTO spbu (nama_spbu, nomor_wa, buka_24_jam, latitude, longitude) VALUES
|
|
('Pertamina Ahmad Yani', '6281234567890', 1, -0.0500000, 109.3450000),
|
|
('Pertamina Sungai Raya', '6281234567891', 0, -0.0870000, 109.3620000),
|
|
('Shell Pontianak Tengah', '6281234567892', 1, -0.0320000, 109.3300000),
|
|
('Pertamina Tanjung Raya', '6281234567893', 0, -0.0130000, 109.3550000),
|
|
('Vivo Khatulistiwa', '6281234567894', 1, 0.0020000, 109.3280000),
|
|
('Pertamina Khatulistiwa', '6281234567895', 0, -0.0650000, 109.3400000),
|
|
('SPBU Gajah Mada', '6281234567896', 1, -0.0210000, 109.3350000);
|
|
|
|
|
|
-- ============================================================
|
|
-- 2. webgis_db — used by Project 02 (Jalan & Tanah)
|
|
-- ============================================================
|
|
|
|
CREATE DATABASE IF NOT EXISTS webgis_db
|
|
CHARACTER SET utf8mb4
|
|
COLLATE utf8mb4_unicode_ci;
|
|
|
|
USE webgis_db;
|
|
|
|
-- ── 2a. Jalan (polylines) ──────────────────────────────────
|
|
|
|
DROP TABLE IF EXISTS jalan;
|
|
|
|
CREATE TABLE jalan (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
nama_jalan VARCHAR(200) NOT NULL,
|
|
status_jalan ENUM('Nasional','Provinsi','Kabupaten')
|
|
NOT NULL DEFAULT 'Kabupaten',
|
|
panjang_meter DECIMAL(12, 2) NOT NULL DEFAULT 0.00
|
|
COMMENT 'Panjang jalan dalam meter, dihitung otomatis',
|
|
geojson MEDIUMTEXT NOT NULL
|
|
COMMENT 'GeoJSON LineString geometry (WGS84)',
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
ON UPDATE CURRENT_TIMESTAMP,
|
|
|
|
INDEX idx_status (status_jalan)
|
|
) ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8mb4
|
|
COLLATE = utf8mb4_unicode_ci;
|
|
|
|
-- Sample jalan data
|
|
INSERT INTO jalan (nama_jalan, status_jalan, panjang_meter, geojson) VALUES
|
|
(
|
|
'Jl. Ahmad Yani',
|
|
'Nasional',
|
|
4200.00,
|
|
'{"type":"LineString","coordinates":[[109.325,-0.045],[109.330,-0.042],[109.335,-0.038],[109.340,-0.034],[109.345,-0.030]]}'
|
|
),
|
|
(
|
|
'Jl. Gajah Mada',
|
|
'Provinsi',
|
|
2800.00,
|
|
'{"type":"LineString","coordinates":[[109.330,-0.020],[109.333,-0.018],[109.336,-0.016],[109.340,-0.014]]}'
|
|
),
|
|
(
|
|
'Jl. Teuku Umar',
|
|
'Kabupaten',
|
|
1900.00,
|
|
'{"type":"LineString","coordinates":[[109.315,-0.055],[109.318,-0.052],[109.322,-0.050],[109.326,-0.048]]}'
|
|
),
|
|
(
|
|
'Jl. Sultan Abdurrahman',
|
|
'Provinsi',
|
|
3300.00,
|
|
'{"type":"LineString","coordinates":[[109.320,-0.028],[109.323,-0.025],[109.327,-0.022],[109.331,-0.019]]}'
|
|
),
|
|
(
|
|
'Jl. Tanjungpura',
|
|
'Nasional',
|
|
5100.00,
|
|
'{"type":"LineString","coordinates":[[109.310,-0.035],[109.315,-0.033],[109.320,-0.031],[109.326,-0.029],[109.333,-0.027]]}'
|
|
);
|
|
|
|
-- ── 2b. Parsil Tanah (polygons) ────────────────────────────
|
|
|
|
DROP TABLE IF EXISTS parsil_tanah;
|
|
|
|
CREATE TABLE parsil_tanah (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
nama_parsil VARCHAR(200) NOT NULL,
|
|
status_kepemilikan ENUM('SHM','HGB','HGU','HP')
|
|
NOT NULL DEFAULT 'SHM',
|
|
luas_m2 DECIMAL(14, 2) NOT NULL DEFAULT 0.00
|
|
COMMENT 'Luas parsil dalam meter persegi, dihitung otomatis',
|
|
geojson MEDIUMTEXT NOT NULL
|
|
COMMENT 'GeoJSON Polygon geometry (WGS84)',
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
ON UPDATE CURRENT_TIMESTAMP,
|
|
|
|
INDEX idx_status (status_kepemilikan)
|
|
) ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8mb4
|
|
COLLATE = utf8mb4_unicode_ci;
|
|
|
|
-- Sample parsil data
|
|
INSERT INTO parsil_tanah (nama_parsil, status_kepemilikan, luas_m2, geojson) VALUES
|
|
(
|
|
'Kavling A-01',
|
|
'SHM',
|
|
2400.00,
|
|
'{"type":"Polygon","coordinates":[[[109.328,-0.038],[109.330,-0.038],[109.330,-0.040],[109.328,-0.040],[109.328,-0.038]]]}'
|
|
),
|
|
(
|
|
'Kavling B-03',
|
|
'HGB',
|
|
3100.00,
|
|
'{"type":"Polygon","coordinates":[[[109.340,-0.025],[109.343,-0.025],[109.343,-0.028],[109.340,-0.028],[109.340,-0.025]]]}'
|
|
),
|
|
(
|
|
'Kavling C-07',
|
|
'HGU',
|
|
4800.00,
|
|
'{"type":"Polygon","coordinates":[[[109.318,-0.050],[109.322,-0.050],[109.322,-0.054],[109.318,-0.054],[109.318,-0.050]]]}'
|
|
),
|
|
(
|
|
'Kavling D-02',
|
|
'HP',
|
|
1750.00,
|
|
'{"type":"Polygon","coordinates":[[[109.335,-0.016],[109.337,-0.016],[109.337,-0.018],[109.335,-0.018],[109.335,-0.016]]]}'
|
|
);
|
|
|
|
|
|
-- ============================================================
|
|
-- 3. kepadatan_db — reference for Project 03
|
|
-- (geometry is embedded statically in the front-end via
|
|
-- UTM reprojection; this table stores the attribute data)
|
|
-- ============================================================
|
|
|
|
CREATE DATABASE IF NOT EXISTS kepadatan_db
|
|
CHARACTER SET utf8mb4
|
|
COLLATE utf8mb4_unicode_ci;
|
|
|
|
USE kepadatan_db;
|
|
|
|
DROP TABLE IF EXISTS kecamatan;
|
|
|
|
CREATE TABLE kecamatan (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
nama VARCHAR(100) NOT NULL,
|
|
density DECIMAL(6, 3) NOT NULL
|
|
COMMENT 'Kepadatan penduduk: ribu jiwa per km²',
|
|
color_class ENUM('rendah','sedang','tinggi')
|
|
NOT NULL
|
|
COMMENT 'rendah ≤5, sedang 5-7, tinggi >7',
|
|
geojson MEDIUMTEXT NOT NULL DEFAULT ''
|
|
COMMENT 'GeoJSON Polygon geometry (WGS84); populasi via front-end UTM reprojection',
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
INDEX idx_class (color_class)
|
|
) ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8mb4
|
|
COLLATE = utf8mb4_unicode_ci;
|
|
|
|
-- Data kepadatan dari BPS Kota Pontianak
|
|
INSERT INTO kecamatan (nama, density, color_class) VALUES
|
|
('Pontianak Selatan', 5.673, 'sedang'),
|
|
('Pontianak Utara', 3.642, 'rendah'),
|
|
('Pontianak Timur', 9.196, 'tinggi'),
|
|
('Pontianak Tenggara', 3.103, 'rendah'),
|
|
('Pontianak Kota', 8.102, 'tinggi'),
|
|
('Pontianak Barat', 9.331, 'tinggi');
|
|
|
|
|
|
-- ============================================================
|
|
-- End of schema
|
|
-- ============================================================
|