<div dir="ltr"><span style="font-size:13.1999998092651px;line-height:19.7999992370605px">Hi guys.</span><div style="font-size:13.1999998092651px;line-height:19.7999992370605px"><br></div><div style="font-size:13.1999998092651px;line-height:19.7999992370605px">That is my output...<br><br><div><div>SulSP_2015=# database_name=#\dx</div><div><br></div><div> Lista de extens§es instaladas</div><div> Nome | VersÒo | Esquema | DescriþÒo</div><div><br></div><div>------------------+--------+------------+---------------------------------------<span style="line-height:1.5;font-size:13.1999998092651px">------------------------------</span></div><div> hstore | 1.2 | public | data type for storing sets of (key, va<span style="line-height:1.5;font-size:13.1999998092651px">lue) pairs</span></div><div> pgrouting | 2.0.0 | public | pgRouting Extension</div><div> plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language</div><div> postgis | 2.1.2 | public | PostGIS geometry, geography, and raste<span style="line-height:1.5;font-size:13.1999998092651px">r spatial types and functions</span></div><div> postgis_topology | 2.1.2 | topology | PostGIS topology spatial types and fun<span style="line-height:1.5;font-size:13.1999998092651px">ctions</span></div><div>(5 registros)</div></div><div><br></div><div>Some another ideas?</div></div><br><div class="gmail_quote">Em Thu Feb 12 2015 at 18:02:07, <<a href="mailto:pgrouting-users-request@lists.osgeo.org">pgrouting-users-request@lists.osgeo.org</a>> escreveu:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Send Pgrouting-users mailing list submissions to<br>
<a href="mailto:pgrouting-users@lists.osgeo.org" target="_blank">pgrouting-users@lists.osgeo.<u></u>org</a><br>
<br>
To subscribe or unsubscribe via the World Wide Web, visit<br>
<a href="http://lists.osgeo.org/mailman/listinfo/pgrouting-users" target="_blank">http://lists.osgeo.org/<u></u>mailman/listinfo/pgrouting-<u></u>users</a><br>
or, via email, send a message with subject or body 'help' to<br>
<a href="mailto:pgrouting-users-request@lists.osgeo.org" target="_blank">pgrouting-users-request@lists.<u></u>osgeo.org</a><br>
<br>
You can reach the person managing the list at<br>
<a href="mailto:pgrouting-users-owner@lists.osgeo.org" target="_blank">pgrouting-users-owner@lists.<u></u>osgeo.org</a><br>
<br>
When replying, please edit your Subject line so it is more specific<br>
than "Re: Contents of Pgrouting-users digest..."<br>
<br>
<br>
Today's Topics:<br>
<br>
1. Re: Performance problems with pgr_dijkstra (Eric Scheibler)<br>
<br>
<br>
------------------------------<u></u>------------------------------<u></u>----------<br>
<br>
Message: 1<br>
Date: Wed, 11 Feb 2015 21:39:33 +0100<br>
From: Eric Scheibler <<a href="mailto:email@eric-scheibler.de" target="_blank">email@eric-scheibler.de</a>><br>
To: pgRouting users mailing list <<a href="mailto:pgrouting-users@lists.osgeo.org" target="_blank">pgrouting-users@lists.osgeo.<u></u>org</a>><br>
Subject: Re: [pgrouting-users] Performance problems with pgr_dijkstra<br>
Message-ID: <20150211203932.GC10577@<u></u>scimitar><br>
Content-Type: text/plain; charset="us-ascii"<br>
<br>
Eric Scheibler <<a href="mailto:email@eric-scheibler.de" target="_blank">email@eric-scheibler.de</a>> schrieb am 11.02.2015, 12:23 +0100:<br>
>Daniel Kastl <<a href="mailto:daniel@georepublic.de" target="_blank">daniel@georepublic.de</a>> schrieb am 11.02.2015, 10:54 +0900:<br>
>>You're right, that with pgRouting the amount of data selected from the<br>
>>network table matters. And the fastest way to select only a part of the<br>
>>network table is by selecting a bounding box. You should have an index on<br>
>>your geometry column as well. Then you don't need to create temporary<br>
>>tables.<br>
><br>
>Do you have an example for a bounding box? How to determine the box size? I know the distance<br>
>between the starting and destination point in meters. Could that be used?<br>
<br>
Found that, works.<br>
<br>
>>Back to your question: as far as I remember, the size of ID's can matter. I<br>
>>experienced this when I used data, that had already source and target ID's<br>
>>in place, which all had the same number of digits. Renumbering (starting<br>
>>from 1) helped to improve the speed. Though I can't tell this is the reason<br>
>>in your case.<br>
><br>
>Very interesting. You could be right. I created a temp routing table in the Saxony database, took<br>
>start and destination vertex from my program and verified the process time and the result (4 rows<br>
>and 60 ms for a very short way, approximately 100 meters). Then I dumped the created table with<br>
>pg_dump and restored it into the Europe database. Now the same routing query runs as fast as in the<br>
>small database. So maybe the higher source and target id's are responsible for that.<br>
<br>
Yes, that solved the problem. Now the routing query completes after 30-40 ms. So it's even a bit<br>
faster than at the small database. I've created a SQL function, which recreates the source and<br>
target id's of the temp routing table:<br>
<br>
CREATE OR REPLACE FUNCTION recreate_vertex_of_routing_<u></u>table(regclass)<br>
RETURNS void<br>
AS $$<br>
DECLARE<br>
row RECORD;<br>
vertex_storage hstore;<br>
new_vertex int;<br>
BEGIN<br>
vertex_storage := ''::hstore;<br>
new_vertex := 1;<br>
FOR row in EXECUTE FORMAT('SELECT id, source, target FROM %I', $1)<br>
LOOP<br>
IF NOT vertex_storage ? row.source::text THEN<br>
vertex_storage = vertex_storage || (row.source::text => new_vertex::text);<br>
new_vertex := new_vertex + 1;<br>
END IF;<br>
IF NOT vertex_storage ? row.target::text THEN<br>
vertex_storage = vertex_storage || (row.target::text => new_vertex::text);<br>
new_vertex := new_vertex + 1;<br>
END IF;<br>
END LOOP;<br>
FOR row IN SELECT key, value FROM EACH(vertex_storage)<br>
LOOP<br>
EXECUTE FORMAT('UPDATE %I SET source=$1 WHERE source = $2', $1) USING row.value::int, row.key::int;<br>
EXECUTE FORMAT('UPDATE %I SET target=$1 WHERE target = $2', $1) USING row.value::int, row.key::int;<br>
END LOOP;<br>
END;<br>
$$ LANGUAGE plpgsql;<br>
<br>
Best regards<br>
Eric<br>
-------------- next part --------------<br>
A non-text attachment was scrubbed...<br>
Name: signature.asc<br>
Type: application/pgp-signature<br>
Size: 473 bytes<br>
Desc: Digital signature<br>
URL: <<a href="http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20150211/d33fef4d/attachment-0001.pgp" target="_blank">http://lists.osgeo.org/<u></u>pipermail/pgrouting-users/<u></u>attachments/20150211/d33fef4d/<u></u>attachment-0001.pgp</a>><br>
<br>
------------------------------<br>
<br>
______________________________<u></u>_________________<br>
Pgrouting-users mailing list<br>
<a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.<u></u>org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/pgrouting-users" target="_blank">http://lists.osgeo.org/<u></u>mailman/listinfo/pgrouting-<u></u>users</a><br>
<br>
End of Pgrouting-users Digest, Vol 77, Issue 3<br>
******************************<u></u>****************<br>
</blockquote></div></div>