POSTGIS 线路规划和爆管分析

原创已于 2023-12-06 15:18:15 修改

· 506 阅读

3 ·

版权

文章标签：

#gis #postgis #爆管 #线路规划 #sql

于 2023-11-21 17:38:33 首次发布

爆管分析

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 上发布的服务新建视图那种可以动态传参