This repository was archived by the owner on Oct 25, 2025. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 1
Expand file tree
/
Copy pathpostgreSQL_script.sql
More file actions
179 lines (172 loc) · 6.27 KB
/
postgreSQL_script.sql
File metadata and controls
179 lines (172 loc) · 6.27 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
CREATE TABLE node (
node_id TEXT PRIMARY KEY,
x DECIMAL,
y DECIMAL
);
CREATE TABLE links_metadata (
links_metadata_id TEXT PRIMARY KEY,
effective_cell_size DECIMAL,
effective_lane_width DECIMAL,
capperiod TEXT
);
CREATE TABLE link (
link_id TEXT PRIMARY KEY,
links_metadata_id TEXT,
from_node TEXT,
to_node TEXT,
freespeed DECIMAL,
capacity DECIMAL,
permlanes DECIMAL,
oneway BOOLEAN,
modes TEXT,
link_length DECIMAL,
FOREIGN KEY (links_metadata_id) REFERENCES links_metadata(links_metadata_id),
FOREIGN KEY (from_node) REFERENCES node(node_id),
FOREIGN KEY (to_node) REFERENCES node(node_id)
);
CREATE TABLE event_data (
event_id SERIAL PRIMARY KEY,
event_time DECIMAL NOT NULL,
person TEXT,
vehicle TEXT,
event_type TEXT NOT NULL,
link_id TEXT REFERENCES link
);
CREATE TABLE act_start (
event_id INT,
FOREIGN KEY (event_id) REFERENCES event_data(event_id),
act_type TEXT,
x DECIMAL,
y DECIMAL
);
CREATE TABLE act_end (
event_id INT,
FOREIGN KEY (event_id) REFERENCES event_data(event_id),
act_type TEXT
);
CREATE TABLE dvrp_task (
event_id INT,
FOREIGN KEY (event_id) REFERENCES event_data(event_id),
dvrp_vehicle TEXT,
task_type TEXT,
task_index TEXT,
dvrp_mode TEXT
);
CREATE TABLE enter_left_link (
event_id INT,
FOREIGN KEY (event_id) REFERENCES event_data(event_id)
);
CREATE TABLE arrival_departure (
event_id INT,
FOREIGN KEY (event_id) REFERENCES event_data(event_id),
legmode TEXT
);
CREATE TABLE vehicle_traffic (
event_id INT,
FOREIGN KEY (event_id) REFERENCES event_data(event_id),
relative_position DECIMAL,
network_mode TEXT
);
CREATE TABLE travelled (
event_id INT,
FOREIGN KEY (event_id) REFERENCES event_data(event_id),
mode TEXT,
distance DECIMAL
);
CREATE TABLE transit_driver_starts (
event_id INT,
FOREIGN KEY (event_id) REFERENCES event_data(event_id),
driver_id TEXT,
vehicle_id TEXT,
departure_id TEXT,
transit_line_id TEXT,
transit_route_id TEXT
);
CREATE TABLE passenger_pick_drop (
event_id INT,
FOREIGN KEY (event_id) REFERENCES event_data(event_id),
request TEXT,
mode TEXT
);
CREATE TABLE vehicle_facility (
event_id INT,
FOREIGN KEY (event_id) REFERENCES event_data(event_id),
time_delay DECIMAL,
facility TEXT
);
-- Not technically needed, but might be helpful if we need to add more data later on
CREATE TABLE person_vehicle (
event_id INT,
FOREIGN KEY (event_id) REFERENCES event_data(event_id)
);
CREATE TABLE waiting_for_pt (
event_id INT,
FOREIGN KEY (event_id) REFERENCES event_data(event_id),
destination_stop DECIMAL,
at_stop DECIMAL,
agent TEXT
);
CREATE TABLE person_money (
event_id INT,
FOREIGN KEY (event_id) REFERENCES event_data(event_id),
transaction_partner TEXT,
amount DECIMAL,
purpose TEXT
);
/*Indexes*/
CREATE INDEX ev_time ON event_data USING BTREE(event_time);
CREATE INDEX ev_link ON event_data USING BTREE(link_id);
CREATE INDEX ev_type ON event_data(event_type);
/*Views*/
CREATE VIEW megaview AS
SELECT a.*,act_end.act_type AS actend_type,act_start.act_type AS actstart_type,
act_start.x,act_start.y,arrival_departure.legmode,
dvrp_task.dvrp_vehicle,dvrp_task.task_type,dvrp_task.task_index,
dvrp_task.dvrp_mode,passenger_pick_drop.request,vehicle_facility.facility,
passenger_pick_drop.mode AS passenger_mode,person_money.transaction_partner,
person_money.amount,person_money.purpose,travelled.mode AS travel_mode,
travelled.distance,vehicle_traffic.relative_position,vehicle_traffic.network_mode,
waiting_for_pt.destination_stop,waiting_for_pt.at_stop,waiting_for_pt.agent,
vehicle_facility.time_delay,transit_driver_starts.driver_id,
transit_driver_starts.vehicle_id,transit_driver_starts.departure_id,
transit_driver_starts.transit_line_id,transit_driver_starts.transit_route_id
FROM event_data a LEFT JOIN act_end ON (a.event_id = act_end.event_id)
LEFT JOIN act_start ON (a.event_id = act_start.event_id)
LEFT JOIN arrival_departure ON (a.event_id = arrival_departure.event_id)
LEFT JOIN dvrp_task ON (a.event_id = dvrp_task.event_id)
LEFT JOIN passenger_pick_drop ON (a.event_id = passenger_pick_drop.event_id)
LEFT JOIN person_money ON (a.event_id = person_money.event_id)
LEFT JOIN person_vehicle ON (a.event_id = person_vehicle.event_id)
LEFT JOIN travelled ON (a.event_id = travelled.event_id)
LEFT JOIN vehicle_traffic ON (a.event_id = vehicle_traffic.event_id)
LEFT JOIN waiting_for_pt ON (a.event_id = waiting_for_pt.event_id)
LEFT JOIN vehicle_facility ON (a.event_id = vehicle_facility.event_id)
LEFT JOIN transit_driver_starts ON (a.event_id = transit_driver_starts.event_id);
CREATE VIEW megapersonview AS
SELECT a.*,act_end.act_type AS actend_type,act_start.act_type AS actstart_type,
act_start.x,act_start.y,arrival_departure.legmode,
dvrp_task.dvrp_vehicle,dvrp_task.task_type,dvrp_task.task_index,
dvrp_task.dvrp_mode,passenger_pick_drop.request,
passenger_pick_drop.mode AS passenger_mode,person_money.transaction_partner,
person_money.amount,person_money.purpose,travelled.mode AS travel_mode,
travelled.distance,vehicle_traffic.relative_position,vehicle_traffic.network_mode,
waiting_for_pt.destination_stop,waiting_for_pt.at_stop,waiting_for_pt.agent
FROM event_data a LEFT JOIN act_end ON (a.event_id = act_end.event_id)
LEFT JOIN act_start ON (a.event_id = act_start.event_id)
LEFT JOIN arrival_departure ON (a.event_id = arrival_departure.event_id)
LEFT JOIN dvrp_task ON (a.event_id = dvrp_task.event_id)
LEFT JOIN passenger_pick_drop ON (a.event_id = passenger_pick_drop.event_id)
LEFT JOIN person_money ON (a.event_id = person_money.event_id)
LEFT JOIN travelled ON (a.event_id = travelled.event_id)
LEFT JOIN vehicle_traffic ON (a.event_id = vehicle_traffic.event_id)
LEFT JOIN waiting_for_pt ON (a.event_id = waiting_for_pt.event_id);
CREATE VIEW megalinkview AS
SELECT a.*,act_end.act_type AS actend_type,act_start.act_type AS actstart_type,
act_start.x,act_start.y,arrival_departure.legmode,dvrp_task.dvrp_vehicle,
dvrp_task.task_type,dvrp_task.task_index,dvrp_task.dvrp_mode,
vehicle_traffic.relative_position,vehicle_traffic.network_mode
FROM event_data a LEFT JOIN act_end ON (a.event_id = act_end.event_id)
LEFT JOIN act_start ON (a.event_id = act_start.event_id)
LEFT JOIN arrival_departure ON (a.event_id = arrival_departure.event_id)
LEFT JOIN dvrp_task ON (a.event_id = dvrp_task.event_id)
LEFT JOIN vehicle_traffic ON (a.event_id = vehicle_traffic.event_id);