pgrouting 路径规划，设置单向通行、双向通行、障碍点、限制通行
1. 创建表 nyc_roads，并将数据导入该表
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.添加道路权重值
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.创建索引
create index idx_line_geom on cim.nyc_roads using gist(geom);
8.为cim.nyc_roads表创建拓扑布局，即为source和target字段赋值
SELECT pgr_createTopology('cim.nyc_roads',0.00001, 'geom', 'gid','source','target',true,true); --4326投影容差<=0.00001
9.为source和target字段创建索引
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; --创建起点经度x1
ALTER TABLE cim.nyc_roads ADD COLUMN y1 double precision; --创建起点纬度y1
ALTER TABLE cim.nyc_roads ADD COLUMN x2 double precision; --创建起点经度x2
ALTER TABLE cim.nyc_roads ADD COLUMN y2 double precision; --创建起点经度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赋值
--设置为双向
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.创建函数
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;--最短路径分析结果
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坐标系
--找起点100米范围内的最近线
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米范围内的最近线
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去掉
--从开始的起点到结束的起点最短路径
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(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)
