1

I've made network nodes in PostgreSQL SQL based on Osm2po. I can find the closest way among two points based on the pgr_dikstra algorithm but I want to find the closest route between one single source point and several destination points just in one query and choose the closest one. How can I implement this query? For example, suppose I want to find the closest vehicle to me when there are many available vehicles

This is my code

SELECT * FROM pgr_dijkstra(
        'SELECT gid as id, source, target, length_m AS cost FROM ' || $1,
        -- source
        (SELECT id FROM ways_vertices_pgr
            ORDER BY the_geom <-> ST_SetSRID(ST_Point(x1,y1),4326) LIMIT 1),
        -- target
        (SELECT id FROM ways_vertices_pgr
            ORDER BY the_geom <-> ST_SetSRID(ST_Point(x2,y2),4326) LIMIT 1),
        false) 

but this code works just for two points

1

Read the docs carefully: pgr_Dijkstra has multiple signatures, and one of them runs the one-to-many routing you are looking for.

You simply pass in all your target nodes as ARRAY:

SELECT *
FROM   pgr_Dijkstra(
         'SELECT gid as id, source, target, length_m AS cost FROM ' || $1,
         <src_id>,
         ARRAY[<tgt_id>, <tgt_id>, ...],
         FALSE
       )
;

UPDATE:

And I should read you question carefully; to actually get only those rows that belong to the shortest path, run

WITH
    paths AS (
        SELECT *
        FROM   pgr_Dijkstra(
                 'SELECT gid as id, source, target, length_m AS cost FROM ' || $1,
                 <src_id>,
                 ARRAY[<tgt_id>, <tgt_id>, ...],
                 FALSE
               )
    )

SELECT  *
FROM    paths
WHERE   end_vid = (
    SELECT  end_vid
    FROM    paths
    GROUP BY
            end_vid
    ORDER BY
            MAX(agg_cost)
    LIMIT   1
);
| improve this answer | |
  • tnx for the response but I need something like this: ARRAY[ (lat,lng),(lat,lng),(lat,lng),...] – hamed Dec 11 '19 at 12:22
  • you know what i wanna send pairs of lat ,lng in array – hamed Dec 11 '19 at 12:24
  • @hamed you will likely need to wrap the conversion between your coordinate pairs and nodes of the graph into a function, as you are presumably already trying to do; is the real question here how to match your coordinate pairs to the network topology? – geozelot Dec 11 '19 at 12:47

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.