Sample Data¶
The documentation provides very simple example queries based on a small sample network. To be able to execute the sample queries, run the following SQL commands to create a table with a small network data set.
City Network¶
Create table
CREATE TABLE public.edge_table (
id BIGSERIAL,
source BIGINT,
target BIGINT,
cost FLOAT,
reverse_cost FLOAT,
x1 FLOAT,
y1 FLOAT,
x2 FLOAT,
y2 FLOAT,
geom geometry
);
Populate
INSERT INTO public.edge_table (
cost, reverse_cost,
x1, y1,
x2, y2) VALUES
( 1, 1, 2, 0, 2, 1),
(-1, 1, 2, 1, 3, 1),
(-1, 1, 3, 1, 4, 1),
( 1, 1, 2, 1, 2, 2),
( 1, -1, 3, 1, 3, 2),
( 1, 1, 0, 2, 1, 2),
( 1, 1, 1, 2, 2, 2),
( 1, 1, 2, 2, 3, 2),
( 1, 1, 3, 2, 4, 2),
( 1, 1, 2, 2, 2, 3),
( 1, -1, 3, 2, 3, 3),
( 1, -1, 2, 3, 3, 3),
( 1, -1, 3, 3, 4, 3),
( 1, 1, 2, 3, 2, 4),
( 1, 1, 4, 2, 4, 3),
( 1, 1, 4, 1, 4, 2),
( 1, 1, 0.5, 3.5, 1.999999999999, 3.5),
( 1, 1, 3.5, 2.3, 3.5, 4);
Update geometry
UPDATE public.edge_table SET geom = st_makeline(st_point(x1,y1),st_point(x2,y2));
Add Topology
SELECT pgr_createTopology('public.edge_table',0.001, the_geom =>'geom');
pgr_PickDeliver data¶
Vehicles table¶
CREATE TABLE public.vehicles_1 (
id BIGSERIAL PRIMARY KEY,
s_id BIGINT,
s_x FLOAT,
s_y FLOAT,
s_open BIGINT,
s_close BIGINT,
capacity BIGINT
);
INSERT INTO public.vehicles_1
(s_id, s_x, s_y, s_open, s_close, capacity) VALUES
( 6, 3, 2, 0, 50, 50),
( 6, 3, 2, 0, 50, 50);
Orders table¶
CREATE TABLE public.orders_1 (
id BIGSERIAL PRIMARY KEY,
amount BIGINT,
-- the pickups
p_id BIGINT,
p_x FLOAT,
p_y FLOAT,
p_open BIGINT,
p_close BIGINT,
p_service BIGINT,
-- the deliveries
d_id BIGINT,
d_x FLOAT,
d_y FLOAT,
d_open BIGINT,
d_close BIGINT,
d_service BIGINT
);
INSERT INTO public.orders_1
(amount,
p_id, p_x, p_y, p_open, p_close, p_service,
d_id, d_x, d_y, d_open, d_close, d_service) VALUES
(10,
3, 3, 1, 2, 10, 3,
8, 1, 2, 6, 15, 3),
(20,
9, 4, 2, 4, 15, 2,
4, 4, 1, 6, 20, 3),
(30,
5, 2, 2, 2, 10, 3,
11, 3, 3, 3, 20, 3);
vrp_oneDepot data¶
DROP TABLE IF EXISTS public.solomon_100_RC_101 cascade;
CREATE TABLE public.solomon_100_RC_101 (
id integer NOT NULL PRIMARY KEY,
order_unit integer,
open_time integer,
close_time integer,
service_time integer,
x float8,
y float8
);
COPY public.solomon_100_RC_101
(id, x, y, order_unit, open_time, close_time, service_time) FROM stdin;
1 40.000000 50.000000 0 0 240 0
2 25.000000 85.000000 20 145 175 10
3 22.000000 75.000000 30 50 80 10
4 22.000000 85.000000 10 109 139 10
5 20.000000 80.000000 40 141 171 10
6 20.000000 85.000000 20 41 71 10
7 18.000000 75.000000 20 95 125 10
8 15.000000 75.000000 20 79 109 10
9 15.000000 80.000000 10 91 121 10
10 10.000000 35.000000 20 91 121 10
11 10.000000 40.000000 30 119 149 10
\.
DROP TABLE IF EXISTS public.vrp_vehicles cascade;
CREATE TABLE public.vrp_vehicles (
vehicle_id integer not null primary key,
capacity integer,
case_no integer
);
copy public.vrp_vehicles (vehicle_id, capacity, case_no) from stdin;
1 200 5
2 200 5
3 200 5
\.
DROP TABLE IF EXISTS public.vrp_distance cascade;
WITH
the_matrix_info AS (
SELECT A.id AS src_id, B.id AS dest_id, sqrt( (a.x - b.x) * (a.x - b.x) + (a.y - b.y) * (a.y - b.y)) AS cost
FROM solomon_100_rc_101 AS A, solomon_100_rc_101 AS B WHERE A.id != B.id
)
SELECT src_id, dest_id, cost, cost AS distance, cost AS traveltime
INTO public.vrp_distance
FROM the_matrix_info;
VROOM Data¶
Jobs¶
CREATE TABLE vroom.jobs (
id BIGSERIAL PRIMARY KEY,
location_id BIGINT,
service INTEGER,
delivery BIGINT[],
pickup BIGINT[],
skills INTEGER[],
priority INTEGER
);
INSERT INTO vroom.jobs (
id, location_id, service, delivery, pickup, skills, priority)
VALUES
(1, 1, 250, ARRAY[20], ARRAY[20], ARRAY[0], 0),
(2, 2, 250, ARRAY[30], ARRAY[30], ARRAY[0], 0),
(3, 3, 250, ARRAY[10], ARRAY[10], ARRAY[0], 0),
(4, 3, 250, ARRAY[40], ARRAY[40], ARRAY[0], 0),
(5, 4, 250, ARRAY[20], ARRAY[20], ARRAY[0], 0);
Jobs Time Windows¶
CREATE TABLE vroom.jobs_time_windows (
id BIGINT REFERENCES vroom.jobs(id),
tw_open INTEGER,
tw_close INTEGER
);
INSERT INTO vroom.jobs_time_windows (
id, tw_open, tw_close)
VALUES
(1, 3625, 4375),
(2, 1250, 2000),
(3, 2725, 3475),
(4, 3525, 4275),
(5, 1025, 1775);
Shipments¶
CREATE TABLE vroom.shipments (
id BIGSERIAL PRIMARY KEY,
p_location_id BIGINT,
p_service INTEGER,
d_location_id BIGINT,
d_service INTEGER,
amount BIGINT[],
skills INTEGER[],
priority INTEGER
);
INSERT INTO vroom.shipments (
id, p_location_id, p_service, d_location_id, d_service,
amount, skills, priority)
VALUES
(1, 3, 2250, 5, 2250, ARRAY[10], ARRAY[0], 0),
(2, 5, 2250, 6, 2250, ARRAY[10], ARRAY[0], 0),
(3, 1, 2250, 2, 2250, ARRAY[20], ARRAY[0], 0),
(4, 1, 2250, 4, 2250, ARRAY[20], ARRAY[0], 0),
(5, 2, 2250, 2, 2250, ARRAY[10], ARRAY[0], 0);
Shipments Time Windows¶
CREATE TABLE vroom.shipments_time_windows (
id BIGINT REFERENCES vroom.shipments(id),
kind CHAR(1),
tw_open INTEGER,
tw_close INTEGER
);
INSERT INTO vroom.shipments_time_windows (
id, kind, tw_open, tw_close)
VALUES
(1, 'p', 1625, 3650),
(1, 'd', 24925, 26700),
(2, 'p', 375, 1675),
(2, 'd', 4250, 5625),
(3, 'p', 15525, 17550),
(3, 'd', 20625, 21750),
(4, 'p', 6375, 8100),
(4, 'd', 8925, 10250),
(5, 'p', 13350, 15125),
(5, 'd', 18175, 19550);
Vehicles¶
CREATE TABLE vroom.vehicles (
id BIGSERIAL PRIMARY KEY,
start_id BIGINT,
end_id BIGINT,
capacity BIGINT[],
skills INTEGER[],
tw_open INTEGER,
tw_close INTEGER,
speed_factor FLOAT,
max_tasks INTEGER
);
INSERT INTO vroom.vehicles (
id, start_id, end_id, capacity, skills,
tw_open, tw_close, speed_factor, max_tasks)
VALUES
(1, 1, 1, ARRAY[200], ARRAY[0], 0, 30900, 1.0, 20),
(2, 1, 3, ARRAY[200], ARRAY[0], 100, 30900, 1.0, 20),
(3, 1, 1, ARRAY[200], ARRAY[0], 0, 30900, 1.0, 20),
(4, 3, 3, ARRAY[200], ARRAY[0], 0, 30900, 1.0, 20);
Breaks¶
CREATE TABLE vroom.breaks (
id BIGINT PRIMARY KEY,
vehicle_id BIGINT REFERENCES vroom.vehicles(id),
service INTEGER
);
INSERT INTO vroom.breaks (
id, vehicle_id, service)
VALUES
(1, 1, 0),
(2, 2, 10),
(3, 3, 0),
(4, 4, 0);
Breaks Time Windows¶
CREATE TABLE vroom.breaks_time_windows (
id BIGINT REFERENCES vroom.breaks(id),
tw_open INTEGER,
tw_close INTEGER
);
INSERT INTO vroom.breaks_time_windows (
id, tw_open, tw_close)
VALUES
(1, 250, 300),
(2, 250, 275),
(3, 0, 0),
(4, 250, 250);
Matrix¶
CREATE TABLE vroom.matrix (
start_id BIGINT,
end_id BIGINT,
duration INTEGER,
cost INTEGER
);
INSERT INTO vroom.matrix (
start_id, end_id, duration)
VALUES
(1, 1, 0), (1, 2, 50), (1, 3, 90), (1, 4, 75), (1, 5, 106), (1, 6, 127),
(2, 1, 50), (2, 2, 0), (2, 3, 125), (2, 4, 90), (2, 5, 145), (2, 6, 127),
(3, 1, 90), (3, 2, 125), (3, 3, 0), (3, 4, 50), (3, 5, 25), (3, 6, 90),
(4, 1, 75), (4, 2, 90), (4, 3, 50), (4, 4, 0), (4, 5, 75), (4, 6, 55),
(5, 1, 106), (5, 2, 145), (5, 3, 25), (5, 4, 75), (5, 5, 0), (5, 6, 111),
(6, 1, 127), (6, 2, 127), (6, 3, 90), (6, 4, 55), (6, 5, 111), (6, 6, 0);
UPDATE vroom.matrix SET cost = duration;
Modified VROOM Data¶
The tables created using the above VROOM Data are modified for the VROOM functions with timestamps/interval, as:
ALTER TABLE vroom.jobs ALTER COLUMN service TYPE INTERVAL USING make_interval(secs => service);
ALTER TABLE vroom.shipments ALTER COLUMN p_service TYPE INTERVAL USING make_interval(secs => p_service);
ALTER TABLE vroom.shipments ALTER COLUMN d_service TYPE INTERVAL USING make_interval(secs => d_service);
ALTER TABLE vroom.vehicles ALTER COLUMN tw_open TYPE TIMESTAMP USING (to_timestamp(tw_open + 1630573200) at time zone 'UTC')::TIMESTAMP;
ALTER TABLE vroom.vehicles ALTER COLUMN tw_close TYPE TIMESTAMP USING (to_timestamp(tw_close + 1630573200) at time zone 'UTC')::TIMESTAMP;
ALTER TABLE vroom.breaks ALTER COLUMN service TYPE INTERVAL USING make_interval(secs => service);
ALTER TABLE vroom.jobs_time_windows ALTER COLUMN tw_open TYPE TIMESTAMP USING (to_timestamp(tw_open + 1630573200) at time zone 'UTC')::TIMESTAMP;
ALTER TABLE vroom.jobs_time_windows ALTER COLUMN tw_close TYPE TIMESTAMP USING (to_timestamp(tw_close + 1630573200) at time zone 'UTC')::TIMESTAMP;
ALTER TABLE vroom.shipments_time_windows ALTER COLUMN tw_open TYPE TIMESTAMP USING (to_timestamp(tw_open + 1630573200) at time zone 'UTC')::TIMESTAMP;
ALTER TABLE vroom.shipments_time_windows ALTER COLUMN tw_close TYPE TIMESTAMP USING (to_timestamp(tw_close + 1630573200) at time zone 'UTC')::TIMESTAMP;
ALTER TABLE vroom.breaks_time_windows ALTER COLUMN tw_open TYPE TIMESTAMP USING (to_timestamp(tw_open + 1630573200) at time zone 'UTC')::TIMESTAMP;
ALTER TABLE vroom.breaks_time_windows ALTER COLUMN tw_close TYPE TIMESTAMP USING (to_timestamp(tw_close + 1630573200) at time zone 'UTC')::TIMESTAMP;
ALTER TABLE vroom.matrix ALTER COLUMN duration TYPE INTERVAL USING make_interval(secs => duration);
Images¶
Red arrows correspond when
cost> 0 in the edge table.Blue arrows correspond when
reverse_cost> 0 in the edge table.Points are outside the graph.
Click on the graph to enlarge.
Currently VRP functions work on an undirected graph
