1

I have this below GetNearest function that will get every user's nearest node from ways (imported using osm2pgrouting).

CREATE or REPLACE FUNCTION GetNearest(NUMERIC, NUMERIC) 
  RETURNS BIGINT AS 
  $$
  SELECT id FROM ways_vertices_pgr AS vertices 
  ORDER BY vertices.the_geom <-> ST_SetSrid(ST_MakePoint($1, $2), 4326) LIMIT 1;
  $$ 
LANGUAGE SQL;

Below is the query that runs and insert into another table the every possible user to user travel time duration.

INSERT INTO "UserTravelTime" ("FromLat", "FromLng", "ToLat", "ToLng", "Duration", "CreatedOn", "UpdatedOn")
SELECT C1."Lat" AS "FromLat", C1."Lng" AS "FromLng", C2."Lat" AS "ToLat", C2."Lng" "ToLng", 
  (SELECT ROUND(SUM(cost)) AS Cost FROM pgr_dijkstra(
    'SELECT gid AS id,
         source,
         target,
         cost_s as cost
        FROM ways',
    (SELECT GetNearest(C1."Lng", C1."Lat")), (SELECT GetNearest(C2."Lng", C2."Lat")),
    directed := true)),
    NOW(),
    NOW()
  FROM 
  "UserCoordinate" C1,
  "UserCoordinate" C2;

It works but it takes a lot of time to complete nearly 15 minutes, is there anything that I am doing wrong or could do better to make it faster?

1

As I needed only the cost and not the entire path, I used pgr_dijkstraCost to calculate the costs and now it takes only 2 minutes for more than a million records.

I have also added another column to user information to store the nearest osm node and that made my query much faster than expected.

Below is the query.

INSERT INTO "UserTravelTime" ("FromId", "ToId", "Duration", "ParentId", "CreatedOn", "UpdatedOn")
SELECT *, NULL, NOW(), NOW() FROM pgr_dijkstraCost(
    'SELECT gid AS id, source, target, cost_s as cost, reverse_cost_s as reverse_cost FROM ways',
    ARRAY(SELECT DISTINCT "OsmNodeId" FROM "UserCoordinate"), 
    ARRAY(SELECT DISTINCT "OsmNodeId" FROM "UserCoordinate"), 
    directed := true) AS "Duration";

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.