基于pgrouting的任意两点间的最短路径查询函数三→跨楼层 - cytggit/Map-openlayers GitHub Wiki

前一篇查询任意两点间最短路径的函数，但只是平面上的，尚未实现跨楼层的路径规划。现在对该函数进行扩展，支持用户自己输入起点和终点的楼层，从而实现跨楼层的路径规划。

* <只能实现起点到终点一个楼梯或电梯，没有换乘的情况>

跨楼层最短路径规划的处理步骤，首先要确定用户的出发点和目的地所在的楼层，再在相应的楼层上找到所有可上下楼的位置，查找起点到楼梯的距离加上楼梯到终点距离的最短路径。具体过程为：

（1）：查找用户出发点所在楼层的所有楼梯。

（2）：判断（1）中的楼梯是否可到达终点所在楼层。

（3）：（2）中删选出来的楼梯分别计算起点到楼梯的距离和楼梯到终点的距离。

（4）：比较（3）中的距离，选择最短距离和相应的楼梯。

（5）：分别计算起点到楼梯的路径和楼梯到终点的路径，最为最短路径。

代码如下（已经过调整和测试！）：

declare    
	v_sql varchar;
	
	v_firstFid varchar;--起点最近电梯的fid    
	v_endFid varchar;--起点最近电梯的fid    
	v_enddummyFid varchar;--临时起点最近电梯的fid    
	
	v_firstFloor varchar;--起点最近电梯的floorid
	v_endFloor varchar;--起点最近电梯的floorid
	v_enddummyFloor varchar;--临时起点最近电梯的floorid
	
	v_firstGeom geometry;--起点最近电梯的geom   
	v_endGeom geometry;--起点最近电梯的geom   
	v_enddummyGeom geometry;--临时起点最近电梯的geom   
	
	v_Node integer;--可到达终点的楼梯或电梯编号
	
	v_startx double precision;
	v_starty double precision;
	v_endx double precision;
	v_endy double precision;
	
	v_record record;--起点所在楼层可上下楼的所有记录
	v_dummydistance float;--临时存放起点到终点总距离
	v_dummydistancefirst float;--临时存放起点到电梯距离
	v_dummydistanceend float;--临时存放电梯到终点距离
	v_distance float;--存放起点到终点总距离
begin    
	-- 初始化最短距离
	v_distance = 300;
	
	-- 查找起点同楼层可上下楼的电梯
	v_sql = 'select fid ,floor_id ,geom ,node from ' ||tbl1||
			' where feature_id in (''30050300'',''30050200'') and floor_id = '''||startfloor||'''';
		
	for v_record in EXECUTE v_sql loop
		EXECUTE 'select fid ,floor_id ,geom from '||tbl1||' where floor_id = '''||endfloor||''' and node = '||v_record.node||''
		into v_enddummyFid ,v_enddummyFloor ,v_enddummyGeom;
		
		if (v_enddummyFid is null)then 
			continue;
		end if;
		
		-- 计算起点到楼梯的距离
		select ST_Distance(v_record.geom,ST_GeometryFromText('point('|| startx ||' '|| starty ||')',4326))
		into v_dummydistancefirst;
		
		-- 计算终点到楼梯的距离
		select ST_Distance(v_enddummyGeom,ST_GeometryFromText('point('|| endx ||' '|| endy ||')',4326))
		into v_dummydistanceend;
					
		-- 距离之和判断是否最短
		v_dummydistance = v_dummydistancefirst + v_dummydistanceend;
		if (v_dummydistance < v_distance) then
			v_firstFid = v_record.fid;
			v_firstFloor = v_record.floor_id;
			v_firstGeom = v_record.geom; 
			
			v_endFid = v_enddummyFid;
			v_endFloor = v_enddummyFloor;
			v_endGeom = v_enddummyGeom;
			
			v_Node = v_record.node;
			v_distance = v_dummydistance;
		end if;
	end loop;				
	
	select st_x(v_firstGeom) into v_startx;
	select st_y(v_firstGeom) into v_starty;
	select st_x(v_endGeom) into v_endx;
	select st_y(v_endGeom) into v_endy;
	
	EXECUTE
		'select ST_MakeLine(route1.geom) as line1, ST_MakeLine(route2.geom) as line2 '
		'from '
			'(select geom from pgr_fromatob('''||tbl2||''', '|| startx ||','|| starty ||', '||v_startx||', '||v_starty||' )as geom)as route1,'
			'(select geom from pgr_fromatob('''||tbl2||''',  '||v_endx||', '||v_endy||', '|| endx ||', '|| endy ||' )as geom )as route2'
	into line1,line2;

	return;	
end;    

整体函数如下（已经过调整和测试！）：

-- Function: public.pgr_fromatob_withfloor(character varying, character varying, character varying, character varying, double precision, double precision, double precision, double precision)

-- DROP FUNCTION public.pgr_fromatob_withfloor(character varying, character varying, character varying, character varying, double precision, double precision, double precision, double precision);

CREATE OR REPLACE FUNCTION public.pgr_fromatob_withfloor(IN tbl1 character varying, IN tbl2 character varying, IN startfloor character varying, IN endfloor character varying, IN startx double precision, IN starty double precision, IN endx double precision, IN endy double precision, OUT line1 geometry, OUT line2 geometry)
RETURNS record AS
$BODY$	
declare    
	v_sql varchar;
	
	v_firstFid varchar;--起点最近电梯的fid    
	v_endFid varchar;--起点最近电梯的fid    
	v_enddummyFid varchar;--临时起点最近电梯的fid    
	
	v_firstFloor varchar;--起点最近电梯的floorid
	v_endFloor varchar;--起点最近电梯的floorid
	v_enddummyFloor varchar;--临时起点最近电梯的floorid
	
	v_firstGeom geometry;--起点最近电梯的geom   
	v_endGeom geometry;--起点最近电梯的geom   
	v_enddummyGeom geometry;--临时起点最近电梯的geom   
	
	v_Node integer;--可到达终点的楼梯或电梯编号
	
	v_startx double precision;
	v_starty double precision;
	v_endx double precision;
	v_endy double precision;
	
	v_record record;--起点所在楼层可上下楼的所有记录
	v_dummydistance float;--临时存放起点到终点总距离
	v_dummydistancefirst float;--临时存放起点到电梯距离
	v_dummydistanceend float;--临时存放电梯到终点距离
	v_distance float;--存放起点到终点总距离
begin    
	-- 初始化最短距离
	v_distance = 300;
	
	-- 查找起点同楼层可上下楼的电梯
	v_sql = 'select fid ,floor_id ,geom ,node from ' ||tbl1||
			' where feature_id in (''30050300'',''30050200'') and floor_id = '''||startfloor||'''';
		
	for v_record in EXECUTE v_sql loop
		EXECUTE 'select fid ,floor_id ,geom from '||tbl1||' where floor_id = '''||endfloor||''' and node = '||v_record.node||''
		into v_enddummyFid ,v_enddummyFloor ,v_enddummyGeom;
		
		if (v_enddummyFid is null)then 
			continue;
		end if;
		
		-- 计算起点到楼梯的距离
		select ST_Distance(v_record.geom,ST_GeometryFromText('point('|| startx ||' '|| starty ||')',4326))
		into v_dummydistancefirst;
		
		-- 计算终点到楼梯的距离
		select ST_Distance(v_enddummyGeom,ST_GeometryFromText('point('|| endx ||' '|| endy ||')',4326))
		into v_dummydistanceend;
					
		-- 距离之和判断是否最短
		v_dummydistance = v_dummydistancefirst + v_dummydistanceend;
		if (v_dummydistance < v_distance) then
			v_firstFid = v_record.fid;
			v_firstFloor = v_record.floor_id;
			v_firstGeom = v_record.geom; 
			
			v_endFid = v_enddummyFid;
			v_endFloor = v_enddummyFloor;
			v_endGeom = v_enddummyGeom;
			
			v_Node = v_record.node;
			v_distance = v_dummydistance;
		end if;
	end loop;				
	
	select st_x(v_firstGeom) into v_startx;
	select st_y(v_firstGeom) into v_starty;
	select st_x(v_endGeom) into v_endx;
	select st_y(v_endGeom) into v_endy;
	
	EXECUTE
		'select ST_MakeLine(route1.geom) as line1, ST_MakeLine(route2.geom) as line2 '
		'from '
			'(select geom from pgr_fromatob('''||tbl2||''', '|| startx ||','|| starty ||', '||v_startx||', '||v_starty||' )as geom)as route1,'
			'(select geom from pgr_fromatob('''||tbl2||''',  '||v_endx||', '||v_endy||', '|| endx ||', '|| endy ||' )as geom )as route2'
	into line1,line2;

	return;	
	end;    
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION public.pgr_fromatob_withfloor(character varying, character varying, character varying, character varying, double precision, double precision, double precision, double precision)
OWNER TO postgres;

GEOSERVER的SQL视图：

select line1,line2 from pgr_fromatob_withfloor('point','polyline','%startfloor%','%endfloor%',%x1%,%y1%,%x2%,%y2%)

正则表达式输入：^-?[\d.]+$