Navigation :

Table of Contents - pgRouting Manual (3.4) > pgr_pickDeliver - Experimental - pgRouting Manual (3.4)

pgr_pickDeliver - Experimental - pgRouting Manual (3.4)

Supported versions: Latest ( 3.4 ) 3.3 3.2 3.1 ) 3.0

`pgr_pickDeliver` - Experimental

pgr_pickDeliver - Pickup and delivery Vehicle Routing Problem

Warning

Possible server crash

These functions might create a server crash

Warning

Experimental functions

They are not officially of the current release.
They likely will not be officially be part of the next release:
- The functions might not make use of ANY-INTEGER and ANY-NUMERICAL
- Name might change.
- Signature might change.
- Functionality might change.
- pgTap tests might be missing.
- Might need c/c++ coding.
- May lack documentation.
- Documentation if any might need to be rewritten.
- Documentation examples might need to be automatically generated.
- Might need a lot of feedback from the comunity.
- Might depend on a proposed function of pgRouting
- Might depend on a deprecated function of pgRouting

Availability

Version 3.0.0
- New experimental function

Synopsis

Problem: Distribute and optimize the pickup-delivery pairs into a fleet of vehicles.

Optimization problem is NP-hard.
pickup and Delivery with time windows.
All vehicles are equal.
- Same Starting location.
- Same Ending location which is the same as Starting location.
- All vehicles travel at the same speed.
A customer is for doing a pickup or doing a deliver.
- has an open time.
- has a closing time.
- has a service time.
- has an (x, y) location.
There is a customer where to deliver a pickup.
- travel time between customers is distance / speed
- pickup and delivery pair is done with the same vehicle.
- A pickup is done before the delivery.

Characteristics

All trucks depart at time 0.
No multiple time windows for a location.
Less vehicle used is considered better.
Less total duration is better.
Less wait time is better.
the algorithm will raise an exception when
- If there is a pickup-deliver pair than violates time window
- The speed, max_cycles, ma_capacity have illegal values
Six different initial will be optimized - the best solution found will be result

Signature

pgr_pickDeliver( Orders SQL , Vehicles SQL , Matrix SQL , [ options ])

options:


       
        [factor,
       
       
        max_cycles,
       
       
        initial_sol]

RETURNS SET OF


       
        (seq,
       
       
        vehicle_number,
       
       
        vehicle_id,
       
       
        stop,
       
       
        order_id,
       
       
        stop_type,
       
       
        cargo,
       
       
        travel_time,
       
       
        arrival_time,
       
       
        wait_time,
       
       
        service_time,
       
       
        departure_time)

Example :: Solve the following problem

Given the vehicles:

     SELECT id, capacity, start_node_id, start_open, start_close
FROM vehicles;
 id  capacity  start_node_id  start_open  start_close
----+----------+---------------+------------+-------------
  1        50             11           0           50
  2        50             11           0           50
(2 rows)


    

and the orders:

     SELECT id, demand,
       p_node_id, p_open, p_close, p_service,
       d_node_id, d_open, d_close, d_service
FROM orders;
 id  demand  p_node_id  p_open  p_close  p_service  d_node_id  d_open  d_close  d_service
----+--------+-----------+--------+---------+-----------+-----------+--------+---------+-----------
  1      10         10       2       10          3          3       6       15          3
  2      20         16       4       15          2         15       6       20          3
  3      30          7       2       10          3         12       3       20          3
(3 rows)


    

The query:

     SELECT * FROM pgr_pickDeliver(
  $$SELECT id, demand,
       p_node_id, p_open, p_close, p_service,
       d_node_id, d_open, d_close, d_service
    FROM orders$$,
  $$SELECT id, capacity, start_node_id, start_open, start_close
    FROM vehicles$$,
  $$SELECT * from pgr_dijkstraCostMatrix(
    'SELECT * FROM edges ',
    (SELECT array_agg(id) FROM (SELECT p_node_id AS id FROM orders
        UNION
        SELECT d_node_id FROM orders
        UNION
        SELECT start_node_id FROM vehicles) a))
  $$);
 seq  vehicle_seq  vehicle_id  stop_seq  stop_type  stop_id  order_id  cargo  travel_time  arrival_time  wait_time  service_time  departure_time
-----+-------------+------------+----------+-----------+---------+----------+-------+-------------+--------------+-----------+--------------+----------------
   1            1           1         1          1       11        -1      0            0             0          0             0               0
   2            1           1         2          2        7         3     30            1             1          1             3               5
   3            1           1         3          3       12         3      0            2             7          0             3              10
   4            1           1         4          2       16         2     20            2            12          0             2              14
   5            1           1         5          3       15         2      0            1            15          0             3              18
   6            1           1         6          6       11        -1      0            2            20          0             0              20
   7            2           2         1          1       11        -1      0            0             0          0             0               0
   8            2           2         2          2       10         1     10            3             3          0             3               6
   9            2           2         3          3        3         1      0            3             9          0             3              12
  10            2           2         4          6       11        -1      0            2            14          0             0              14
  11           -2           0         0         -1       -1        -1     -1           16            -1          1            17              34
(11 rows)


    

Parameters

The parameters are:

Column	Type	Description
Orders SQL	`TEXT`	Orders SQL as described below.
Vehicles SQL	`TEXT`	Vehicles SQL as described below.
Matrix SQL	`TEXT`	Matrix SQL as described below.

Pick-Deliver optional parameters

Column	Type	Default	Description
`factor`	`NUMERIC`	1	Travel time multiplier. See Factor handling
`max_cycles`	`INTEGER`	10	Maximum number of cycles to perform on the optimization.
`initial_sol`	`INTEGER`	4	Initial solution to be used. `1` One order per truck `2` Push front order. `3` Push back order. `4` Optimize insert. `5` Push back order that allows more orders to be inserted at the back `6` Push front order that allows more orders to be inserted at the front

Column

Type

Default

Description

factor

NUMERIC

Travel time multiplier. See Factor handling

max_cycles

INTEGER

Maximum number of cycles to perform on the optimization.

initial_sol

INTEGER

Initial solution to be used.

1 One order per truck
2 Push front order.
3 Push back order.
4 Optimize insert.
5 Push back order that allows more orders to be inserted at the back
6 Push front order that allows more orders to be inserted at the front

Orders SQL

A SELECT statement that returns the following columns:

       id, demand
      
       p_node_id, p_open, p_close, [p_service,]
      
       d_node_id, d_open, d_close, [d_service,]

where:

Column	Type	Description
`id`	ANY-INTEGER	Identifier of the pick-delivery order pair.
`demand`	ANY-NUMERICAL	Number of units in the order
`p_open`	ANY-NUMERICAL	The time, relative to 0, when the pickup location opens.
`p_close`	ANY-NUMERICAL	The time, relative to 0, when the pickup location closes.
[ `p_service` ]	ANY-NUMERICAL	The duration of the loading at the pickup location. When missing: 0 time units are used
`d_open`	ANY-NUMERICAL	The time, relative to 0, when the delivery location opens.
`d_close`	ANY-NUMERICAL	The time, relative to 0, when the delivery location closes.
[ `d_service` ]	ANY-NUMERICAL	The duration of the unloading at the delivery location. When missing: 0 time units are used

Where:

ANY-INTEGER :: SMALLINT , INTEGER , BIGINT
ANY-NUMERICAL :: SMALLINT , INTEGER , BIGINT , REAL , FLOAT

Column	Type	Description
`p_node_id`	ANY-INTEGER	The node identifier of the pickup, must match a vertex identifier in the Matrix SQL .
`d_node_id`	ANY-INTEGER	The node identifier of the delivery, must match a vertex identifier in the Matrix SQL .

Where:

ANY-INTEGER :: SMALLINT , INTEGER , BIGINT

Vehicles SQL

A SELECT statement that returns the following columns:

       id, capacity
      
       start_node_id, start_open, start_close [, start_service,]
      
       [end_node_id, end_open, end_close, end_service]

where:

Column	Type	Description
`id`	ANY-NUMERICAL	Identifier of the vehicle.
`capacity`	ANY-NUMERICAL	Maiximum capacity units
`start_open`	ANY-NUMERICAL	The time, relative to 0, when the starting location opens.
`start_close`	ANY-NUMERICAL	The time, relative to 0, when the starting location closes.
[ `start_service` ]	ANY-NUMERICAL	The duration of the loading at the starting location. When missing: A duration of \(0\) time units is used.
[ `end_open` ]	ANY-NUMERICAL	The time, relative to 0, when the ending location opens. When missing: The value of `start_open` is used
[ `end_close` ]	ANY-NUMERICAL	The time, relative to 0, when the ending location closes. When missing: The value of `start_close` is used
[ `end_service` ]	ANY-NUMERICAL	The duration of the loading at the ending location. When missing: A duration in `start_service` is used.

Column	Type	Description
`start_node_id`	ANY-INTEGER	The node identifier of the start location, must match a vertex identifier in the Matrix SQL .
[ `end_node_id` ]	ANY-INTEGER	The node identifier of the end location, must match a vertex identifier in the Matrix SQL . When missing: `end_node_id` is used.

Column

Type

Description

start_node_id

ANY-INTEGER

The node identifier of the start location, must match a vertex identifier in the Matrix SQL .

[ end_node_id ]

ANY-INTEGER

The node identifier of the end location, must match a vertex identifier in the Matrix SQL .

When missing: end_node_id is used.

Where:

ANY-INTEGER :: SMALLINT , INTEGER , BIGINT

Matrix SQL

Where:

ANY-INTEGER :: SMALLINT, INTEGER, BIGINT
ANY-NUMERICAL :: SMALLINT, INTEGER, BIGINT, REAL, FLOAT

Return columns

     RETURNS SET OF
 (seq, vehicle_seq, vehicle_id, stop_seq, stop_type,
     travel_time, arrival_time, wait_time, service_time,  departure_time)
 UNION
 (summary row)

    

Column	Type	Description
`seq`	`INTEGER`	Sequential value starting from 1 .
`vehicle_seq`	`INTEGER`	Sequential value starting from 1 for current vehicles. The \(n_{th}\) vehicle in the solution. Value \(-2\) indicates it is the summary row.
`vehicle_id`	BIGINT	Current vehicle identifier. Sumary row has the total capacity violations . A capacity violation happens when overloading or underloading a vehicle.
`stop_seq`	INTEGER	Sequential value starting from 1 for the stops made by the current vehicle. The \(m_{th}\) stop of the current vehicle. Sumary row has the total time windows violations . A time window violation happens when arriving after the location has closed.
`stop_type`	`INTEGER`	Kind of stop location the vehicle is at \(-1\) : at the solution summary row \(1\) : Starting location \(2\) : Pickup location \(3\) : Delivery location \(6\) : Ending location and indicates the vehicle’s summary row
`order_id`	`BIGINT`	Pickup-Delivery order pair identifier. Value \(-1\) : When no order is involved on the current stop location.
`cargo`	`FLOAT`	Cargo units of the vehicle when leaving the stop. Value \(-1\) on solution summary row.
`travel_time`	`FLOAT`	Travel time from previous `stop_seq` to current `stop_seq` . Summary has the total traveling time : The sum of all the `travel_time` .
`arrival_time`	`FLOAT`	Time spent waiting for current location to open. \(-1\) : at the solution summary row. \(0\) : at the starting location.
`wait_time`	`FLOAT`	Time spent waiting for current location to open. Summary row has the total waiting time : The sum of all the `wait_time` .
`service_time`	`FLOAT`	Service duration at current location. Summary row has the total service time : The sum of all the `service_time` .
`departure_time`	`FLOAT`	The time at which the vehicle departs from the stop. \(arrival\_time + wait\_time + service\_time\) . The ending location has the total time used by the current vehicle. Summary row has the total solution time : \(total\ traveling\ time + total\ waiting\ time + total\ service\ time\) .

pgr_pickDeliver - Experimental - pgRouting Manual (3.4)