pgrouting最短路径

仅支持起点和终点,postgis2.2版本测试

使用方法 select pro_short_path('POINT(101.2496030330658 21.932321190834045)','POINT(101.25415205955505 21.925079226493835)',150) 

150表示在150米范围内查找路网中距离起点和终点最近的道路

drop function if exists pro_truncation(geometry,geometry,geometry,spheroid);
drop function if exists pro_short_path(text,text,float);
/*
	截取线段
	最短路径起点或终点第一要素
	最短路径起点下一要素或终点的前一要素
	起点或终点在线段上的点
	spheroid参数
*/
create function pro_truncation(i_lfirst geometry,i_lsecond geometry,i_linepoint geometry,i_spheroid  spheroid)
returns geometry as
$$
	declare
		v_distance1 float;	--距离
		v_distance2 float;	--距离
		v_spoint geometry;   --线段的起点
		v_epoint geometry;  --线段的终点
		v_p float;					--截取线段的比例
	begin
		--raise notice  '%,%,%', ST_AsText(i_lfirst), ST_AsText(i_lsecond), ST_AsText(i_linepoint);
		v_p := ST_LineLocatePoint(i_lfirst,i_linepoint);  --计算线段上的点在线段上的比例
		v_spoint := ST_StartPoint(i_lfirst);--获取线段的起点
		v_epoint := ST_EndPoint(i_lfirst);--获取线段的终点
		v_distance1 := ST_DistanceSpheroid(i_lsecond ,v_spoint, i_spheroid);--计算距离
		v_distance2 := ST_DistanceSpheroid(i_lsecond ,v_epoint, i_spheroid);--计算距离	
		v_p := ST_LineLocatePoint(i_lfirst,i_linepoint);  --计算线段上的点在线段上的比例			
		if( v_distance1 > v_distance2 ) then   --终点连接最短路径
			i_lfirst := ST_LineSubstring(i_lfirst,v_p, 1);
		else --起点连接最短路径
			i_lfirst := ST_LineSubstring(i_lfirst,0, v_p);
		end if;		
		return i_lfirst;
	end;
$$ language plpgsql;


create function pro_short_path(i_start text,i_end text,i_distance float)
returns text as
$$
	declare
		v_spheroid  spheroid;
		v_pstart geometry; --起点
		v_pend geometry;  --终点
		v_lstart geometry;--离起点最近的线
		v_lend geometry;--离终点最近的线
		v_statpoint geometry;--在v_lstart上距离起点最近的点
		v_endpoint geometry;--在v_lend上距离终点最近的点
		--缓冲距离起点或终点最近的线段上点,用于判断是否相交
		v_sbuffer geometry;
		v_ebuffer geometry; 

		v_sTarget road_2015.target%type;--在v_lstart上距离起点最近的点
		v_eTarget road_2015.target%type;--在v_lend上距离起点最近的点
		--最短路径起点和终点信息及几何对象
		v_path pgr_costResult3;
		v_shorts pgr_costResult3[];
		v_line geometry;
		v_geoms geometry[];

		v_i integer;
		v_count integer;
		v_curs cursor (itarget road_2015.target%type,isource road_2015.target%type) 
			for select a.seq,a.id1,a.id2,a.id3,a.cost,ST_LineMerge(b.geom) from 
				pgr_kdijkstraPath('select gid as id, source, target, length as cost from road_2015', itarget, array[isource], false, false)  a,road_2015 b 
					where a.id3=b.gid order by a.seq;

		v_results text;
	begin
		v_spheroid := 'SPHEROID["WGS84",6378137,298.257223563]' ;  --WGS84椭球体参数定义
		v_pstart := ST_SetSRID(ST_GeomFromText(i_start),4326);
		v_pend := ST_SetSRID(ST_GeomFromText(i_end),4326);
		--查询i_distance米范围内离起点最近的线
		select ST_LineMerge(geom),target into v_lstart,v_sTarget from  road_2015 
			where ST_DWithin(geom::geography ,v_pstart::geography,i_distance,true)
			order by ST_DistanceSpheroid(geom,v_pstart,v_spheroid)  limit 1;
		--查询i_distance米范围内离终点最近的线
		select ST_LineMerge(geom),target into v_lend,v_eTarget from  road_2015 
			where ST_DWithin(geom::geography ,v_pend::geography,i_distance,true)
			order by ST_DistanceSpheroid(geom,v_pend,v_spheroid)  limit 1;				
		if (v_lstart is null or v_lend is null) then
			return null;--如果没找到最近的道路，就返回null
		end if;
		--计算起点和终点在最近的线段距离最近的点
		select  ST_ClosestPoint(v_lstart, v_pstart ) into v_statpoint;
		select  ST_ClosestPoint(v_lend, v_pend ) into v_endpoint;
		v_sbuffer := ST_Buffer(v_statpoint::geography, 3);
		v_ebuffer := ST_Buffer(v_endpoint::geography, 3);
		
		--raise notice  '%,%', v_sTarget, v_eTarget;
		--获取起点线段和编号
		open v_curs(v_sTarget,v_eTarget);
		loop
			fetch v_curs into v_path.seq,v_path.id1,v_path.id2,v_path.id3,v_path.cost,v_line;
			exit when not found;-- 假如没有检索到(主表)数据，结束循环处理
			v_shorts := array_append(v_shorts,v_path);
			v_geoms := array_append(v_geoms,v_line);
		end loop;
		close v_curs;
		v_count := array_length(v_shorts, 1);
		if( v_shorts is null ) then
			raise exception '%','您设置的起点和终点没有连接的道路！';
		end if;
		--起点线段处理
		v_line := ST_MakeLine(v_pstart,v_statpoint);
		if( false = ST_Intersects(v_geoms[1],v_sbuffer) ) then  --如果起点和起点线段没有相连
			v_lstart := pro_truncation(v_lstart,v_geoms[1], v_statpoint,v_spheroid);
			v_geoms[1] :=  ST_Union(v_geoms[1],v_lstart);
			v_geoms[1] := ST_Union(v_geoms[1],v_line);
		else
			v_geoms[1] := pro_truncation(v_geoms[1],v_geoms[2], v_statpoint,v_spheroid);
			v_geoms[1] := ST_Union(v_geoms[1],v_line);
		end if;
		--终点线段处理		
		v_line := ST_MakeLine(v_pend,v_endpoint);
		--raise notice  '%,%,%',ST_Intersects(v_geoms[v_count],v_ebuffer),ST_AsText(v_geoms[v_count]),ST_AsText(v_ebuffer);
		if( false = ST_Intersects(v_geoms[v_count],v_ebuffer) ) then  --如果终点和终点线段没有相连
			v_lend := pro_truncation(v_lend,v_geoms[v_count], v_endpoint,v_spheroid);			
			v_geoms[v_count] :=  ST_Union(v_geoms[v_count],v_lend);
			v_geoms[v_count] := ST_Union(v_geoms[v_count],v_line);
		else
			v_i := v_count-1;
			v_geoms[v_count] := pro_truncation(v_geoms[v_count],v_geoms[v_i], v_endpoint,v_spheroid);
			v_geoms[v_count] := ST_Union(v_geoms[v_count],v_line);
		end if;

		v_results ='[';
		for v_i in 1..v_count loop
			v_results := v_results || '{';
			v_results := v_results || '"seq":' || v_shorts[v_i].seq::varchar;
			v_results := v_results || ',"id1":' || v_shorts[v_i].id1::varchar;
			v_results := v_results || ',"id2":' || v_shorts[v_i].id2::varchar;
			v_results := v_results || ',"id3":' || v_shorts[v_i].id3::varchar;
			v_results := v_results || ',"cost":' || v_shorts[v_i].cost::varchar;
			v_results := v_results || ',"geom":' || '"' || ST_AsText(v_geoms[v_i])  || '"' ;
			v_results := v_results || '}';
			if( v_i < v_count ) then
				v_results := v_results || ',';
			end if;
		end loop;
		v_results := v_results || ']';
		return v_results;
	end;
$$ language plpgsql;