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@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 = 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@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@[georepublic.de](http://georepublic.de)
Web: [https://georepublic.info](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