pgrouting 路徑規(guī)劃，設(shè)置單向通行、雙向通行、障礙點、限制通行

1. 創(chuàng)建表 nyc_roads，并將數(shù)據(jù)導(dǎo)入該表

CREATE TABLE IF NOT EXISTS cim.nyc_roads
(
    gid integer NOT NULL DEFAULT nextval('cim.nyc_roads_gid_seq'::regclass),
    modified character varying(25) COLLATE pg_catalog."default",
    name character varying(32) COLLATE pg_catalog."default",
    vsam numeric,
    sourcedate character varying(25) COLLATE pg_catalog."default",
    sourcetype character varying(15) COLLATE pg_catalog."default",
    source_id numeric,
    borough character varying(13) COLLATE pg_catalog."default",
    feat_code integer,
    feat_desc character varying(50) COLLATE pg_catalog."default",
    exported character varying(12) COLLATE pg_catalog."default",
    feat_type double precision,
    geom geometry(LineString),
    CONSTRAINT nyc_roads_pkey PRIMARY KEY (gid)
)

2.添加起點id

ALTER TABLE cim.nyc_roads ADD COLUMN source integer; 

3.添加終點id

ALTER TABLE cim.nyc_roads ADD COLUMN target integer; 

4.添加道路權(quán)重值

ALTER TABLE cim.nyc_roads ADD COLUMN length double precision;

5.修改空間參考,并賦值lin_geom

alter table cim.nyc_roads add column lin_geom  geometry(LineString,0)
update cim.nyc_roads set lin_geom  = st_geometryfromtext(st_astext(ST_LineMerge(geom) ),0)

6.刪除原字段geom，修改lin_geom字段名稱為geom

7.創(chuàng)建索引

create index idx_line_geom on cim.nyc_roads using gist(geom);

8.為cim.nyc_roads表創(chuàng)建拓撲布局，即為source和target字段賦值

SELECT pgr_createTopology('cim.nyc_roads',0.00001, 'geom', 'gid','source','target',true,true); --4326投影容差<=0.00001

9.為source和target字段創(chuàng)建索引

CREATE INDEX source_idx ON cim.nyc_roads("source"); 
CREATE INDEX target_idx ON cim.nyc_roads("target"); 
ALTER TABLE cim.nyc_roads ADD COLUMN x1 double precision;       --創(chuàng)建起點經(jīng)度x1
ALTER TABLE cim.nyc_roads ADD COLUMN y1 double precision;       --創(chuàng)建起點緯度y1
ALTER TABLE cim.nyc_roads ADD COLUMN x2 double precision;       --創(chuàng)建起點經(jīng)度x2
ALTER TABLE cim.nyc_roads ADD COLUMN y2 double precision;       --創(chuàng)建起點經(jīng)度y2
--UPDATE road SET geom_s =ST_LineMerge(geom);              
UPDATE cim.nyc_roads SET x1 =ST_x(ST_PointN(lin_geom, 1));  
UPDATE cim.nyc_roads SET y1 =ST_y(ST_PointN(lin_geom, 1));  
UPDATE cim.nyc_roads SET x2 =ST_x(ST_PointN(lin_geom, ST_NumPoints(lin_geom))); 
UPDATE cim.nyc_roads SET y2 =ST_y(ST_PointN(lin_geom, ST_NumPoints(lin_geom))); --給x1、y1、x2、y2賦值
--為length賦值
--設(shè)置為雙向
update cim.nyc_roads set length =st_length(geom);
--將長度值賦給reverse_cost，作為路線選擇標準
ALTER TABLE cim.nyc_roads ADD COLUMN reverse_cost double precision;
UPDATE cim.nyc_roads SET reverse_cost = st_length(geom);

10.創(chuàng)建函數(shù)

CREATE OR REPLACE FUNCTION pgr_fromctod(
    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;--最短路徑分析結(jié)果
    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;--開始到結(jié)束
    v_shPath_es geometry;--結(jié)束到開始 
    v_shPath geometry;--最終結(jié)果 
    tempnode float;       
begin 
    --查詢離起點最近的線 
    --4326坐標系
    --找起點100米范圍內(nèi)的最近線 
    execute 'select geom, source, target  from ' ||tbl||
 
                            ' where ST_DWithin(geom,ST_Geometryfromtext(''point('||startx ||' ' || starty||')'',0),100)
                            order by ST_Distance(geom,ST_GeometryFromText(''point('|| startx ||' '|| starty ||')'',0))  limit 1'
 
                            into v_startLine, v_startSource ,v_startTarget; 
    --查詢離終點最近的線 
    --找終點100米范圍內(nèi)的最近線 
    execute 'select geom, source, target from ' ||tbl||
 
                            ' where ST_DWithin(geom,ST_Geometryfromtext(''point('|| endx || ' ' || endy ||')'',0),100)
                            order by ST_Distance(geom,ST_GeometryFromText(''point('|| endx ||' ' || endy ||')'',0))  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 ||')',0)) into v_statpoint; 
    select  ST_ClosestPoint(v_endLine, ST_GeometryFromText('point('|| endx ||' ' || endy ||')',0)) into v_endpoint; 
    
   -- ST_Distance 
    --注意KSP返回的edge即id 把原方法后面的group by id去掉
    --從開始的起點到結(jié)束的起點最短路徑 
    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);
    --從開始的終點到結(jié)束的起點最短路徑 
    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);
    --從開始的起點到結(jié)束的終點最短路徑 
    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); 
    --從開始的終點到結(jié)束的終點最短路徑
     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(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;
    return v_shPath; 
end; 
 
$BODY$
  LANGUAGE plpgsql VOLATILE STRICT
  COST 100;
ALTER FUNCTION pgr_fromctod(character varying, double precision, double precision, double precision, double precision)
  OWNER TO postgres;

最短路徑查詢

select * from pgr_fromctod('cim.nyc_roads',984956, 215877, 987624, 209369,988778,213468)