51 lines
2.1 KiB
SQL
51 lines
2.1 KiB
SQL
-- database.sql - Tugas SIG Kelas Freya
|
|
-- Cocok untuk import via Coolify. Tidak memakai CREATE DATABASE/USE.
|
|
-- Akan memakai database aktif dari Coolify, misalnya: gis_spbu.
|
|
|
|
SET FOREIGN_KEY_CHECKS = 0;
|
|
DROP TABLE IF EXISTS kavling;
|
|
DROP TABLE IF EXISTS jalan;
|
|
DROP TABLE IF EXISTS spbu;
|
|
SET FOREIGN_KEY_CHECKS = 1;
|
|
|
|
CREATE TABLE spbu (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama VARCHAR(150) NOT NULL,
|
|
wa VARCHAR(30) DEFAULT NULL,
|
|
buka ENUM('Iya','Tidak') NOT NULL DEFAULT 'Tidak',
|
|
lat DOUBLE NOT NULL,
|
|
lng DOUBLE NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE jalan (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama VARCHAR(150) NOT NULL,
|
|
status ENUM('Nasional','Provinsi','Kabupaten') NOT NULL DEFAULT 'Kabupaten',
|
|
panjang DOUBLE DEFAULT 0,
|
|
geom LONGTEXT NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE kavling (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
pemilik VARCHAR(150) NOT NULL,
|
|
status ENUM('SHM','HGB','HGU','HP') NOT NULL DEFAULT 'SHM',
|
|
luas DOUBLE DEFAULT 0,
|
|
geom LONGTEXT NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
INSERT INTO spbu (nama, wa, buka, lat, lng) VALUES
|
|
('SPBU Ahmad Yani', '081234567890', 'Iya', -0.0556, 109.3468),
|
|
('SPBU Tanjungpura', '081234567891', 'Tidak', -0.0319, 109.3422),
|
|
('SPBU Siantan', '081234567892', 'Iya', -0.0055, 109.3320);
|
|
|
|
INSERT INTO jalan (nama, status, panjang, geom) VALUES
|
|
('Jalan Ahmad Yani', 'Nasional', 1200, '[{"lat":-0.062,"lng":109.343},{"lat":-0.055,"lng":109.347},{"lat":-0.050,"lng":109.350}]'),
|
|
('Jalan Gajah Mada', 'Provinsi', 900, '[{"lat":-0.035,"lng":109.337},{"lat":-0.031,"lng":109.341},{"lat":-0.027,"lng":109.345}]');
|
|
|
|
INSERT INTO kavling (pemilik, status, luas, geom) VALUES
|
|
('Kavling Contoh A', 'SHM', 1500, '[{"lat":-0.045,"lng":109.335},{"lat":-0.044,"lng":109.338},{"lat":-0.047,"lng":109.339},{"lat":-0.048,"lng":109.336}]'),
|
|
('Kavling Contoh B', 'HGB', 1000, '[{"lat":-0.025,"lng":109.350},{"lat":-0.023,"lng":109.352},{"lat":-0.026,"lng":109.354},{"lat":-0.028,"lng":109.351}]');
|