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; 140.00000050.000000002400 225.00000085.0000002014517510 322.00000075.00000030508010 422.00000085.0000001010913910 520.00000080.0000004014117110 620.00000085.00000020417110 718.00000075.000000209512510 815.00000075.000000207910910 915.00000080.000000109112110 1010.00000035.000000209112110 1110.00000040.0000003011914910 \. 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; 12005 22005 32005 \. 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_index BIGINT, service INTEGER, delivery BIGINT[], pickup BIGINT[], skills INTEGER[], priority INTEGER ); INSERT INTO vroom.jobs ( id, location_index, 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_index BIGINT, p_service INTEGER, d_location_index BIGINT, d_service INTEGER, amount BIGINT[], skills INTEGER[], priority INTEGER ); INSERT INTO vroom.shipments ( id, p_location_index, p_service, d_location_index, 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_index BIGINT, end_index BIGINT, capacity BIGINT[], skills INTEGER[], tw_open INTEGER, tw_close INTEGER, speed_factor FLOAT ); INSERT INTO vroom.vehicles ( id, start_index, end_index, capacity, skills, tw_open, tw_close, speed_factor) VALUES (1, 1, 1, ARRAY[200], ARRAY[0], 0, 30900, 1.0), (2, 1, 3, ARRAY[200], ARRAY[0], 100, 30900, 1.0), (3, 1, 1, ARRAY[200], ARRAY[0], 0, 30900, 1.0), (4, 3, 3, ARRAY[200], ARRAY[0], 0, 30900, 1.0);

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_vid BIGINT, end_vid BIGINT, agg_cost INTEGER ); INSERT INTO vroom.matrix ( start_vid, end_vid, agg_cost) 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);

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

_images/Fig6-undirected.png