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

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
   [, 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

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.

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\).