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

[Omar Fernando Pessôa]

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
> **********************************************
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20150213/fb804a74/attachment.html>