postgis 拓扑网路实现沿路测距通过shp导入postgis后的拓扑路网，实现沿路测距，通过wfs请求完成前端的服务实现 1、增加数据字段 -- Function: public.pgr_fromatob_4(character varying, double precision, double precision, double precision, double precision) -- DROP FUNCTION public.pgr_fromatob_4(character varying, double precision, double precision, double precision, double precision); CREATE OR REPLACE FUNCTION public.pgr_fromatob_4( 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 --select st_asText(pgr_fromatob_4) from pgr_fromatob_4('rshenzhen_polyline',113.87584447860719,22.5617927312851,113.88471722602846,22.563627362251285) --查询离起点最近的线 execute 'select geom, source, target from ' ||tbl|| ' where ST_DWithin(geom,ST_Geometryfromtext(''point('|| startx ||' ' || starty||')'',4326),100) order by ST_Distance(geom,ST_GeometryFromText(''point('|| startx ||' '|| starty ||')'',4326)) limit 1' into v_startLine, v_startSource ,v_startTarget; -- select * from public.shenzhen where shenzhen.direction = '2' limit 10 --查询离终点最近的线 execute 'select geom, source, target from ' ||tbl|| ' where ST_DWithin(geom,ST_Geometryfromtext(''point('|| endx || ' ' || endy ||')'',4326),100) order by ST_Distance(geom,ST_GeometryFromText(''point('|| endx ||' ' || endy ||')'',4326)) 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 ; select ST_ClosestPoint(v_startLine, ST_Geometryfromtext('point('|| startx ||' ' || starty ||')',4326)) into v_statpoint; select ST_ClosestPoint(v_endLine, ST_GeometryFromText('point('|| endx ||' ' || endy ||')',4326)) into v_endpoint; raise notice '%------------------------------------', st_asText(v_statpoint); raise notice '%------------------------------------', st_asText(v_endpoint); -- ST_Distance --从开始的起点到结束的起点最短路径 execute ' SELECT st_linemerge(st_union(b.geom)) FROM pgr_dijkstra('' SELECT gid AS id, source::integer, target::integer, cost::double precision AS cost, reverse_cost::double precision AS reverse_cost FROM '||tbl||''', '||v_startSource||', '||v_endSource||', true, true)a LEFT JOIN '||tbl||' b ON (a.id2 = b.gid) ' into v_res; --从开始的终点到结束的起点最短路径 execute ' SELECT st_linemerge(st_union(b.geom)) FROM pgr_dijkstra('' SELECT gid AS id, source::integer, target::integer, cost::double precision AS cost, reverse_cost::double precision AS reverse_cost FROM '||tbl||''', '||v_startTarget||', '||v_endSource||', true, true)a LEFT JOIN '||tbl||' b ON (a.id2 = b.gid) ' into v_res_b; --从开始的起点到结束的终点最短路径 execute ' SELECT st_linemerge(st_union(b.geom)) FROM pgr_dijkstra('' SELECT gid AS id, source::integer, target::integer, cost::double precision AS cost, reverse_cost::double precision AS reverse_cost FROM '||tbl||''', '||v_startSource||', '||v_endTarget||', true, true)a LEFT JOIN '||tbl||' b ON (a.id2 = b.gid) ' into v_res_c; --从开始的终点到结束的终点最短路径 execute ' SELECT st_linemerge(st_union(b.geom)) FROM pgr_dijkstra('' SELECT gid AS id, source::integer, target::integer, cost::double precision AS cost, reverse_cost::double precision AS reverse_cost FROM '||tbl||''', '||v_startTarget||', '||v_endTarget||', true, true)a LEFT JOIN '||tbl||' b ON (a.id2 = b.gid) ' into 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; --如果找不到最短路径，就返回null --if(v_res is null) then -- return null; --end if; --将v_res,v_startLine,v_endLine进行拼接 select st_linemerge(ST_Union(array[v_res,v_startLine,v_endLine])) into v_res; select ST_LineLocatePoint(v_res, v_statpoint) into v_perStart; select ST_LineLocatePoint(v_res, v_endpoint) into v_perEnd; -- raise notice '%------------------------------------v_perStart', st_asText(v_perStart); -- raise notice '%------------------------------------v_perEnd', st_asText(v_perEnd); if(v_perStart > v_perEnd) then tempnode = v_perStart; v_perStart = v_perEnd; v_perEnd = tempnode; end if; --截取v_res SELECT ST_Line_SubString(v_res,v_perStart, v_perEnd) into v_shPath; raise notice '%------------------------------------v_shPath', st_asText(v_shPath); return v_shPath; end; $BODY$ LANGUAGE plpgsql VOLATILE STRICT COST 100; ALTER FUNCTION public.pgr_fromatob_4(character varying, double precision, double precision, double precision, double precision) OWNER TO postgres; 2、调用 select st_asText(pgr_fromatob_4) from pgr_fromatob_4('tablename',113.87584447860719,22.5617927312851,113.88471722602846,22.563627362251285)

pgRouting pgr_dijkstra道路拓扑分析与方向分析

vip168

postgis 拓扑网路实现沿路测距
通过shp导入postgis后的拓扑路网，实现沿路测距，通过wfs请求完成前端的服务实现

1、增加数据字段

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

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

CREATE OR REPLACE FUNCTION public.pgr_fromatob_4(
    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;

此内容登录或注册后可见