PostGIS最短路径导航构建

一、构建SQL

-- 添加起点id
ALTER TABLE public.roads ADD COLUMN source integer;

-- 添加终点id
ALTER TABLE public.roads ADD COLUMN target integer;

-- 添加道路权重值
ALTER TABLE public.roads ADD COLUMN length double precision;


-- 创建拓扑结构　　　　　　　　　　　　　　　　　　
-- 为roads表创建拓扑布局，即为source和target字段赋值
SELECT pgr_createTopology('roads',0.00001, 'geom','gid');


-- 创建索引
-- 为source和target字段创建索引
CREATE INDEX source_idx ON roads ("source");
CREATE INDEX target_idx ON roads ("target");


-- 为length赋值，这里在计算的时候用 ST_Transform 进行了转换
UPDATE roads SET length = ST_Length(geom);


ALTER TABLE roads ADD COLUMN x1 double precision;
ALTER TABLE roads ADD COLUMN y1 double precision;	
ALTER TABLE roads ADD COLUMN x2 double precision;
ALTER TABLE roads ADD COLUMN y2 double precision;

UPDATE roads SET x1 =ST_x(ST_PointN(geom, 1));	
UPDATE roads SET y1 =ST_y(ST_PointN(geom, 1));	
UPDATE roads SET x2 =ST_x(ST_PointN(geom, ST_NumPoints(geom)));	
UPDATE roads SET y2 =ST_y(ST_PointN(geom, ST_NumPoints(geom)));	

二、

CREATE OR REPLACE FUNCTION pgr_fromAtoB(
    tbl character varying,
    startx double precision,
    starty double precision,
    endx double precision,
    endy double precision)
  RETURNS geometry AS
$BODY$  
 
declare 
 	startpoint geometry; -- 起点
 	endpoint geometry; -- 终点
	
	star_line geometry; -- 起点到最近线上点的线段
	end_line geometry; -- 终点到最近线上点的线段 
    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_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;      
begin
 
    --查询离起点最近的线 
    --4326坐标系
    --找起点10000000米范围内的最近线
 
    execute 'select geom, source, target  from ' ||tbl||
	' where ST_DWithin(geom,ST_Geometryfromtext(''point('||startx ||' ' || starty||')'',4326), 10000000)
	order by ST_Distance(geom,ST_GeometryFromText(''point('|| startx ||' '|| starty ||')'',4326))  limit 1'
	into v_startLine, v_startSource ,v_startTarget; 
 
    --查询离终点最近的线 
    --找终点10000000米范围内的最近线
 
    execute 'select geom, source, target from ' ||tbl||
	' where ST_DWithin(geom,ST_Geometryfromtext(''point('|| endx || ' ' || endy ||')'',4326), 10000000)
	order by ST_Distance(geom,ST_GeometryFromText(''point('|| endx ||' ' || endy ||')'',4326))  limit 1'
	into v_endLine, v_endSource,v_endTarget; 
 
    --如果没找到最近的线，就返回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; 
 
   
    --raise notice '%',  v_startSource;  -- 12
    --raise notice '%',  v_endSource;     -- 3
    --raise notice '%',  v_startTarget;   -- 1
    --raise notice '%',  v_endTarget;   -- 6
    -- 从开始的起点到结束的起点最短路径
 
    execute 'SELECT st_linemerge(st_union(b.geom)) ' ||
    'FROM pgr_dijkstra( 
    ''SELECT gid as id, source, target, length as cost FROM ' ||tbl|| '''::text,' ||'array['||v_startSource||'] , ' ||'array['||v_endSource||'] , false 
    ) a, ' ||tbl|| ' b 
    WHERE a.edge=b.gid' into v_res ; 
 
    --从开始的终点到结束的起点最短路径
 
    execute 'SELECT st_linemerge(st_union(b.geom)) ' ||
    'FROM pgr_dijkstra( 
    ''SELECT gid as id, source, target, length as cost FROM ' ||tbl|| '''::text,' ||'array['||v_startTarget||'] , ' ||'array['||v_endSource||'] , false 
    ) a, ' ||tbl|| ' b 
    WHERE a.edge=b.gid' into 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|| '''::text,' ||'array['||v_startSource||'] , ' ||'array['||v_endTarget||'] , false 
    ) a, ' ||tbl|| ' b 
    WHERE a.edge=b.gid' into 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|| '''::text,' ||'array['||v_startTarget||'] , ' ||'array['||v_endTarget||'] , false 
    ) a, ' ||tbl|| ' b 
    WHERE a.edge=b.gid' into 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_res,v_startLine,v_endLine])) into 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_linesubstring(v_res,v_perStart, v_perEnd) into 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( endpoint,v_endpoint) into end_line;   
	select st_linemerge(st_union(array[end_line,v_shPath,star_line])) into v_shPath;  
    
return v_shPath; 
 
end; 
 
$BODY$
  LANGUAGE plpgsql VOLATILE STRICT
  COST 100;

三、测试代码

SELECT pgr_fromAtoB('roads', 118.734334,32.043263,118.764762,32.056174);
SELECT pgr_fromAtoB('daolu', 114.55028100125969,38.02584803826188,114.54948954932497,38.0259989280112);