-
Notifications
You must be signed in to change notification settings - Fork 1
/
3_CREATE_TABLES_hslstoptimes_parsed.sql
233 lines (214 loc) · 8.59 KB
/
3_CREATE_TABLES_hslstoptimes_parsed.sql
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
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
/*NOTE THE DATE FILTER IN WHERE-CLAUSES!
CAN BE REMOVED AFTER TESTING, NOW JUST FOR LIMITING THE DATA TO A REASONABLE LEVEL */
/* TRAMS */
DROP TABLE IF EXISTS public.hslstoptimes_tram CASCADE;
CREATE TABLE public.hslstoptimes_tram AS
SELECT
hslstops.stop_name,
hslstops.stop_desc,
hslstops.stop_lon AS stop_lon_float,
hslstops.stop_lat AS stop_lat_float,
hslstops.zone_id,
hslstops.vehicle_type,
hslroutes.route_short_name,
hslroutes.route_long_name,
hslroutes.route_desc,
hsltrips.trip_headsign,
hslstop_times.stop_headsign,
split_part(hsltrips.trip_id, '_', 2) AS trip_YYYYMMDD_txt,
left(split_part(hsltrips.trip_id, '_', 2),4) AS trip_YYYY_txt,
substring(split_part(hsltrips.trip_id, '_', 2),5,2) AS trip_MM_txt,
right(split_part(hsltrips.trip_id, '_', 2),2) AS trip_DD_txt,
split_part(hsltrips.trip_id, '_', 3) AS trip_weekday_txt,
hsltrips.trip_id,
to_timestamp(hslstop_times.arrival_time, 'HH24:MI:SS'::text)::time without time zone AS arrival_time,
to_timestamp(hslstop_times.departure_time, 'HH24:MI:SS'::text)::time without time zone AS departure_time,
hslstop_times.arrival_time AS arrival_time_txt,
hslstop_times.departure_time AS departure_time_txt,
hslstop_times.stop_sequence,
hslroutes.route_type,
hsltrips.direction_id,
hslstops.location_type,
ST_SetSRID(ST_Point(hslstops.stop_lon, hslstops.stop_lat), 4326) AS stop_point
FROM hslstop_times,
hslstops,
hsltrips,
hslcalendar,
hslroutes
WHERE hslstop_times.stop_id = hslstops.stop_id AND
hsltrips.service_id = hslcalendar.service_id AND
hsltrips.trip_id = hslstop_times.trip_id AND
hslroutes.route_id = hsltrips.route_id AND
hslcalendar.wednesday = 1 AND
split_part(hsltrips.trip_id, '_', 2) = '20180205' AND
hslstops.vehicle_type = 0;
/* METRO */
DROP TABLE IF EXISTS public.hslstoptimes_metro CASCADE;
CREATE TABLE public.hslstoptimes_metro AS
SELECT
hslstops.stop_name,
hslstops.stop_desc,
hslstops.stop_lon AS stop_lon_float,
hslstops.stop_lat AS stop_lat_float,
hslstops.zone_id,
hslstops.vehicle_type,
hslroutes.route_short_name,
hslroutes.route_long_name,
hslroutes.route_desc,
hsltrips.trip_headsign,
hslstop_times.stop_headsign,
split_part(hsltrips.trip_id, '_', 2) AS trip_YYYYMMDD_txt,
left(split_part(hsltrips.trip_id, '_', 2),4) AS trip_YYYY_txt,
substring(split_part(hsltrips.trip_id, '_', 2),5,2) AS trip_MM_txt,
right(split_part(hsltrips.trip_id, '_', 2),2) AS trip_DD_txt,
split_part(hsltrips.trip_id, '_', 3) AS trip_weekday_txt,
hsltrips.trip_id,
to_timestamp(hslstop_times.arrival_time, 'HH24:MI:SS'::text)::time without time zone AS arrival_time,
to_timestamp(hslstop_times.departure_time, 'HH24:MI:SS'::text)::time without time zone AS departure_time,
hslstop_times.arrival_time AS arrival_time_txt,
hslstop_times.departure_time AS departure_time_txt,
hslstop_times.stop_sequence,
hslroutes.route_type,
hsltrips.direction_id,
hslstops.location_type,
ST_SetSRID(ST_Point(hslstops.stop_lon, hslstops.stop_lat), 4326) AS stop_point
FROM hslstop_times,
hslstops,
hsltrips,
hslcalendar,
hslroutes
WHERE hslstop_times.stop_id = hslstops.stop_id AND
hsltrips.service_id = hslcalendar.service_id AND
hsltrips.trip_id = hslstop_times.trip_id AND
hslroutes.route_id = hsltrips.route_id AND
hslcalendar.wednesday = 1 AND
split_part(hsltrips.trip_id, '_', 2) = '20180205' AND
hslstops.vehicle_type = 1 AND
left(hslstops.stop_id,1) in ('1', '2');
/* TRAINS */
DROP TABLE IF EXISTS public.hslstoptimes_train CASCADE;
CREATE TABLE public.hslstoptimes_train AS
SELECT
hslstops.stop_name,
hslstops.stop_desc,
hslstops.stop_lon AS stop_lon_float,
hslstops.stop_lat AS stop_lat_float,
hslstops.zone_id,
hslstops.vehicle_type,
hslroutes.route_short_name,
hslroutes.route_long_name,
hslroutes.route_desc,
hsltrips.trip_headsign,
hslstop_times.stop_headsign,
split_part(hsltrips.trip_id, '_', 2) AS trip_YYYYMMDD_txt,
left(split_part(hsltrips.trip_id, '_', 2),4) AS trip_YYYY_txt,
substring(split_part(hsltrips.trip_id, '_', 2),5,2) AS trip_MM_txt,
right(split_part(hsltrips.trip_id, '_', 2),2) AS trip_DD_txt,
split_part(hsltrips.trip_id, '_', 3) AS trip_weekday_txt,
hsltrips.trip_id,
to_timestamp(hslstop_times.arrival_time, 'HH24:MI:SS'::text)::time without time zone AS arrival_time,
to_timestamp(hslstop_times.departure_time, 'HH24:MI:SS'::text)::time without time zone AS departure_time,
hslstop_times.arrival_time AS arrival_time_txt,
hslstop_times.departure_time AS departure_time_txt,
hslstop_times.stop_sequence,
hslroutes.route_type,
hsltrips.direction_id,
hslstops.location_type,
ST_SetSRID(ST_Point(hslstops.stop_lon, hslstops.stop_lat), 4326) AS stop_point
FROM hslstop_times,
hslstops,
hsltrips,
hslcalendar,
hslroutes
WHERE hslstop_times.stop_id = hslstops.stop_id AND
hsltrips.service_id = hslcalendar.service_id AND
hsltrips.trip_id = hslstop_times.trip_id AND
hslroutes.route_id = hsltrips.route_id AND
hslcalendar.wednesday = 1 AND
split_part(hsltrips.trip_id, '_', 2) = '20180205' AND
hslstops.vehicle_type = 109;
/* FERRIES */
DROP TABLE IF EXISTS public.hslstoptimes_ferry CASCADE;
CREATE TABLE public.hslstoptimes_ferry AS
SELECT
hslstops.stop_name,
hslstops.stop_desc,
hslstops.stop_lon AS stop_lon_float,
hslstops.stop_lat AS stop_lat_float,
hslstops.zone_id,
hslstops.vehicle_type,
hslroutes.route_short_name,
hslroutes.route_long_name,
hslroutes.route_desc,
hsltrips.trip_headsign,
hslstop_times.stop_headsign,
split_part(hsltrips.trip_id, '_', 2) AS trip_YYYYMMDD_txt,
left(split_part(hsltrips.trip_id, '_', 2),4) AS trip_YYYY_txt,
substring(split_part(hsltrips.trip_id, '_', 2),5,2) AS trip_MM_txt,
right(split_part(hsltrips.trip_id, '_', 2),2) AS trip_DD_txt,
split_part(hsltrips.trip_id, '_', 3) AS trip_weekday_txt,
hsltrips.trip_id,
to_timestamp(hslstop_times.arrival_time, 'HH24:MI:SS'::text)::time without time zone AS arrival_time,
to_timestamp(hslstop_times.departure_time, 'HH24:MI:SS'::text)::time without time zone AS departure_time,
hslstop_times.arrival_time AS arrival_time_txt,
hslstop_times.departure_time AS departure_time_txt,
hslstop_times.stop_sequence,
hslroutes.route_type,
hsltrips.direction_id,
hslstops.location_type,
ST_SetSRID(ST_Point(hslstops.stop_lon, hslstops.stop_lat), 4326) AS stop_point
FROM hslstop_times,
hslstops,
hsltrips,
hslcalendar,
hslroutes
WHERE hslstop_times.stop_id = hslstops.stop_id AND
hsltrips.service_id = hslcalendar.service_id AND
hsltrips.trip_id = hslstop_times.trip_id AND
hslroutes.route_id = hsltrips.route_id AND
hslcalendar.wednesday = 1 AND
split_part(hsltrips.trip_id, '_', 2) = '20180205' AND
hslstops.vehicle_type = 4 AND
left(hslstops.stop_id,1)='1';
/* BUSES */
DROP TABLE IF EXISTS public.hslstoptimes_bus CASCADE;
CREATE TABLE public.hslstoptimes_bus AS
SELECT
hslstops.stop_name,
hslstops.stop_desc,
hslstops.stop_lon AS stop_lon_float,
hslstops.stop_lat AS stop_lat_float,
hslstops.zone_id,
hslstops.vehicle_type,
hslroutes.route_short_name,
hslroutes.route_long_name,
hslroutes.route_desc,
hsltrips.trip_headsign,
hslstop_times.stop_headsign,
split_part(hsltrips.trip_id, '_', 2) AS trip_YYYYMMDD_txt,
left(split_part(hsltrips.trip_id, '_', 2),4) AS trip_YYYY_txt,
substring(split_part(hsltrips.trip_id, '_', 2),5,2) AS trip_MM_txt,
right(split_part(hsltrips.trip_id, '_', 2),2) AS trip_DD_txt,
split_part(hsltrips.trip_id, '_', 3) AS trip_weekday_txt,
hsltrips.trip_id,
to_timestamp(hslstop_times.arrival_time, 'HH24:MI:SS'::text)::time without time zone AS arrival_time,
to_timestamp(hslstop_times.departure_time, 'HH24:MI:SS'::text)::time without time zone AS departure_time,
hslstop_times.arrival_time AS arrival_time_txt,
hslstop_times.departure_time AS departure_time_txt,
hslstop_times.stop_sequence,
hslroutes.route_type,
hsltrips.direction_id,
hslstops.location_type,
ST_SetSRID(ST_Point(hslstops.stop_lon, hslstops.stop_lat), 4326) AS stop_point
FROM hslstop_times,
hslstops,
hsltrips,
hslcalendar,
hslroutes
WHERE hslstop_times.stop_id = hslstops.stop_id AND
hsltrips.service_id = hslcalendar.service_id AND
hsltrips.trip_id = hslstop_times.trip_id AND
hslroutes.route_id = hsltrips.route_id AND
hslcalendar.wednesday = 1 AND
split_part(hsltrips.trip_id, '_', 2) = '20180205' AND
hslstops.vehicle_type = 3;