<div dir="ltr"><div class="gmail_default" style="font-family:comic sans ms,sans-serif">Hello Omar,<br><br></div><div class="gmail_default" style="font-family:comic sans ms,sans-serif">I don't have your data (not intending to have it) so, lets go by steps:<br></div><div class="gmail_default" style="font-family:comic sans ms,sans-serif">First of all the code looks quite "crowded", so I took the liberty of formatting it a little, see at the bottom.<br></div><div class="gmail_default" style="font-family:comic sans ms,sans-serif"><br></div><div class="gmail_default" style="font-family:comic sans ms,sans-serif">If its a matter of analysis, lets start with using <br></div><div class="gmail_default" style="font-family:comic sans ms,sans-serif"><span style="font-family:monospace,monospace">\timing</span><br></div><div class="gmail_default" style="font-family:comic sans ms,sans-serif">so after each command you get the time it took to do things<br><br></div><div class="gmail_default" style="font-family:comic sans ms,sans-serif">The <span style="font-family:monospace,monospace">insert</span> clause is doing many things:<br><ul><li> has a JOIN that takes time,</li><li> has this <span style="font-family:monospace,monospace">ST_LineMerge(st_collect(the_geom)</span> that also consume time</li><li>has this <span style="font-family:monospace,monospace">array_to_string(array_agg(edge),',')</span> that also consume time</li><li>has the call to pgr_dijkstra that also consumes time, etc.</li></ul></div><div class="gmail_default" style="font-family:comic sans ms,sans-serif">if you want to time how long it takes to make the pgr_dijkstra just do:<br><br><span style="font-family:monospace,monospace">SELECT * FROM pgr_dijkstra('SELECT id, source, target, cost, reverse_cost FROM view_vertex', <br> array[3922267,3922268,3922269,3922270,3922271,3922272,3922273,3922274,3922275,3922276,3922277,3922278,<br> 3922279,2667391,3922280,3922281,3922282,3922283,3922284,3922285,3922286,3922287,3922288,3922289,<br> 3922290,3922291,3922292,3922293,3922294,3922295,3922296,3922297,3922298,3922299,3922300,3922301,<br> 3922302,3922303,3922304,3922305,3922306,3922307,3922308,3922309,3922310,3922311,3922312,3922313,<br> 3922314,3922315,3922316,3922317,3922318,3922319,3922320,3922321,3922322,3922323,3922324,3922325,<br> 3922326,3922327,3922328,3922329,3922330,3922331,3922332,3922333,3922334,3922335,3922336,3922337,<br> 3922338,3922339,3922340,3922341,3922342,3922343,3922344,3922345,3922346,3922347,3922348,3922349,<br> 3922350,3922351,3922352,3922353,3922354,3922355,3922356,3922357,3922358,3922359,3922360,3922361,<br> 3922362,3922363,3922364,3922365,3922366,3922367,3922368,3922369,3922370],<br> array[3922267,3922268,3922269,3922270,3922271,3922272,3922273,3922274,3922275,3922276,3922277,3922278,<br> 3922279,2667391,3922280,3922281,3922282,3922283,3922284,3922285,3922286,3922287,3922288,3922289,<br> 3922290,3922291,3922292,3922293,3922294,3922295,3922296,3922297,3922298,3922299,3922300,3922301,<br> 3922302,3922303,3922304,3922305,3922306,3922307,3922308,3922309,3922310,3922311,3922312,3922313,<br> 3922314,3922315,3922316,3922317,3922318,3922319,3922320,3922321,3922322,3922323,3922324,3922325,<br> 3922326,3922327,3922328,3922329,3922330,3922331,3922332,3922333,3922334,3922335,3922336,3922337,<br> 3922338,3922339,3922340,3922341,3922342,3922343,3922344,3922345,3922346,3922347,3922348,3922349,<br> 3922350,3922351,3922352,3922353,3922354,3922355,3922356,3922357,3922358,3922359,3922360,3922361,<br> 3922362,3922363,3922364,3922365,3922366,3922367,3922368,3922369,3922370]<br> , true);<br><br></span></div><div class="gmail_default" style="font-family:comic sans ms,sans-serif">you can save the results in a temporary table:<br><br></div><div class="gmail_default" style="font-family:comic sans ms,sans-serif"><span style="font-family:monospace,monospace">select * INTO dijkstra_results FROM pgr_dijkstra(......);<br></span></div><div class="gmail_default" style="font-family:comic sans ms,sans-serif"><span style="font-family:monospace,monospace">-- do the insert clause using dijkstra_results<br></span></div><div class="gmail_default" style="font-family:comic sans ms,sans-serif"><span style="font-family:monospace,monospace">drop table </span><span style="font-family:monospace,monospace">dijkstra_results</span></div><div class="gmail_default" style="font-family:comic sans ms,sans-serif"><br></div><div class="gmail_default" style="font-family:comic sans ms,sans-serif">Hope I was of help.<br></div><div class="gmail_default" style="font-family:comic sans ms,sans-serif">Vicky<br></div><div class="gmail_default" style="font-family:comic sans ms,sans-serif"><br></div><div class="gmail_default" style="font-family:comic sans ms,sans-serif">/* a little formatting */<br></div><div class="gmail_default" style="font-family:comic sans ms,sans-serif"><span style="font-family:monospace,monospace"><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 -26.6055895,-48.7818607 -26.6055895,-48.7818607 -27.0998678,-49.3954855 -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 -26.6055895,-48.7818607 -26.6055895,-48.7818607 -27.0998678,-49.3954855 -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 , sequencia, sequencia_vertex, the_geom, vel_med <br> from (<br> SELECT start_vid || '-' || end_vid as id,start_vid as ids, end_vid as idt, array_to_string(array_agg(edge),',') as sequencia,<br> array_to_string(array_agg(node),',') as sequencia_vertex,ST_LineMerge(st_collect(the_geom)) as the_geom, avg(vel_med) as vel_med<br><br> FROM pgr_dijkstra('SELECT id, source, target, cost, reverse_cost FROM view_vertex', <br> array[3922267,3922268,3922269,3922270,3922271,3922272,3922273,3922274,3922275,3922276,3922277,3922278,<br> 3922279,2667391,3922280,3922281,3922282,3922283,3922284,3922285,3922286,3922287,3922288,3922289,<br> 3922290,3922291,3922292,3922293,3922294,3922295,3922296,3922297,3922298,3922299,3922300,3922301,<br> 3922302,3922303,3922304,3922305,3922306,3922307,3922308,3922309,3922310,3922311,3922312,3922313,<br> 3922314,3922315,3922316,3922317,3922318,3922319,3922320,3922321,3922322,3922323,3922324,3922325,<br> 3922326,3922327,3922328,3922329,3922330,3922331,3922332,3922333,3922334,3922335,3922336,3922337,<br> 3922338,3922339,3922340,3922341,3922342,3922343,3922344,3922345,3922346,3922347,3922348,3922349,<br> 3922350,3922351,3922352,3922353,3922354,3922355,3922356,3922357,3922358,3922359,3922360,3922361,<br> 3922362,3922363,3922364,3922365,3922366,3922367,3922368,3922369,3922370],<br> array[3922267,3922268,3922269,3922270,3922271,3922272,3922273,3922274,3922275,3922276,3922277,3922278,<br> 3922279,2667391,3922280,3922281,3922282,3922283,3922284,3922285,3922286,3922287,3922288,3922289,<br> 3922290,3922291,3922292,3922293,3922294,3922295,3922296,3922297,3922298,3922299,3922300,3922301,<br> 3922302,3922303,3922304,3922305,3922306,3922307,3922308,3922309,3922310,3922311,3922312,3922313,<br> 3922314,3922315,3922316,3922317,3922318,3922319,3922320,3922321,3922322,3922323,3922324,3922325,<br> 3922326,3922327,3922328,3922329,3922330,3922331,3922332,3922333,3922334,3922335,3922336,3922337,<br> 3922338,3922339,3922340,3922341,3922342,3922343,3922344,3922345,3922346,3922347,3922348,3922349,<br> 3922350,3922351,3922352,3922353,3922354,3922355,3922356,3922357,3922358,3922359,3922360,3922361,<br> 3922362,3922363,3922364,3922365,3922366,3922367,3922368,3922369,3922370]<br> , true)<br><br> join view_vertex t on <a href="http://t.id">t.id</a> = edge group by start_vid, end_vid order by start_vid, end_vid) a;<br></span><br></div><div class="gmail_default" style="font-family:comic sans ms,sans-serif"><br></div><div class="gmail_default" style="font-family:comic sans ms,sans-serif"><br><br><br></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Thu, Aug 11, 2016 at 1:42 PM, Omar Fernando Pessôa <span dir="ltr"><<a href="mailto:omar.pessoa@gmail.com" target="_blank">omar.pessoa@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div>Hi guys.</div><div><br></div><div>I am using OSM to generate my road postgis database (south of Brazil).</div><div><br></div><div>My vertex table has 1282959 rows.</div><div><br></div><div>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.</div><div><br></div><div>Then running PGR_DIJKSTRA, with k x k (k = 100+- vertex). The running time of PGR_DIJKSTRA is something about 30s.</div><div><br></div><div>This is a good "time"? Can I do it better?</div><div><br></div><div>'''</div><div><blockquote style="margin:0px 0px 0px 40px;border:none;padding:0px"><div><div><b>drop materialized view if exists view_vertex;</b></div><div><b>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));<span style="white-space:pre-wrap"> </span></b></div><div><b>CREATE UNIQUE INDEX idx_id_view_vertex ON view_vertex(id);</b></div><div><b>CREATE INDEX idx_source_view_vertex ON view_vertex(source);</b></div><div><b>CREATE INDEX idx_target_view_vertex ON view_vertex(target);</b></div><div><b>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(<wbr>edge),',') as sequencia, array_to_string(array_agg(<wbr>node),',') as sequencia_vertex,ST_LineMerge(<wbr>st_collect(the_geom)) as the_geom, avg(vel_med) as vel_med FROM pgr_dijkstra(<span style="white-space:pre-wrap"> </span>'SELECT id, source, target, cost, reverse_cost FROM view_vertex'<span style="white-space:pre-wrap"> </span>, 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], 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], true)join view_vertex t on <a href="http://t.id" target="_blank">t.id</a> = edge group by start_vid, end_vid order by start_vid, end_vid)a;</b></div></div></blockquote></div><div>'''</div><div><br></div><div>This is my way to do this. (example).</div><div><br></div><div>Thanks guys! Waiting for withpoints to make me happy. This <b>vertex_ruas_aux </b>table is a helper table to create new vertex using <b>ST_LineLocatePoint </b>from <b>vertex_ruas, </b>status is set to 1 to make this invalid, generating 2 new linestrings <b>vertex_ruas_aux</b>. </div><div><div><div data-smartmail="gmail_signature"><div dir="ltr"><div><div dir="ltr"><div>..<span class="HOEnZb"><font color="#888888"><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</font></span></div></div></div></div></div></div>
</div></div>
<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></blockquote></div><br><br clear="all"><br>-- <br><div class="gmail_signature" data-smartmail="gmail_signature"><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><pre>Georepublic UG (haftungsbeschränkt)
Salzmannstraße 44,
81739 München, Germany
Vicky Vergara
Operations Research
eMail: vicky@<a href="http://georepublic.de" target="_blank">georepublic.de</a>
Web: <a href="https://georepublic.info" target="_blank">https://georepublic.info</a>
Tel: +49 (089) 4161 7698-1
Fax: +49 (089) 4161 7698-9
Commercial register: Amtsgericht München, HRB 181428
CEO: Daniel Kastl
<span></span></pre></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div>
</div>