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