[pgrouting-users] Pgrouting-users Digest, Vol 77, Issue 4

[Eric Scheibler]

Hello Omar,

I've tested my function with the Postgresql version 9.1. I discovered, that the "=>" operator is
marked as deprecated. So maybe it was removed in a newer Postgresql version. Try to replace with the
following line:

vertex_storage = vertex_storage || hstore(row.source::text, new_vertex::text);

And another hint: The routing table must have an index at the source and target columns like this:

CREATE INDEX idx_tmp_routing_source ON tmp_routing_table USING btree (source);
CREATE INDEX idx_tmp_routing_target ON tmp_routing_table USING btree (target);

Otherwise it's much slower.

And here is the full function again:

CREATE OR REPLACE FUNCTION recreate_vertex_of_routing_table(regclass)
RETURNS void
AS $$
DECLARE
    row RECORD;
    vertex_storage hstore;
    new_vertex int;
BEGIN
    vertex_storage := ''::hstore;
    new_vertex := 1;
    FOR row in EXECUTE FORMAT('SELECT id, source, target FROM %I', $1)
    LOOP
        IF NOT vertex_storage ? row.source::text THEN
            vertex_storage = vertex_storage || hstore(row.source::text, new_vertex::text);
            new_vertex := new_vertex + 1;
        END IF;
        IF NOT vertex_storage ? row.target::text THEN
            vertex_storage = vertex_storage || hstore(row.target::text, new_vertex::text);
            new_vertex := new_vertex + 1;
        END IF;
    END LOOP;
    FOR row IN SELECT key, value FROM EACH(vertex_storage)
    LOOP
        EXECUTE FORMAT('UPDATE %I SET source=$1 WHERE source = $2', $1) USING row.value::int, row.key::int;
        EXECUTE FORMAT('UPDATE %I SET target=$1 WHERE target = $2', $1) USING row.value::int, row.key::int;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Hope, it helps
Best regards
Eric
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 473 bytes
Desc: Digital signature
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20150213/aff577ca/attachment.pgp>