181 lines
6.8 KiB
SQL
181 lines
6.8 KiB
SQL
-- ============================================================
|
||
-- DATABASE: webgis_sig
|
||
-- Sistem Manajemen Data Jalan, Parsil Tanah & SPBU
|
||
-- ============================================================
|
||
-- ============================================================
|
||
-- TABEL: data_jalan (Line / Polyline)
|
||
-- Menyimpan data ruas jalan berdasarkan status jalan
|
||
-- Geometri disimpan dalam format GeoJSON (LineString)
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS data_jalan (
|
||
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
||
nama_jalan VARCHAR(150) NOT NULL COMMENT 'Nama ruas jalan',
|
||
status_jalan ENUM (
|
||
'Jalan Nasional',
|
||
'Jalan Provinsi',
|
||
'Jalan Kabupaten'
|
||
) NOT NULL COMMENT 'Status / klasifikasi jalan',
|
||
panjang_m DECIMAL(12, 2) DEFAULT 0.00 COMMENT 'Panjang jalan dalam meter (dihitung otomatis oleh LeafletJS)',
|
||
geojson LONGTEXT NOT NULL COMMENT 'Geometri LineString dalam format GeoJSON',
|
||
keterangan TEXT DEFAULT NULL COMMENT 'Keterangan tambahan',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
|
||
|
||
-- ============================================================
|
||
-- TABEL: data_parsil (Polygon)
|
||
-- Menyimpan data parsil tanah / kavling berdasarkan status kepemilikan
|
||
-- Geometri disimpan dalam format GeoJSON (Polygon)
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS data_parsil (
|
||
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
||
nama_parsil VARCHAR(150) NOT NULL COMMENT 'Nama / nomor parsil',
|
||
status_hak ENUM (
|
||
'Sertifikat Hak Milik',
|
||
'Sertifikat Hak Guna Bangunan',
|
||
'Sertifikat Hak Guna Usaha',
|
||
'Sertifikat Hak Pakai'
|
||
) NOT NULL COMMENT 'Status kepemilikan / hak atas tanah',
|
||
luas_m2 DECIMAL(15, 2) DEFAULT 0.00 COMMENT 'Luas tanah dalam meter persegi (dihitung otomatis oleh LeafletJS)',
|
||
geojson LONGTEXT NOT NULL COMMENT 'Geometri Polygon dalam format GeoJSON',
|
||
pemilik VARCHAR(150) DEFAULT NULL COMMENT 'Nama pemilik (opsional)',
|
||
keterangan TEXT DEFAULT NULL COMMENT 'Keterangan tambahan',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
|
||
|
||
-- ============================================================
|
||
-- DATA CONTOH – Jalan
|
||
-- ============================================================
|
||
INSERT INTO
|
||
data_jalan (
|
||
nama_jalan,
|
||
status_jalan,
|
||
panjang_m,
|
||
geojson,
|
||
keterangan
|
||
)
|
||
VALUES
|
||
(
|
||
'Jl. Sudirman',
|
||
'Jalan Nasional',
|
||
1250.50,
|
||
'{"type":"LineString","coordinates":[[106.8272,-6.2088],[106.8290,-6.2075],[106.8310,-6.2060],[106.8330,-6.2045]]}',
|
||
'Jalan protokol pusat kota'
|
||
),
|
||
(
|
||
'Jl. Gatot Subroto',
|
||
'Jalan Provinsi',
|
||
980.00,
|
||
'{"type":"LineString","coordinates":[[106.8200,-6.2100],[106.8220,-6.2095],[106.8240,-6.2090],[106.8260,-6.2085]]}',
|
||
'Penghubung antar kecamatan'
|
||
),
|
||
(
|
||
'Jl. Pramuka',
|
||
'Jalan Kabupaten',
|
||
620.75,
|
||
'{"type":"LineString","coordinates":[[106.8150,-6.2180],[106.8160,-6.2170],[106.8175,-6.2160],[106.8185,-6.2150]]}',
|
||
'Jalan lingkungan kabupaten'
|
||
);
|
||
|
||
-- ============================================================
|
||
-- DATA CONTOH – Parsil Tanah
|
||
-- ============================================================
|
||
INSERT INTO
|
||
data_parsil (
|
||
nama_parsil,
|
||
status_hak,
|
||
luas_m2,
|
||
geojson,
|
||
pemilik,
|
||
keterangan
|
||
)
|
||
VALUES
|
||
(
|
||
'Parsil A-001',
|
||
'Sertifikat Hak Milik',
|
||
1200.00,
|
||
'{"type":"Polygon","coordinates":[[[106.8200,-6.2000],[106.8210,-6.2000],[106.8210,-6.2010],[106.8200,-6.2010],[106.8200,-6.2000]]]}',
|
||
'Budi Santoso',
|
||
'Tanah kavling perumahan'
|
||
),
|
||
(
|
||
'Parsil B-002',
|
||
'Sertifikat Hak Guna Bangunan',
|
||
850.50,
|
||
'{"type":"Polygon","coordinates":[[[106.8220,-6.2000],[106.8230,-6.2000],[106.8230,-6.2008],[106.8220,-6.2008],[106.8220,-6.2000]]]}',
|
||
'PT. Maju Bersama',
|
||
'Kavling komersial'
|
||
),
|
||
(
|
||
'Parsil C-003',
|
||
'Sertifikat Hak Guna Usaha',
|
||
2500.00,
|
||
'{"type":"Polygon","coordinates":[[[106.8240,-6.1990],[106.8260,-6.1990],[106.8260,-6.2010],[106.8240,-6.2010],[106.8240,-6.1990]]]}',
|
||
'CV. Tani Makmur',
|
||
'Lahan pertanian/perkebunan'
|
||
),
|
||
(
|
||
'Parsil D-004',
|
||
'Sertifikat Hak Pakai',
|
||
4000.00,
|
||
'{"type":"Polygon","coordinates":[[[106.8270,-6.1985],[106.8295,-6.1985],[106.8295,-6.2005],[106.8270,-6.2005],[106.8270,-6.1985]]]}',
|
||
'Pemerintah Kota',
|
||
'Lahan fasilitas umum'
|
||
);
|
||
|
||
-- ============================================================
|
||
-- TABEL: data_spbu (Point)
|
||
-- Menyimpan data SPBU (Stasiun Pengisian Bahan Bakar Umum)
|
||
-- Geometri disimpan dalam format GeoJSON (Point)
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS data_spbu (
|
||
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
||
nama_spbu VARCHAR(150) NOT NULL COMMENT 'Nama SPBU / nama cabang',
|
||
telepon VARCHAR(20) DEFAULT NULL COMMENT 'Nomor telepon SPBU',
|
||
buka_24jam TINYINT (1) NOT NULL DEFAULT 0 COMMENT '1 = buka 24 jam (marker hijau), 0 = tidak (marker merah)',
|
||
lat DECIMAL(10, 7) NOT NULL COMMENT 'Latitude titik SPBU',
|
||
lng DECIMAL(10, 7) NOT NULL COMMENT 'Longitude titik SPBU',
|
||
geojson LONGTEXT NOT NULL COMMENT 'Geometri Point dalam format GeoJSON',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
|
||
|
||
-- ============================================================
|
||
-- DATA CONTOH – SPBU di Pontianak
|
||
-- ============================================================
|
||
INSERT INTO
|
||
data_spbu (nama_spbu, telepon, buka_24jam, lat, lng, geojson)
|
||
VALUES
|
||
(
|
||
'SPBU Jl. Ahmad Yani',
|
||
'0561-123456',
|
||
1,
|
||
-0.0200,
|
||
109.3300,
|
||
'{"type":"Point","coordinates":[109.3300,-0.0200]}'
|
||
),
|
||
(
|
||
'SPBU Jl. Gajah Mada',
|
||
'0561-654321',
|
||
0,
|
||
-0.0350,
|
||
109.3200,
|
||
'{"type":"Point","coordinates":[109.3200,-0.0350]}'
|
||
),
|
||
(
|
||
'SPBU Jl. Tanjungpura',
|
||
'0561-789012',
|
||
1,
|
||
-0.0100,
|
||
109.3450,
|
||
'{"type":"Point","coordinates":[109.3450,-0.0100]}'
|
||
),
|
||
(
|
||
'SPBU Jl. Sutan Syahrir',
|
||
'0561-345678',
|
||
0,
|
||
-0.0480,
|
||
109.3380,
|
||
'{"type":"Point","coordinates":[109.3380,-0.0480]}'
|
||
); |