使用postgis3计算两点间的最短路径

--添加起点id 
ALTER TABLE road_gaosu ADD COLUMN source integer; 
--添加终点id 
ALTER TABLE road_gaosu ADD COLUMN target integer; 
--添加道路权重值 
ALTER TABLE road_gaosu ADD COLUMN length double precision;
ALTER TABLE road_gaosu ADD COLUMN cost double precision;
ALTER TABLE road_gaosu ADD COLUMN reverse_cost double precision;
--创建拓扑  
SELECT pgr_createTopology('road_gaosu',0.00001,'geom','gid','source','target');
--为source和target字段创建索引 
CREATE INDEX source_idx ON road_gaosu("source"); 
CREATE INDEX target_idx ON road_gaosu("target"); 
--计算每一段路的长度
update road_gaosu set length =st_length(geom);
update road_gaosu set cost = st_length(geom);
UPDATE road_gaosu SET reverse_cost = st_length(geom);

CREATE OR REPLACE FUNCTION _myshortpath(
    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_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坐标系
    --找起点15米范围内的最近线 
    execute 'select geom, source, 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_startSource ,v_startTarget; 
    --查询离终点最近的线 
    --找终点15米范围内的最近线 
    execute 'select geom, source, target 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,v_endTarget; 
    --如果没找到最近的线，就返回null  
    if (v_startLine is null) or (v_endLine is null) then  
        return null;  
    end if ;  
    raise notice 'v_startLine 1-%',geometrytype(st_linemerge(v_startLine));
    raise notice 'v_endLine 1-%',geometrytype(v_endLine);
    raise notice 'v_startSource 1-%',v_startSource;
    raise notice 'v_endSource 1-%',(v_endSource);
    raise notice 'v_startTarget 1-%',(v_startTarget);
    raise notice 'v_endTarget 1-%',(v_endTarget);
    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; 
    
   -- ST_Distance 
    --注意KSP返回的edge即id 把原方法后面的group by id去掉
    --从开始的起点到结束的起点最短路径 
    execute 'SELECT st_linemerge(st_union(b.geom)) ' ||
    'FROM pgr_KSP( 
    ''SELECT gid as id, source, target, length as cost,length as reverse_cost FROM ' || tbl ||''','|| v_startSource||', '||v_endSource||', 1,false  
    ) a, '  ||tbl|| ' b 
    WHERE a.edge=b.gid' into v_res ;
    raise notice 'v_res 1-% length-%',geometrytype(v_res),ST_Length(v_res);
    --从开始的终点到结束的起点最短路径 
    execute 'SELECT st_linemerge(st_union(b.geom)) ' ||
    'FROM pgr_KSP( 
    ''SELECT gid as id, source, target, length as cost,length as reverse_cost FROM ' || tbl ||''','|| v_startTarget||', '||v_endSource||', 1,false 
    ) a, '  
    ||tbl|| ' b 
    WHERE a.edge=b.gid' into v_res_b ;
    raise notice 'v_res_b 1-% length-%',geometrytype(v_res_b),ST_Length(v_res_b);
    --从开始的起点到结束的终点最短路径 
    execute 'SELECT st_linemerge(st_union(b.geom)) ' ||
     'FROM pgr_KSP( 
    ''SELECT gid as id, source, target, length as cost,length as reverse_cost FROM ' || tbl ||''','|| v_startSource||', '||v_endTarget||', 1,false 
    ) a, '  
    || tbl || ' b 
    WHERE a.edge=b.gid ' into v_res_c ;
    raise notice 'v_res_c 1-% length-%',geometrytype(v_res_c),ST_Length(v_res_c); 
    --从开始的终点到结束的终点最短路径
     execute 'SELECT st_linemerge(st_union(b.geom)) ' || 
    'FROM pgr_KSP( 
    ''SELECT gid as id, source, target, length as cost,length as reverse_cost FROM ' || tbl ||''','|| v_startTarget||', '||v_endTarget||', 1,false 
    ) a, '  
    || tbl || ' b 
    WHERE a.edge=b.gid ' into v_res_d ;
    raise notice 'v_res_d 1-% length-%',geometrytype(v_res_d),ST_Length(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;       
 
    --将v_res,v_startLine,v_endLine进行拼接 
 
    select  st_linemerge(ST_Union(array[v_startLine,v_res,v_endLine])) into v_res;
    raise notice 'this is raise demo , v_res is % ,leng is % ',v_res,geometrytype(v_res) ; 


    select  ST_LineLocatePoint(ST_GeometryN(v_res,1), v_statpoint) into v_perStart;
    select  ST_LineLocatePoint(ST_GeometryN(v_res,1), 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;
    return v_shPath; 
end;


$BODY$
  LANGUAGE plpgsql VOLATILE STRICT
  COST 100;
ALTER FUNCTION _myshortpath(character varying, double precision, double precision, double precision, double precision)
  OWNER TO postgres;