grouting(2.4)+postgis 寻求三条最短路径方法

其他创建数据及拓扑请参考网上他人已有教程

   CREATE OR REPLACE FUNCTION pgr_fromAtoBKSP(       
    IN tbl varchar,         
    IN x1 double precision,
    IN y1 double precision,   
    IN x2 double precision,
    IN y2 double precision,   
    OUT seq integer,     
    OUT gid integer,
    OUT name text,
    OUT heading double precision,
    OUT cost double precision,   
    OUT geom geometry,
    OUT pathid integer   
)       
RETURNS SETOF record
AS $BODY$
DECLARE       
sql     text;       
rec     record;     
source    integer;     
target    integer;   
point    integer;     
BEGIN
-- 查询距离出发点最近的道路节点
EXECUTE 'SELECT id::integer FROM road_vertices_pgr     
ORDER BY the_geom <-> ST_GeometryFromText(''POINT('     
|| x1 || ' ' || y1 || ')'',4326) LIMIT 1' INTO rec;     
source := rec.id;     
-- 查询距离目的地最近的道路节点     
EXECUTE 'SELECT id::integer FROM road_vertices_pgr     
ORDER BY the_geom <-> ST_GeometryFromText(''POINT('     
|| x2 || ' ' || y2 || ')'',4326) LIMIT 1' INTO rec;     
target := rec.id;
-- 最短路径查询     
seq := 0;     
sql := 'SELECT gid, geom, name, cost, source, target,path_id,
ST_Reverse(geom) AS flip_geom FROM ' ||         
'pgr_KSP(''SELECT gid as id, source::int, target::int, '           
|| 'road_length::float AS cost,x1,y1,x2,y2 FROM '         
|| quote_ident(tbl) || ''', '                       
|| source || ', ' || target           
|| ' ,3,false,false ), '             
|| quote_ident(tbl) || ' WHERE edge = gid ORDER BY seq';       
-- Remember start point     
point := source;       
FOR rec IN EXECUTE sql     
LOOP         
-- Flip geometry (if required)         
IF ( point != rec.source ) THEN     
rec.geom := rec.flip_geom;             
point := rec.source;         
ELSE         
point := rec.target;     
END IF;         
-- Calculate heading (simplified)         
EXECUTE 'SELECT degrees( ST_Azimuth(         
ST_StartPoint(''' || rec.geom::text || '''),         
ST_EndPoint(''' || rec.geom::text || ''') ) )'     
INTO heading;         
-- Return record     
seq     := seq + 1;     
gid     := rec.gid;     
name    := rec.name;             
cost    := rec.cost;           
geom    := rec.geom;   
pathid  :=rec.path_id;    
RETURN NEXT;         END LOOP;     
RETURN; END;
$BODY$ LANGUAGE 'plpgsql' VOLATILE STRICT;

create table ksp as SELECT * FROM pgr_fromAtoBKSP('road',x1,y1,x2,y2);