pgr_pickDeliver - Pickup and delivery Vehicle Routing Problem
Warning
Possible server crash
Warning
Experimental functions
Availability
Support
Problem: Distribute and optimize the pickup-delivery pairs into a fleet of vehicles.
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)
The parameters are:
orders_sql, vehicles_sql, matrix_sql [, factor, max_cycles, initial_sol]
| Column | Type | Default | Description |
|---|---|---|---|
| orders_sql | TEXT |
Pick & Deliver Orders SQL query contianing the orders to be processed. | |
| vehicles_sql | TEXT |
Pick & Deliver Vehicles SQL query containing the vehicles to be used. | |
| matrix_sql | TEXT |
Pick & Deliver Matrix SQL query containing the distance or travel times. | |
| 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.
|
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 | Default | 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. | |
| d_service | ANY-NUMERICAL | 0 | The duration of the loading at the pickup location. |
| 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 | 0 | The duration of the loading at the delivery location. |
For the non euclidean implementation, the starting and ending identifiers are needed:
| Column | Type | Description |
|---|---|---|
| p_node_id | ANY-INTEGER | The node identifier of the pickup, must match a node identifier in the matrix table. |
| d_node_id | ANY-INTEGER | The node identifier of the delivery, must match a node identifier in the matrix table. |
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 | Default | Description |
|---|---|---|---|
| id | ANY-INTEGER | Identifier of the pick-delivery order pair. | |
| capacity | ANY-NUMERICAL | Number of units in the order | |
| speed | ANY-NUMERICAL | 1 | Average speed of the vehicle. |
| 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 | 0 | The duration of the loading at the starting location. |
| end_open | ANY-NUMERICAL | start_open | The time, relative to 0, when the ending location opens. |
| end_close | ANY-NUMERICAL | start_close | The time, relative to 0, when the ending location closes. |
| end_service | ANY-NUMERICAL | start_service | The duration of the loading at the ending location. |
For the non euclidean implementation, the starting and ending identifiers are needed:
| Column | Type | Default | Description |
|---|---|---|---|
| start_node_id | ANY-INTEGER | The node identifier of the starting location, must match a node identifier in the matrix table. | |
| end_node_id | ANY-INTEGER | start_node_id | The node identifier of the ending location, must match a node identifier in the matrix table. |
A SELECT statement that returns the following columns:
Warning
TODO
Where:
| ANY-INTEGER: | SMALLINT, INTEGER, BIGINT |
|---|---|
| ANY-NUMERICAL: | SMALLINT, INTEGER, BIGINT, REAL, FLOAT |
This example use the following data: TODO put link
SELECT * FROM pgr_pickDeliver(
$$ SELECT * FROM orders ORDER BY id $$,
$$ SELECT * FROM vehicles ORDER BY id$$,
$$ SELECT * from pgr_dijkstraCostMatrix(
'SELECT * FROM edge_table ',
(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 | 6 | -1 | 0 | 0 | 0 | 0 | 0 | 0
2 | 1 | 1 | 2 | 2 | 5 | 3 | 30 | 1 | 1 | 1 | 3 | 5
3 | 1 | 1 | 3 | 3 | 11 | 3 | 0 | 2 | 7 | 0 | 3 | 10
4 | 1 | 1 | 4 | 2 | 9 | 2 | 20 | 2 | 12 | 0 | 2 | 14
5 | 1 | 1 | 5 | 3 | 4 | 2 | 0 | 1 | 15 | 0 | 3 | 18
6 | 1 | 1 | 6 | 6 | 6 | -1 | 0 | 2 | 20 | 0 | 0 | 20
7 | 2 | 1 | 1 | 1 | 6 | -1 | 0 | 0 | 0 | 0 | 0 | 0
8 | 2 | 1 | 2 | 2 | 3 | 1 | 10 | 3 | 3 | 0 | 3 | 6
9 | 2 | 1 | 3 | 3 | 8 | 1 | 0 | 3 | 9 | 0 | 3 | 12
10 | 2 | 1 | 4 | 6 | 6 | -1 | 0 | 2 | 14 | 0 | 0 | 14
11 | -2 | 0 | 0 | -1 | -1 | -1 | -1 | 16 | -1 | 1 | 17 | 34
(11 rows)
Indices and tables