Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Repo info
Activity
  • Aug 20 20:26
    vicennial edited #1240
  • Aug 18 21:47
    dkastl commented #1239
  • Aug 16 22:39

    cvvergara on gh-pages

    adding documentation of new fu… (compare)

  • Aug 16 15:46
    cvvergara closed #1240
  • Aug 15 19:56
    cvvergara review_requested #1240
  • Aug 15 19:55
    vicennial synchronize #1240
  • Aug 15 19:23
    vicennial synchronize #1240
  • Aug 15 19:22
    vicennial synchronize #1240
  • Aug 15 19:10
    cvvergara review_requested #1240
  • Aug 15 19:10
    cvvergara review_requested #1240
  • Aug 15 19:10
    cvvergara review_requested #1240
  • Aug 15 19:09
    cvvergara labeled #1240
  • Aug 15 19:09
    cvvergara labeled #1240
  • Aug 15 19:09
    cvvergara milestoned #1240
  • Aug 15 19:08
    vicennial opened #1240
  • Aug 15 17:39
    benglass opened #1239
  • Aug 15 04:58

    cvvergara on develop

    [pgRouting] Change doc files. Merge pull request #1238 from n… (compare)

  • Aug 15 04:58
    cvvergara closed #1238
  • Aug 15 04:57
    cvvergara review_requested #1238
  • Aug 15 04:47
    cvvergara labeled #1237
Vicky Vergara
@cvvergara
once merged we are going to make a tag:
Vicky Vergara
@cvvergara
git tag -a nike0good-2019 -m "<list of functions added>"

where

<list of functions added> can be: "pgr_topologicalSort pgr_transitiveClosure"

that is an annotated tag.
about tags: https://git-scm.com/book/en/v2/Git-Basics-Tagging

Pusing the tag:

git push gsoc nike0good-2019

if everything went right, the tag should be here:
https://github.com/pgRouting/GSoC-pgRouting/tags

Vicky Vergara
@cvvergara
important IF you plan to do more work after the tag, the extra work should also needs to be tagged before the final report
the tags will never be deleted, but the branches, when the next year program starts, will be deleted, so its important that you tag your work.
Once you made the tag:
git fetch gsoc

hmmm I forgot a step before:
you have to:

git fetch gsoc
git checkout gsoc/<your branch>
git checkout -b boost-algs

the name can be any name you want
that is, you have to make sure to tag the exact contents of your branch on the gsoc repo

Vicky Vergara
@cvvergara
So, back to after making the tag

Now, after the tag has being made, lets check if the main repo changed.
suppose that upstream is the main repo

git fetch upstream

if develop changed then you have to do another rebase (see other day topic)

Vicky Vergara
@cvvergara
once everything is ok, you need to push to your pgrouting fork
git push nike0good
Vicky Vergara
@cvvergara
after you push to your repository make the PR to main repo's develop branch
I will check and approve or ask for changes before merging
you will not do the merge, only one of your mentors will do the merge

end of topic

JavierGIS
@JavierGIS
hi¡¡I am trying to perform a function with dijkstravia guiding me through the FOSS4G.
But I have the doubt of how to do the function so that I can only put the view and the osm_id array.
CREATE OR REPLACE FUNCTION wrk_dijkstravia( IN edges_subset regclass, IN via_vertices ANYARRAY, OUT seq INTEGER, OUT gid BIGINT, OUT name TEXT, OUT cost FLOAT, OUT azimuth FLOAT, OUT route_readable TEXT, OUT route_geom geometry ) RETURNS SETOF record AS $BODY$ WITH dijkstra AS (SELECT * FROM pgr_dijkstravia( 'SELECT gid as id, reverse_cost, * FROM ' || $1, ARRAY [ (SELECT id FROM ways_vertices_pgr WHERE osm_id = $2), (SELECT id FROM ways_vertices_pgr WHERE osm_id = $3), (SELECT id FROM ways_vertices_pgr WHERE osm_id = $4)], true ,false , U_turn_on_edge:=false)), get_geom AS( SELECT dijkstra.*, ways.name, CASE WHEN dijkstra.node = ways.source THEN the_geom ELSE ST_Reverse(the_geom) END AS route_geom FROM dijkstra LEFT JOIN ways ON (edge = gid) ORDER BY seq) SELECT seq, name, cost, route_agg_cost, degrees(ST_azimuth(ST_StartPoint(route_geom), ST_EndPoint(route_geom))) AS azimuth, ST_Astext(route_geom) FROM get_geom ORDER BY seq; $BODY$ LANGUAGE 'sql';
Vicky Vergara
@cvvergara
hello @JavierGIS, 2 things,
1) what do you mean by "only put the view and the osm_id array"?
2) and the code is unreadable, can click on the markup help button and see how to write code in this chat.
Hang Wu
@nike0good
PR done.
@cvvergara
Vicky Vergara
@cvvergara
@nike0good I dont see your PR
https://github.com/pgRouting/pgrouting/pulls
Hang Wu
@nike0good
wait
Hang Wu
@nike0good
@cvvergara
JavierGIS
@JavierGIS
Hi. This function is based on that created in the FOSS4 workshop.
But the function created there only has two points, start and end. I am looking for a similar function, but instead of just two points, that the route can be multipoint. So what I want to do is an array. But I don't know how to prepare the array variable in dijkstravia. I don't know if you have this pgrouting functionality contemplated
this is my function:
CREATE OR REPLACE FUNCTION wrk_dijkstravia( 
IN edges_subset regclass, 
IN via_vertices ANYARRAY, 
OUT seq INTEGER, OUT gid BIGINT, 
OUT name TEXT, OUT cost FLOAT, 
OUT azimuth FLOAT, 
OUT route_readable TEXT, 
OUT route_geom geometry ) 
RETURNS SETOF record 
AS 
$BODY$ 
WITH dijkstra AS 
    (SELECT * FROM pgr_dijkstravia( 'SELECT gid as id, reverse_cost, * FROM ' || $1,
    ARRAY [ (SELECT id FROM ways_vertices_pgr WHERE osm_id = $2), 
    (SELECT id FROM ways_vertices_pgr WHERE osm_id = $3), 
    (SELECT id FROM ways_vertices_pgr WHERE osm_id = $4)], true ,false , U_turn_on_edge:=false)), 
get_geom AS
    (SELECT dijkstra.*, ways.name, 
    CASE WHEN dijkstra.node = ways.source THEN the_geom ELSE ST_Reverse(the_geom) 
    END 
    AS route_geom FROM dijkstra LEFT JOIN ways ON (edge = gid) ORDER BY seq) 
SELECT 
seq, 
name, 
cost, 
route_agg_cost, 
degrees(ST_azimuth(ST_StartPoint(route_geom), 
ST_EndPoint(route_geom))) AS azimuth, ST_Astext(route_geom) 
FROM get_geom ORDER BY seq; 
$BODY$ 
LANGUAGE 'sql';
JavierGIS
@JavierGIS
Then when I test the function:
select * from wrk_dijkstravia('vehiculo_tiempo', ARRAY [1781811097, 1932350038])
I get the following error
ERROR:  operator does not exist: bigint = integer[]
LINE 4: ...RAY [ (SELECT id FROM ways_vertices_pgr WHERE osm_id = $2),
in view what I am referring to is "vehiculo_tiempo" that what it does is exclude the streets through which the car cannot drive
JavierGIS
@JavierGIS
I have modified the function a bit:
CREATE OR REPLACE FUNCTION wrk3_dijkstravia( 
IN edges_subset regclass, 
IN via_vertices ANYARRAY,
OUT seq INTEGER, 
OUT path_id integer,
OUT path_seq integer,
OUT start_vid BIGINT,
OUT end_vid BIGINT,
OUT node BIGINT,
OUT edge BIGINT,
OUT name TEXT, 
OUT cost FLOAT, 
OUT agg_cost FLOAT,
OUT azimuth FLOAT,
OUT route_agg_cost FLOAT, 
OUT route_geom geometry ) 
RETURNS SETOF record 
AS 
$BODY$ 
WITH dijkstra AS 
    (SELECT * FROM pgr_dijkstravia( 'SELECT gid as id, reverse_cost, * FROM ' || $1,
    $2::BIGINT[], true ,false)), 
get_geom AS
    (SELECT dijkstra.*, ways.name, 
    CASE WHEN dijkstra.node = ways.source THEN the_geom ELSE ST_Reverse(the_geom) 
    END 
    AS route_geom FROM dijkstra LEFT JOIN ways ON (edge = gid) ORDER BY seq) 
SELECT 
seq,
path_id,
path_seq,
start_vid,
end_vid,
node,
edge,
name, 
cost,
agg_cost, 
route_agg_cost, 
degrees(ST_azimuth(ST_StartPoint(route_geom), 
ST_EndPoint(route_geom))) AS azimuth, 
ST_Astext(route_geom) 
FROM get_geom ORDER BY seq; 
$BODY$ 
LANGUAGE 'sql';
this is the query
SELECT * FROM wrk3_dijkstravia('vehiculo_tiempo', ARRAY [20412,  3044])
But now I get this error
ERROR:  return type mismatch in function declared to return record
DETAIL:  Final statement returns text instead of geometry at column 13.
CONTEXT:  SQL function "wrk3_dijkstravia" during startup
********** Error **********
Vicky Vergara
@cvvergara

On the wrk_dijkstraVia you have 2 IN parameters

IN edges_subset regclass, 
IN via_vertices ANYARRAY,

but are trying to use it like if it had 4 IN parameters

(SELECT id FROM ways_vertices_pgr WHERE osm_id = $3),   -- this use the 3rd IN paramenter
(SELECT id FROM ways_vertices_pgr WHERE osm_id = $4)  ---  this use the 4th IN paramenter

On the wrt3_dijkstraVia is complaining because you have

ST_Astext(route_geom)

it should be

route_geom

becuase it expecting a geometry not a TEXT

JavierGIS
@JavierGIS
Thanks for answering. I already have the query ready and it works.
CREATE OR REPLACE FUNCTION wrk3_dijkstravia( 
IN edges_subset regclass, 
IN via_vertices ANYARRAY,
OUT seq INTEGER, 
OUT path_id integer,
OUT path_seq integer,
OUT start_vid BIGINT,
OUT end_vid BIGINT,
OUT node BIGINT,
OUT edge BIGINT,
OUT name TEXT, 
OUT cost FLOAT, 
OUT agg_cost FLOAT,
OUT azimuth FLOAT,
OUT route_agg_cost FLOAT,
OUT route_readable TEXT, 
OUT route_geom geometry ) 
RETURNS SETOF record 
AS 
$BODY$ 
WITH dijkstra AS 
    (SELECT * FROM pgr_dijkstravia( 'SELECT gid as id, reverse_cost, * FROM ' || $1,
    $2::BIGINT[], true ,false)), 
get_geom AS
    (SELECT dijkstra.*, ways.name, 
    CASE WHEN dijkstra.node = ways.source THEN the_geom ELSE ST_Reverse(the_geom) 
    END 
    AS route_geom FROM dijkstra LEFT JOIN ways ON (edge = gid) ORDER BY seq) 
SELECT 
seq,
path_id,
path_seq,
start_vid,
end_vid,
node,
edge,
name, 
cost,
agg_cost, 
route_agg_cost, 
degrees(ST_azimuth(ST_StartPoint(route_geom), 
ST_EndPoint(route_geom))) AS azimuth, 
ST_Astext(route_geom),
route_geom
FROM get_geom ORDER BY seq; 
$BODY$ 
LANGUAGE 'sql';
Now the next step is to create another function from wrk3_dijkstra. To find out the route by entering as parameters the
"x" and "y" of the different destinations
This would be the function.
CREATE OR REPLACE FUNCTION wrk1_fromAtoB(
    IN edges_subset regclass,
    IN x1 ANYARRAY, IN y1 ANYARRAY,
    OUT seq INTEGER,
    OUT path_id integer,
OUT path_seq integer,
OUT start_vid BIGINT,
OUT end_vid BIGINT,
OUT node BIGINT,
OUT edge BIGINT,
OUT name TEXT, 
OUT cost FLOAT, 
OUT agg_cost FLOAT,
OUT azimuth FLOAT,
OUT route_agg_cost FLOAT,
OUT route_readable TEXT, 
OUT route_geom geometry )

RETURNS SETOF record AS
$BODY$
DECLARE
    final_query TEXT;
BEGIN

    final_query :=
        FORMAT( $$
            WITH
            vertices AS (
                SELECT * FROM ways_vertices_pgr
                WHERE id IN (
                    SELECT source FROM %1$I
                    UNION
                    SELECT target FROM %1$I)
            ),
            dijkstra AS (
                SELECT *
                FROM wrk3_dijkstravia(
                    '%1$I',

                    (SELECT id FROM vertices 
                        ORDER BY the_geom <-> ST_SetSRID(ST_Point(%2$s, %3$s), 4258)LIMIT 1),
            ))
            SELECT
                seq,
                dijkstra.path_id,
                dijkstra.path_seq,
                dijkstra.start_vid,
                dijkstra.end_vid,
                dijkstra.node,
                dijkstra.edge,
                dijkstra.name,
                TRUNC (ways.length_m::numeric) AS distancia_m,
                TRUNC (sum(length_m::numeric) OVER (ORDER BY seq), 2) as distancia_total,
                ROUND (dijkstra.cost::numeric, 2) AS cost,
                ROUND (dijkstra.agg_cost::numeric, 2) as agg_cost,
                azimuth,
                route_geom AS geom
            FROM dijkstra JOIN ways USING (gid);$$,
        edges_subset, x1,y1);
    RAISE notice '%', final_query;
    RETURN QUERY EXECUTE final_query;
END;
$BODY$
LANGUAGE 'plpgsql';
JavierGIS
@JavierGIS
But this would not be worth it, since I need to put all the "x" e "y" of the origin and destination
This would be an example query with what I want to put in my function
select * from wrk1_fromatob('vehiculo_tiempo', ARRAY [-4.534907341003418 , 42.011900946753414, -4.525036811828612,  42.00408831109371, -4.523491859436035, 41.99825212460214])
In this query go 3 points with their coordinates "x" e "y"