[pgrouting-users] Pgrouting-users Digest, Vol 94, Issue 3
Omar Fernando Pessôa
omar.pessoa at gmail.com
Fri Aug 12 12:33:04 PDT 2016
Thanks Vicky.
So, my time problem is to get geometry from "roads" that make the vertex
connections.
Just for fun:
Executing this 10 times alternated:
select * INTO dijkstra_results FROM pgr_dijkstra(
Query returned successfully: 1528890 rows affected, 6 to 7 seconds
execution time.
Create temporary table dijkstra_results as select * from pgr_dijkstra(
Query returned successfully: 1528890 rows affected, 2 to 3 seconds
execution time.
To do idea:
Some pgr function thats return and use geometry like reverse_cost, maybe
has_geometry than pass to the 'sql', and results like cost
vertex_start/vertex_end linestring, and agg_cost making final linemerge
linestring...
Executing my query separatelly get same time result. :P
Thanks again Vicky.
..
*Omar Fernando Pessôa*
http://www.opessoa.com
Desenvolvedor de Sistemas / Systems Software Developer
C++ / PHP / C# / Javascript
2016-08-11 19:37 GMT-03:00 <pgrouting-users-request at lists.osgeo.org>:
> 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: PGR_DIJKSTRA execution time (Vicky Vergara)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Thu, 11 Aug 2016 17:37:30 -0500
> From: Vicky Vergara <vicky at georepublic.de>
> To: pgRouting users mailing list <pgrouting-users at lists.osgeo.org>
> Subject: Re: [pgrouting-users] PGR_DIJKSTRA execution time
> Message-ID:
> <CAK_FzuW75U35T2jckH=E3iuAiQGYKhi2Er+kfhiBGL+m++O_jA at mail.
> gmail.com>
> Content-Type: text/plain; charset="utf-8"
>
> Hello Omar,
>
> I don't have your data (not intending to have it) so, lets go by steps:
> First of all the code looks quite "crowded", so I took the liberty of
> formatting it a little, see at the bottom.
>
> If its a matter of analysis, lets start with using
> \timing
> so after each command you get the time it took to do things
>
> The insert clause is doing many things:
>
> - has a JOIN that takes time,
> - has this ST_LineMerge(st_collect(the_geom) that also consume time
> - has this array_to_string(array_agg(edge),',') that also consume time
> - has the call to pgr_dijkstra that also consumes time, etc.
>
> if you want to time how long it takes to make the pgr_dijkstra just do:
>
> SELECT * FROM pgr_dijkstra('SELECT id, source, target, cost, reverse_cost
> FROM view_vertex',
>
> array[3922267,3922268,3922269,3922270,3922271,3922272,
> 3922273,3922274,3922275,3922276,3922277,3922278,
>
> 3922279,2667391,3922280,3922281,3922282,3922283,3922284,3922285,3922286,
> 3922287,3922288,3922289,
>
> 3922290,3922291,3922292,3922293,3922294,3922295,3922296,3922297,3922298,
> 3922299,3922300,3922301,
>
> 3922302,3922303,3922304,3922305,3922306,3922307,3922308,3922309,3922310,
> 3922311,3922312,3922313,
>
> 3922314,3922315,3922316,3922317,3922318,3922319,3922320,3922321,3922322,
> 3922323,3922324,3922325,
>
> 3922326,3922327,3922328,3922329,3922330,3922331,3922332,3922333,3922334,
> 3922335,3922336,3922337,
>
> 3922338,3922339,3922340,3922341,3922342,3922343,3922344,3922345,3922346,
> 3922347,3922348,3922349,
>
> 3922350,3922351,3922352,3922353,3922354,3922355,3922356,3922357,3922358,
> 3922359,3922360,3922361,
>
> 3922362,3922363,3922364,3922365,3922366,3922367,3922368,3922369,3922370],
>
> array[3922267,3922268,3922269,3922270,3922271,3922272,
> 3922273,3922274,3922275,3922276,3922277,3922278,
>
> 3922279,2667391,3922280,3922281,3922282,3922283,3922284,3922285,3922286,
> 3922287,3922288,3922289,
>
> 3922290,3922291,3922292,3922293,3922294,3922295,3922296,3922297,3922298,
> 3922299,3922300,3922301,
>
> 3922302,3922303,3922304,3922305,3922306,3922307,3922308,3922309,3922310,
> 3922311,3922312,3922313,
>
> 3922314,3922315,3922316,3922317,3922318,3922319,3922320,3922321,3922322,
> 3922323,3922324,3922325,
>
> 3922326,3922327,3922328,3922329,3922330,3922331,3922332,3922333,3922334,
> 3922335,3922336,3922337,
>
> 3922338,3922339,3922340,3922341,3922342,3922343,3922344,3922345,3922346,
> 3922347,3922348,3922349,
>
> 3922350,3922351,3922352,3922353,3922354,3922355,3922356,3922357,3922358,
> 3922359,3922360,3922361,
>
> 3922362,3922363,3922364,3922365,3922366,3922367,3922368,3922369,3922370]
> , true);
>
> you can save the results in a temporary table:
>
> select * INTO dijkstra_results FROM pgr_dijkstra(......);
> -- do the insert clause using dijkstra_results
> drop table dijkstra_results
>
> Hope I was of help.
> Vicky
>
> /* a little formatting */
>
> DROP materialized view if exists view_vertex;
>
> CREATE materialized view view_vertex as (
> (select id, the_geom, vel_med,source, target, cost, reverse_cost
> from vertex_ruas
> where the_geom && 'POLYGON((-49.3954855 -27.0998678,-49.3954855
> -26.6055895,-48.7818607 -26.6055895,-48.7818607 -27.0998678,-49.3954855
> -27.0998678))'::geometry and status <> 1)
> UNION ALL
> (select id, the_geom, vel_med,source, target, cost, reverse_cost
> from vertex_ruas_aux
> where the_geom && 'POLYGON((-49.3954855 -27.0998678,-49.3954855
> -26.6055895,-48.7818607 -26.6055895,-48.7818607 -27.0998678,-49.3954855
> -27.0998678))'::geometry and status <> 1));
>
> CREATE UNIQUE INDEX idx_id_view_vertex ON view_vertex(id);
> CREATE INDEX idx_source_view_vertex ON view_vertex(source);
> CREATE INDEX idx_target_view_vertex ON view_vertex(target);
>
> insert into trajetos
> select id, ids, idt, st_length(the_geom::geography) as distancia ,
> sequencia, sequencia_vertex, the_geom, vel_med
> from (
> SELECT start_vid || '-' || end_vid as id,start_vid as ids, end_vid as
> idt, array_to_string(array_agg(edge),',') as sequencia,
> array_to_string(array_agg(node),',') as
> sequencia_vertex,ST_LineMerge(st_collect(the_geom)) as the_geom,
> avg(vel_med) as vel_med
>
> FROM pgr_dijkstra('SELECT id, source, target, cost, reverse_cost FROM
> view_vertex',
>
> array[3922267,3922268,3922269,3922270,3922271,3922272,
> 3922273,3922274,3922275,3922276,3922277,3922278,
>
> 3922279,2667391,3922280,3922281,3922282,3922283,3922284,3922285,3922286,
> 3922287,3922288,3922289,
>
> 3922290,3922291,3922292,3922293,3922294,3922295,3922296,3922297,3922298,
> 3922299,3922300,3922301,
>
> 3922302,3922303,3922304,3922305,3922306,3922307,3922308,3922309,3922310,
> 3922311,3922312,3922313,
>
> 3922314,3922315,3922316,3922317,3922318,3922319,3922320,3922321,3922322,
> 3922323,3922324,3922325,
>
> 3922326,3922327,3922328,3922329,3922330,3922331,3922332,3922333,3922334,
> 3922335,3922336,3922337,
>
> 3922338,3922339,3922340,3922341,3922342,3922343,3922344,3922345,3922346,
> 3922347,3922348,3922349,
>
> 3922350,3922351,3922352,3922353,3922354,3922355,3922356,3922357,3922358,
> 3922359,3922360,3922361,
>
> 3922362,3922363,3922364,3922365,3922366,3922367,3922368,3922369,3922370],
>
> array[3922267,3922268,3922269,3922270,3922271,3922272,
> 3922273,3922274,3922275,3922276,3922277,3922278,
>
> 3922279,2667391,3922280,3922281,3922282,3922283,3922284,3922285,3922286,
> 3922287,3922288,3922289,
>
> 3922290,3922291,3922292,3922293,3922294,3922295,3922296,3922297,3922298,
> 3922299,3922300,3922301,
>
> 3922302,3922303,3922304,3922305,3922306,3922307,3922308,3922309,3922310,
> 3922311,3922312,3922313,
>
> 3922314,3922315,3922316,3922317,3922318,3922319,3922320,3922321,3922322,
> 3922323,3922324,3922325,
>
> 3922326,3922327,3922328,3922329,3922330,3922331,3922332,3922333,3922334,
> 3922335,3922336,3922337,
>
> 3922338,3922339,3922340,3922341,3922342,3922343,3922344,3922345,3922346,
> 3922347,3922348,3922349,
>
> 3922350,3922351,3922352,3922353,3922354,3922355,3922356,3922357,3922358,
> 3922359,3922360,3922361,
>
> 3922362,3922363,3922364,3922365,3922366,3922367,3922368,3922369,3922370]
> , true)
>
> join view_vertex t on t.id = edge group by start_vid, end_vid order by
> start_vid, end_vid) a;
>
>
>
>
>
>
> On Thu, Aug 11, 2016 at 1:42 PM, Omar Fernando Pessôa <
> omar.pessoa at gmail.com
> > wrote:
>
> > Hi guys.
> >
> > I am using OSM to generate my road postgis database (south of Brazil).
> >
> > My vertex table has 1282959 rows.
> >
> > To decrease time to run PGR_DIJKSTRA I have make a box from my
> > "destinations" position. Decreasing it to 18264 rows in a materialized
> view
> > with indexes.
> >
> > Then running PGR_DIJKSTRA, with k x k (k = 100+- vertex). The running
> > time of PGR_DIJKSTRA is something about 30s.
> >
> > This is a good "time"? Can I do it better?
> >
> > '''
> >
> > *drop materialized view if exists view_vertex;*
> > *create materialized view view_vertex as((select id, the_geom,
> > vel_med,source, target, cost, reverse_cost from vertex_ruas where
> the_geom
> > && 'POLYGON((-49.3954855 -27.0998678,-49.3954855 -26.6055895,-48.7818607
> > -26.6055895,-48.7818607 -27.0998678,-49.3954855 -27.0998678))'::geometry
> > and status <> 1) UNION ALL(select id, the_geom, vel_med,source, target,
> > cost, reverse_cost from vertex_ruas_aux where the_geom &&
> > 'POLYGON((-49.3954855 -27.0998678,-49.3954855 -26.6055895,-48.7818607
> > -26.6055895,-48.7818607 -27.0998678,-49.3954855 -27.0998678))'::geometry
> > and status <> 1)); *
> > *CREATE UNIQUE INDEX idx_id_view_vertex ON view_vertex(id);*
> > *CREATE INDEX idx_source_view_vertex ON view_vertex(source);*
> > *CREATE INDEX idx_target_view_vertex ON view_vertex(target);*
> > *insert into trajetos select id, ids, idt, st_length(the_geom::geography)
> > as distancia , sequencia, sequencia_vertex, the_geom, vel_med from(SELECT
> > start_vid || '-' || end_vid as id,start_vid as ids, end_vid as idt,
> > array_to_string(array_agg(edge),',') as sequencia,
> > array_to_string(array_agg(node),',') as
> > sequencia_vertex,ST_LineMerge(st_collect(the_geom)) as the_geom,
> > avg(vel_med) as vel_med FROM pgr_dijkstra( 'SELECT id, source, target,
> > cost, reverse_cost FROM view_vertex' ,
> > array[3922267,3922268,3922269,3922270,3922271,3922272,
> 3922273,3922274,3922275,3922276,3922277,3922278,3922279,2667391,3922280,
> 3922281,3922282,3922283,3922284,3922285,3922286,3922287,3922288,3922289,
> 3922290,3922291,3922292,3922293,3922294,3922295,3922296,3922297,3922298,
> 3922299,3922300,3922301,3922302,3922303,3922304,3922305,3922306,3922307,
> 3922308,3922309,3922310,3922311,3922312,3922313,3922314,3922315,3922316,
> 3922317,3922318,3922319,3922320,3922321,3922322,3922323,3922324,3922325,
> 3922326,3922327,3922328,3922329,3922330,3922331,3922332,3922333,3922334,
> 3922335,3922336,3922337,3922338,3922339,3922340,3922341,3922342,3922343,
> 3922344,3922345,3922346,3922347,3922348,3922349,3922350,3922351,3922352,
> 3922353,3922354,3922355,3922356,3922357,3922358,3922359,3922360,3922361,
> 3922362,3922363,3922364,3922365,3922366,3922367,3922368,3922369,3922370],
> > array[3922267,3922268,3922269,3922270,3922271,3922272,
> 3922273,3922274,3922275,3922276,3922277,3922278,3922279,2667391,3922280,
> 3922281,3922282,3922283,3922284,3922285,3922286,3922287,3922288,3922289,
> 3922290,3922291,3922292,3922293,3922294,3922295,3922296,3922297,3922298,
> 3922299,3922300,3922301,3922302,3922303,3922304,3922305,3922306,3922307,
> 3922308,3922309,3922310,3922311,3922312,3922313,3922314,3922315,3922316,
> 3922317,3922318,3922319,3922320,3922321,3922322,3922323,3922324,3922325,
> 3922326,3922327,3922328,3922329,3922330,3922331,3922332,3922333,3922334,
> 3922335,3922336,3922337,3922338,3922339,3922340,3922341,3922342,3922343,
> 3922344,3922345,3922346,3922347,3922348,3922349,3922350,3922351,3922352,
> 3922353,3922354,3922355,3922356,3922357,3922358,3922359,3922360,3922361,
> 3922362,3922363,3922364,3922365,3922366,3922367,3922368,3922369,3922370],
> > true)join view_vertex t on t.id <http://t.id> = edge group by start_vid,
> > end_vid order by start_vid, end_vid)a;*
> >
> > '''
> >
> > This is my way to do this. (example).
> >
> > Thanks guys! Waiting for withpoints to make me happy. This
> *vertex_ruas_aux
> > *table is a helper table to create new vertex using *ST_LineLocatePoint
> *from
> > *vertex_ruas, *status is set to 1 to make this invalid, generating 2 new
> > linestrings *vertex_ruas_aux*.
> > ..
> > *Omar Fernando Pessôa*
> > http://www.opessoa.com
> > Desenvolvedor de Sistemas / Systems Software Developer
> > C++ / PHP / C# / Javascript
> >
> > _______________________________________________
> > Pgrouting-users mailing list
> > Pgrouting-users at lists.osgeo.org
> > http://lists.osgeo.org/mailman/listinfo/pgrouting-users
> >
>
>
>
> --
>
> Georepublic UG (haftungsbeschränkt)
> Salzmannstraße 44,
> 81739 München, Germany
>
> Vicky Vergara
> Operations Research
>
> eMail: vicky at georepublic.de
> Web: https://georepublic.info
>
> Tel: +49 (089) 4161 7698-1
> Fax: +49 (089) 4161 7698-9
>
> Commercial register: Amtsgericht München, HRB 181428
> CEO: Daniel Kastl
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: <http://lists.osgeo.org/pipermail/pgrouting-users/
> attachments/20160811/d4da0913/attachment.html>
>
> ------------------------------
>
> Subject: Digest Footer
>
> _______________________________________________
> Pgrouting-users mailing list
> Pgrouting-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>
> ------------------------------
>
> End of Pgrouting-users Digest, Vol 94, Issue 3
> **********************************************
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20160812/38afffc0/attachment-0001.html>
More information about the Pgrouting-users
mailing list