PostGIS 爆管分析之找出总阀门
这个算法算是被摒弃了，但是很多自己思考过后留下的成果，虽然不用了，留着做记录。
算法目的是为了发生爆管后找到总阀门，这里分了几个步骤：
1、找到爆管点所在管段
2、通过遍历找到爆管点所有影响的阀门
3、找到影响阀门中的上游阀门
4、在上游阀门中进行遍历，看相互关系，与其他阀门都联通的则视为总阀门
5、找出阀门中的总阀门
#考虑了一下，所有与爆点相连的上游阀门其实在爆管发生后都需要关闭。所以算法中4、5步，不需要了。
-- Function: test_getpoint7(character varying, double precision, double precision) -- DROP FUNCTION test_getpoint7(character varying, double precision, double precision); CREATE OR REPLACE FUNCTION test_getpoint7( IN tbl character varying, IN startx double precision, IN starty double precision) RETURNS TABLE(v_gid integer, v_res geometry, v_type integer) AS $BODY$ 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排除 v_down_where integer[];--where条件，将遍历到阀门的管段gid排除 up_temprow record ; --v_cost record;--记录阀门管段source（用于计算消耗，判断方向） m_cost integer; m_cost_value integer; temprow record; v_cost integer[]; res_source integer; res_tap_pipe text[]; m_tap_pipe text; idx_tap_pipe integer; --遍历结果游标 m_up_cost integer;--上游阀门 v_up_cost integer[];--上游阀门集合 res_main_pipe integer[];--总阀门集合 m_main_pipe integer;--总阀门 begin --查询离起点最近的线 --3857坐标系 --找起点15米范围内的最近线 execute 'select geom, source, target, ST_StartPoint(geom) as startpoint,ST_EndPoint(geom) as endpoint from ' ||tbl|| ' where ST_DWithin(geom,ST_Geometryfromtext(''point('|| startx ||' ' || starty ||')'',3857),15) order by ST_Distance(geom,ST_GeometryFromText(''point('|| startx ||' '|| starty ||')'',3857)) limit 1' into v_startLine, v_startSource ,v_startTarget, v_statpoint ,v_endpoint; IF(v_startLine is not null) THEN --查找上游阀门 v_up_idx = 0; v_up_source = 1; --寻找上游阀门 SELECT array_append(v_up_where, v_startSource) into v_up_where; --如果没有下级节点需要遍历 WHILE array_length(v_up_where,1) > 0 LOOP --游标归零 v_up_source = 0; --记录层级 v_up_idx = v_up_idx + 1; --获取当前层级节点 FOR up_temprow IN select zy1.gid,zy1.source,zy1.target from zy zy1 where source = any(v_up_where) or target = any(v_up_where) --select zy1.gid,zy1.source,zy1.target from zy zy1 where target = any(v_up_where)--找上游 LOOP --清空需要查的点 IF(v_up_source = 0) THEN v_up_where = null; END IF; --清空初始执行节点 v_startSource = 0; --标志执行有数据 v_up_source = 1; --查询管网上的点 select t.gid,t.geom from fm t where t.gid in ( select a.gid from fm a,(select c.* from zy c where c.gid = up_temprow.gid) b where ST_intersects(a.geom,b.geom) ) into v_uptap_gid, v_uptap_geom; raise notice '%' , 'UP---'||up_temprow.gid; --如果没查找到阀门，则继续往下查 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; ELSE raise notice '%' , up_temprow.source; --记录阀门所在管段source IF (v_cost @> ARRAY[up_temprow.source::integer]) THEN ELSE SELECT array_append(v_cost,up_temprow.source) into v_cost; SELECT array_append(v_cost,up_temprow.target) into v_cost; END IF; --insert into v_cost values (up_temprow.gid,up_temprow.source,v_uptap_gid,v_uptap_geom ); --insert into v_cost values (up_temprow.gid,up_temprow.target,v_uptap_gid,v_uptap_geom ); --insert into v_cost values (up_temprow.gid,v_uptap_geom ,v_uptap_gid); --return next v_cost; --insert into v_taps values (v_uptap_gid, v_uptap_geom, up_temprow.source); --v_cost = up_temprow; --执行返回结果 --阀门id，阀门图形要素，阀门类型（上游/下游） --return query --select v_uptap_gid as res_uptap_gid,v_uptap_geom as res_uptap_geom ,up_temprow.source as res_source; --raise notice '%' , 'res_tap_pipe---'||cast(res_tap_pipe as text); IF (res_tap_pipe is not null) THEN --SELECT array_append(res_tap_pipe,ARRAY[v_uptap_gid,up_temprow.source]) into res_tap_pipe; --select cast(res_tap_pipe as text); select res_tap_pipe || ARRAY[v_uptap_gid||','||cast(v_uptap_geom as text)||','||up_temprow.source] into res_tap_pipe; ELSE select ARRAY[v_uptap_gid||','||cast(v_uptap_geom as text)||','||up_temprow.source] into res_tap_pipe; END IF; END IF; --return next; END LOOP; END LOOP; --raise notice '%' , v_cost; raise notice '%' , 'res_tap_pipe---'||cast(res_tap_pipe as text); --return query select * from v_cost; raise notice '%' , 'v_cost---'||cast(v_cost as text); --查找上游阀门 FOREACH m_cost IN ARRAY v_cost LOOP SELECT count(*) FROM pgr_dijkstraCost('select gid as id, source, target, length as cost, reverse_cost from zy',m_cost, ARRAY[v_startSource,v_startTarget], true) where agg_cost >= 9999999 into m_cost_value; raise notice '%' , 'm_cost_value---'||cast(m_cost_value as text); --如果没有消耗大于9999999的（阈值），则认为是上游阀门 IF(m_cost_value = 0) THEN --判断上游阀门间是否有上下游关系 SELECT array_append(v_up_cost,m_cost) into v_up_cost; END IF; --IF(m_cost_value = array_length(v_cost,1)/2 - 1) THEN ----return query ---- select * from v_taps where source = m_cost; ----raise notice '%' , 'res_tap_pipe---'||cast(unnest(res_tap_pipe) as text); --FOREACH m_tap_pipe IN ARRAY res_tap_pipe --LOOP --raise notice '%' , 'm_cost---'||cast(m_cost as text) ; --IF (split_part(m_tap_pipe, ',', 3)::integer = m_cost) THEN ----阀门id，阀门图形要素，阀门类型（上游/下游） --return query --select split_part(m_tap_pipe, ',', 1)::integer as res_uptap_gid,split_part(m_tap_pipe, ',', 2)::geometry as res_uptap_geom ,split_part(m_tap_pipe, ',', 3)::integer as res_source; --END IF; --idx_tap_pipe = idx_tap_pipe+1; --END LOOP; --END IF; END LOOP; raise notice '%' , '上游阀门---'||cast(v_up_cost as text); --循环遍历找主阀门 FOREACH m_up_cost IN ARRAY v_up_cost LOOP SELECT count(*) FROM pgr_dijkstraCost('select gid as id, source, target, length as cost, reverse_cost from zy',m_up_cost, v_up_cost, true) where agg_cost >= 9999999 into m_cost_value; IF(m_cost_value = 0) THEN SELECT array_append(res_main_pipe,m_up_cost) into res_main_pipe; END IF; END LOOP; raise notice '%' , 'res_main_pipe---'||cast(res_main_pipe as text); IF(res_main_pipe IS NULL OR array_length(res_main_pipe,1)=0) THEN res_main_pipe = v_up_cost; END IF; FOREACH m_main_pipe IN ARRAY res_main_pipe LOOP FOREACH m_tap_pipe IN ARRAY res_tap_pipe LOOP raise notice '%' , 'm_cost---'||cast(m_cost as text) ; IF (split_part(m_tap_pipe, ',', 3)::integer = m_main_pipe) THEN --阀门id，阀门图形要素，阀门类型（上游/下游） return query select split_part(m_tap_pipe, ',', 1)::integer as res_uptap_gid,split_part(m_tap_pipe, ',', 2)::geometry as res_uptap_geom ,split_part(m_tap_pipe, ',', 3)::integer as res_source; END IF; END LOOP; END LOOP;
END IF;
--查找总阀门 --FOR temprow IN -- select * from v_cost --LOOP -- SELECT count(*) FROM pgr_dijkstraCost('select gid as id, source, target, length as cost, reverse_cost from zy',m_cost, v_cost, true) where agg_cost <9999999 into m_cost_value; -- if(m_cost_value == ) --END LOOP;
end; $BODY$ LANGUAGE plpgsql VOLATILE STRICT COST 100 ROWS 1000; ALTER FUNCTION test_getpoint7(character varying, double precision, double precision) OWNER TO postgres;
