postGISに基づく室内地図最短経路アルゴリズム2

前編の室内地図アルゴリズムは、新バージョンのPostgreSQLでは使用できませんが、提示関数が古いため、以下のアルゴリズムを修正しました

DROP FUNCTION pgr_floor(tbl varchar,startx float, starty float,endx float,endy float,fnumber integer);
--tbl    
--startx    
--starty    
--endx    
--endy    
--fnumber  
CREATE OR REPLACE function pgr_floor(tbl varchar,startx float, starty float,endx float,endy float,fnumber integer) 
 
--      
returns geometry as 

$body$  

  

declare 

    fmin integer;
    fmax integer;

    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

raise notice '%','    ';

fmin=fnumber*1000;
fmax=fmin+1000;
raise notice '%', fmin;
raise notice '%', fmax;
    --          
    --4326   
    --   15        

    execute 'select geom, source, target  from ' ||tbl||

                            ' where ST_DWithin(geom,ST_Geometryfromtext(''point('||         startx ||' ' || starty||')'',4326),15)

                            and source between '||fmin||' and '||fmax||' order by ST_Distance(geom,ST_GeometryFromText(''point('|| startx ||' '|| starty ||')'',4326))  limit 1'

                            into v_startLine, v_startSource ,v_startTarget; 

raise notice '%',  v_startSource;
raise notice '%', v_startTarget;

    --          
    --   15        

    execute 'select geom, source, target from ' ||tbl||

                            ' where ST_DWithin(geom,ST_Geometryfromtext(''point('|| endx || ' ' || endy ||')'',4326),15) 
                            and source between '||fmin||' and '||fmax||' 

                            order by ST_Distance(geom,ST_GeometryFromText(''point('|| endx ||' ' || endy ||')'',4326))  limit 1'

                            into v_endLine, v_endSource,v_endTarget; 
raise notice '%',  v_endSource;
raise notice '%', v_endTarget;
 

    --         ，   null 

    if (v_startLine is null) or (v_endLine is null) then 
        return null; 
         raise notice '%', 'null1';
    end if ; 

 raise notice '%', '      ';
 


    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_Distance 

     

    --                

    execute 'SELECT st_linemerge(st_union(ad.geom)) FROM (SELECT geom FROM road_line where gid in (SELECT id2 AS gid  FROM pgr_dijkstra(''
                SELECT  gid AS id,
                         source::integer,
                         target::integer,
                         length::double precision AS cost
                        FROM ' || tbl ||''',
               ' ||v_startSource|| ', ' ||v_endSource||' , false, false))) ad' into v_res ;

    raise notice '%', '  1';

    --                
      execute 'SELECT st_linemerge(st_union(ad.geom)) FROM (SELECT geom FROM road_line where gid in (SELECT id2 AS gid  FROM pgr_dijkstra(''
                SELECT  gid AS id,
                         source::integer,
                         target::integer,
                         length::double precision AS cost
                        FROM ' || tbl ||''',
               ' ||v_startTarget|| ', ' ||v_endSource||' , false, false))) ad' into v_res_b ;

    raise notice '%', '  2';


    --                
      execute 'SELECT st_linemerge(st_union(ad.geom)) FROM (SELECT geom FROM road_line where gid in (SELECT id2 AS gid  FROM pgr_dijkstra(''
                SELECT  gid AS id,
                         source::integer,
                         target::integer,
                         length::double precision AS cost
                        FROM ' || tbl ||''',
               ' ||v_startSource|| ', ' ||v_endTarget||' , false, false))) ad' into v_res_c ;

    raise notice '%', '  3';
    
 

    --                
    
        execute 'SELECT st_linemerge(st_union(ad.geom)) FROM (SELECT geom FROM road_line where gid in (SELECT id2 AS gid  FROM pgr_dijkstra(''
                SELECT  gid AS id,
                         source::integer,
                         target::integer,
                         length::double precision AS cost
                        FROM ' || tbl ||''',
               ' ||v_startTarget|| ', ' ||v_endTarget||' , false, false))) ad' into v_res_d ;

    raise notice '%', '  4';

  

    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; 
        raise notice '%', 'null2';
    end if; 

     
 raise notice '%', '      ';
    -- 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; 

        

    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 '%', '    ';

    return v_shPath; 

 

end; 

$body$ 

LANGUAGE plpgsql VOLATILE STRICT;