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

-- 7.24

-- Auto-generated SQL script #202407240932
UPDATE base.t_jsc_sql
SET jsc_sql='select #{id} as id,round((coalesce(sum(xz_st),0)*0.01)::NUMERIC,2)st_mj, round((coalesce(sum(xz_sjd),0)*0.01)::NUMERIC,2)sjd_mj,round((coalesce(sum(xz_hd),0)*0.01)::NUMERIC,2)hd_mj from ( SELECT xz_st, xz_sjd, xz_hd FROM vector."AFTER_JCSH" x where xzqdm like #{id}||''%'' )t'
WHERE id='40daac2a-8a4a-4e38-9e66-973615b6b5d5';

UPDATE base.t_jsc_sql
SET jsc_sql='select #{id} as id,dj,round((coalesce(sum(tbdlmj),0)/ 1000000)::numeric,2)tbmj from( select case when gjlyd>=1 and gjlyd <= 4 then 1 when gjlyd>=5 and gjlyd <= 8 then 2 when gjlyd>=9 and gjlyd <= 12 then 3 else 4 end dj, tbdlmj from vector."TB_GDZL" x where xzqhdm like #{id} || ''%'' )t group by dj order by dj'
WHERE id='3cfe4498-b451-4833-bb9e-7ff55b10472c';

-- 新增表
vector."TB_GDZL"
vector."TB_HDGLX"
vector."TB_GYL"

-- 修改表结构和数据，需要同步到现场
vector."AFTER_JCSH"

-- 添加影像数据
--  D:/onemapfile/15影像.tif

--修改表数据，需要同步到现场
vector."AFTER_BJXM_SZ"

-- 只同步数据
base."t_fzss_fzxz_factor"

-- 需要修改的nacos
-- onemap-spatial-dev.yml
-- onemap-analyse-dev.yml

-- 添加多地块合并方法
drop function if exists area_in_range;

CREATE OR REPLACE FUNCTION  area_in_range(jsonarg json, systemarg json)
RETURNS VARCHAR AS $$
DECLARE
sql TEXT;
    records VARCHAR;
begin
execute format('
	insert into %s
	with t as(
	select  array[id] idarray, st_area (st_transform(geom, 3857) ) /2000*3  area, ''%s'' feature , 0.0 weight,geom
	from %s order by area
	)
	select t.* from t where  area between %s and %s
	limit 3; ', systemarg->>'选址结果',systemarg->>'选址方式',systemarg->>'初步结果',jsonarg->>'面积下限', jsonarg->>'面积上限');
return 'OK';
END;
$$ LANGUAGE plpgsql;

drop function if exists block_merge;

CREATE OR REPLACE FUNCTION  block_merge(jsonarg json , systemarg json)
RETURNS VARCHAR AS $$
DECLARE
sql TEXT;
    records VARCHAR;
BEGIN
with a as(select jsonb(jsonarg) arg),
     an as (select  regexp_split_to_table( substring('abcdefghijklmnopqrstuvwxyz',1, cast(a.arg->>'小地块最多合并块数' as integer) ), '') alias from a),
     t1 as ( select  string_agg( an.alias || '.id', ',') idarr,
                     string_agg( an.alias || '.area' , ' + ') areaarr,
                     string_agg ('t2 as ' || an.alias , ',') tblarr,
                     string_agg( an.alias || '.geom', ',' ) geomarr
             from an),
     t2 as (select alias, lead(alias) over() n from an ),
     t3 as ( select string_agg( alias || '.id < ' || n ||'.id', ' and ') cond from t2 where n notnull),
    in_t1 as (select format('select id, st_area (st_transform(geom, 3857) ) /2000*3  area, geom from %s order by area ', systemarg->>'初步结果' ) t ) ,
    in_t2 as (select format('select id,area,geom from t1 where area < %s', a.arg->>'面积下限') t from a) ,
    in_t3 as (select format(
    'select ARRAY[%s] idarray, %s area ,st_collect(ARRAY[%s]) geom
        from %s
        where %s', idarr, areaarr,geomarr , tblarr, cond ) t from t1,t3)
select
    format('insert into %s
	with t1 as (%s),
		 t2 as (%s),
		 t3 as(%s)
	select idarray,area, ''%s'' feature, 0.0 weight, geom from t3
		where area between %s and %s
		order by area
		limit 3',systemarg->>'选址结果',
           in_t1.t, in_t2.t, in_t3.t,systemarg->>'选址方式',a.arg->>'面积下限',a.arg->>'面积上限'  )  into  sql from in_t1, in_t2, in_t3,a;
EXECUTE sql;
return 'OK';
END;
$$ LANGUAGE plpgsql;


drop function if exists big_area_block;
CREATE OR REPLACE FUNCTION  big_area_block(jsonarg json, systemarg json)
RETURNS VARCHAR AS $$
DECLARE
sql TEXT;
    records VARCHAR;
begin
execute format('
insert into %s
with t as(
select array[id] idarray, st_area (st_transform(geom, 3857) ) /2000*3  area, ''%s'' feature ,0 weight, geom
from %s order by area
)select * from t
where area > %s order by area limit 3;

with recursive cte(arealimit,tolerance, movedirection, area, grade, movescale, interarea, idarray,width,height,geom,env) as(
select cast(arg->>''面积上限'' as float8) arealimit, (cast(arg->>''面积上限'' as float8)  - cast(arg->>''面积下限'' as float8) ) / 100.0 tolerance,
        array[sign(cast(arg->>''截取地块X方位'' as integer)), sign(cast(arg->>''截取地块Y方位'' as integer))] movedirection,   area,
        1 grade, 0.5::float4 movescale,
        st_area(st_intersection(st_transform(geom,3857), st_translate(st_envelope(st_transform(geom,3857)), (st_xmax(st_transform(geom,3857)) - st_xmin(st_transform(geom,3857)))*abs(sign(cast(arg->>''截取地块X方位'' as integer)))*0.5,
                                                        (st_ymax(st_transform(geom,3857)) - st_ymin(st_transform(geom,3857)))*abs(sign(cast(arg->>''截取地块Y方位'' as integer)))*0.5) ) ) *3/2000 interarea ,
        idarray, (st_xmax(st_transform(geom,3857)) - st_xmin(st_transform(geom,3857))) width,
	                     (st_ymax(st_transform(geom,3857)) - st_ymin(st_transform(geom,3857))) height,
	                     st_transform(geom,3857) geom,
	                     st_envelope(st_transform(geom,3857)) env
	from vector.选址结果, jsonb(''%s'') arg
    where feature like ''%s''
union all
select arealimit, tolerance, movedirection, area,  grade + 1 grade,
          movescale + (1.0/power(2, (grade + 1) )*(case when arealimit < interarea then 1 else -1 end)  )::float4  movescale,
       st_area(st_intersection(geom, st_translate(env,
            width*(movescale + (1.0/power(2, (grade + 1) )*(case when arealimit < interarea then 1 else -1 end)  )::float4)*movedirection[1],
            height*(movescale + (1.0/power(2, (grade + 1) )*(case when arealimit < interarea then 1 else -1 end)  )::float4)*movedirection[2])
           )
         ) *3/2000 interarea ,
idarray,width,height,geom,env from cte
where grade < 16 and abs(interarea - arealimit) > tolerance
),
t as(
select idarray, st_transform( st_collectionextract(  st_intersection(geom, st_translate(env,
            width*(movescale::float4)*movedirection[1],
            height*(movescale::float4)*movedirection[2])
           ) ,3), 4326) geom
           from cte order by grade DESC limit 1)
update %s f set geom = t.geom from t
where  feature like ''%s'' and f.idarray = t.idarray  and t.geom is not null
', systemarg->>'选址结果',systemarg->>'选址方式', systemarg->>'初步结果', jsonarg->>'面积上限',jsonarg, systemarg->>'选址方式', systemarg->>'选址结果', systemarg->>'选址方式' );
return 'OK';
END;
$$ LANGUAGE plpgsql;