# pgRouting Manual (3.6)

## pgr_pickDeliver - Experimental

# `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

### 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

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

## See Also

- Vehicle Routing Functions - Category (Experimental)

- Sample Data

Indices and tables

- Index

- Search Page