Ticket #87: routing_core_141207.patch

File routing_core_141207.patch, 8.1 KB (added by rodj59, 3 years ago)

  • .sql

    old new  
    2020 
    2121CREATE TYPE path_result AS (vertex_id integer, edge_id integer, cost float8); 
    2222CREATE TYPE vertex_result AS (x float8, y float8); 
    23  
    24 ----------------------------------------------------------------------- 
    2523-- Core function for shortest_path computation 
    2624-- See README for description 
    2725----------------------------------------------------------------------- 
    … …  
    9189       RETURNS void AS 
    9290$$ 
    9391BEGIN 
    94                 EXECUTE 'SELECT create_graph_tables('''||quote_ident(geom_table)||''',''int4'',' || '''gid'' ,''source_id'',''target_id'')'; 
     92                EXECUTE 'SELECT create_graph_tables('||quote_literal(geom_table)||',''int4'',' || '''gid'' ,''source_id'',''target_id'')'; 
    9593END; 
    9694$$ 
    9795LANGUAGE 'plpgsql' VOLATILE STRICT; 
    … …  
    10199       RETURNS void AS 
    102100$$ 
    103101BEGIN 
    104                 EXECUTE 'SELECT create_graph_tables('''||quote_ident(geom_table)||''','''||quote_ident(column_type)||''',' || '''gid'' ,''source_id'',''target_id'')'; 
     102                EXECUTE 'SELECT create_graph_tables('||quote_literal(geom_table)||','||quote_literal(column_type)||',' || '''gid'' ,''source_id'',''target_id'')'; 
    105103END; 
    106104$$ 
    107105LANGUAGE 'plpgsql' VOLATILE STRICT; 
    … …  
    111109       RETURNS void AS 
    112110$$ 
    113111BEGIN 
    114                 EXECUTE 'SELECT create_graph_tables('''||quote_literal(geom_table)||''','''||quote_literal(column_type)||''',''' || quote_literal(id_name) || ''' ,''source_id'',''target_id'')'; 
     112                EXECUTE 'SELECT create_graph_tables('||quote_literal(geom_table)||','||quote_literal(column_type)||',' || quote_literal(id_name) || ' ,''source_id'',''target_id'')'; 
    115113        RETURN; 
    116114END; 
    117115$$ 
    … …  
    206204-- This function uses the internal vertices identifiers. 
    207205----------------------------------------------------------------------- 
    208206CREATE OR REPLACE FUNCTION shortest_path_as_geometry_internal_id( 
    209        geom_table varchar, source int4, target int4)  
     207       geom_table varchar, source int4, target int4,geomcol varchar,costcol varchar)  
    210208       RETURNS SETOF GEOMS AS 
    211209$$ 
    212210DECLARE  
    … …  
    215213        v_id integer; 
    216214        e_id integer; 
    217215        geom geoms; 
     216        query text; 
    218217BEGIN 
    219          
    220         FOR path_result IN EXECUTE 'SELECT gid,the_geom FROM ' || 
    221           'shortest_path(''SELECT gid as id, source::integer, target::integer, ' ||  
    222           'length::double precision as cost FROM ' || 
    223           quote_ident(geom_table) || ''', ' || quote_literal(source) ||  
    224           ' , ' || quote_literal(target) || ' , false, false), ' ||  
    225           quote_ident(geom_table) || ' where edge_id = gid '  
     218                 
     219        query := 'SELECT a.edge_id as gid,'||quote_ident(geomcol)||' as the_geom FROM ' || 
     220          'shortest_path(''SELECT source ,id,  target, ' || quote_ident(costcol)|| 
     221          ' as cost from '||quote_ident(geom_table)||'_edges'','||source||','||target||',false,false)' || 
     222                        ' a, '||quote_ident(geom_table)||' b where b.edge_id = a.edge_id'; 
     223           
     224        FOR path_result IN EXECUTE query 
    226225        LOOP 
    227  
    228226                 geom.gid      := path_result.gid; 
    229227                 geom.the_geom := path_result.the_geom; 
    230228                  
    231229                 RETURN NEXT geom; 
    232  
    233230        END LOOP; 
    234231        RETURN; 
    235232END; 
    … …  
    237234LANGUAGE 'plpgsql' VOLATILE STRICT;  
    238235 
    239236CREATE OR REPLACE FUNCTION shortest_path_as_geometry_internal_id_directed( 
    240        geom_table varchar, source int4, target int4, dir boolean, rc boolean)  
     237       geom_table varchar, source int4, target int4, dir boolean, rc boolean 
     238           ,geomcol varchar,costcol varchar,rcostcol varchar)  
     239 
    241240       RETURNS SETOF GEOMS AS 
    242241$$ 
    243242DECLARE  
    … …  
    249248        query text; 
    250249BEGIN 
    251250         
    252         query := 'SELECT gid,the_geom FROM ' || 
    253           'shortest_path(''SELECT gid as id, source::integer, target::integer, ' ||  
    254           'length::double precision as cost '; 
     251        query := 'SELECT a.edge_id as gid,'||quote_ident(geomcol)||' as the_geom FROM ' || 
     252          'shortest_path(''SELECT source ,id,  target, ' || quote_ident(costcol)|| 
     253          ' as cost '; 
     254                 
     255 
     256--      query := 'SELECT gid,the_geom FROM ' || 
     257--          'shortest_path(''SELECT gid as id, source::integer, target::integer, ' ||  
     258--          'length::double precision as cost '; 
     259--      query := query || 'FROM ' ||  quote_ident(geom_table) || ''', ' || quote_literal(source) ||  
     260--          ' , ' || quote_literal(target) || ' , '''||dir||''', '''||rc||'''), ' ||  
     261--         quote_ident(geom_table) || ' where edge_id = gid '; 
    255262           
    256         IF rc THEN query := query || ', reverse_cost ';   
     263        IF rc THEN  
     264                query := query ||  
     265                ','||quote_ident(rcostcol)||' as reverse_cost '; 
    257266        END IF; 
    258          
    259         query := query || 'FROM ' ||  quote_ident(geom_table) || ''', ' || quote_literal(source) ||  
    260           ' , ' || quote_literal(target) || ' , '''||dir||''', '''||rc||'''), ' ||  
    261           quote_ident(geom_table) || ' where edge_id = gid '; 
     267        query := query || ' from '||quote_ident(geom_table)||'_edges'','||quote_literal(source)||','||quote_literal(target)||','||dir||','||rc||') a, ' || quote_ident(geom_table)||' b where a.edge_id = b.edge_id '; 
     268 
    262269 
    263270        FOR path_result IN EXECUTE query 
    264271        LOOP 
    … …  
    279286--  the result as a set of (gid integer, the_geom gemoetry) records. 
    280287----------------------------------------------------------------------- 
    281288CREATE OR REPLACE FUNCTION shortest_path_as_geometry(geom_table varchar,  
    282        geom_source anyelement,geom_target anyelement)  
     289       geom_source anyelement,geom_target anyelement) 
     290       RETURNS SETOF GEOMS AS  
     291$$ 
     292select * from shortest_path_as_geometry($1,$2,$3,'the_geom') 
     293$$ 
     294LANGUAGE 'sql' VOLATILE STRICT;  
     295 
     296 
     297CREATE OR REPLACE FUNCTION shortest_path_as_geometry(geom_table varchar,  
     298       geom_source anyelement,geom_target anyelement,geomcol varchar,costcol varchar)  
    283299       RETURNS SETOF GEOMS AS 
    284300$$ 
    285301DECLARE  
    … …  
    292308        geom geoms; 
    293309BEGIN 
    294310        FOR r IN EXECUTE 'SELECT id FROM ' || quote_ident(geom_table) ||  
    295            '_vertices WHERE geom_id = ' || quote_literal(geom_source) LOOP 
     311           '_vertices WHERE id = ' || quote_literal(geom_source) LOOP 
    296312 
    297313                source = r.id; 
    298314 
    … …  
    303319        END IF; 
    304320 
    305321        FOR r IN EXECUTE 'SELECT id FROM ' || quote_ident(geom_table) ||  
    306             '_vertices WHERE geom_id = ' || quote_literal(geom_target) LOOP 
     322            '_vertices WHERE id = ' || quote_literal(geom_target) LOOP 
    307323                target = r.id; 
    308324        END LOOP; 
    309325 
    … …  
    313329         
    314330        FOR geom IN SELECT * FROM  
    315331          shortest_path_as_geometry_internal_id(geom_table,  
    316                                                 source, target) LOOP 
     332                                                source, target,geomcol,costcol) LOOP 
    317333                RETURN NEXT geom; 
    318334        END LOOP; 
    319335 
    … …  
    322338$$ 
    323339LANGUAGE 'plpgsql' VOLATILE STRICT;  
    324340 
    325  
    326  
     341-- 
     342-- Sometimes imported graphs have problems: unremedied the distances can be multiplied 
     343-- class is the column name which determines primary precedence with lower values  
     344-- having higher priority. Only one record is kept for each distinct geometry. 
     345-- pkey is the unique primary key for the 'tab' geotable. 
     346-- 
     347CREATE OR REPLACE FUNCTION remove_duplicate_geoms(tab varchar,geocol varchar,class varchar,pkey varchar) 
     348    RETURNS void AS 
     349$$ 
     350DECLARE 
     351    row record; 
     352    row2 record; 
     353BEGIN 
     354    -- find duplicate rows having the same geometry,choose the lowest class to keep 
     355    FOR row IN EXECUTE  
     356            'select astext('||quote_ident(geocol)||') as geom,min('||quote_ident(class)||') as class from '|| 
     357                quote_ident(tab)||' group by '||quote_ident(geocol) 
     358                || ' having count('||quote_ident(class)||') > 1' 
     359    LOOP 
     360                -- given the lowest class for each, get the primary key of one &  delete the rest 
     361                EXECUTE 'select min('||quote_ident(pkey)||') as key from '||quote_ident(tab)||' where '||quote_ident(class)|| 
     362                ' = '||row.class||' and astext('||quote_ident(geocol)||') = '''||row.geom||''' limit 1'  
     363            INTO row2; 
     364                EXECUTE 'delete from '||quote_ident(tab)||' where astext('||quote_ident(geocol)||') = '''||row.geom|| 
     365                ''' and '||quote_ident(pkey)||' != '||row2.key; 
     366    END LOOP; 
     367        -- don't ask me ??? 
     368    EXECUTE 'delete from '||quote_ident(tab)||' where astext('||quote_ident(geocol)||') = '''||row.geom|| 
     369                ''' and '||quote_ident(pkey)||' != '||row2.key; 
     370END; 
     371$$ 
     372LANGUAGE 'plpgsql' ;