Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Repo info
Activity
  • 08:01
    transifex-integration[bot] opened #1323
  • 08:01

    transifex-integration[bot] on translations_pgr-tspeuclidean-po--master_ja

    Apply translations in ja trans… (compare)

  • 08:01

    transifex-integration[bot] on translations_pgr-tspeuclidean-po--master_ja

    (compare)

  • 06:51
    transifex-integration[bot] opened #1322
  • 06:51

    transifex-integration[bot] on translations_tsp-family-po--master_ja

    Apply translations in ja trans… (compare)

  • 06:51

    transifex-integration[bot] on translations_tsp-family-po--master_ja

    (compare)

  • 05:16
    transifex-integration[bot] opened #1321
  • 05:16

    transifex-integration[bot] on translations_pgr-tsp-po--master_ja

    Translate /locale/en/LC_MESSAGE… (compare)

  • 05:16

    transifex-integration[bot] on translations_pgr-tsp-po--master_ja

    (compare)

  • Dec 04 06:53
    cvvergara closed #261
  • Dec 04 06:53
    cvvergara commented #261
  • Dec 04 06:51
    cvvergara opened #261
  • Dec 04 06:50

    cvvergara on capitalizecertainfilenames

    (compare)

  • Dec 03 05:11

    cvvergara on v3.0.0-rc1

    (compare)

  • Dec 03 05:10
    cvvergara commented #1243
  • Dec 03 05:10
    cvvergara commented #1243
  • Dec 03 05:10
    cvvergara commented #1243
  • Dec 03 05:10
    cvvergara commented #1243
  • Dec 03 05:10
    cvvergara commented #1243
  • Dec 03 05:10
    cvvergara commented #1243
Vicky Vergara
@cvvergara

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

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

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"
Jin Igarashi
@JinIgarashi
hello
Vicky Vergara
@cvvergara
connichiwa
Mohammed Mahmoud
@mohammed16
hello ,,,
hello ,,,
Vicky Vergara
@cvvergara
hello @mohammed16
Cayetano Benavent
@cayetanobv
@cvvergara @dkastl could you review PR pgRouting/osm2pgrouting#258
Chintan Pathak
@chintanp
has anyone installed pgRouting with Postgres 12 on windows?
Vicky Vergara
@cvvergara
I think there is a package, let me double check