[pgrouting-users] Pgrouting-users Digest, Vol 77, Issue 4
Omar Fernando Pessôa
omar.pessoa at gmail.com
Fri Feb 13 07:13:39 PST 2015
Hi Daniel. I already did it. Had already installed the extension.
text => text operator doesnt exist?
Em Fri Feb 13 2015 at 12:00:01, <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: Pgrouting-users Digest, Vol 77, Issue 3 (Omar Fernando Pess?a)
> 2. Re: Pgrouting-users Digest, Vol 77, Issue 3 (Daniel Kastl)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Fri, 13 Feb 2015 12:29:18 +0000
> From: Omar Fernando Pess?a <omar.pessoa at gmail.com>
> To: pgrouting-users at lists.osgeo.org
> Subject: Re: [pgrouting-users] Pgrouting-users Digest, Vol 77, Issue 3
> Message-ID:
> <CAOikBm5o36YG0rqNFOxzycFf3sAsLLGn5C+VuEtbk07D3SYJ7Q at mail.
> gmail.com>
> Content-Type: text/plain; charset="utf-8"
>
> 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-0001.html>
>
> ------------------------------
>
> Message: 2
> Date: Fri, 13 Feb 2015 22:59:34 +0900
> From: Daniel Kastl <daniel at georepublic.de>
> To: pgRouting users mailing list <pgrouting-users at lists.osgeo.org>
> Subject: Re: [pgrouting-users] Pgrouting-users Digest, Vol 77, Issue 3
> Message-ID:
> <CABXBSH9DL8JisK5zG-cQ8WgdEze9cA9rp3iG51JcEfL71N1J
> CA at mail.gmail.com>
> Content-Type: text/plain; charset="utf-8"
>
> 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.html>
>
> ------------------------------
>
> _______________________________________________
> 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 4
> **********************************************
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20150213/fecb1631/attachment-0001.html>
More information about the Pgrouting-users
mailing list