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

[Daniel Kastl]

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 at 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 at lists.osgeo.org>
> escreveu:
>
>> Send Pgrouting-users mailing list submissions to
>>         pgrouting-users at 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 at lists.osgeo.org
>>
>> You can reach the person managing the list at
>>         pgrouting-users-owner at 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 at eric-scheibler.de>
>> To: pgRouting users mailing list <pgrouting-users at lists.osgeo.org>
>> Subject: Re: [pgrouting-users] Performance problems with pgr_dijkstra
>> Message-ID: <20150211203932.GC10577 at scimitar>
>> Content-Type: text/plain; charset="us-ascii"
>>
>> Eric Scheibler <email at eric-scheibler.de> schrieb am 11.02.2015, 12:23
>> +0100:
>> >Daniel Kastl <daniel at 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 at 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 at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>



-- 
Georepublic UG & Georepublic Japan
eMail: daniel.kastl at 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-0001.html>