pgrouting路径分析（任意两点之间的最短路径）

drop function if exists pro_short_path2(varchar, float, float, float, float, float);
create function pro_short_path2(tbl varchar, startx float, starty float, endx float, endy float, i_distance float)
returns geometry as
$$
    declare
v_startPointSeg geometry[];--起点所在线两侧的折线
v_endPointSeg geometry[];  --终点所在线两侧的折线
v_startPointSegLen float[];
v_endPointSegLen float[];
v_startPointOut boolean[];
v_endPointIn boolean[];
v_startNodeId integer[];
v_endNodeId integer[];
   
v_spheroid  spheroid;
v_pstart geometry; --起点
v_pend geometry;  --终点
v_perStart float;
v_perEnd float;
v_lstart geometry;--离起点最近的线
v_lend geometry;--离终点最近的线
v_statpoint geometry;--在v_lstart上距离起点最近的点
v_endpoint geometry;--在v_lend上距离终点最近的点
--缓冲距离起点或终点最近的线段上点,用于判断是否相交
v_sbuffer geometry;
v_ebuffer geometry; 


v_sGid integer;
v_sSource integer;
v_sTarget integer;
v_sOneway varchar;

v_eGid integer;
v_eSource integer;
v_eTarget integer;
v_eOneway varchar;

--最短路径起点和终点信息及几何对象
v_path pgr_costResult;
v_shorts pgr_costResult[];
v_line geometry;
v_geoms geometry[];


v_i integer;
v_count integer;
v_curs cursor (tbl varchar, isource integer, itarget integer) 
for select a.seq,a.id1,a.id2,a.cost,ST_LineMerge(b.geom) from 
pgr_dijkstra('select gid as id, source::integer, target::integer, 
cost::double precision AS cost, reverse_cost::double precision AS reverse_cost 
from changsharoad_connected', 
isource, itarget, true, true)a left join changsharoad_connected b 
ON (a.id2=b.gid) order by a.seq asc;


v_res geometry;   --dijkstra最短路径
v_resLen float;
v_shPath geometry;--最终结果
v_shPathLen float; --最短路径长度

v_results text;


v_startNodeIndex integer;
v_endNodeIndex integer;

begin
v_shPath := null;
v_shPathLen := 1000000;
v_spheroid := 'SPHEROID["WGS84", 6378137, 298.257223563]' ;  --WGS84椭球体参数定义
v_pstart := ST_GeomFromText('point(' || startx || ' ' || starty|| ')',4326);--ST_SetSRID(ST_GeomFromText(i_start),4326);
v_pend := ST_GeomFromText('point(' || endx || ' ' || endy|| ')',4326);--ST_SetSRID(ST_GeomFromText(i_end),4326);
--查询i_distance米范围内离起点最近的线
execute 'select ST_SetSRID(ST_LineMerge(geom), 4326),gid, source, target, oneway  from ' ||tbl||  
   ' where   
   ST_DWithin(geom::geography ,ST_Geometryfromtext(''point('|| startx ||' ' || starty||')'')::geography,' || i_distance || ' ,true)
   order by ST_Distance(geom,ST_Geometryfromtext(''point('|| startx ||' ' || starty||')''))  limit 1'   
   into v_lstart, v_sGid, v_sSource, v_sTarget, v_sOneway;
       
--查询i_distance米范围内离终点最近的线
execute 'select ST_SetSRID(ST_LineMerge(geom), 4326),gid, source, target, oneway  from ' ||tbl||  
   ' where   
   ST_DWithin(geom::geography ,ST_Geometryfromtext(''point('|| endx ||' ' || endy||')'')::geography,' || i_distance || ' ,true)
   order by ST_Distance(geom,ST_Geometryfromtext(''point('|| endx ||' ' || endy||')''))  limit 1'   
   into v_lend, v_eGid, v_eSource, v_eTarget, v_eOneway;  
   
if (v_lstart is null or v_lend is null) then
return null;--如果没找到最近的道路，就返回null
end if;
--计算起点和终点在最近的线段距离最近的点
select  ST_ClosestPoint(v_lstart, v_pstart ) into v_statpoint;
select  ST_ClosestPoint(v_lend, v_pend ) into v_endpoint;

select  ST_LineLocatePoint(v_lstart, v_statpoint) into v_perStart;  
select  ST_LineLocatePoint(v_lend, v_endpoint) into v_perEnd;
   
v_sbuffer := ST_Buffer(v_statpoint::geography, 3);
v_ebuffer := ST_Buffer(v_endpoint::geography, 3);

v_startPointOut := array_append(v_startPointOut, (v_sOneway = 'B' OR v_sOneway = 'T'));
v_startPointOut := array_append(v_startPointOut, (v_sOneway = 'B' OR v_sOneway = 'F'));
v_startNodeId := array_append(v_startNodeId, v_sSource);
v_startNodeId := array_append(v_startNodeId, v_sTarget);
v_startPointSeg := array_append(v_startPointSeg, ST_SetSRID(st_linesubstring(v_lstart, 0, v_perStart), 4326));
v_startPointSeg := array_append(v_startPointSeg, ST_SetSRID(st_linesubstring(v_lstart, v_perStart, 1), 4326));

v_endPointIn := array_append(v_endPointIn, (v_eOneway = 'B' OR v_eOneway = 'F'));
v_endPointIn := array_append(v_endPointIn, (v_eOneway = 'B' OR v_eOneway = 'T'));
v_endNodeId := array_append(v_endNodeId, v_eSource);
v_endNodeId := array_append(v_endNodeId, v_eTarget);
v_endPointSeg := array_append(v_endPointSeg, ST_SetSRID(st_linesubstring(v_lend, 0, v_perEnd), 4326));
v_endPointSeg := array_append(v_endPointSeg, ST_SetSRID(st_linesubstring(v_lend, v_perEnd, 1), 4326));


----raise notice  '准备开始循环 -------------%, %-------------', array_length(v_startPointOut, 1), array_length(v_endPointIn, 1);
v_startNodeIndex := 1;
v_results := '{"totalLen": -9999, "paths": []';
/*drop table if exists dijkstra_res;
CREATE TABLE dijkstra_res
(
gid integer,
seq integer,
geom geometry(LineString)
);*/


<<label1>>
while (v_startNodeIndex <= array_length(v_startPointOut, 1)) loop
v_endNodeIndex := 1;
----raise notice  'v_startPointOut[%]: %', v_startNodeIndex, v_startPointOut[v_startNodeIndex];
if (v_startPointOut[v_startNodeIndex] AND v_startNodeId[v_startNodeIndex] != v_endNodeId[v_endNodeIndex]) then
<<label2>>
while (v_endNodeIndex <= array_length(v_endPointIn, 1)) loop
----raise notice  'v_endPointIn[%]: %', v_endNodeIndex, v_endPointIn[v_endNodeIndex];
----raise notice  '   组 -------------  %-->%  -------------', v_startNodeId[v_startNodeIndex], v_endNodeId[v_endNodeIndex];
if (v_endPointIn[v_endNodeIndex]) then
--获取起点线段和编号

while array_length(v_shorts, 1) > 0 loop
v_shorts := array_remove(v_shorts, v_shorts[1]);
end loop;
while array_length(v_geoms, 1) > 0 loop
v_geoms := array_remove(v_geoms, v_geoms[1]);
end loop;

open v_curs($1, v_startNodeId[v_startNodeIndex], v_endNodeId[v_endNodeIndex]);
<<label3>>
loop
fetch v_curs into v_path.seq,v_path.id1,v_path.id2,v_path.cost,v_line;
--exit when not found;-- 假如没有检索到(主表)数据，结束循环处理
----raise notice  'v_path.seq,v_path.id1,v_path.id2,v_path.cost: %,%,%,%', v_path.seq,v_path.id1,v_path.id2,v_path.cost;
if not found then exit;end if;
v_shorts := array_append(v_shorts,v_path);
v_geoms := array_append(v_geoms, ST_SetSRID(v_line, 4326));
end loop label3;
close v_curs;
v_count := array_length(v_shorts, 1);
----raise notice  'v_count: %', v_count;


if( v_count > 1 ) then
if (v_sGid = v_eGid) then
----raise notice  '起点线段处理开始1';
v_line := ST_SetSRID(ST_MakeLine(v_pstart,v_statpoint), 4326);
if( false = ST_Intersects(v_geoms[1], v_sbuffer) ) then  --如果起点和起点线段没有相连，增加起始路段
----raise notice  '起点线段处理开始2';
--v_lstart := pro_truncation(v_lstart,v_geoms[1], v_statpoint,v_spheroid);
--v_geoms[1] :=  ST_Union(v_geoms[1],v_lstart);
v_geoms[1] := st_linemerge(ST_Union(array[v_line, v_startPointSeg[v_startNodeIndex], v_geoms[1]], 
v_endPointSeg[v_endNodeIndex], ST_SetSRID(ST_MakeLine(v_endpoint, v_pend), 4326)));
else    --如果起点和起点线段有相连，去除起始路段
select  ST_LineLocatePoint(v_geoms[1], v_statpoint) into v_perStart;  
select  ST_LineLocatePoint(v_geoms[1], v_endpoint) into v_perEnd;
v_geoms[1] = ST_LineSubstring(v_geoms[1], v_perStart, v_perEnd);
v_geoms[1] := st_linemerge(ST_Union(array[v_line, v_geoms[1], ST_SetSRID(ST_MakeLine(v_endpoint, v_pend), 4326)]));
end if;
else
--起点线段处理
----raise notice  '起点线段处理开始1';
v_line := ST_SetSRID(ST_MakeLine(v_pstart,v_statpoint), 4326);
if( false = ST_Intersects(v_geoms[1],v_sbuffer) ) then  --如果起点和起点线段没有相连，增加起始路段
----raise notice  '起点线段处理开始2';
--v_lstart := pro_truncation(v_lstart,v_geoms[1], v_statpoint,v_spheroid);
--v_geoms[1] :=  ST_Union(v_geoms[1],v_lstart);
v_geoms[1] := st_linemerge(ST_Union(array[v_line, v_startPointSeg[v_startNodeIndex], v_geoms[1]]));
else    --如果起点和起点线段有相连，去除起始路段
if (v_startNodeIndex = 1) then
----raise notice  '起点线段处理开始3';
v_geoms[1] := st_linemerge(ST_Union(array[v_line, v_startPointSeg[2]]));
else
----raise notice  '起点线段处理开始4';
v_geoms[1] := st_linemerge(ST_Union(array[v_line, v_startPointSeg[1]]));
end if;
end if;
----raise notice  '起点线段处理完成5';

--终点线段处理
----raise notice  '终点线段处理开始1';
v_line := ST_SetSRID(ST_MakeLine(v_endpoint, v_pend), 4326);
----raise notice  '%,%,%',ST_Intersects(v_geoms[v_count],v_ebuffer),ST_AsText(v_geoms[v_count]),ST_AsText(v_ebuffer);
if (false = ST_Intersects(v_geoms[v_count - 1], v_ebuffer)) then  --如果终点和终点线段没有相连
----raise notice  '终点线段处理开始2';
v_geoms[v_count - 1] := st_linemerge(ST_Union(array[v_geoms[v_count - 1], v_endPointSeg[v_endNodeIndex], v_line]));
else
----raise notice  '最后的线段：: %,%,%,%', v_shorts[v_count - 1].seq,v_shorts[v_count - 1].id1,v_shorts[v_count - 1].id2,v_shorts[v_count - 1].cost;
if (v_endNodeIndex = 1) then
----raise notice  '终点线段处理开始3';
v_geoms[v_count - 1] := st_linemerge(ST_Union(array[v_endPointSeg[2], v_line]));
else
----raise notice  '终点线段处理开始4';
v_geoms[v_count - 1] := st_linemerge(ST_Union(array[v_endPointSeg[1], v_line]));
end if;
end if;
----raise notice  '终点线段处理完成5';
end if;
v_res := ST_SetSRID(st_linemerge(ST_Union(v_geoms)), 4326);
----raise notice  '合并所有路段，用来计算总长';
--长度判断
v_resLen := ST_LengthSpheroid(v_res, v_spheroid);
----raise notice  '合并总长：%', v_resLen;
if (v_resLen < v_shPathLen) then
v_shPath := v_res;
/*delete from dijkstra_res;
insert into dijkstra_res(gid, seq, geom) values(1, 1, ST_GeomFromText(st_astext(v_res), 4326));*/
v_shPathLen := v_resLen;
end if;
end if;
end if;
v_endNodeIndex := v_endNodeIndex + 1;
end loop label2;
end if;
v_startNodeIndex := v_startNodeIndex + 1;
end loop label1;

--raise notice  '计算结果为: %', v_results;
return v_shPath;
end;
$$ language plpgsql;