爆管分析
CREATE OR REPLACE FUNCTION "sde"."a_bao_line_newer"("tbl" varchar, "startx" float8, "starty" float8, "tenant_id" int4)
RETURNS TABLE("v_gid" int4, "v_res" "public"."geometry") AS $BODY$BEGIN
-- Routine body goes here...
--声明变量
declare
v_startLine geometry;--离起点最近的线
v_startTarget integer;--距离起点最近线的终点
v_startSource integer;
v_statpoint geometry;--在v_startLine上距离起点最近的点
v_endpoint geometry;--在v_endLine上距离终点最近的点
v_up_source integer;--游标,记录是否有记录
v_up_idx integer;--记录遍历到多少层级
v_uptap_gid integer;--上游阀门gid
v_uptap_geom geometry;--上游阀门要素
v_all_where integer[];--记录所有查询过的管段
v_up_where integer[];--where条件,将遍历到阀门的管段gid排除
up_temprow record ;
test integer;
begin
--查询基础数据
execute 'select shape, pgr_source, pgr_target, ST_StartPoint(shape) as startpoint,ST_EndPoint(shape) as endpoint from cloud_pipe_line_1_1 where ST_DWithin(shape,ST_Geometryfromtext(''point('|| startx ||' ' || starty ||')'',3857),15) AND tenant_id='''|| tenant_id ||'''
order by ST_Distance(shape,ST_GeometryFromText(''point('|| startx ||' '|| starty ||')'',3857)) limit 1'
into v_startLine, v_startSource ,v_startTarget, v_statpoint ,v_endpoint;
raise notice '开始点%' , v_startSource;
--raise notice '%' , 'source';
--raise notice '%' , v_startSource;
--raise notice '%' , 'target';
--raise notice '%' , v_startTarget;
--如果可以查询到起始点
IF(v_startTarget is not null) THEN
--查找上游阀门
v_up_idx = 0;
v_up_source = 1;
test = 0;
--往下游开始查
SELECT array_append(v_up_where, v_startTarget) into v_up_where;
--开始循环查询 【target】 判断数组长度大于0 时候
WHILE array_length(v_up_where,1) > 0
LOOP
--游标归零
v_up_source = 0;
v_up_idx = v_up_idx + 1;
--如果查不到了终止循环
IF(v_up_idx > 1000) THEN
v_up_where = null;
END IF;
--获取当前层级节点
FOR up_temprow IN
--往下游查 【target】 应该是 pgr_source 等于上一个 节点
select zy1.objectid as gid,zy1.pgr_source as source ,zy1.pgr_target as target,zy1.shape from cloud_pipe_line_1_1 zy1 where pgr_source = any(v_up_where)
LOOP
raise notice '递归%' , up_temprow.gid;
IF(v_up_source = 0) THEN
v_up_where = null;
END IF;
--标志执行有数据
v_up_source = 1;
--查询出结果
-- 如果不存在 添加进去
IF (v_up_where @> ARRAY[up_temprow.target::integer] OR v_all_where @> ARRAY[up_temprow.target::integer]) THEN
ELSE
--进入下一次循环
SELECT array_append(v_up_where,up_temprow.target) into v_up_where;
SELECT array_append(v_all_where,up_temprow.target) into v_all_where;
END IF;
return query select up_temprow.gid as res_uptap_gid,up_temprow.shape as res_uptap_geom ;
END LOOP;
END LOOP;
raise notice '%' , v_up_idx;
--return query SELECT 1111 as res_uptap_gid, z.shape as res_uptap_geom FROM cloud_pipe_line_bao z WHERE pgr_target =v_startSource ;
END IF;
end;
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000
CREATE OR REPLACE FUNCTION "sde"."a_bao_newer"("tbl" varchar, "startx" float8, "starty" float8, "tenantid" int4)
RETURNS TABLE("v_gid" int4, "v_type" varchar, "v_res" "public"."geometry") AS $BODY$BEGIN
-- Routine body goes here...
--声明变量
declare
v_startLine geometry;--离起点最近的线
v_startTarget integer;--距离起点最近线的终点
v_startSource integer;
v_statpoint geometry;--在v_startLine上距离起点最近的点
v_endpoint geometry;--在v_endLine上距离终点最近的点
v_up_source integer;--游标,记录是否有记录
v_up_idx integer;--记录遍历到多少层级
v_uptap_gid integer;--上游阀门gid
v_data_type VARCHAR;
v_uptap_geom geometry;--上游阀门要素
v_all_where integer[];--记录所有查询过的管段
v_up_where integer[];--where条件,将遍历到阀门的管段gid排除
up_temprow record ;
test integer;
v_line_gid INTEGER;
v_line_geom geometry;
v_online_device geometry;
v_online_sline geometry;
v_startId INTEGER;
v_sore BOOLEAN;
begin
--查询基础数据
execute 'select objectid, shape, pgr_source, pgr_target, ST_StartPoint(shape) as startpoint,ST_EndPoint(shape) as endpoint from cloud_pipe_line_1_1 where ST_DWithin(shape,ST_Geometryfromtext(''point('|| startx ||' ' || starty ||')'',3857),15) AND tenant_id='''|| tenantid ||'''
order by ST_Distance(shape,ST_GeometryFromText(''point('|| startx ||' '|| starty ||')'',3857)) limit 1'
into v_startId ,v_startLine, v_startSource ,v_startTarget, v_statpoint ,v_endpoint;
raise notice '%' ,v_startSource;
raise notice '%' ,v_startTarget;
--如果可以查询到起始点
IF(v_startLine is not null) THEN
raise notice '%' ,v_startLine;
-- 查询有没有自相交得设备 获取到点信息
select a.shape from cloud_device_prod_1 a,(select c.* from cloud_pipe_line_1_1 c where c.objectid = v_startId) b where ST_intersects(st_buffer(a.shape,3),b.shape) limit 1 into v_online_device;
IF(v_online_device is not null) THEN
--能查询到爆管点到起点得线
select st_makeline(st_startpoint(shape), st_closestpoint(shape,st_setsrid(st_makepoint(startx,starty),3857))) as endline FROM cloud_pipe_line_1_1 WHERE objectid =v_startId into v_online_sline;
--SELECT ST_intersects(st_buffer(v_online_device,3),v_online_sline) INTO v_sore;
IF(ST_intersects(st_buffer(v_online_device,3),v_online_sline)) THEN
--如果是在左侧
SELECT array_append(v_up_where, v_startTarget) into v_up_where;
ELSE
--如果是在右侧
SELECT array_append(v_up_where, v_startSource) into v_up_where;
END IF;
ELSE
--其他情况 两个任取一个都行
SELECT array_append(v_up_where, v_startTarget) into v_up_where;
END IF;
raise notice '这是返回决定%s' ,v_sore;
--查找上游阀门
v_up_idx = 0;
v_up_source = 1;
test = 0;
--起始点作为查询条件
--SELECT array_append(v_up_where, v_startSource) into v_up_where;
--SELECT array_append(v_up_where, v_startTarget) into v_up_where;
--开始循环查询
WHILE array_length(v_up_where,1) > 0
LOOP
--游标归零
v_up_source = 0;
--IF(v_up_idx = 0) THEN
--SELECT array_append(v_up_where, v_startSource) into v_up_where;
--SELECT array_append(v_up_where, v_startTarget) into v_up_where;
--ELSE
--v_up_where = null;
--END IF;
--记录层级
v_up_idx = v_up_idx + 1;
--如果查不到了终止循环
IF(v_up_idx > 50) THEN
v_up_where = null;
END IF;
--获取当前层级节点
FOR up_temprow IN
--查询起始点的相邻 就是上下游
select zy1.objectid as gid,zy1.pgr_source as source ,zy1.pgr_target as target,shape,section_no from cloud_pipe_line_1_1 zy1 where tenant_id=cast(tenantid as VARCHAR) AND (pgr_source = any(v_up_where) or pgr_target = any(v_up_where) )
LOOP
test = test +1;
--清空需要查的点
IF(v_up_source = 0) THEN
v_up_where = null;
END IF;
--清空初始执行节点
v_startSource = 0;
--标志执行有数据
v_up_source = 1;
--查询管网上的点
select t.objectid,t."is_conduit_up" ,t.shape from cloud_device_prod_1 t where t.tenant_id = cast(tenantid as VARCHAR) and t.objectid in (
select a.objectid from cloud_device_prod_1 a,(select c.* from cloud_pipe_line_1_1 c where c.objectid = up_temprow.gid AND tenant_id = cast(tenantid as VARCHAR)) b where ST_intersects(st_buffer(a.shape,3),b.shape)
) into v_uptap_gid,v_data_type, v_uptap_geom;
--如果没查找到阀门,则继续往下查
IF(v_uptap_gid is null) then
--source去重,判断如果数组中已有,则不添加
IF (v_up_where @> ARRAY[up_temprow.source::integer] OR v_all_where @> ARRAY[up_temprow.source::integer]) THEN
ELSE
SELECT array_append(v_up_where,up_temprow.source) into v_up_where;
SELECT array_append(v_all_where,up_temprow.source) into v_all_where;
END IF;
--target去重,判断如果数组中已有,则不添加
IF (v_up_where @> ARRAY[up_temprow.target::integer] OR v_all_where @> ARRAY[up_temprow.target::integer]) THEN
ELSE
SELECT array_append(v_up_where,up_temprow.target) into v_up_where;
SELECT array_append(v_all_where,up_temprow.target) into v_all_where;
END IF;
-- 返回中间管线信息
return query SELECT up_temprow.gid as res_uptap_gid,up_temprow.section_no as v_type,up_temprow.shape as res_uptap_geom;
ELSE
--执行返回结果 阀门信息
return query
select v_uptap_gid as res_uptap_gid,v_data_type as v_type,v_uptap_geom as res_uptap_geom ;
--多返回一条数据 涵盖阀门所在管线
return query SELECT up_temprow.gid as res_uptap_gid,up_temprow.section_no as v_type,up_temprow.shape as res_uptap_geom;
END IF;
--return next;
END LOOP;
END LOOP;
END IF;
end;
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000
线路规划
SELECT objectid,shape FROM cloud_pipe_line_1 WHERE objectid in( select id2 from pgr_dijkstra('select objectid as id, pgr_source as source,pgr_target as target,pgr_length as cost FROM cloud_pipe_line_1 WHERE pgr_source is not null',%start%,%end%,false,false))
这个是直接在geoServer 上发布的服务新建视图那种 可以动态传参