0

I try to use pgr_Dijkstra through Postgis and Pgadmin to find the shortest path in a network between a lot of nodes.
So I thought I could to "automate" the process with a PLSQL query. Here is a query I simplified as example. It's a loop where I try to use pgr_Dijkstra between start nodes and end nodes having respectively _ids and _ide as id in the table network:

DO $$ DECLARE

_ids integer;
_ide integer;

BEGIN
    _ids:=161794;
    _ide:=161794;
    DROP TABLE IF EXISTS test_dijkstra;
    CREATE TABLE test_dijkstra(seq integer, node integer, edge integer, cost double precision);
    WHILE _ids <=161797 LOOP
        WHILE _ide <=161797 LOOP
            INSERT INTO test_dijkstra(seq, node, edge, cost)
            SELECT seq, id1 AS node, id2 AS edge, cost
            FROM pgr_Dijkstra('SELECT id, source, target, cost, x1, y1, x2, y2 FROM network', _ids, _ide, FALSE, FALSE);
            _ide:=_ide+3;
        END LOOP;
        _ids:=_ids+3;
    END LOOP;
END$$;  

My problem is that even with this simple example, the NOTICE tells me :

NOTICE:  Deprecated function
CONTEXT:  instruction SQL « INSERT INTO test_dijkstra(seq, node, edge, cost)
            SELECT seq, id1 AS node, id2 AS edge, cost
            FROM pgr_Dijkstra('SELECT id, source, target, cost, x1, y1, x2, y2 FROM network', _ids, _ide, FALSE, FALSE) »
fonction PL/pgsql inline_code_block, ligne 13 à instruction SQL 

I think the function doesn't want to have variables (_ids and _ide) as parameters, but I can't figure out why.

Is there a way to make it work? I have a lot of nodes, so I can't process pgr_Dijsktra manually for every couple!

  • It seems that you are using a deprecated call to the pgr_dijkstra function. Check the manual: docs.pgrouting.org/2.2/en/src/dijkstra/doc/pgr_dijkstra_v2.html Did you try to call the pgr_dijkstra function manually? Did that work without notice? – tilt May 22 '16 at 12:02
  • Yes, for example, it works perfectly fine if I run it with two fixed id: SELECT seq, id1 AS node, id2 AS edge, cost FROM pgr_Dijkstra('SELECT id, source, target, cost, x1, y1, x2, y2 FROM network', 200, 300, FALSE, FALSE); – user37103 May 22 '16 at 12:22
  • 1
    You need to remove the 2nd false parameter from the pgr_Dijkstra function call. I just had a similar issue. – John Powell May 25 '16 at 21:03
1

Based on:

SELECT generate_series(161794, 161797, 3);    
 generate_series 
-----------------
          161794
          161797
(2 rows)

you can use the many to many signature of pgr_dijkstra: http://docs.pgrouting.org/2.2/en/src/dijkstra/doc/pgr_dijkstra.html#dijkstra-many-to-many

WITH
my_source_series AS (SELECT generate_series(161794, 161797, 3) AS sources,
my_target_series AS (SELECT generate_series(161794, 161797, 4) AS targets,
SELECT * FROM pgr_Dijkstra('SELECT id, source, target, cost FROM network',
 (select array_agg(sources) FROM my_source_series),
 (select array_agg(targets) FROM my_target_series),
 FALSE);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.