postgresqlopenstreetmappostgispgrouting

Split lines of OSM road network after OSM2PO import for creating a catchment with pgrouting


I want to create a catchment with pgrouting based on an osm.pbf file. After importing the pbf-file with OSM2PO the calculation of an catchment with pgrouting works fine. But for a more detailed result it would be nice to split all longer roads at least after 2km. Splitting lines with PostGIS is no problem, but this would destroy my routing network. Is the another way to add more vertices? Thanks!


Solution

  • In fact, you can split road lines how you like and then recreate your road graph by pgr_createTopology and pgr_analyzeGraph. I've managed to do this using the following function in plpgsql

    CREATE OR REPLACE FUNCTION public.__create_roads_graph(
        tabname text)
        RETURNS text
        LANGUAGE 'plpgsql'
    AS $BODY$
    
    DECLARE
            counter integer;
            expr text;
            vert_tab character varying;
            r record;
    BEGIN
        expr := 'select count(*) as cnt from %s';
        expr := format(expr, tabname);
        counter := 0;
        for r in execute expr
        loop
            counter = r.cnt;
        end loop;
        if counter = 0
        then
            return 'Cancel';
        end if;
    
        expr := 'update ' || tabname || ' set km = ST_Length(geom_way::geography)/1000';
        execute expr;
        expr := 'update ' || tabname || ' set kmh=(CASE WHEN class=1 THEN 90 WHEN class=2 THEN 60 WHEN class=3 THEN 30 WHEN class=4 THEN 10 ELSE 0 END)
                                WHERE kmh is null OR kmh < 1';
        execute expr;
        expr := 'update ' || tabname || ' set cost = km / kmh where cost is null or cost=0';
        execute expr;
        expr := 'update ' || tabname || ' set reverse_cost = km / kmh where reverse_cost is null or reverse_cost=0';
        execute expr;
        expr := 'update ' || tabname || ' set x1=ST_X(ST_StartPoint(geom_way)), 
                               y1=ST_Y(ST_StartPoint(geom_way)), 
                               x2=ST_X(ST_EndPoint(geom_way)), 
                               y2=ST_Y(ST_EndPoint(geom_way))';
        execute expr;
        expr := format('select * from pgr_createTopology( %L, %s, %L, %L, %L, %L, %L, %s)', 
                       tabname, 0.000001, 'geom_way', 'id', 'source', 'target', 'true', 'true');
        execute expr;
    
        vert_tab := tabname ||  '_vertices_pgr';
        perform __bvv_recreate_vertices_table(vert_tab);
    
        expr := format('insert into %s(id, cnt, the_geom) with RECURSIVE united as (SELECT source AS id, x1 AS x, y1 AS y
                           FROM %s UNION ALL select target AS id, x2 AS x, y2 AS y
                           FROM %s) select id, count(*) as cnt, 
                           ST_SetSrid(ST_MakePoint(avg(x), avg(y)), 4326) from united group by id order by id', 
                            vert_tab, tabname, tabname);
    
    
    
        /*expr := format('select * from pgr_analyzeGraph( %L, %s, %L, %L, %L, %L)', 
                       tabname, 0.000001, 'geom_way', 'id', 'source', 'target');*/
        execute expr;
        RETURN 'Ok';
    END
    

    You can see how route looks before splitting road segment Nodes and route before splitting

    After splitting and doing the function _create_roads_graph you can see additional node in the centre of the line, changed numeration of the nodes and the identical to the previous route

    After splitting