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;