此最短路径函数的核心算法为dijkstra算法,该函数中可能遇到的问题:
1、st_内置算法介绍:
2、函数的具体介绍和使用:
3、pgr_dijkstra算法详解:
pgRouting官方文档:pgr_dijkstra - 知乎
在使用函数前需要先导入:
-- FUNCTION: public.pgr_final_v1(character varying, double precision, double precision, double precision, double precision)
-- DROP FUNCTION IF EXISTS public.pgr_final_v1(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
LANGUAGE 'plpgsql'
COST 100
VOLATILE STRICT PARALLEL UNSAFE
AS $BODY$
declare
v_startLine geometry;--离起点最近的线
v_endLine geometry;--离终点最近的线
v_startTarget integer;--距离起点最近线的终点
v_startSource integer;
v_endSource integer;--距离终点最近线的起点
v_endTarget integer;
v_statpoint geometry;--在v_startLine上距离起点最近的点
v_endpoint geometry;--在v_endLine上距离终点最近的点
v_res geometry;--最短路径分析结果
v_res_a geometry;
v_res_b geometry;
v_res_c geometry;
v_res_d geometry;
v_start_temp geometry;
v_start_temp1 geometry;
v_start_temp2 geometry;
v_end_temp geometry;
v_end_temp1 geometry;
v_end_temp2 geometry;
v_perStart float;--v_statpoint在v_res上的百分比
v_perEnd float;--v_endpoint在v_res上的百分比
v_shPath_se geometry;--开始到结束
v_shPath_es geometry;--结束到开始
v_shPath geometry;--最终结果
tempnode float;
startpoint geometry;
endpoint geometry;
v_shPath1 geometry;--一次结果
v_shPath2 geometry;--二次结果
star_line geometry; --起点到最近点的线
end_line geometry; --终点到最近点的线
geoARR geometry[];
begin
--查询离起点最近的线
--4326坐标系
--找起点15米范围内的最近线
execute 'select geom, source, target from '|| tbl ||
' where ST_DWithin(geom,ST_Geometryfromtext(''point('|| startx ||' ' || starty||')'',4326),3000)
order by ST_Distance(geom,ST_GeometryFromText(''point('|| startx ||' '|| starty ||')'',4326)) limit 1'
into v_startLine, v_startSource ,v_startTarget;
raise notice '起点最近的source,%', v_startSource;
raise notice '起点最近的target,%', v_startTarget;
--查询离终点最近的线
--找终点15米范围内的最近线
execute 'select geom, source, target from '|| tbl ||
' where ST_DWithin(geom,ST_Geometryfromtext(''point('|| endx || ' ' || endy ||')'',4326),3000)
order by ST_Distance(geom,ST_GeometryFromText(''point('|| endx ||' ' || endy ||')'',4326)) limit 1'
into v_endLine, v_endSource,v_endTarget;
raise notice '终点最近的source,%', v_endSource;
raise notice '终点最近的target,%', v_endTarget;
--如果没找到最近的线,就返回null
if (v_startLine is null) or (v_endLine is null) then
return null;
raise notice '没找到最近的线';
end if ;
--找startline上距离起点最近的点
select ST_ClosestPoint(v_startLine, ST_Geometryfromtext('point('|| startx ||' ' || starty ||')',4326)) into v_statpoint;
--找endline上距离终点最近的点
select ST_ClosestPoint(v_endLine, ST_GeometryFromText('point('|| endx ||' ' || endy ||')',4326)) into v_endpoint;
-- ST_Distance
--从开始的起点到结束的起点最短路径
execute 'SELECT st_linemerge(st_union(b.geom)) ' ||
'FROM pgr_dijkstra(
''SELECT gid as id, source, target, length as cost FROM '|| tbl ||' '','
||v_startSource || ', ' ||v_endSource||' , true
) a, my_table b
WHERE edge = gid
' into v_res ;
raise notice 'v_res,%',v_res;
--从开始的终点到结束的起点最短路径
execute 'SELECT st_linemerge(st_union(b.geom)) ' ||
'FROM pgr_dijkstra(
''SELECT gid as id, source, target, length as cost FROM '|| tbl ||' '','
||v_startTarget || ', ' || v_endSource||' , true
)a, my_table b
WHERE edge=b.gid
' into v_res_b ;
raise notice 'v_res_b,%',v_res_b;
--从开始的起点到结束的终点最短路径
execute 'SELECT st_linemerge(st_union(b.geom)) ' ||
'FROM pgr_dijkstra(
''SELECT gid as id, source, target, length as cost FROM '|| tbl ||' '','
||v_startSource || ', ' ||v_endTarget||' , true
) a, my_table b
WHERE edge=b.gid
' into v_res_c ;
raise notice 'v_res_c,%',v_res_c;
--从开始的终点到结束的终点最短路径
execute 'SELECT st_linemerge(st_union(b.geom)) ' ||
'FROM pgr_dijkstra(
''SELECT gid as id, source, target, length as cost FROM '|| tbl ||' '','
||v_startTarget || ', ' || v_endTarget||' , true
) a, my_table b
WHERE edge=b.gid
' into v_res_d ;
raise notice 'v_res_d,%',v_res_d;
if(ST_Length(v_res) > ST_Length(v_res_b)) then
v_res = v_res_b;
end if;
if(ST_Length(v_res) > ST_Length(v_res_c)) then
v_res = v_res_c;
end if;
if(ST_Length(v_res) > ST_Length(v_res_d)) then
v_res = v_res_d;
end if;
--如果找不到最短路径,就返回null
if(v_res is null) then
return null;
end if;
--将v_res,v_startLine,v_endLine进行拼接
--select st_linemerge(ST_Union(array[v_startLine,v_res,v_endLine])) into v_res;
select ST_LineLocatePoint(v_startLine, v_statpoint) into v_perStart;
select ST_LineLocatePoint(v_endLine, v_endpoint) into v_perEnd;
raise notice 'v_statpoint,%', v_statpoint;
raise notice 'v_endpoint,%', v_endpoint;
SELECT ST_LineSubString(v_startLine,v_perStart, 1) into v_start_temp1;
SELECT ST_LineSubString(v_startLine,0,v_perStart) into v_start_temp2;
SELECT ST_LineSubString(v_endLine,v_perEnd, 1) into v_end_temp1;
SELECT ST_LineSubString(v_endLine,0,v_perEnd) into v_end_temp2;
raise notice 'v_statpoint在v_res上的百分比,%', v_perStart;
raise notice 'v_endpoint在v_res上的百分比%', v_perEnd;
raise notice '%', ST_Distance(v_res,v_start_temp1);
raise notice '%', ST_Distance(v_res,v_start_temp2);
raise notice '%', ST_Distance(v_res,v_end_temp2);
raise notice '%', ST_Distance(v_res,v_end_temp1);
v_start_temp=v_start_temp1;
if(ST_Distance(v_res,v_start_temp1) > ST_Distance(v_res,v_start_temp2)) then
v_start_temp=v_start_temp2;
end if;
v_end_temp=v_end_temp1;
if(ST_Distance(v_res,v_end_temp1) > ST_Distance(v_res,v_end_temp2)) then
v_end_temp=v_end_temp2;
end if;
--return ST_AsGeoJSON(v_res);
--return v_res;
-- select ST_LineLocatePoint(v_res, v_statpoint) into v_perStart;
-- select ST_LineLocatePoint(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_shPath1;
select st_linemerge(ST_Union(array[v_start_temp,v_res,v_end_temp])) into v_shPath1;
raise notice 'v_shPath1,%', v_shPath1;
--return v_shPath1;
--起点到最近道路
select st_makeline(
(ST_GeometryFromText('POINT(' || startx || ' ' || starty || ')',4326)),
(v_statpoint)
)into startpoint;
--终点到最近道路
select st_makeline(
(v_endpoint),
(ST_GeometryFromText('POINT(' || endx || ' ' || endy || ')',4326))
)into endpoint;
select st_union((st_union(startpoint,v_shPath1)),endpoint) into v_shPath;
return v_shPath;
--后续为将起点终点与路网连接起来,因发布时有点问题,暂不使用
--找线的端点
-- select ST_SetSRID( ST_MakePoint(startx , starty),4326 )into startpoint;
-- select ST_SetSRID( ST_MakePoint(endx , endy),4326 )into endpoint;
-- select ST_MakeLine( v_statpoint,startpoint) into star_line;
-- select ST_MakeLine( v_endpoint,endpoint) into end_line;
--
-- geoARR :=array[end_line,v_shPath1,star_line];
-- select st_union(geoARR) into v_shPath;
-- raise notice '返回数据';
-- --raise notice '%', ST_AsGeoJSON(v_shPath);
-- return v_shPath;
end;
$BODY$;
ALTER FUNCTION public.pgr_fromAtoB(character varying, double precision, double precision, double precision, double precision)
OWNER TO postgres;