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

pgRouting pgrouting-users
1

Hi Daniel. I already did it. Had already installed the extension.

text => text operator doesnt exist?

Em Fri Feb 13 2015 at 12:00:01, <pgrouting-users-request@lists.osgeo.org> escreveu:

Send Pgrouting-users mailing list submissions to
pgrouting-users@lists.osgeo.org

To subscribe or unsubscribe via the World Wide Web, visit
http://lists.osgeo.org/mailman/listinfo/pgrouting-users
or, via email, send a message with subject or body ‘help’ to
pgrouting-users-request@lists.osgeo.org

You can reach the person managing the list at
pgrouting-users-owner@lists.osgeo.org

When replying, please edit your Subject line so it is more specific
than “Re: Contents of Pgrouting-users digest…”

Today’s Topics:

  1. Re: Pgrouting-users Digest, Vol 77, Issue 3 (Omar Fernando Pess?a)
  2. Re: Pgrouting-users Digest, Vol 77, Issue 3 (Daniel Kastl)

Message: 1
Date: Fri, 13 Feb 2015 12:29:18 +0000
From: Omar Fernando Pess?a <omar.pessoa@gmail.com>
To: pgrouting-users@lists.osgeo.org
Subject: Re: [pgrouting-users] Pgrouting-users Digest, Vol 77, Issue 3
Message-ID:
<CAOikBm5o36YG0rqNFOxzycFf3sAsLLGn5C+VuEtbk07D3SYJ7Q@mail.gmail.com>
Content-Type: text/plain; charset=“utf-8”

Hi Erick I have same error, but creating your function I get this error:

ERROR: operator does not exists: text => text
LINE 1: SELECT vertex_storage || (row.source::text => new_vertex::te…

Thanks.

Em Thu Feb 12 2015 at 18:02:07, <pgrouting-users-request@lists.osgeo.org>
escreveu:

Send Pgrouting-users mailing list submissions to
pgrouting-users@lists.osgeo.org

To subscribe or unsubscribe via the World Wide Web, visit
http://lists.osgeo.org/mailman/listinfo/pgrouting-users
or, via email, send a message with subject or body ‘help’ to
pgrouting-users-request@lists.osgeo.org

You can reach the person managing the list at
pgrouting-users-owner@lists.osgeo.org

When replying, please edit your Subject line so it is more specific
than “Re: Contents of Pgrouting-users digest…”

Today’s Topics:

  1. Re: Performance problems with pgr_dijkstra (Eric Scheibler)

Message: 1
Date: Wed, 11 Feb 2015 21:39:33 +0100
From: Eric Scheibler <email@eric-scheibler.de>
To: pgRouting users mailing list <pgrouting-users@lists.osgeo.org>
Subject: Re: [pgrouting-users] Performance problems with pgr_dijkstra
Message-ID: 20150211203932.GC10577@scimitar
Content-Type: text/plain; charset=“us-ascii”

Eric Scheibler <email@eric-scheibler.de> schrieb am 11.02.2015, 12:23
+0100:

Daniel Kastl <daniel@georepublic.de> schrieb am 11.02.2015, 10:54 +0900:

You’re right, that with pgRouting the amount of data selected from the
network table matters. And the fastest way to select only a part of the
network table is by selecting a bounding box. You should have an index on
your geometry column as well. Then you don’t need to create temporary
tables.

Do you have an example for a bounding box? How to determine the box size?
I know the distance
between the starting and destination point in meters. Could that be used?

Found that, works.

Back to your question: as far as I remember, the size of ID’s can
matter. I
experienced this when I used data, that had already source and target
ID’s
in place, which all had the same number of digits. Renumbering (starting
from 1) helped to improve the speed. Though I can’t tell this is the
reason
in your case.

Very interesting. You could be right. I created a temp routing table in
the Saxony database, took
start and destination vertex from my program and verified the process
time and the result (4 rows
and 60 ms for a very short way, approximately 100 meters). Then I dumped
the created table with
pg_dump and restored it into the Europe database. Now the same routing
query runs as fast as in the
small database. So maybe the higher source and target id’s are
responsible for that.

Yes, that solved the problem. Now the routing query completes after 30-40
ms. So it’s even a bit
faster than at the small database. I’ve created a SQL function, which
recreates the source and
target id’s of the temp routing table:

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 || (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 || (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;

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/20150211/d33fef4d/attachment-0001.pgp>

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

End of Pgrouting-users Digest, Vol 77, Issue 3

-------------- next part --------------
An HTML attachment was scrubbed…
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20150213/fb804a74/attachment-0001.html>

Message: 2
Date: Fri, 13 Feb 2015 22:59:34 +0900
From: Daniel Kastl <daniel@georepublic.de>
To: pgRouting users mailing list <pgrouting-users@lists.osgeo.org>
Subject: Re: [pgrouting-users] Pgrouting-users Digest, Vol 77, Issue 3
Message-ID:
<CABXBSH9DL8JisK5zG-cQ8WgdEze9cA9rp3iG51JcEfL71N1JCA@mail.gmail.com>
Content-Type: text/plain; charset=“utf-8”

Hi Omar,

I think Erik used “hstore” in his function, which you first have to add to
your database with

CREATE EXTENSION hstore;

Hope this helps,
Daniel

On Fri, Feb 13, 2015 at 9:29 PM, Omar Fernando Pess?a <omar.pessoa@gmail.com

wrote:

Hi Erick I have same error, but creating your function I get this error:

ERROR: operator does not exists: text => text
LINE 1: SELECT vertex_storage || (row.source::text => new_vertex::te…

Thanks.

Em Thu Feb 12 2015 at 18:02:07, <pgrouting-users-request@lists.osgeo.org>
escreveu:

Send Pgrouting-users mailing list submissions to
pgrouting-users@lists.osgeo.org

To subscribe or unsubscribe via the World Wide Web, visit
http://lists.osgeo.org/mailman/listinfo/pgrouting-users
or, via email, send a message with subject or body ‘help’ to
pgrouting-users-request@lists.osgeo.org

You can reach the person managing the list at
pgrouting-users-owner@lists.osgeo.org

When replying, please edit your Subject line so it is more specific
than “Re: Contents of Pgrouting-users digest…”

Today’s Topics:

  1. Re: Performance problems with pgr_dijkstra (Eric Scheibler)

Message: 1
Date: Wed, 11 Feb 2015 21:39:33 +0100
From: Eric Scheibler <email@eric-scheibler.de>
To: pgRouting users mailing list <pgrouting-users@lists.osgeo.org>
Subject: Re: [pgrouting-users] Performance problems with pgr_dijkstra
Message-ID: 20150211203932.GC10577@scimitar
Content-Type: text/plain; charset=“us-ascii”

Eric Scheibler <email@eric-scheibler.de> schrieb am 11.02.2015, 12:23
+0100:

Daniel Kastl <daniel@georepublic.de> schrieb am 11.02.2015, 10:54 +0900:

You’re right, that with pgRouting the amount of data selected from the
network table matters. And the fastest way to select only a part of the
network table is by selecting a bounding box. You should have an index
on
your geometry column as well. Then you don’t need to create temporary
tables.

Do you have an example for a bounding box? How to determine the box
size? I know the distance
between the starting and destination point in meters. Could that be used?

Found that, works.

Back to your question: as far as I remember, the size of ID’s can
matter. I
experienced this when I used data, that had already source and target
ID’s
in place, which all had the same number of digits. Renumbering (starting
from 1) helped to improve the speed. Though I can’t tell this is the
reason
in your case.

Very interesting. You could be right. I created a temp routing table in
the Saxony database, took
start and destination vertex from my program and verified the process
time and the result (4 rows
and 60 ms for a very short way, approximately 100 meters). Then I dumped
the created table with
pg_dump and restored it into the Europe database. Now the same routing
query runs as fast as in the
small database. So maybe the higher source and target id’s are
responsible for that.

Yes, that solved the problem. Now the routing query completes after 30-40
ms. So it’s even a bit
faster than at the small database. I’ve created a SQL function, which
recreates the source and
target id’s of the temp routing table:

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 || (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 || (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;

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/20150211/d33fef4d/attachment-0001.pgp>

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

End of Pgrouting-users Digest, Vol 77, Issue 3

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

–
Georepublic UG & Georepublic Japan
eMail: daniel.kastl@georepublic.de
Web: http://georepublic.info
-------------- next part --------------
An HTML attachment was scrubbed…
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20150213/5bd90a12/attachment.html>

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

End of Pgrouting-users Digest, Vol 77, Issue 4

2

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