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!
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