[postgis-users] Pb whith pgrouting create_graph_tables function

[Eric FRANCOIS]

Hello,

I've try all Pgrouting functions on a streets table with 100000 records and
It works fine.

But when I try to use my real streets table (about 7 millions records from
navtech streets shape and dbf), the function  "create_graph_tables" run
during hours and hours and use 99%CPU. And I don't know how many time the
query will still running.



Thanks for help

ERIC

My conf:

Toshiba Tecra A4 with 1 Go Ram running PostgreSQl 8.1.4
Database on external disk, pg_xlog on local disk
No index on streets table.
dump and restore database before use the create_graph_tables function

postgresql.conf:
shared_buffers = 15000
work_mem = 10240
maintenance_work_mem = 400000
autovacuum=off
max_fsm_pages = 150000
fsync = off
checkpoint_segments = 8
effective_cache_size = 5000


the function:
CREATE OR REPLACE FUNCTION create_graph_tables_matiasat(geom_table varchar,
       column_type varchar)
       RETURNS void AS
$$
DECLARE
        geom record;
        edge_id int;
        myrec record;
        source_id int;
        target_id int;
        vertices_table varchar := quote_ident(geom_table) || '_vertices';
        edges_table varchar := quote_ident(geom_table) || '_edges';
BEGIN

        EXECUTE 'CREATE TABLE ' || vertices_table ||
                ' (id serial, geom_id ' || quote_ident(column_type) ||
                '  NOT NULL UNIQUE)';

        EXECUTE 'CREATE INDEX ' || vertices_table || '_id_idx on ' ||
                vertices_table || ' (id)';

        EXECUTE 'CREATE TABLE ' || edges_table ||
                ' (id serial, source int, target int, ' ||
                'cost float8, reverse_cost float8, UNIQUE (source,
target))';

        EXECUTE 'CREATE INDEX ' || edges_table ||
                '_source_target_idx on ' || edges_table ||
                ' (source, target)';

        FOR geom IN EXECUTE 'SELECT distinct on (link_id) link_id,gid as id,
' ||
             ' ref_in_id AS source, ' ||
             ' nref_in_id AS target FROM ' || quote_ident(geom_table) LOOP

                SELECT INTO source_id insert_vertex(vertices_table,
                                                    geom.source);

                SELECT INTO target_id insert_vertex(vertices_table,
                                                    geom.target);

                BEGIN
                    EXECUTE 'INSERT INTO ' || edges_table ||
                            ' (source, target) VALUES ('  ||
                            quote_literal(source_id) || ', ' ||
                            quote_literal(target_id) || ')';

                EXCEPTION
                        WHEN UNIQUE_VIOLATION THEN
                END;

                FOR myrec IN EXECUTE 'SELECT id FROM ' || edges_table ||
                    ' e WHERE ' || ' e.source = ' ||
                    quote_literal(source_id) ||
                    ' and e.target = ' ||
                    quote_literal(target_id) LOOP
                END LOOP;

                edge_id := myrec.id;

                IF edge_id IS NULL OR edge_id < 0 THEN
                        RAISE EXCEPTION 'Bad edge id';
                END IF;

		BEGIN
                EXECUTE 'UPDATE ' || quote_ident(geom_table) ||
                        ' SET edge_id = ' || edge_id ||
                        ' WHERE gid =  ' || geom.id;
		END;
        END LOOP;
        RETURN;
END;
$$
LANGUAGE 'plpgsql' VOLATILE STRICT;

CREATE OR REPLACE FUNCTION insert_vertex(vertices_table varchar,
       geom_id anyelement)
       RETURNS int AS
$$
DECLARE
        vertex_id int;
        myrec record;
BEGIN
        LOOP
          FOR myrec IN EXECUTE 'SELECT id FROM ' ||
                     quote_ident(vertices_table) ||
                     ' WHERE geom_id = ' || quote_literal(geom_id)  LOOP

                        IF myrec.id IS NOT NULL THEN
                                RETURN myrec.id;
                        END IF;
          END LOOP;
          EXECUTE 'INSERT INTO ' || quote_ident(vertices_table) ||
                  ' (geom_id) VALUES (' || quote_literal(geom_id) || ')';
        END LOOP;
END;
$$
LANGUAGE 'plpgsql' VOLATILE STRICT;