htlee
b9d924e81e
WING-GIS 해양경찰 통합 GIS 위치정보시스템. 모노레포: frontend(React 19 + MapLibre + deck.gl) + services(Spring Boot + Gradle). - npm + Nexus 프록시 레지스트리 설정 - 팀 워크플로우 v1.6.1 부트스트랩 파일 배치 - .githooks (commit-msg, post-checkout) - custom_pre_commit: true (모노레포 pre-commit 별도 관리) Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
285 lines
11 KiB
SQL
285 lines
11 KiB
SQL
-- WING-GIS Database Schema
|
|
-- PostgreSQL 16 + PostGIS 3.4
|
|
-- 해양경찰청 통합 GIS 위치정보시스템
|
|
|
|
-- Extensions
|
|
CREATE EXTENSION IF NOT EXISTS postgis;
|
|
CREATE EXTENSION IF NOT EXISTS pg_trgm; -- 부분 검색용 trigram
|
|
|
|
-- ============================================================
|
|
-- 1. 물표/선박 (SFR-05, SFR-08)
|
|
-- ============================================================
|
|
CREATE TABLE vessel (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
mmsi VARCHAR(20) UNIQUE,
|
|
imo VARCHAR(20),
|
|
name VARCHAR(100),
|
|
callsign VARCHAR(20),
|
|
ship_type VARCHAR(50),
|
|
flag VARCHAR(5),
|
|
gt DECIMAL,
|
|
dwt DECIMAL,
|
|
loa DECIMAL,
|
|
beam DECIMAL,
|
|
draft DECIMAL,
|
|
status VARCHAR(30),
|
|
source VARCHAR(20) NOT NULL DEFAULT 'AIS',
|
|
position GEOMETRY(Point, 4326),
|
|
sog DECIMAL,
|
|
cog DECIMAL,
|
|
heading DECIMAL,
|
|
nav_status VARCHAR(30),
|
|
destination VARCHAR(100),
|
|
eta TIMESTAMP,
|
|
owner VARCHAR(200),
|
|
operator VARCHAR(200),
|
|
last_updated TIMESTAMP DEFAULT NOW(),
|
|
created_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_vessel_position ON vessel USING GIST(position);
|
|
CREATE INDEX idx_vessel_mmsi ON vessel(mmsi);
|
|
CREATE INDEX idx_vessel_name_trgm ON vessel USING GIN(name gin_trgm_ops);
|
|
CREATE INDEX idx_vessel_source ON vessel(source);
|
|
CREATE INDEX idx_vessel_flag ON vessel(flag);
|
|
CREATE INDEX idx_vessel_ship_type ON vessel(ship_type);
|
|
|
|
COMMENT ON TABLE vessel IS '물표/선박 정보 (AIS/V-Pass/VTS/Radar/E-Nav/VHF-DSC/항공기AIS)';
|
|
COMMENT ON COLUMN vessel.source IS 'AIS, V-Pass, E-NAV, VTS, VTS-RADAR, AIR-AIS, VHF-DSC';
|
|
|
|
-- ============================================================
|
|
-- 2. 항적 이력 (SFR-05)
|
|
-- ============================================================
|
|
CREATE TABLE vessel_track (
|
|
id BIGSERIAL,
|
|
vessel_id BIGINT NOT NULL REFERENCES vessel(id) ON DELETE CASCADE,
|
|
position GEOMETRY(Point, 4326) NOT NULL,
|
|
sog DECIMAL,
|
|
cog DECIMAL,
|
|
heading DECIMAL,
|
|
source VARCHAR(20),
|
|
recorded_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
PRIMARY KEY (id, recorded_at)
|
|
);
|
|
|
|
CREATE INDEX idx_track_vessel_time ON vessel_track(vessel_id, recorded_at DESC);
|
|
CREATE INDEX idx_track_position ON vessel_track USING GIST(position);
|
|
|
|
COMMENT ON TABLE vessel_track IS '선박 항적 이력 (TimescaleDB hypertable 전환 권장)';
|
|
|
|
-- ============================================================
|
|
-- 3. 레이어 관리 (SFR-03, SFR-07)
|
|
-- ============================================================
|
|
CREATE TABLE layer_group (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
name_en VARCHAR(100),
|
|
parent_id INTEGER REFERENCES layer_group(id),
|
|
sort_order INTEGER DEFAULT 0,
|
|
icon VARCHAR(10),
|
|
is_system BOOLEAN DEFAULT FALSE,
|
|
created_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE layer (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
group_id INTEGER REFERENCES layer_group(id),
|
|
name VARCHAR(200) NOT NULL,
|
|
name_en VARCHAR(200),
|
|
layer_type VARCHAR(30) NOT NULL DEFAULT 'VECTOR',
|
|
s100_product VARCHAR(10),
|
|
geometry_type VARCHAR(20),
|
|
srid INTEGER DEFAULT 4326,
|
|
style_sld TEXT,
|
|
min_scale INTEGER,
|
|
max_scale INTEGER,
|
|
is_visible BOOLEAN DEFAULT TRUE,
|
|
is_default_on BOOLEAN DEFAULT FALSE,
|
|
feature_count INTEGER DEFAULT 0,
|
|
source_url VARCHAR(500),
|
|
file_path VARCHAR(500),
|
|
created_by BIGINT,
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
updated_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
COMMENT ON COLUMN layer.s100_product IS 'S-101, S-102, S-104, S-111, S-122, S-124, S-127, S-412, S-414';
|
|
|
|
-- ============================================================
|
|
-- 4. S-100 전자해도 데이터셋 (SFR-04)
|
|
-- ============================================================
|
|
CREATE TABLE chart_dataset (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
cell_name VARCHAR(20) NOT NULL,
|
|
product VARCHAR(10) NOT NULL DEFAULT 'S-101',
|
|
edition INTEGER NOT NULL DEFAULT 1,
|
|
update_num INTEGER DEFAULT 0,
|
|
scale INTEGER,
|
|
status VARCHAR(20) DEFAULT 'CURRENT',
|
|
coverage GEOMETRY(Polygon, 4326),
|
|
fc_version VARCHAR(20),
|
|
pc_version VARCHAR(20),
|
|
s100_version VARCHAR(20) DEFAULT '5.2.1',
|
|
feature_count INTEGER DEFAULT 0,
|
|
file_path VARCHAR(500),
|
|
issued_date DATE,
|
|
created_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_chart_coverage ON chart_dataset USING GIST(coverage);
|
|
CREATE INDEX idx_chart_cell ON chart_dataset(cell_name);
|
|
|
|
-- ============================================================
|
|
-- 5. 해도 피처 (SFR-03, SFR-04)
|
|
-- ============================================================
|
|
CREATE TABLE chart_feature (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
dataset_id BIGINT REFERENCES chart_dataset(id) ON DELETE CASCADE,
|
|
feature_type VARCHAR(50) NOT NULL,
|
|
category VARCHAR(30),
|
|
feature_name_ko VARCHAR(200),
|
|
feature_name_en VARCHAR(200),
|
|
geometry GEOMETRY(Geometry, 4326) NOT NULL,
|
|
attributes JSONB DEFAULT '{}',
|
|
scamin INTEGER,
|
|
created_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_feature_geom ON chart_feature USING GIST(geometry);
|
|
CREATE INDEX idx_feature_type ON chart_feature(feature_type);
|
|
CREATE INDEX idx_feature_dataset ON chart_feature(dataset_id);
|
|
CREATE INDEX idx_feature_attrs ON chart_feature USING GIN(attributes);
|
|
|
|
COMMENT ON COLUMN chart_feature.feature_type IS 'S-101 FC: Lighthouse, DepthArea, DepthContour, Buoy, Rock, Wreck, FairwaySystem, AnchorageArea, RestrictedArea, etc.';
|
|
|
|
-- ============================================================
|
|
-- 6. 경계/관할구역 (SFR-03)
|
|
-- ============================================================
|
|
CREATE TABLE boundary (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
name_en VARCHAR(100),
|
|
boundary_type VARCHAR(30) NOT NULL,
|
|
geometry GEOMETRY(MultiPolygon, 4326),
|
|
organization VARCHAR(100),
|
|
parent_id INTEGER REFERENCES boundary(id),
|
|
attributes JSONB DEFAULT '{}',
|
|
created_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_boundary_geom ON boundary USING GIST(geometry);
|
|
CREATE INDEX idx_boundary_type ON boundary(boundary_type);
|
|
|
|
COMMENT ON COLUMN boundary.boundary_type IS 'EEZ, NLL, TERRITORIAL, JURISDICTION, PATROL_ROUTE, RESTRICTED, MILITARY';
|
|
|
|
-- ============================================================
|
|
-- 7. 분석 결과 (SFR-09)
|
|
-- ============================================================
|
|
CREATE TABLE analysis_result (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
analysis_type VARCHAR(50) NOT NULL,
|
|
sub_type VARCHAR(50),
|
|
name VARCHAR(200),
|
|
geometry GEOMETRY(Geometry, 4326),
|
|
result_data JSONB DEFAULT '{}',
|
|
confidence DECIMAL,
|
|
status VARCHAR(20) DEFAULT 'ACTIVE',
|
|
created_by BIGINT,
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
expires_at TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX idx_analysis_geom ON analysis_result USING GIST(geometry);
|
|
CREATE INDEX idx_analysis_type ON analysis_result(analysis_type);
|
|
|
|
COMMENT ON COLUMN analysis_result.analysis_type IS 'ILLEGAL_FISHING, TRANSSHIPMENT, DARK_VESSEL, DOKDO, PASSENGER, CN_FISHING, TRAFFIC';
|
|
|
|
-- ============================================================
|
|
-- 8. 사용자 (INR-04)
|
|
-- ============================================================
|
|
CREATE TABLE app_user (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
username VARCHAR(50) UNIQUE NOT NULL,
|
|
password_hash VARCHAR(200),
|
|
name VARCHAR(50) NOT NULL,
|
|
rank VARCHAR(30),
|
|
department VARCHAR(100),
|
|
organization VARCHAR(100),
|
|
role VARCHAR(30) NOT NULL DEFAULT 'VIEWER',
|
|
jurisdiction_id INTEGER REFERENCES boundary(id),
|
|
sso_id VARCHAR(100),
|
|
gpki_dn VARCHAR(500),
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
last_login TIMESTAMP,
|
|
created_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
COMMENT ON COLUMN app_user.role IS 'ADMIN, OPERATOR, VIEWER';
|
|
COMMENT ON COLUMN app_user.organization IS '해양경찰청, 서해지방청, 인천해양경찰서, ...';
|
|
|
|
-- ============================================================
|
|
-- 9. 경보/알림
|
|
-- ============================================================
|
|
CREATE TABLE alert (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
alert_type VARCHAR(30) NOT NULL,
|
|
severity VARCHAR(10) NOT NULL DEFAULT 'INFO',
|
|
title VARCHAR(200) NOT NULL,
|
|
description TEXT,
|
|
vessel_id BIGINT REFERENCES vessel(id),
|
|
position GEOMETRY(Point, 4326),
|
|
is_acknowledged BOOLEAN DEFAULT FALSE,
|
|
acknowledged_by BIGINT REFERENCES app_user(id),
|
|
acknowledged_at TIMESTAMP,
|
|
created_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_alert_severity ON alert(severity, created_at DESC);
|
|
CREATE INDEX idx_alert_vessel ON alert(vessel_id);
|
|
|
|
COMMENT ON COLUMN alert.alert_type IS 'SOS, ZONE_VIOLATION, SIGNAL_LOST, EQUIPMENT_FAULT, WEATHER, AI_DETECTION';
|
|
|
|
-- ============================================================
|
|
-- 10. 감사 로그 (SER-09)
|
|
-- ============================================================
|
|
CREATE TABLE audit_log (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
user_id BIGINT REFERENCES app_user(id),
|
|
action VARCHAR(50) NOT NULL,
|
|
target_type VARCHAR(50),
|
|
target_id VARCHAR(50),
|
|
detail JSONB,
|
|
ip_address VARCHAR(45),
|
|
user_agent VARCHAR(500),
|
|
created_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_audit_user ON audit_log(user_id, created_at DESC);
|
|
CREATE INDEX idx_audit_action ON audit_log(action, created_at DESC);
|
|
|
|
-- ============================================================
|
|
-- 초기 데이터
|
|
-- ============================================================
|
|
|
|
-- 레이어 그룹
|
|
INSERT INTO layer_group (name, name_en, icon, is_system, sort_order) VALUES
|
|
('사용자 레이어', 'User Layers', '📁', FALSE, 1),
|
|
('S-100 차세대 전자해도', 'S-100 ENC Layers', '🗺', TRUE, 2),
|
|
('물표 레이어', 'Vessel Layers', '🚢', TRUE, 3),
|
|
('해양안전 레이어', 'Maritime Safety', '🔴', TRUE, 4),
|
|
('연계 레이어', 'Integration Layers', '🔗', TRUE, 5);
|
|
|
|
-- 경계 구역
|
|
INSERT INTO boundary (name, name_en, boundary_type, organization) VALUES
|
|
('배타적 경제수역', 'EEZ', 'EEZ', '해양경찰청'),
|
|
('북방한계선', 'NLL', 'NLL', '해양경찰청'),
|
|
('영해 기선', 'Territorial Sea', 'TERRITORIAL', '해양경찰청'),
|
|
('인천해양경찰서 관할', 'Incheon KCG', 'JURISDICTION', '인천해양경찰서'),
|
|
('태안해양경찰서 관할', 'Taean KCG', 'JURISDICTION', '태안해양경찰서'),
|
|
('군산해양경찰서 관할', 'Gunsan KCG', 'JURISDICTION', '군산해양경찰서'),
|
|
('목포해양경찰서 관할', 'Mokpo KCG', 'JURISDICTION', '목포해양경찰서');
|
|
|
|
-- 기본 관리자
|
|
INSERT INTO app_user (username, name, rank, department, organization, role) VALUES
|
|
('admin', '김영수', '사무관', '정보통신과', '해양경찰청', 'ADMIN');
|