Using pgr_withPointsCost from pgrouting 2.2.2

Question

I'm trying to calculate the cost of travelling in a network from one point to many points (adresses). To increase the precision of the analysis i want to use the function pgr_withPointsCost as it (as far as I understand) can calculate to and from points that are not the actual vertexes of the network.

I have loaded osm data into postgres using osm2pgrouting and I have created a table containing the id's of my points of interest (poi), the id of the closest edge (edge_id) and the projected location of my poi on the edge as a fraction.

When running the pgr_withPointsCost query I get an output of 0 rows. Below is my create table statements and the current query - can anyone help me find the problem with my query?

ways table from OSM2pgrouting :

CREATE TABLE osm_network.herlev_ways
(
  gid bigint,
  class_id integer,
  length double precision,
  length_m double precision,
  name text,
  source bigint,
  target bigint,
  x1 double precision,
  y1 double precision,
  x2 double precision,
  y2 double precision,
  cost double precision,
  reverse_cost double precision,
  cost_s double precision,
  reverse_cost_s double precision,
  rule text,
  one_way integer,
  maxspeed_forward integer,
  maxspeed_backward integer,
  osm_id bigint,
  source_osm bigint,
  target_osm bigint,
  priority double precision,
  the_geom geometry(LineString,4326)
)

Points of interest table:

 CREATE TABLE base_data.geo_borger_aktiv
(
  ogc_fid serial NOT NULL,
  wkb_geometry geometry(Point,25832),
  kvh_adr_key character varying,
  CONSTRAINT geo_borger_aktiv_pkey PRIMARY KEY (ogc_fid)
)

Nearest network edge to poi - including fraction:

CREATE TABLE base_data.nearest_ways_borger_aktiv
(
  id bigint,
  pid character varying,
  edge_id bigint,
  fraction double precision
)

Query:

SELECT * 
FROM pgr_withPointsCost('SELECT gid AS id, source, 
target, length_m AS cost, length_m AS reverse_cost 
FROM osm_network.herlev_ways
WHERE class_id > 105
ORDER BY gid',
'SELECT id*-1 AS pid, edge_id, fraction FROM
base_data.nearest_ways_borger_aktiv',
117558
,
(SELECT array_agg(nearest_ways_borger_aktiv.id*-1) 
 FROM base_data.nearest_ways_borger_aktiv
 WHERE nearest_ways_borger_aktiv.id IS NOT NULL))

UPDATED withPointsCost query:

SELECT * 
FROM pgr_withPointsCost('SELECT gid AS id, source, target, length_m AS cost, length_m AS reverse_cost 
FROM osm_network.herlev_ways
ORDER BY gid',
'SELECT id AS pid, edge_id, fraction FROM base_data.nearest_ways_borger_aktiv',
-150980,
(SELECT array_agg(nearest_ways_borger_aktiv.id*-1) 
 FROM base_data.nearest_ways_borger_aktiv
 WHERE fraction BETWEEN 0.0001 AND 0.9999
 ))

Creating edge_id and fraction:

CREATE TABLE base_data.nearest_ways_for_busstops AS

SELECT DISTINCT ON(ogc_fid) 
ogc_fid AS id, 
gid AS edge_id, 
ST_LineLocatePoint(ver.the_geom,ST_Transform(akt.wkb_geometry,4326)) AS fraction
FROM osm_network.herlev_ways ver, base_data.nearest_ways_borger_aktiv akt
WHERE ST_DWithin(ver.the_geom, st_transform(akt.wkb_geometry,4326), 0.001) 
ORDER BY ogc_fid, ST_Distance(ver.the_geom, ST_Transform(akt.wkb_geometry,4326)) ASC

Points of interest (nearest_ways_borger_aktiv) visualised, including edge_id of nearest ways and calculated fraction, along with ways with corresponding gid:

Answer 1

Two problems I see:

1) For the points_sql, the pid should be positive, though when you use it in the array it should have a negative sign.

2) If your point falls close to an existing node (meaning fraction is close to 0 or 1) then you should not include it. In this case, you might want to store virtual pid in your table (because sometimes it might be a true node id). When I store it, I always create the pid as - (and then your *-1 would be right and only output the ones that are negative since the positive ones represent true nodes in network)

Without storing the node id (whether virtual or real), it's hard to join back to your point data if you have point data that falls on a network node

So change your queries like this:

-- the virtual nodes table
-- note we for convention make injected nodes negative and points that fall  on nodes in network positive

 CREATE TABLE base_data.nearest_ways_for_busstops as
  WITH ve AS (
  select distinct on(ogc_fid) ogc_fid as id, ver.gid as edge_id

   , ST_LineLocatePoint(ver.the_geom,st_transform(akt.wkb_geometry,4326)) as  fraction, 
   ver.source, ver.target
from osm_network.herlev_ways ver, base_data.nearest_ways_borger_aktiv akt
where st_dwithin(ver.the_geom, st_transform(akt.wkb_geometry,4326), 0.001) 
order by ogc_fid, st_distance(ver.the_geom, st_transform(akt.wkb_geometry,4326)) ASC)
SELECT id, 
   CASE WHEN fraction = 0 THEN source
        WHEN fraction = 1 THEN target ELSE -id END AS pid,
   edge_id, fraction
FROM ve;


SELECT * 
  FROM pgr_withPointsCost('select gid as id, source, target, 
    length_m as cost, length_m as reverse_cost 
    from osm_network.herlev_ways',
-- we want only virtual nodes (those not in our graph) (and make node id +)
'select pid*-1 as pid, edge_id, fraction 
from base_data.nearest_ways_borger_aktiv
WHERE pid < 0',
-150980,
(select array_agg(nearest_ways_borger_aktiv.pid) 
 from base_data.nearest_ways_borger_aktiv
 ));

Answer 2

Going to simplify the problem, and go step by step. given this table:

CREATE TABLE pointsofinterest(
  pid bigserial PRIMARY KEY,
  vertex_id bigint, -- id of a vertex on ways_vertices_pgr
  edge_id bigint,   -- id of an edge on ways
  fraction double precision, -- null when vertex_id has a value
  side character(1), -- not needed in the example
  the_geom geometry(Point,4326),  -- not used in the example
  CONSTRAINT pointsofinterest_pkey PRIMARY KEY (pid)
)

This is a POI that the fraction was 1 or 0. (the value of fraction is irrelevant in this case)

SELECT pid, vertex_id, edge_id, fraction
FROM pointsofinterest
WHERE pid = 45;
 pid | vertex_id | edge_id | fraction 
-----+-----------+---------+----------
  45 |       291 |         |         
(1 row)

This is a POI that the fraction is really a fraction.

SELECT pid, vertex_id, edge_id, fraction
FROM pointsofinterest
WHERE pid = 340;
 pid | vertex_id | edge_id |      fraction      
-----+-----------+---------+--------------------
 340 |           |     188 | 0.0884753248888406
(1 row)

Executing a pgr_dijkstra can be done to the POI that has a vertex_id value:

SELECT * from pgr_dijkstra(
    $$ SELECT id, source, target, length_m as cost, length_m as reverse_cost  
    FROM ways 
    $$,                           
    (SELECT vertex_id FROM pointsofinterest WHERE pid = 45),
    830
);
 seq | path_seq | node | edge |       cost       |     agg_cost     
-----+----------+------+------+------------------+------------------
   1 |        1 |  291 |  339 | 3.68492816941643 |                0
   2 |        2 |  800 |  907 | 2.82722550904328 | 3.68492816941643
   3 |        3 |  830 |   -1 |                0 | 6.51215367845971
(3 rows)

The pgr_withPoints gives the same results:

SELECT * from pgr_withPoints(
    $$ SELECT id, source, target, length_m as cost, length_m as reverse_cost 
    FROM ways
    $$,
    $$ SELECT pid, edge_id, fraction   
    FROM pointsofinterest
    WHERE edge_id IS NOT NULL
    $$,
    (SELECT vertex_id FROM pointsofinterest WHERE pid = 45),
    830
);
 seq | path_seq | node | edge |       cost       |     agg_cost     
-----+----------+------+------+------------------+------------------
   1 |        1 |  291 |  339 | 3.68492816941643 |                0
   2 |        2 |  800 |  907 | 2.82722550904328 | 3.68492816941643
   3 |        3 |  830 |   -1 |                0 | 6.51215367845971
(3 rows)

For the other POI where edge_id is defined can only route using pgr_withPoints, because it is a real point outside of the routing topology

SELECT * from pgr_withPoints(
    $$ SELECT id, source, target, length_m as cost, length_m as reverse_cost
    FROM ways
    $$,
    $$ SELECT pid, edge_id, fraction
    FROM pointsofinterest
    WHERE edge_id IS NOT NULL
    $$,
    (SELECT -pid FROM pointsofinterest WHERE pid = 340),
    830
);
 seq | path_seq | node | edge |       cost       |     agg_cost     
-----+----------+------+------+------------------+------------------
   1 |        1 | -340 |  188 | 2.04022932919851 |                0
   2 |        2 |  176 |  208 | 16.8493631274143 | 2.04022932919851
   3 |        3 |  513 | 1143 | 65.5444358626425 | 18.8895924566128
   4 |        4 |  993 |  939 | 105.367043006667 | 84.4340283192553
   5 |        5 |  830 |   -1 |                0 | 189.801071325922
(5 rows)

Executing a pgr_withPoints(many to one) can be done with a case statement

SELECT * from pgr_withPoints(
    $$ SELECT id, source, target, length_m as cost, length_m as reverse_cost
    FROM ways
    $$,
    $$ SELECT pid, edge_id, fraction
    FROM pointsofinterest
    WHERE edge_id IS NOT NULL
    $$,

    (SELECT array_agg(
        CASE
           WHEN -- edge_id IS NOT NULL THEN -pid
            pid = 340 THEN -pid
           WHEN -- vertex_id IS NOT NULL THEN vertex_id
            pid = 45 THEN vertex_id
        END)
     FROM pointsofinterest
     WHERE pid in (45, 340) -- remove this
    ),
    830
);
 seq | path_seq | start_pid | node | edge |       cost       |     agg_cost     
-----+----------+-----------+------+------+------------------+------------------
   1 |        1 |      -340 | -340 |  188 | 2.04022932919851 |                0
   2 |        2 |      -340 |  176 |  208 | 16.8493631274143 | 2.04022932919851
   3 |        3 |      -340 |  513 | 1143 | 65.5444358626425 | 18.8895924566128
   4 |        4 |      -340 |  993 |  939 | 105.367043006667 | 84.4340283192553
   5 |        5 |      -340 |  830 |   -1 |                0 | 189.801071325922
   6 |        1 |       291 |  291 |  339 | 3.68492816941643 |                0
   7 |        2 |       291 |  800 |  907 | 2.82722550904328 | 3.68492816941643
   8 |        3 |       291 |  830 |   -1 |                0 | 6.51215367845971
(8 rows)

The last query has in comments what you need to put if you want to route all the points of interest to destination 830.