[pgrouting-users] Pgrouting-users Digest, Vol 94, Issue 3

Omar_Fernando_Pessoa · August 12, 2016, 7:33pm

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.

Thanks again Vicky.

···

2016-08-11 19:37 GMT-03:00 <pgrouting-users-request@lists.osgeo.org>:

Send Pgrouting-users mailing list submissions to
pgrouting-users@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@lists.osgeo.org

You can reach the person managing the list at
pgrouting-users-owner@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:

Re: PGR_DIJKSTRA execution time (Vicky Vergara)

Message: 1
Date: Thu, 11 Aug 2016 17:37:30 -0500
From: Vicky Vergara <vicky@georepublic.de>
To: pgRouting users mailing list <pgrouting-users@lists.osgeo.org>
Subject: Re: [pgrouting-users] PGR_DIJKSTRA execution time
Message-ID:
<CAK_FzuW75U35T2jckH=E3iuAiQGYKhi2Er+kfhiBGL+m++O_jA@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@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@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
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@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

End of Pgrouting-users Digest, Vol 94, Issue 3

…
Omar Fernando Pessôa
http://www.opessoa.com
Desenvolvedor de Sistemas / Systems Software Developer
C++ / PHP / C# / Javascript

cvvergara · August 13, 2016, 12:50am

hello again:

···

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…

Inventing a term, “middleend”. Based on a backend function results a middleend function will use the information and send the results that the frontend needs.

Based on that:

pgRouting is a backend,.

“middleend” functions we notice are very application specific.

frontend is in other languages that call the database (and is the application from the user’s point of view).

For example, in this wiki I made, where geometries are not aggregated (I need to make one with aggregating geometries):
http://talks.vicky.georepublic.info/howto/flipGeometries.html#/

you can see that, besides the geometry, additional information can be needed, and the query looks similar but not quite the same.

lots of howto are needed so people can copy/paste/adjust to what they need.

Also, like for example in your queries you were using Spanish (where are you from?, I am from México), or your data comes from other source, maybe your geometry column is not the_geom, maybe its geometria, or geom

pgRouting’s focus is on the algorithms for graphs, and the graph abstraction does not have a geometry involved.

Vicky.

–

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