TRSP for railroads doesn't work

[alexander-koval]

Problem Hello guys. I have a problem with your new version of pgr_trsp_withPoints function. Specifically I try to use your wiki TRSP-for-railroads. Example with restrictions doesn't work properly.

To Reproduce

-- Create network table
CREATE TABLE network (
    id serial,
    source integer,
    target integer,
    cost double precision,
    reverse_cost double precision,
    x1 double precision,
    y1 double precision,
    x2 double precision,
    y2 double precision,
    the_geom geometry
);

-- Create restrictions table
CREATE TABLE restrictions (
    id serial,
    cost FLOAT,
    path BIGINT[]
);

-- Populate network table
INSERT INTO network (x1,y1,x2,y2) VALUES
  (0,0,1,0),(1,0,4,0),(4,0,5,0),(5,0,5,5),(5,5,0,5),(0,5,0,0),                                                          
  (1,0,2,1),(2,1,3,1),(3,1,4,0)                                                                                         
;

UPDATE network SET the_geom = ST_makeline(ST_point(x1,y1),ST_point(x2,y2));
UPDATE network SET cost = ST_length(the_geom), reverse_cost = ST_length(the_geom);
SELECT pgr_createTopology('network',0.001)

INSERT INTO restrictions (id, COST, path) VALUES
    (1, 100, ARRAY[9,2]), (2, 100, ARRAY[2, 9]), (3, 100, ARRAY[7,2]), (4, 100, ARRAY[2,7])
;   

SELECT *
FROM pgr_trsp_withPoints(
  $$SELECT id, source, target, cost, reverse_cost FROM network$$,
  $$SELECT * FROM restrictions$$,
  $$SELECT * FROM (VALUES (1, 2, 0.75),(2, 8, 0.5)) AS t(pid, edge_id, fraction)$$,
  4, 1
);

The result is:

seq|path_seq|start_vid|end_vid|node|edge|cost|agg_cost|
---+--------+---------+-------+----+----+----+--------+
  1|       1|        4|      1|   4|   3| 1.0|     0.0|
  2|       2|        4|      1|   3|   2| 3.0|     1.0|
  3|       3|        4|      1|   2|   1| 1.0|     4.0|
  4|       4|        4|      1|   1|  -1| 0.0|     5.0|

Expectation According to your wiki, it should be:

 seq | path_seq | start_vid | end_vid | node | edge |        cost        |      agg_cost      
-----+----------+-----------+---------+------+------+--------------------+--------------------
   1 |        1 |        -1 |      -2 |   -1 |    2 |               0.75 |                  0
   2 |        2 |        -1 |      -2 |    3 |    3 |                  1 |               0.75
   3 |        3 |        -1 |      -2 |    4 |    4 |                  5 |               1.75
   4 |        4 |        -1 |      -2 |    5 |    5 |                  5 |               6.75
   5 |        5 |        -1 |      -2 |    6 |    6 |                  5 |              11.75
   6 |        6 |        -1 |      -2 |    1 |    1 |                  1 |              16.75
   7 |        7 |        -1 |      -2 |    2 |    7 | 1.4142135623730958 |              17.75
   8 |        8 |        -1 |      -2 |    7 |    8 |                0.5 | 19.164213562373096
   9 |        9 |        -1 |      -2 |   -2 |   -1 |                  0 | 19.664213562373096

Sample Data Just try to reproduce your example TRSP-for-railroads

Platform/versions

SELECT version();
PostgreSQL 15.4 (Debian 15.4-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
SELECT postgis_full_version();
POSTGIS="3.3.4 3.3.4" [EXTENSION] PGSQL="150" GEOS="3.9.0-CAPI-1.16.2" PROJ="7.2.1" LIBXML="2.9.10" LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" (core procs from "3.3.2 4975da8" need upgrade) TOPOLOGY (topology procs from "3.3.2 4975da8" need upgrade)
SELECT pgr_version();
3.5.1

[cvvergara]

Wiki is wrong, query should be:

SELECT *
FROM pgr_trsp_withPoints(
  $$SELECT id, source, target, cost, reverse_cost FROM network$$,
  $$SELECT * FROM restrictions$$,
  $$SELECT * FROM (VALUES (1, 2, 0.75),(2, 8, 0.5)) AS t(pid, edge_id, fraction)$$,
  -1, -2
);

Thanks for noticing ... fixing the wiki

[cvvergara]

See #2593