问导入OSM2PO后拆分OSM道路网络的线，以创建具有注浆功能的汇流

EN

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