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$$declarev_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$$declarev_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;beginv_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) thenreturn null;--如果没找到最近的道路，就返回nullend 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);loopfetch 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 ) thenraise 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);elsev_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);elsev_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 loopv_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 ) thenv_results := v_results || ',';end if;end loop;v_results := v_results || ']';return v_results;end;$$ language plpgsql;