要利用rgrouting实现像QGIS那样求任意两点间的最短路径,可以按照以下步骤使用pl/pgsql进行自定义函数:
1 函数的参数为:_myShortPath(startxfloat, starty float,endx float,endy float,costfile varchar),前四个参数为起点和终点的坐标,最后一个参数为最短路径分析时的cost字段;
2 分别找出距离起点和终点最近的两条线,可以使用:
ST_DWithin(geography gg1, geography gg2, double precisiondistance_meters);
在距离gg2在distance_meters范围内的gg1的线,之后使用ST_Distance(geometryg1, geometry g2);进行排序,并取第一个。
3 找出在上一步求出最近线上距离起点(终点)最近的点。使用:
ST_ClosestPoint(geometryg1, geometry g2)
4 根据第2步求出的离起点最近先的target和离终点最近线的source,求最短路径。使用
SELECTst_linemerge(st_union(b.geom)) into v_res
FROMpgr_kdijkstraPath(
'SELECTid, source, target, ' || costfile ||' as cost FROM route',
v_startTarget,array[v_endSource], true, false
)a,
routeb
WHEREa.id3=b.id
GROUPby id1
ORDERby id1;
5 将求出的路径与第2步找出的两条线进行合并,可以使用:
st_linemerge(ST_Union(array[v_res,v_startLine,v_endLine]))
6 求出第3步找出的点在上一步中线的上的百分比,可以使用:
ST_Line_Locate_Point(v_res,v_statpoint)
7 使用上一步求出的百分比来截取第5步的结果:
ST_Line_SubString(v_res,v_perStart,v_perEnd)
8 最后将截取的结果返回;
具体代码:
create function _myShortPath(startx float, starty float,endx float,endy float,costfile varchar)
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;--最终结果
begin
--查询离起点最近的线
select geom ,target into v_startLine ,v_startTarget from route where
ST_DWithin(geom,ST_Geometryfromtext('point('|| startx ||' ' || starty ||')'),0.015)
order by ST_Distance(geom,ST_GeometryFromText('point('|| startx ||' '|| starty ||')')) limit 1;
--查询离终点最近的线
select geom,source into v_endLine,v_endSource from route where
ST_DWithin(geom,ST_Geometryfromtext('point('|| endx || ' ' || endy ||')'),0.015)
order by ST_Distance(geom,ST_GeometryFromText('point('|| endx ||' ' || endy ||')')) limit 1;
--如果没找到最近的线,就返回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 ||')')) into v_statpoint;
select ST_ClosestPoint(v_endLine, ST_GeometryFromText('point('|| endx ||' ' || endy ||')')) into v_endpoint;
--最短路径
SELECT st_linemerge(st_union(b.geom)) into v_res
FROM pgr_kdijkstraPath(
'SELECT id, source, target, ' || costfile ||' as cost FROM route',
v_startTarget, array[v_endSource], true, false
) a,
route b
WHERE a.id3=b.id
GROUP by id1
ORDER by id1;
--如果找不到最短路径,就返回null
if(v_res is null) then
return null;
end if;
--将v_res,v_startLine,v_endLine进行拼接
select st_linemerge(ST_Union(array[v_res,v_startLine,v_endLine])) into v_res;
select ST_Line_Locate_Point(v_res, v_statpoint) into v_perStart;
select ST_Line_Locate_Point(v_res, v_endpoint) into v_perEnd;
--截取v_res
SELECT ST_Line_SubString(v_res,v_perStart, v_perEnd) into v_shPath;
return v_shPath;
end;
$body$
LANGUAGE plpgsql VOLATILE STRICT
select_myShortPath(119.274,26.0649,119.258,26.019,'length') into shortPath;
在QGIS中打开shortPath,可以看出结果与在QGIS执行最短路径结果一致: