<div dir="ltr">Hi Daniel. I already did it. Had already installed the extension.<br><br><div>text => text operator doesnt exist?</div></div><br><div class="gmail_quote">Em Fri Feb 13 2015 at 12:00:01, <<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: Pgrouting-users Digest, Vol 77, Issue 3 (Omar Fernando Pess?a)<br>
2. Re: Pgrouting-users Digest, Vol 77, Issue 3 (Daniel Kastl)<br>
<br>
<br>
------------------------------<u></u>------------------------------<u></u>----------<br>
<br>
Message: 1<br>
Date: Fri, 13 Feb 2015 12:29:18 +0000<br>
From: Omar Fernando Pess?a <<a href="mailto:omar.pessoa@gmail.com" target="_blank">omar.pessoa@gmail.com</a>><br>
To: <a href="mailto:pgrouting-users@lists.osgeo.org" target="_blank">pgrouting-users@lists.osgeo.<u></u>org</a><br>
Subject: Re: [pgrouting-users] Pgrouting-users Digest, Vol 77, Issue 3<br>
Message-ID:<br>
<<a href="mailto:CAOikBm5o36YG0rqNFOxzycFf3sAsLLGn5C%2BVuEtbk07D3SYJ7Q@mail.gmail.com" target="_blank">CAOikBm5o36YG0rqNFOxzycFf3sAs<u></u>LLGn5C+VuEtbk07D3SYJ7Q@mail.<u></u>gmail.com</a>><br>
Content-Type: text/plain; charset="utf-8"<br>
<br>
Hi Erick I have same error, but creating your function I get this error:<br>
<br>
<br>
ERROR: operator does not exists: text => text<br>
LINE 1: SELECT vertex_storage || (row.source::text => new_vertex::te...<br>
<br>
Thanks.<br>
<br>
Em Thu Feb 12 2015 at 18:02:07, <<a href="mailto:pgrouting-users-request@lists.osgeo.org" target="_blank">pgrouting-users-request@<u></u>lists.osgeo.org</a>><br>
escreveu:<br>
<br>
> 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<br>
> +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?<br>
> 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<br>
> matter. I<br>
> >>experienced this when I used data, that had already source and target<br>
> 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<br>
> reason<br>
> >>in your case.<br>
> ><br>
> >Very interesting. You could be right. I created a temp routing table in<br>
> the Saxony database, took<br>
> >start and destination vertex from my program and verified the process<br>
> time and the result (4 rows<br>
> >and 60 ms for a very short way, approximately 100 meters). Then I dumped<br>
> the created table with<br>
> >pg_dump and restored it into the Europe database. Now the same routing<br>
> query runs as fast as in the<br>
> >small database. So maybe the higher source and target id's are<br>
> responsible for that.<br>
><br>
> Yes, that solved the problem. Now the routing query completes after 30-40<br>
> ms. So it's even a bit<br>
> faster than at the small database. I've created a SQL function, which<br>
> 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 =><br>
> 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 =><br>
> 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)<br>
> USING row.value::int, row.key::int;<br>
> EXECUTE FORMAT('UPDATE %I SET target=$1 WHERE target = $2', $1)<br>
> 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/" target="_blank">http://lists.osgeo.org/<u></u>pipermail/pgrouting-users/</a><br>
> attachments/20150211/d33fef4d/<u></u>attachment-0001.pgp><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>
><br>
-------------- next part --------------<br>
An HTML attachment was scrubbed...<br>
URL: <<a href="http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20150213/fb804a74/attachment-0001.html" target="_blank">http://lists.osgeo.org/<u></u>pipermail/pgrouting-users/<u></u>attachments/20150213/fb804a74/<u></u>attachment-0001.html</a>><br>
<br>
------------------------------<br>
<br>
Message: 2<br>
Date: Fri, 13 Feb 2015 22:59:34 +0900<br>
From: Daniel Kastl <<a href="mailto:daniel@georepublic.de" target="_blank">daniel@georepublic.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] Pgrouting-users Digest, Vol 77, Issue 3<br>
Message-ID:<br>
<<a href="mailto:CABXBSH9DL8JisK5zG-cQ8WgdEze9cA9rp3iG51JcEfL71N1JCA@mail.gmail.com" target="_blank">CABXBSH9DL8JisK5zG-<u></u>cQ8WgdEze9cA9rp3iG51JcEfL71N1J<u></u>CA@mail.gmail.com</a>><br>
Content-Type: text/plain; charset="utf-8"<br>
<br>
Hi Omar,<br>
<br>
I think Erik used "hstore" in his function, which you first have to add to<br>
your database with<br>
<br>
CREATE EXTENSION hstore;<br>
<br>
Hope this helps,<br>
Daniel<br>
<br>
<br>
On Fri, Feb 13, 2015 at 9:29 PM, Omar Fernando Pess?a <<a href="mailto:omar.pessoa@gmail.com" target="_blank">omar.pessoa@gmail.com</a><br>
> wrote:<br>
<br>
> Hi Erick I have same error, but creating your function I get this error:<br>
><br>
><br>
> ERROR: operator does not exists: text => text<br>
> LINE 1: SELECT vertex_storage || (row.source::text => new_vertex::te...<br>
><br>
> Thanks.<br>
><br>
> Em Thu Feb 12 2015 at 18:02:07, <<a href="mailto:pgrouting-users-request@lists.osgeo.org" target="_blank">pgrouting-users-request@<u></u>lists.osgeo.org</a>><br>
> escreveu:<br>
><br>
>> 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<br>
>> +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<br>
>> 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<br>
>> 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<br>
>> matter. I<br>
>> >>experienced this when I used data, that had already source and target<br>
>> 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<br>
>> reason<br>
>> >>in your case.<br>
>> ><br>
>> >Very interesting. You could be right. I created a temp routing table in<br>
>> the Saxony database, took<br>
>> >start and destination vertex from my program and verified the process<br>
>> time and the result (4 rows<br>
>> >and 60 ms for a very short way, approximately 100 meters). Then I dumped<br>
>> the created table with<br>
>> >pg_dump and restored it into the Europe database. Now the same routing<br>
>> query runs as fast as in the<br>
>> >small database. So maybe the higher source and target id's are<br>
>> responsible for that.<br>
>><br>
>> Yes, that solved the problem. Now the routing query completes after 30-40<br>
>> ms. So it's even a bit<br>
>> faster than at the small database. I've created a SQL function, which<br>
>> 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 =><br>
>> 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 =><br>
>> 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)<br>
>> USING row.value::int, row.key::int;<br>
>> EXECUTE FORMAT('UPDATE %I SET target=$1 WHERE target = $2', $1)<br>
>> 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/" target="_blank">http://lists.osgeo.org/<u></u>pipermail/pgrouting-users/</a><br>
>> attachments/20150211/d33fef4d/<u></u>attachment-0001.pgp><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>
>><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>
<br>
<br>
<br>
--<br>
Georepublic UG & Georepublic Japan<br>
eMail: <a href="mailto:daniel.kastl@georepublic.de" target="_blank">daniel.kastl@georepublic.de</a><br>
Web: <a href="http://georepublic.info" target="_blank">http://georepublic.info</a><br>
-------------- next part --------------<br>
An HTML attachment was scrubbed...<br>
URL: <<a href="http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20150213/5bd90a12/attachment.html" target="_blank">http://lists.osgeo.org/<u></u>pipermail/pgrouting-users/<u></u>attachments/20150213/5bd90a12/<u></u>attachment.html</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 4<br>
******************************<u></u>****************<br>
</blockquote></div>