pipe-ner-server/pipe-network-service/sql/water_supply_facility_gis.sql

-- 供水设施 GIS 一张图：WGS84 坐标字段增量脚本（KingbaseES / PostgreSQL 兼容）
-- 可重复执行；仅增加可空字段、注释和索引，不修改或删除现有业务数据。

ALTER TABLE app_user.water_source_info
    ADD COLUMN IF NOT EXISTS longitude numeric(11,8),
    ADD COLUMN IF NOT EXISTS latitude numeric(10,8);
COMMENT ON COLUMN app_user.water_source_info.longitude IS 'WGS84经度';
COMMENT ON COLUMN app_user.water_source_info.latitude IS 'WGS84纬度';
CREATE INDEX IF NOT EXISTS idx_water_source_info_lon_lat ON app_user.water_source_info (longitude, latitude);

ALTER TABLE app_user.water_plant_info
    ADD COLUMN IF NOT EXISTS longitude numeric(11,8),
    ADD COLUMN IF NOT EXISTS latitude numeric(10,8);
COMMENT ON COLUMN app_user.water_plant_info.longitude IS 'WGS84经度';
COMMENT ON COLUMN app_user.water_plant_info.latitude IS 'WGS84纬度';
CREATE INDEX IF NOT EXISTS idx_water_plant_info_lon_lat ON app_user.water_plant_info (longitude, latitude);

ALTER TABLE app_user.water_pump_station
    ADD COLUMN IF NOT EXISTS longitude numeric(11,8),
    ADD COLUMN IF NOT EXISTS latitude numeric(10,8);
COMMENT ON COLUMN app_user.water_pump_station.longitude IS 'WGS84经度';
COMMENT ON COLUMN app_user.water_pump_station.latitude IS 'WGS84纬度';
CREATE INDEX IF NOT EXISTS idx_water_pump_station_lon_lat ON app_user.water_pump_station (longitude, latitude);

ALTER TABLE app_user.water_user_info
    ADD COLUMN IF NOT EXISTS longitude numeric(11,8),
    ADD COLUMN IF NOT EXISTS latitude numeric(10,8);
COMMENT ON COLUMN app_user.water_user_info.longitude IS 'WGS84经度';
COMMENT ON COLUMN app_user.water_user_info.latitude IS 'WGS84纬度';
CREATE INDEX IF NOT EXISTS idx_water_user_info_lon_lat ON app_user.water_user_info (longitude, latitude);

ALTER TABLE app_user.water_pipe_info
    ADD COLUMN IF NOT EXISTS start_longitude numeric(11,8),
    ADD COLUMN IF NOT EXISTS start_latitude numeric(10,8),
    ADD COLUMN IF NOT EXISTS end_longitude numeric(11,8),
    ADD COLUMN IF NOT EXISTS end_latitude numeric(10,8);
COMMENT ON COLUMN app_user.water_pipe_info.start_longitude IS '管网起点WGS84经度';
COMMENT ON COLUMN app_user.water_pipe_info.start_latitude IS '管网起点WGS84纬度';
COMMENT ON COLUMN app_user.water_pipe_info.end_longitude IS '管网终点WGS84经度';
COMMENT ON COLUMN app_user.water_pipe_info.end_latitude IS '管网终点WGS84纬度';
CREATE INDEX IF NOT EXISTS idx_water_pipe_info_start_end_lon_lat
    ON app_user.water_pipe_info (start_longitude, start_latitude, end_longitude, end_latitude);

-- 以下菜单/按钮权限 SQL 需在目标库核对供水模块父菜单后手动启用：
-- waterSupply:pipeNetwork:statistics
-- waterSupply:pipeNetwork:statistics:export
-- waterSupply:gis:map
-- waterSupply:waterSource:export
-- waterSupply:waterPlant:export
-- waterSupply:waterPumpStation:export
-- waterSupply:waterPipe:export
-- waterSupply:waterUser:export