基于pgrouting的任意两点间的最短路径查询函数二 - cytggit/Map-openlayers GitHub Wiki
前一篇查询任意两点间最短路径的函数，功能不完善。现在对该函数进行扩展，支持用户自己输入查询的数据库表，另外进行跨楼层的最短路径规划时，需要查询从A到楼梯口的最短路径和楼梯口到B的最短路径，这些都需要进行最短路径规划的时候能够自己选择数据表。
最短路径规划的处理步骤，首先要确定用户的出发点和目的地所在的道路，再在相应的道路上确定道路的节点，查找这两个节点之间的最短路径，最后再处理出发点和目的地到道路节点之间的路段。具体过程为：
（1）查找距离用户出发点最近的道路和该道路的终点T。
（2）查找距离用户目的地最近的道路和该道路的起点S。
（3）计算前两步找出的两点之间的最短路径。
（4）处理出发点和道路终点T以及目的去和道路起点S之间的路段。
代码如下（已经过调整和测试！）：
declare
v_startLine geometry;--离起点最近的线
v_endLine geometry;--离终点最近的线
v_startTarget integer;--距离起点最近线的终点
v_endSource integer;--距离终点最近线的起点
v_statpoint geometry;--在v_startLine上距离起点最近的点
v_endpoint geometry;--在v_endLine上距离终点最近的点
v_res geometry;--最短路径分析结果
v_perStart float;--v_statpoint在v_res上的百分比
v_perEnd float;--v_endpoint在v_res上的百分比
v_shPath geometry;--最终结果
tempnode float;
begin
--查询离起点最近的线
execute 'select geom ,target from ' ||tbl||
' where
ST_DWithin(geom,ST_Geometryfromtext(''point('|| startx ||' ' || starty||') '',4326),15)
order by ST_Distance(geom,ST_GeometryFromText(''point('|| startx ||' '|| starty ||')'',4326)) limit 1'
into v_startLine ,v_startTarget;
--查询离终点最近的线
execute 'select geom,source from ' ||tbl||
' where ST_DWithin(geom,ST_Geometryfromtext(''point('|| endx || ' ' || endy ||') '',4326),15)
order by ST_Distance(geom,ST_GeometryFromText(''point('|| endx ||' ' || endy ||')'',4326)) limit 1'
into v_endLine,v_endSource;
--如果没找到最近的线，就返回null
if (v_startLine is null) or (v_endLine is null) then
return null;
end if ;
select ST_ClosestPoint(v_startLine, ST_Geometryfromtext('point('|| startx ||' ' || starty ||')',4326)) into v_statpoint;
select ST_ClosestPoint(v_endLine, ST_GeometryFromText('point('|| endx ||' ' || endy ||')',4326)) into v_endpoint;
--最短路径
execute 'SELECT st_linemerge(st_union(st_astext(b.geom))) ' ||
'FROM pgr_kdijkstraPath(
''SELECT id as id, source, target, length as cost FROM ' || tbl ||''','
||v_startTarget || ', ' ||'array['||v_endSource||'] , false, false
) a, '
|| tbl || ' b
WHERE a.id3=b.id
GROUP by id1
ORDER by id1' into v_res ;
select st_setsrid(st_astext(v_res), 4326) into v_res ;
--如果v_res已包含 v_startLine或 v_endLine，不做拼接
if(v_res is null) or (ST_Distance(v_res,v_statpoint) > 0.000000001) then
--将v_res,v_startLine进行拼接
select st_linemerge(ST_Union(array[st_astext(v_res),st_astext(v_startLine)])) into v_res;
select st_setsrid(st_astext(v_res), 4326) into v_res ;
end if;
if(v_res is null) or (ST_Distance(v_res,v_endpoint) > 0.000000001) then
--将v_res,v_endLine进行拼接
select st_linemerge(ST_Union(array[st_astext(v_res),st_astext(v_endLine)])) into v_res;
select st_setsrid(st_astext(v_res), 4326) into v_res ;
end if;
select ST_Line_Locate_Point(v_res, v_statpoint) into v_perStart;
select ST_Line_Locate_Point(v_res, v_endpoint) into v_perEnd;
if(v_perStart > v_perEnd) then
tempnode = v_perStart;
v_perStart = v_perEnd;
v_perEnd = tempnode;
end if;
--截取v_res
SELECT ST_Line_SubString(v_res,v_perStart, v_perEnd) into v_shPath;
return v_shPath;
end;
SQL如下（已经过调整和测试！）：
-- Function: public.pgr_fromatob(character varying, double precision, double precision, double precision, double precision)
-- DROP FUNCTION public.pgr_fromatob(character varying, double precision, double precision, double precision, double precision);
CREATE OR REPLACE FUNCTION public.pgr_fromatob(tbl character varying, startx double precision, starty double precision, endx double precision, endy double precision)
RETURNS geometry AS
$BODY$
declare
v_startLine geometry;--离起点最近的线
v_endLine geometry;--离终点最近的线
v_startTarget integer;--距离起点最近线的终点
v_endSource integer;--距离终点最近线的起点
v_statpoint geometry;--在v_startLine上距离起点最近的点
v_endpoint geometry;--在v_endLine上距离终点最近的点
v_res geometry;--最短路径分析结果
v_perStart float;--v_statpoint在v_res上的百分比
v_perEnd float;--v_endpoint在v_res上的百分比
v_shPath geometry;--最终结果
tempnode float;
begin
--查询离起点最近的线
execute 'select geom ,target from ' ||tbl||
' where
ST_DWithin(geom,ST_Geometryfromtext(''point('|| startx ||' ' || starty||') '',4326),15)
order by ST_Distance(geom,ST_GeometryFromText(''point('|| startx ||' '|| starty ||')'',4326)) limit 1'
into v_startLine ,v_startTarget;
--查询离终点最近的线
execute 'select geom,source from ' ||tbl||
' where ST_DWithin(geom,ST_Geometryfromtext(''point('|| endx || ' ' || endy ||') '',4326),15)
order by ST_Distance(geom,ST_GeometryFromText(''point('|| endx ||' ' || endy ||')'',4326)) limit 1'
into v_endLine,v_endSource;
--如果没找到最近的线，就返回null
if (v_startLine is null) or (v_endLine is null) then
return null;
end if ;
select ST_ClosestPoint(v_startLine, ST_Geometryfromtext('point('|| startx ||' ' || starty ||')',4326)) into v_statpoint;
select ST_ClosestPoint(v_endLine, ST_GeometryFromText('point('|| endx ||' ' || endy ||')',4326)) into v_endpoint;
--最短路径
execute 'SELECT st_linemerge(st_union(st_astext(b.geom))) ' ||
'FROM pgr_kdijkstraPath(
''SELECT id as id, source, target, length as cost FROM ' || tbl ||''','
||v_startTarget || ', ' ||'array['||v_endSource||'] , false, false
) a, '
|| tbl || ' b
WHERE a.id3=b.id
GROUP by id1
ORDER by id1' into v_res ;
select st_setsrid(st_astext(v_res), 4326) into v_res ;
--如果v_res已包含 v_startLine或 v_endLine，不做拼接
if(v_res is null) or (ST_Distance(v_res,v_statpoint) > 0.000000001) then
--将v_res,v_startLine进行拼接
select st_linemerge(ST_Union(array[st_astext(v_res),st_astext(v_startLine)])) into v_res;
select st_setsrid(st_astext(v_res), 4326) into v_res ;
end if;
if(v_res is null) or (ST_Distance(v_res,v_endpoint) > 0.000000001) then
--将v_res,v_endLine进行拼接
select st_linemerge(ST_Union(array[st_astext(v_res),st_astext(v_endLine)])) into v_res;
select st_setsrid(st_astext(v_res), 4326) into v_res ;
end if;
select ST_Line_Locate_Point(v_res, v_statpoint) into v_perStart;
select ST_Line_Locate_Point(v_res, v_endpoint) into v_perEnd;
if(v_perStart > v_perEnd) then
tempnode = v_perStart;
v_perStart = v_perEnd;
v_perEnd = tempnode;
end if;
--截取v_res
SELECT ST_Line_SubString(v_res,v_perStart, v_perEnd) into v_shPath;
return v_shPath;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION public.pgr_fromatob(character varying, double precision, double precision, double precision, double precision)
OWNER TO postgres;
GEOSERVER的SQL视图：
SELECT ST_MakeLine(route.geom) FROM (
SELECT geom FROM pgr_fromatob('ways', %x1%, %y1%, %x2%, %y2%
)as geom )AS route
正则表达式输入：^-?[\d.]+$