-
Notifications
You must be signed in to change notification settings - Fork 30
/
ttc_gtfs_create.sql
145 lines (132 loc) · 3.22 KB
/
ttc_gtfs_create.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
DROP SCHEMA gtfs_raw CASCADE;
CREATE SCHEMA gtfs_raw;
CREATE TABLE gtfs_raw.calendar_dates (
service_id smallint not null,
date_ bigint not null ,
exception_type smallint not null
);
CREATE TABLE gtfs_raw.calendar(
service_id smallint not null,
monday smallint not null,
tuesday smallint not null,
wednesday smallint not null,
thursday smallint not null,
friday smallint not null,
saturday smallint not null,
sunday smallint not null,
start_date bigint not null,
end_date bigint not null
);
CREATE TABLE gtfs_raw.routes(
route_id int PRIMARY KEY,
agency_id smallint NOT NULL,
route_short_name TEXT NOT NULL,
route_long_name TEXT NOT NULL,
route_desc TEXT,
route_type smallint NOT NULL,
route_url TEXT,
route_color CHAR(6) NOT NULL,
route_text_color CHAR(6)
);
CREATE TABLE gtfs_raw.stop_times(
trip_id bigint NOT NULL,
arrival_time interval NOT NULL,
departure_time interval NOT NULL,
stop_id int NOT NULL,
stop_sequence smallint NOT NULL,
stop_headsign TEXT ,
pickup_type smallint NOT NULL,
drop_off_type smallint NOT NULL,
shape_dist_traveled numeric(7,4) DEFAULT 0
);
CREATE TABLE gtfs_raw.stops(
stop_id INT PRIMARY KEY,
stop_code TEXT NOT NULL,
stop_name TEXT NOT NULL,
stop_desc TEXT ,
stop_lat TEXT NOT NULL,
stop_lon TEXT NOT NULL,
zone_id SMALLINT,
stop_url TEXT,
location_type TEXT ,
parent_station INT ,
wheelchair_boarding SMALLINT
);
CREATE TABLE gtfs_raw.trips(
route_id INT NOT NULL,
service_id SMALLINT NOT NULL,
trip_id BIGINT NOT NULL,
trip_headsign TEXT NOT NULL,
trip_short_name TEXT,
direction_id SMALLINT NOT NULL,
block_id BIGINT NOT NULL,
shape_id INT NOT NULL,
wheelchair_accessible SMALLINT NOT NULL
);
DROP SCHEMA IF EXISTS gtfs CASCADE;
CREATE SCHEMA gtfs;
GRANT USAGE ON SCHEMA gtfs to public;
CREATE TABLE gtfs.calendar_dates (
service_id smallint not null,
date_ DATE not null ,
exception_type smallint not null
);
CREATE TABLE gtfs.calendar(
service_id smallint not null,
monday boolean not null,
tuesday boolean not null,
wednesday boolean not null,
thursday boolean not null,
friday boolean not null,
saturday boolean not null,
sunday boolean not null,
start_date DATE not null,
end_date dATE not null
);
CREATE TABLE gtfs.routes(
route_id int PRIMARY KEY,
agency_id smallint NOT NULL,
route_short_name TEXT NOT NULL,
route_long_name TEXT NOT NULL,
route_desc TEXT,
route_type smallint NOT NULL,
route_url TEXT,
route_color CHAR(6) NOT NULL,
route_text_color CHAR(6)
);
CREATE TABLE gtfs.stop_times(
trip_id bigint NOT NULL,
arrival_time interval NOT NULL,
departure_time interval NOT NULL,
stop_id int NOT NULL,
stop_sequence smallint NOT NULL,
stop_headsign TEXT ,
pickup_type smallint NOT NULL,
drop_off_type smallint NOT NULL,
shape_dist_traveled numeric(7,4) DEFAULT 0
);
CREATE TABLE gtfs.stops(
stop_id INT PRIMARY KEY,
stop_code TEXT NOT NULL,
stop_name TEXT NOT NULL,
stop_desc TEXT ,
stop_lat TEXT NOT NULL,
stop_lon TEXT NOT NULL,
zone_id SMALLINT,
stop_url TEXT,
location_type TEXT ,
parent_station INT ,
wheelchair_boarding SMALLINT
);
CREATE TABLE gtfs.trips(
route_id INT NOT NULL,
service_id SMALLINT NOT NULL,
trip_id BIGINT NOT NULL,
trip_headsign TEXT NOT NULL,
trip_short_name TEXT,
direction_id SMALLINT NOT NULL,
block_id BIGINT NOT NULL,
shape_id INT NOT NULL,
wheelchair_accessible SMALLINT NOT NULL
);