1\set QUIET 1
2
3SET client_min_messages = WARNING;
4
5
6------------------------------------------------------------------------------------------------------
7------------------------------------------------------------------------------------------------------
8--              SAMPLE DATA
9------------------------------------------------------------------------------------------------------
10------------------------------------------------------------------------------------------------------
11
12DROP TABLE IF EXISTS edge_table;
13DROP TABLE IF EXISTS edge_table_vertices_pgr;
14DROP table if exists pointsOfInterest;
15DROP TABLE IF EXISTS restrictions;
16DROP TABLE IF EXISTS new_restrictions;
17DROP TABLE IF EXISTS retrict;
18DROP TABLE IF EXISTS vertex_table;
19DROP TABLE IF EXISTS categories;
20DROP TABLE IF EXISTS vehicles;
21DROP TABLE IF EXISTS orders;
22
23--EDGE TABLE CREATE start
24CREATE TABLE edge_table (
25    id BIGSERIAL,
26    dir character varying,
27    source BIGINT,
28    target BIGINT,
29    cost FLOAT,
30    reverse_cost FLOAT,
31    capacity BIGINT,
32    reverse_capacity BIGINT,
33    category_id INTEGER,
34    reverse_category_id INTEGER,
35    x1 FLOAT,
36    y1 FLOAT,
37    x2 FLOAT,
38    y2 FLOAT,
39    the_geom geometry
40);
41--EDGE TABLE CREATE end
42--EDGE TABLE ADD DATA start
43INSERT INTO edge_table (
44    category_id, reverse_category_id,
45    cost, reverse_cost,
46    capacity, reverse_capacity,
47    x1, y1,
48    x2, y2) VALUES
49(3, 1,    1,  1,  80, 130,   2,   0,    2, 1),
50(3, 2,   -1,  1,  -1, 100,   2,   1,    3, 1),
51(2, 1,   -1,  1,  -1, 130,   3,   1,    4, 1),
52(2, 4,    1,  1, 100,  50,   2,   1,    2, 2),
53(1, 4,    1, -1, 130,  -1,   3,   1,    3, 2),
54(4, 2,    1,  1,  50, 100,   0,   2,    1, 2),
55(4, 1,    1,  1,  50, 130,   1,   2,    2, 2),
56(2, 1,    1,  1, 100, 130,   2,   2,    3, 2),
57(1, 3,    1,  1, 130,  80,   3,   2,    4, 2),
58(1, 4,    1,  1, 130,  50,   2,   2,    2, 3),
59(1, 2,    1, -1, 130,  -1,   3,   2,    3, 3),
60(2, 3,    1, -1, 100,  -1,   2,   3,    3, 3),
61(2, 4,    1, -1, 100,  -1,   3,   3,    4, 3),
62(3, 1,    1,  1,  80, 130,   2,   3,    2, 4),
63(3, 4,    1,  1,  80,  50,   4,   2,    4, 3),
64(3, 3,    1,  1,  80,  80,   4,   1,    4, 2),
65(1, 2,    1,  1, 130, 100,   0.5, 3.5,  1.999999999999,3.5),
66(4, 1,    1,  1,  50, 130,   3.5, 2.3,  3.5,4);
67--EDGE TABLE ADD DATA end
68
69--EDGE TABLE update geometry start
70
71UPDATE edge_table SET the_geom = st_makeline(st_point(x1,y1),st_point(x2,y2)),
72dir = CASE WHEN (cost>0 AND reverse_cost>0) THEN 'B'   -- both ways
73           WHEN (cost>0 AND reverse_cost<0) THEN 'FT'  -- direction of the LINESSTRING
74           WHEN (cost<0 AND reverse_cost>0) THEN 'TF'  -- reverse direction of the LINESTRING
75           ELSE '' END;                                -- unknown
76
77--EDGE TABLE update geometry end
78
79--EDGE TABLE TOPOLOGY start
80SELECT pgr_createTopology('edge_table',0.001);
81--EDGE TABLE TOPOLOGY end
82
83--POINTS CREATE start
84CREATE TABLE pointsOfInterest(
85    pid BIGSERIAL,
86    x FLOAT,
87    y FLOAT,
88    edge_id BIGINT,
89    side CHAR,
90    fraction FLOAT,
91    the_geom geometry,
92    newPoint geometry
93);
94
95INSERT INTO pointsOfInterest (x, y, edge_id, side, fraction) VALUES
96(1.8, 0.4,   1, 'l', 0.4),
97(4.2, 2.4,  15, 'r', 0.4),
98(2.6, 3.2,  12, 'l', 0.6),
99(0.3, 1.8,   6, 'r', 0.3),
100(2.9, 1.8,   5, 'l', 0.8),
101(2.2, 1.7,   4, 'b', 0.7);
102UPDATE pointsOfInterest SET the_geom = st_makePoint(x,y);
103
104UPDATE pointsOfInterest
105    SET newPoint = ST_LineInterpolatePoint(e.the_geom, fraction)
106    FROM edge_table AS e WHERE edge_id = id;
107--POINTS CREATE end
108
109--RESTRICTIONS CREATE start
110CREATE TABLE restrictions (
111    rid BIGINT NOT NULL,
112    to_cost FLOAT,
113    target_id BIGINT,
114    from_edge BIGINT,
115    via_path TEXT
116);
117
118INSERT INTO restrictions (rid, to_cost, target_id, from_edge, via_path) VALUES
119(1, 100,  7,  4, NULL),
120(1, 100, 11,  8, NULL),
121(1, 100, 10,  7, NULL),
122(2,   4,  8,  3, 5),
123(3, 100,  9, 16, NULL);
124
125CREATE TABLE new_restrictions (
126    id SERIAL PRIMARY KEY,
127    path BIGINT[],
128    cost float
129);
130
131INSERT INTO new_restrictions (path, cost) VALUES
132(ARRAY[4, 7], 100),
133(ARRAY[8, 11], 100),
134(ARRAY[4, 8], 100),
135(ARRAY[5, 9], 100),
136(ARRAY[10, 12], 100),
137(ARRAY[9, 15], 100),
138(ARRAY[3, 5, 8], 100);
139--RESTRICTIONS CREATE end
140
141
142--VEHICLES TABLE START
143
144CREATE TABLE vehicles (
145      id BIGSERIAL PRIMARY KEY,
146      start_node_id BIGINT,
147      start_x FLOAT,
148      start_y FLOAT,
149      start_open FLOAT,
150      start_close FLOAT,
151      number integer,
152      capacity FLOAT
153);
154
155INSERT INTO vehicles
156(start_node_id, start_x,  start_y,  start_open,  start_close,  number,  capacity) VALUES
157(            6,       3,        2,           0,           50,       2,        50);
158
159--VEHICLES TABLE END
160
161
162
163--ORDERS TABLE START
164CREATE TABLE orders (
165    id BIGSERIAL PRIMARY KEY,
166    demand FLOAT,
167    -- the pickups
168    p_node_id BIGINT,
169    p_x FLOAT,
170    p_y FLOAT,
171    p_open FLOAT,
172    p_close FLOAT,
173    p_service FLOAT,
174    -- the deliveries
175    d_node_id BIGINT,
176    d_x FLOAT,
177    d_y FLOAT,
178    d_open FLOAT,
179    d_close FLOAT,
180    d_service FLOAT
181);
182
183
184INSERT INTO orders
185(demand,
186    p_node_id,  p_x, p_y,  p_open,  p_close,  p_service,
187    d_node_id,  d_x, d_y,  d_open,  d_close,  d_service) VALUES
188(10,
189            3,    3,   1,      2,         10,          3,
190            8,    1,   2,      6,         15,          3),
191(20,
192            9,    4,   2,      4,         15,          2,
193            4,    4,   1,      6,         20,          3),
194(30,
195            5,    2,   2,      2,         10,          3,
196           11,    3,   3,      3,         20,          3);
197
198
199--ORDERS TABLE END
200
201--COMBINATIONS CREATE start
202CREATE TABLE combinations_table (
203    source BIGINT,
204    target BIGINT
205);
206
207INSERT INTO combinations_table (
208    source, target) VALUES
209(1, 2),
210(1, 4),
211(2, 1),
212(2, 4),
213(2, 17);
214
215--COMBINATIONS CREATE end
216