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

Omar_Fernando_Pessoa · February 20, 2015, 11:16am

Hi guys.

That is my output…

SulSP_2015=# database_name=#\dx

Lista de extens§es instaladas
Nome | VersÒo | Esquema | DescriþÒo

Some another ideas?

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:

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