通过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)


---------
资料：
--https://www.jianshu.com/p/4b9d22406bce

转载自：https://blog.csdn.net/u012539364/article/details/81478005