39 lines
2.1 KiB
SQL
39 lines
2.1 KiB
SQL
-- ============================================================
|
||
-- DATABASE: webgis_spbu
|
||
-- Pertemuan 3 – Layer Groups and Layers Control
|
||
-- Data SPBU dengan 2 Layer Group: Buka 24 Jam & Tidak 24 Jam
|
||
-- ============================================================
|
||
|
||
CREATE DATABASE IF NOT EXISTS webgis_spbu
|
||
CHARACTER SET utf8mb4
|
||
COLLATE utf8mb4_unicode_ci;
|
||
|
||
USE webgis_spbu;
|
||
|
||
-- buka_24_jam: 'Ya' → Layer Group "SPBU Buka 24 Jam"
|
||
-- 'Tidak' → Layer Group "SPBU Tidak Buka 24 Jam"
|
||
CREATE TABLE IF NOT EXISTS data_spbu (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
nama_spbu VARCHAR(200) NOT NULL,
|
||
alamat VARCHAR(300) 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;
|
||
|
||
-- Migrasi: tambah kolom alamat jika tabel lama dari PovertyMap belum punya
|
||
-- ALTER TABLE data_spbu ADD COLUMN alamat VARCHAR(300) NOT NULL DEFAULT '' AFTER nama_spbu;
|
||
-- UPDATE data_spbu SET alamat = CONCAT(nama_spbu, ', Pontianak') WHERE alamat = '';
|
||
|
||
INSERT IGNORE INTO data_spbu (nama_spbu, alamat, no_wa, buka_24_jam, latitude, longitude) VALUES
|
||
('SPBU Jl. Ahmad Yani', 'Jl. Ahmad Yani No. 12, Pontianak', '0812-1111-0001', 'Ya', -0.0400, 109.3200),
|
||
('SPBU Jl. Gajah Mada', 'Jl. Gajah Mada No. 45, Pontianak', '0812-1111-0002', 'Ya', -0.0520, 109.3450),
|
||
('SPBU Jl. Diponegoro', 'Jl. Diponegoro No. 88, Pontianak', '0812-1111-0003', 'Tidak', -0.0610, 109.3600),
|
||
('SPBU Jl. Sutan Syahrir', 'Jl. Sutan Syahrir No. 5, Pontianak', '0812-1111-0004', 'Tidak', -0.0480, 109.3700),
|
||
('SPBU Jl. Kom. Yos Sudarso', 'Jl. Kom. Yos Sudarso No. 22, Pontianak', '0812-1111-0005', 'Ya', -0.0350, 109.3550),
|
||
('SPBU Jl. Tanjung Raya', 'Jl. Tanjung Raya No. 101, Pontianak', '0812-1111-0006', 'Tidak', -0.0580, 109.3300),
|
||
('SPBU Jl. Veteran', 'Jl. Veteran No. 33, Pontianak', '0812-1111-0007', 'Ya', -0.0450, 109.3380);
|