postgis 2.5 pgr_fromAtoB

CREATE OR REPLACE FUNCTION "public"."pgr_fromatob"("startx" float8, "starty" float8, "endx" float8, "endy" float8, "tbl" varchar='line_guide'::character varying, "directed" bool=true, "topology_geom" varchar='data'::character varying, "topology_id" varchar='gid'::character varying, "topology_source" varchar='source'::character varying, "topology_target" varchar='target'::character varying, "topology_length" varchar='length'::character varying)
  RETURNS "public"."geometry" AS $BODY$  
declare  
    v_preStartLine geometry;-- the line of start point to  v_statpoint
    v_preEndLine geometry;-- the line of end point to  v_endpoint 

    v_preStartLineX float;-- v_preStartLine X
    v_preStartLineY float;-- v_preStartLine Y
    v_preEndLineX float;-- v_preEndLine X
    v_preEndLineY float;-- v_preEndLine Y

    v_startLine geometry;-- start point's nearest line  
    v_endLine geometry;-- end point's nearest line  
      
    v_startTarget integer;-- the end point of the line that nearest start point  
    v_endSource integer;-- the start point of the line that nearest end point 
  
    v_startpoint geometry;-- the point of nearest start point at v_startLine 
    v_endpoint geometry;-- the point of nearest end point at v_endLine  
      
    v_res geometry;-- shortest path geometry result 
  
    v_perStart float;--v_statpoint at v_res percentage
    v_perEnd float;--v_endpoint  at v_res percentage 

    v_perStartLine geometry;--v_statpoint at v_res percentage Line
    v_perEndLine geometry;--v_endpoint  at v_res percentage Line
  
    v_shPath geometry;-- result
begin     
    -- Software Version:
    -- pgrouting version:2.4.1 
    -- postgis 2.3.2
    -- postgresql 9.6.3

    -- Algorithm Version:1.1.1
    -- Author:itas109
    -- http://blog.youkuaiyun.com/itas109
    -- https://github.com/itas109

    -- set topology null
    -- UPDATE ||tbl|| SET source = NULL,target = NULL;    
    -- create topology
    -- select pgr_createTopology('||tbl||','||tolerance||',source:='source',id:='gid',target:='target',the_geom:='data');
    -- set length
    -- UPDATE line_guide SET length = ST_Length(data);

    -- ******************** step 1 start ****************************** 

    -- find nearest start line and start target id in topology
    execute 'select geom,target  from ' ||tbl||    ' 
      where 
            ST_DWithin(geom,ST_Geometryfromtext(''point('||    startx ||' ' || starty||')'',4326),50) 
            order by ST_Distance(geom,ST_GeometryFromText(''point('|| startx ||' '|| starty ||')'',4326))  limit 1' 
            into v_startLine ,v_startTarget;  
      
    -- find nearest end line and end source id in topology 
    execute 'select geom,source  from ' ||tbl||    ' 
            where
            ST_DWithin(geom,ST_Geometryfromtext(''point('|| endx || ' ' || endy ||')'',4326),50) 
            order by ST_Distance(geom,ST_GeometryFromText(''point('|| endx ||' ' || endy ||')'',4326))  limit 1' 
            into v_endLine,v_endSource;  
  
    -- if no shortest path,return null   
    if (v_startLine is null) or (v_endLine is null) then  
        return null;  
    end if ;  
    -- ******************** step 1 end ******************************

    -- ******************** step 2 start ******************************
  
    -- start point nearest point at start line
    select  ST_ClosestPoint(v_startLine, ST_Geometryfromtext('point('|| startx ||' ' || starty ||')',4326)) into v_startpoint;  
        -- end point nearest point at end line
    select  ST_ClosestPoint(v_endLine, ST_GeometryFromText('point('|| endx ||' ' || endy ||')',4326)) into v_endpoint;  

    -- sub v_startLine to v_perStartLine
      select  ST_LineLocatePoint(v_startLine, v_startpoint) into v_perStart;  
    select ST_Line_SubString(v_startLine,v_perStart, 1) into v_perStartLine;

    -- sub v_endLine to v_perEndLine
    select  ST_LineLocatePoint(v_endLine, v_endpoint) into v_perEnd;  
    select ST_Line_SubString(v_endLine,0, v_perEnd) into v_perEndLine;  
  
    --  if v_startLine equal v_endLine,and v_perStart > v_perEnd, represent path is opposite
    if (v_startLine = v_endLine) and (v_perStart > v_perEnd) then  
        return null;  
    end if ;  

    -- if v_startLine equal v_endLine,and path is directed. sub line to result,and return
    if(v_startLine = v_endLine) and (v_perStart < v_perEnd) then  
        select ST_Line_SubString(v_startLine,v_perStart, v_perEnd) into v_shPath; 
                return v_shPath;
    end if;
    -- ******************** step 2 end ******************************

    -- ******************** step 3 start ******************************

    -- get prepare line of start and end point to closed line point
    select ST_X(v_startpoint),ST_Y(v_startpoint) into v_preStartLineX,v_preStartLineY;
    select ST_GeomFromText('LINESTRING('|| startx ||' ' || starty ||',' || v_preStartLineX ||' ' || v_preStartLineY ||')') into v_preStartLine;
    select ST_X(v_endpoint),ST_Y(v_endpoint) into v_preEndLineX,v_preEndLineY;
    select ST_LineFromText('LINESTRING('|| endx ||' ' || endy ||',' || v_preEndLineX ||' ' || v_preEndLineY ||')') into v_preEndLine;
    -- ******************** step 3 end ******************************

    -- ******************** step 4 start ******************************

    -- get dijkstra path
    -- pgr_dijkstra(text sql, integer source, integer target,boolean directed, boolean has_rcost);
    -- we set directed true.
    execute 'SELECT st_linemerge(st_union(b.'||topology_geom||')) ' || 
    'FROM pgr_dijkstra(  
    ''SELECT '||topology_id||' as id, '||topology_source||', '||topology_target||', '||topology_length||' as cost FROM ' || tbl ||''','  
    ||v_startTarget||', '||v_endSource||' , '||directed||'  
    ) a, 
        '||tbl||' b  
    WHERE a.edge=b.gid' into v_res ;  
        
           -- execute 'SELECT st_linemerge(st_union(b.'||topology_geom||')) ' || 
    --'FROM pgr_dijkstra(  
    --''SELECT '||topology_id||' as id, '||topology_source||', '||topology_target||', '||topology_length||' as cost FROM ' || tbl ||''','  
   -- ||v_startTarget||', '||v_endSource||' , '||directed||'  
    --) a, 
        --'||tbl||' b  
    --WHERE a.node=b.gid2  
    --GROUP by node  
   -- ORDER by node' into v_res ; 

    -- if  v_startTarget = v_endSource, v_res equal null is OK
    if(v_res is null) and (v_startTarget != v_endSource) then  
        return null;  
    end if;
    -- ******************** step 4 end ******************************

    -- ******************** step 5 start ******************************

    -- v_preStartLine,v_startLine,v_res,v_endLine,,v_preEndLine merge 
    -- we allow the result is mutilinestring
    --select  st_linemerge(v_res) into v_shPath; 
         select  st_linemerge(ST_Union(array[st_setsrid(v_preStartLine,4326),st_setsrid(v_startLine,4326),st_setsrid(v_res,4326),st_setsrid(v_endLine,4326),st_setsrid(v_preEndLine,4326)])) into v_shPath; 
        --SELECT ST_Line_SubString(v_res,v_preStartLine, v_preEndLine) into v_shPath;
    -- ******************** step 5 end ******************************
    return v_shPath;   
      
end;  
$BODY$
  LANGUAGE plpgsql VOLATILE STRICT
  COST 100

SQL查询SELECT pgr_fromatob AS geojson FROM pgr_fromAtoB('120.15245186846','33.2985247306327','120.150558491682', '33.2972723248588','ws_line_gcj02',false,'geom');

查找线上所有点

select t1.stcd from sewage_inspection_well_gcj02 t1
INNER JOIN (
       select pgr_fromatob as  geomLine  FROM pgr_fromAtoB('120.15245186846','33.2985247306327','120.150558491682', '33.2972723248588','ws_line_gcj02',false,'geom')
) t2 ON st_intersects(st_buffer(t2.geomLine,0.001),t1.geom)