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

Hi guys.

That is my output…

SulSP_2015=# database_name=#\dx

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

hstore | 1.2 | public | data type for storing sets of (key, value) pairs
pgrouting | 2.0.0 | public | pgRouting Extension
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
postgis | 2.1.2 | public | PostGIS geometry, geography, and raster spatial types and functions
postgis_topology | 2.1.2 | topology | PostGIS topology spatial types and functions
(5 registros)

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

To subscribe or unsubscribe via the World Wide Web, visit
or, via email, send a message with subject or body ‘help’ to

You can reach the person managing the list at

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

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)
AS $$
vertex_storage hstore;
new_vertex int;
vertex_storage := ‘’::hstore;
new_vertex := 1;
FOR row in EXECUTE FORMAT(‘SELECT id, source, target FROM %I’, $1)
IF NOT vertex_storage ? row.source::text THEN
vertex_storage = vertex_storage || (row.source::text => new_vertex::text);
new_vertex := new_vertex + 1;
IF NOT vertex_storage ? row.target::text THEN
vertex_storage = vertex_storage || (row.target::text => new_vertex::text);
new_vertex := new_vertex + 1;
FOR row IN SELECT key, value FROM EACH(vertex_storage)
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;
$$ LANGUAGE plpgsql;

Best regards
-------------- 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

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