-- 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');