vrp_pgr_pickDeliverEuclidean - Experimental¶

vrp_pgr_pickDeliverEuclidean - 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 vrpRouting
- Might depend on a deprecated function of vrpRouting

Availability

Version 0.0.0

New experimental function
- Ported pgr_pickDeliverEuclidean from pgRouting v3.1.3

Synopsis¶

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

Optimization problem is NP-hard.
Pickup and Delivery:
- capacitated
- with time windows.
The vehicles
- have (x, y) start and ending locations.
- have a start and ending service times.
- have opening and closing times for the start and ending locations.
An order is for doing a pickup and a a deliver.
- has (x, y) pickup and delivery locations.
- has opening and closing times for the pickup and delivery locations.
- has a pickup and deliver service times.
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¶

No multiple time windows for a location.
Less vehicle used is considered better.
Less total duration is better.
Less wait time is better.
Six different optional different initial solutions
- the best solution found will be result

Signature¶

pgr_pickDeliverEuclidean(
Orders SQL, Vehicles 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

Parameters¶

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

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

Inner Queries¶

Orders SQL¶

A SELECT statement that returns the following columns:

id, amount
p_x, p_y, p_tw_open, p_tw_close, [p_service,]
d_x, d_y, d_tw_open, d_tw_close, [d_service]

Column	Type	Default	Description
`id`	ANY-INTEGER		Identifier of the pick-delivery order pair.
`amount`	ANY-NUMERICAL		Number of units in the order.
`p_x`	ANY-NUMERICAL		\(x\) value of the pickup location.
`p_y`	ANY-NUMERICAL		\(y\) value of the pickup location.
`p_tw_open`	ANY-INTEGER		The time, relative to 0, when the pickup location opens.
`p_tw_close`	ANY-INTEGER		The time, relative to 0, when the pickup location closes. \(p\_tw\_open < p\_tw\_close < 9223372036854775807\)
`p_service`	ANY-INTEGER	0	The duration of the loading at the pickup location.
`d_x`	ANY-NUMERICAL		\(x\) value of the delivery location.
`d_y`	ANY-NUMERICAL		\(y\) value of the delivery location.
`d_tw_open`	ANY-INTEGER		The time, relative to 0, when the delivery location opens.
`d_tw_close`	ANY-INTEGER		The time, relative to 0, when the delivery location closes. \(d\_tw\_open < d\_tw\_close <= 9223372036854775807\)
`d_service`	ANY-INTEGER	0	The duration of the unloading at the delivery location.

Vehicles SQL¶

A SELECT statement that returns the following columns:

id, capacity, [speed,]
s_x, s_y, [s_tw_open, s_tw_close, s_service]
[e_x, e_y, e_tw_open, e_tw_close, e_service]

Column	Type	Default	Description
`id`	ANY-INTEGER		Identifier of the vehicle.
`capacity`	ANY-INTEGER		Capacity of the vehicle. \(0 < capacity <= 4294967295\).
`speed`	ANY-NUMERICAL	1	Speed of the vehicle.
`s_x`	ANY-NUMERICAL		\(x\) value of the coordinate of the starting location.
`s_y`	ANY-NUMERICAL		\(y\) value of the coordinate of the starting location.
`s_tw_open`	ANY-INTEGER	0	The time, relative to 0, when the starting location opens.
`s_tw_close`	ANY-INTEGER	MAX-BIGINT	The time, relative to 0, when the starting location closes. \(s\_tw\_open < s\_tw\_close <= 9223372036854775807\)
`s_service`	ANY-INTEGER	0	Duration of any task at the ending location,
`e_x`	ANY-NUMERICAL		\(x\) value of the coordinate of the ending location.
`e_y`	ANY-NUMERICAL		\(y\) value of the coordinate of the ending location.
`e_tw_open`	ANY-INTEGER	`s_tw_open`	The time, relative to 0, when the ending location opens.
`e_tw_close`	ANY-INTEGER	`s_tw_close`	The time, relative to 0, when the ending location closes. \(e\_tw\_open < e\_tw\_close <= 9223372036854775807\)
`e_service`	ANY-INTEGER	`s_service`	The duration of any task at the ending location

Result Columns¶

Returns set of

seq, vehicle_seq, vehicle_id, stop_seq, stop_type, order_id, cargo,

travel_time, arrival_time, wait_time, service_time, departure_time

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.
`vehicle_id`	`BIGINT`	Current vehicle identifier.
`stop_seq`	`INTEGER`	Sequential value starting from 1 for the stops made by the current vehicle. The \(m_{th}\) stop of the current vehicle.
`stop_type`	`INTEGER`	Kind of stop location the vehicle is at: `1`: Starting location `2`: Pickup location `3`: Delivery location `6`: Ending location
`order_id`	`BIGINT`	Pickup-Delivery order pair identifier. `-1`: When no order is involved on the current stop location.
`cargo`	`BIGINT`	Cargo units of the vehicle when leaving the stop.
`travel_time`	`BIGINT`	Travel time from previous `stop_seq` to current `stop_seq`. `0` When `stop_type = 1`
`arrival_time`	`BIGINT`	Previous `departure_time` plus current `travel_time`.
`wait_time`	`BIGINT`	Time spent waiting for current location to open.
`service_time`	`BIGINT`	Service time at current location.
`departure_time`	`BIGINT`	\(arrival\_time + wait\_time + service\_time\). When `stop_type = 6` has the total_time used for the current vehicle.

Example¶

This example use the following data:

SELECT * FROM vrp_pgr_pickDeliverEuclidean(
    'SELECT * FROM orders_1 ORDER BY id',
    'SELECT * from vehicles_1'
);
 seq | vehicle_seq | vehicle_id | stop_seq | stop_type | order_id | cargo | travel_time | arrival_time | wait_time | service_time | departure_time
-----+-------------+------------+----------+-----------+----------+-------+-------------+--------------+-----------+--------------+----------------
   1 |           1 |          1 |        1 |         1 |       -1 |     0 |           0 |            0 |         1 |            0 |              1
   2 |           1 |          1 |        2 |         2 |        3 |    30 |           1 |            2 |         0 |            3 |              5
   3 |           1 |          1 |        3 |         3 |        3 |     0 |           1 |            6 |         0 |            3 |              9
   4 |           1 |          1 |        4 |         2 |        2 |    20 |           1 |           10 |         0 |            2 |             12
   5 |           1 |          1 |        5 |         3 |        2 |     0 |           1 |           13 |         0 |            3 |             16
   6 |           1 |          1 |        6 |         6 |       -1 |     0 |           1 |           17 |         0 |            0 |             17
   7 |           2 |          2 |        1 |         1 |       -1 |     0 |           0 |            0 |         1 |            0 |              1
   8 |           2 |          2 |        2 |         2 |        1 |    10 |           1 |            2 |         0 |            3 |              5
   9 |           2 |          2 |        3 |         3 |        1 |     0 |           2 |            7 |         0 |            3 |             10
  10 |           2 |          2 |        4 |         6 |       -1 |     0 |           2 |           12 |         0 |            0 |             12
  11 |          -2 |          0 |        0 |        -1 |       -1 |    -1 |          10 |           -1 |         2 |           17 |             29
(11 rows)