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 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 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 edge_table SET geom = st_makeline(st_point(x1,y1),st_point(x2,y2));
Add Topology
SELECT pgr_createTopology('edge_table',0.001, the_geom =>'geom');
pgr_PickDeliver data¶
Vehicles table¶
CREATE TABLE vehicles (
id BIGSERIAL PRIMARY KEY,
start_node_id BIGINT,
start_x FLOAT,
start_y FLOAT,
start_open FLOAT,
start_close FLOAT,
capacity FLOAT
);
INSERT INTO vehicles
(start_node_id, start_x, start_y, start_open, start_close, capacity) VALUES
( 6, 3, 2, 0, 50, 50),
( 6, 3, 2, 0, 50, 50);
Orders table¶
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
demand FLOAT,
-- the pickups
p_node_id BIGINT,
p_x FLOAT,
p_y FLOAT,
p_open FLOAT,
p_close FLOAT,
p_service FLOAT,
-- the deliveries
d_node_id BIGINT,
d_x FLOAT,
d_y FLOAT,
d_open FLOAT,
d_close FLOAT,
d_service FLOAT
);
INSERT INTO orders
(demand,
p_node_id, p_x, p_y, p_open, p_close, p_service,
d_node_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 solomon_100_RC_101 cascade;
CREATE TABLE 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 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 vrp_vehicles cascade;
CREATE TABLE vrp_vehicles (
vehicle_id integer not null primary key,
capacity integer,
case_no integer
);
copy vrp_vehicles (vehicle_id, capacity, case_no) from stdin;
1 200 5
2 200 5
3 200 5
\.
DROP TABLE IF EXISTS 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 vrp_distance
FROM the_matrix_info;
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
