sanya-data-management-back/sql/pgsql/0_init.sql

-- 8.22
-- nacos analyse服务添加下

#
配置 feign 默认请求时间仅几秒钟
，配置请求时间长一些(毫秒)
feign:
  client:
    config:
      default:
        connectTimeout: 600000
        readTimeout: 600000
    default-to-properties: false
  hystrix:
    enabled: false    # 不要开启hystrix
，会导致超时配置不生效

-- 8.27

-- 新增
-- base.t_fzss_fzxz_ai_district

-- 修改
-- base.t_fzss_zhxz_file

-- 添加函数，去除小锐角
drop function remove_smallangle;

CREATE OR REPLACE FUNCTION  remove_smallangle(jsonarg json)
RETURNS VARCHAR AS $$
DECLARE
sql TEXT;
    records VARCHAR;
begin
execute format('
create table %s as
with t0 as(select id, st_removerepeatedPoints(geom,1e-6) geom from %s sa) ,
t1 as(
select id,(st_dump(geom)).path polygonPath, st_dumppoints((st_dump(geom)).geom)  poly  from t0
),t2 as (
select id,polygonPath[1] polyID, (poly).path[1] ringId,(poly).path[2] pointID, (poly).geom from t1)
, t3 as (
select id,polyid, ringid, max(pointid) over (partition  by id,polyid,ringid) maxid,
 lag(pointid) over() lagid, pointid, lead(pointid) over() leadid ,
 st_angle(lag(geom) over() , geom, lead(geom) over() ) angle, --st_distance(lag(geom) over() , geom) dist,
 geom
 from t2)
 , t4 as (
 select * from t3
 where angle is null or not( abs(angle) < %s or ( ''%s'' like ''是'' and abs(angle-pi()) < %s ) or abs(angle - 2*pi() ) < %s) )
 ,t41 as(
  select id,polyid,ringid, array_agg(geom) points from t4 group by id,polyid,ringid
  ),t42 as (
 select id,polyid,ringid,array_length(points, 1) cnt, st_angle(points[2], points[1],points[array_length(points, 1)-1] ) angle,points from t41)
 , t5 as(
  select id,polyid,ringid, st_makeline(
     case when ( abs(angle) < %s or  ( ''%s'' like ''是'' and abs(angle-pi()) < %s ) or abs(angle - 2*pi() ) < %s )
          then array_append( points[2:array_length(points, 1)-1], points[2]) else points end ) geom
     from t42)
 , t6 as(
 select id, polyid, array_agg(geom) geom from t5 group by id, polyid)
 ,t7 as (
 select id, polyid, case array_length(geom,1) when 1 then st_makepolygon( geom[1]) else st_makepolygon(geom[1], geom[2:]) end  geom from t6
 ) select id,st_collect(geom)  from t7 group by id
', jsonarg->>'输出表',jsonarg->>'输入表',
               jsonarg->>'容差',jsonarg->>'去除平直线上的顶点',jsonarg->>'容差',jsonarg->>'容差',jsonarg->>'容差',jsonarg->>'去除平直线上的顶点',jsonarg->>'容差',jsonarg->>'容差');
return 'OK';
END;
$$ LANGUAGE plpgsql;

-- 20240829
DROP TABLE IF EXISTS base.t_gdbh_mx;

CREATE TABLE base.t_gdbh_mx (
                                bsm varchar(50) NOT NULL,
                                bsmmc varchar(255) DEFAULT NULL::character varying NULL,
                                pbsm varchar(50) NOT NULL, -- 父级审核项标识码
                                kzxjb numeric(2) DEFAULT NULL::numeric NULL,
                                xssx numeric(3) DEFAULT NULL::numeric NULL, -- 显示顺序
                                status numeric(1) DEFAULT NULL::numeric NULL, -- 可用状态 1可用 0不可用
                                "parameter" varchar(500) NULL, -- json参数
                                CONSTRAINT t_gdbh_mx_pkey PRIMARY KEY (bsm)
);
COMMENT ON TABLE base.t_gdbh_mx IS '耕地保护模型';

-- Column comments

COMMENT ON COLUMN base.t_gdbh_mx.bsm IS '模型项标识码';
COMMENT ON COLUMN base.t_gdbh_mx.bsmmc IS '模型项标识码名称';
COMMENT ON COLUMN base.t_gdbh_mx.pbsm IS '父级审核项标识码';
COMMENT ON COLUMN base.t_gdbh_mx.kzxjb IS '控制线级别0前端显示，不选中 1前端显示选中 2前端不显示，后台默认选中';
COMMENT ON COLUMN base.t_gdbh_mx.xssx IS '显示顺序';
COMMENT ON COLUMN base.t_gdbh_mx.status IS '可用状态 1可用 0不可用';
COMMENT ON COLUMN base.t_gdbh_mx."parameter" IS 'json参数';




DROP TABLE IF EXISTS base.t_gdbh_mx_yz;

CREATE TABLE base.t_gdbh_mx_yz (
                                   mx_bsm varchar(50) NOT NULL,
                                   bsm varchar(50) NOT NULL,
                                   bsmmc varchar(255) DEFAULT NULL::character varying NULL,
                                   status numeric(1) DEFAULT NULL::numeric NULL,
                                   CONSTRAINT t_gdbh_mx_yz_pkey PRIMARY KEY (bsm)
);
COMMENT ON TABLE base.t_gdbh_mx_yz IS '耕地保护模型对应的因子';

COMMENT ON COLUMN base.t_gdbh_mx_yz.bsm IS '模型对应的标识码';
COMMENT ON COLUMN base.t_gdbh_mx_yz.bsm IS '因子标识码';
COMMENT ON COLUMN base.t_gdbh_mx_yz.bsmmc IS '因子标识码名称';
COMMENT ON COLUMN base.t_gdbh_mx_yz.status IS '可用状态 1可用 0不可用';