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

• 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

• 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

• 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

• 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.

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$$.