基于pgrouting的GeoServer发布最短路径

此最短路径函数的核心算法为dijkstra算法，该函数中可能遇到的问题：

1、st_内置算法介绍：

Geometry SQL参考_关系型数据库-阿里云帮助中心

2、函数的具体介绍和使用：

postgis+geoserver最短路径 | 码农家园

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;