87 lines
2.8 KiB
SQL
87 lines
2.8 KiB
SQL
CREATE DATABASE IF NOT EXISTS webgis_spbu;
|
|
|
|
USE webgis_spbu;
|
|
|
|
CREATE TABLE IF NOT EXISTS spbu (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama VARCHAR(100) NOT NULL,
|
|
no_spbu VARCHAR(50) NOT NULL,
|
|
status ENUM('24jam', 'tidak') NOT NULL,
|
|
latitude DOUBLE NOT NULL,
|
|
longitude DOUBLE NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS jalan (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama VARCHAR(100) NOT NULL,
|
|
status ENUM('Jalan Nasional', 'Jalan Provinsi', 'Jalan Kabupaten') NOT NULL,
|
|
panjang DOUBLE NOT NULL,
|
|
geom LONGTEXT NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS kavling (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama VARCHAR(100) NOT NULL,
|
|
status ENUM('SHM', 'HGB', 'HGU', 'HP') NOT NULL,
|
|
luas DOUBLE NOT NULL,
|
|
geom LONGTEXT NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS kemiskinan (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama_kepala_keluarga VARCHAR(150) NOT NULL,
|
|
jumlah_kk INT NOT NULL,
|
|
alamat TEXT NOT NULL,
|
|
latitude DOUBLE NOT NULL,
|
|
longitude DOUBLE NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS masjid (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama_masjid VARCHAR(150) NOT NULL,
|
|
pic_masjid VARCHAR(150) NOT NULL,
|
|
alamat TEXT NOT NULL,
|
|
latitude DOUBLE NOT NULL,
|
|
longitude DOUBLE NOT NULL,
|
|
radius DOUBLE NOT NULL DEFAULT 500,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
ALTER TABLE spbu
|
|
MODIFY COLUMN nama VARCHAR(100) NOT NULL,
|
|
MODIFY COLUMN no_spbu VARCHAR(50) NOT NULL,
|
|
MODIFY COLUMN status ENUM('24jam', 'tidak') NOT NULL,
|
|
MODIFY COLUMN latitude DOUBLE NOT NULL,
|
|
MODIFY COLUMN longitude DOUBLE NOT NULL;
|
|
|
|
ALTER TABLE jalan
|
|
MODIFY COLUMN nama VARCHAR(100) NOT NULL,
|
|
MODIFY COLUMN status ENUM('Jalan Nasional', 'Jalan Provinsi', 'Jalan Kabupaten') NOT NULL,
|
|
MODIFY COLUMN panjang DOUBLE NOT NULL,
|
|
MODIFY COLUMN geom LONGTEXT NOT NULL;
|
|
|
|
ALTER TABLE kavling
|
|
MODIFY COLUMN nama VARCHAR(100) NOT NULL,
|
|
MODIFY COLUMN status ENUM('SHM', 'HGB', 'HGU', 'HP') NOT NULL,
|
|
MODIFY COLUMN luas DOUBLE NOT NULL,
|
|
MODIFY COLUMN geom LONGTEXT NOT NULL;
|
|
|
|
ALTER TABLE kemiskinan
|
|
MODIFY COLUMN nama_kepala_keluarga VARCHAR(150) NOT NULL,
|
|
MODIFY COLUMN jumlah_kk INT NOT NULL,
|
|
MODIFY COLUMN alamat TEXT NOT NULL,
|
|
MODIFY COLUMN latitude DOUBLE NOT NULL,
|
|
MODIFY COLUMN longitude DOUBLE NOT NULL;
|
|
|
|
ALTER TABLE masjid
|
|
MODIFY COLUMN nama_masjid VARCHAR(150) NOT NULL,
|
|
MODIFY COLUMN pic_masjid VARCHAR(150) NOT NULL,
|
|
MODIFY COLUMN alamat TEXT NOT NULL,
|
|
MODIFY COLUMN latitude DOUBLE NOT NULL,
|
|
MODIFY COLUMN longitude DOUBLE NOT NULL,
|
|
MODIFY COLUMN radius DOUBLE NOT NULL DEFAULT 500;
|