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