3

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: enter image description here

3

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
 ));
  • Thanks a lot for your answer. I tried using your code, but I still get zero rows. I'm not sure I understand the 'virtual pid' part. I'll try some more tonight and leave a comment later. – JonasPedersen Nov 3 '16 at 6:40
  • If you still have trouble, also post how you generated the fracs and edges. Your fractional generation should use the same network query as your pgr_withPointsCost because if you use the full table, then you may come up with edges not in the subnetwork. That would yield no results. – LR1234567 Nov 3 '16 at 15:21
  • I've updated my question with the query to generate edge_id and fractions. Oddly, when I use 1 as the 'start_vid' I get 5 rows returned (I should have around 28000 poi). – JonasPedersen Nov 3 '16 at 21:00
  • Okay there is one problem, your generate edge_id/fractions is using the whole network (it should have class_id > 105 like your network query) otherwise you could end up with edges not in your subnetwork – LR1234567 Nov 4 '16 at 19:58
  • I have removed the class_id > 105 where clause, and changed the fixed start_vid parameter to -150980 (contained in the poi query) from the withpoints query, but I still don't get a result. I'm pretty lost as to why it won't return any rows:-/ – JonasPedersen Nov 4 '16 at 20:55
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.

  • I think I have learned something new from this - very nicely structured answer. – JonasPedersen Nov 18 '16 at 13:58
  • You use $$...$$ instead of '...', is that allowed in PostgreSQL for all cases where an argument is a query? – JonasPedersen Nov 18 '16 at 14:00
  • 1
    I am used to use [dollar quoted strings] (postgresql.org/docs/current/static/…) on the queries. Easier when there is a WHERE statement $$ SELECT * FROM foo WHERE name = 'bar'$$ VS 'SELECT * FROM foo WHERE name = ''bar'' ' – Vicky Nov 18 '16 at 15:13

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.