This repository has been archived by the owner on Feb 27, 2023. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathdata_loading.sql
205 lines (195 loc) · 6.45 KB
/
data_loading.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
--depending on where data is loaded update schema_name (Find - Replace)
--similar update geography_name in query
CREATE TABLE schema_name.geography_name_YYYY_ride_edge_alignment_hourly (
edge_id integer, year integer, day integer,
hour integer, minute integer, athlete_count integer,
rev_athlete_count integer, activity_count integer,
rev_activity_count integer, total_athlete_count integer,
total_activity_count integer, activity_time numeric,
rev_activity_time numeric, commute_count integer
);
CREATE TABLE schema_name.geography_name_YYYY_ride_rollup_month_total (
edge_id Integer, athcnt_0 Integer,
rathcnt_0 Integer, actcnt_0 Integer,
ractcnt_0 Integer, tathcnt_0 Integer,
tactcnt_0 Integer, acttime_0 Numeric,
racttime_0 Numeric, cmtcnt_0 Integer,
rcmtcnt_0 Integer, tcmtcnt_0 Integer,
athcnt_1 Integer, rathcnt_1 Integer,
actcnt_1 Integer, ractcnt_1 Numeric,
tathcnt_1 Numeric, tactcnt_1 Integer,
acttime_1 Numeric, racttime_1 Numeric,
cmtcnt_1 Integer, rcmtcnt_1 Integer,
tcmtcnt_1 Integer, athcnt_2 Integer,
rathcnt_2 Integer, actcnt_2 Integer,
ractcnt_2 Numeric, tathcnt_2 Numeric,
tactcnt_2 Integer, acttime_2 Numeric,
racttime_2 Numeric, cmtcnt_2 Integer,
rcmtcnt_2 Integer, tcmtcnt_2 Integer,
athcnt_3 Integer, rathcnt_3 Integer,
actcnt_3 Integer, ractcnt_3 Numeric,
tathcnt_3 Numeric, tactcnt_3 Integer,
acttime_3 Numeric, racttime_3 Numeric,
cmtcnt_3 Integer, rcmtcnt_3 Integer,
tcmtcnt_3 Integer, athcnt_4 Integer,
rathcnt_4 Integer, actcnt_4 Integer,
ractcnt_4 Numeric, tathcnt_4 Numeric,
tactcnt_4 Integer, acttime_4 Numeric,
racttime_4 Numeric, cmtcnt_4 Integer,
rcmtcnt_4 Integer, tcmtcnt_4 Integer,
athcnt Integer, rathcnt Integer, actcnt Integer,
ractcnt Numeric, tathcnt Numeric,
tactcnt Integer, acttime Numeric,
racttime Numeric, cmtcnt Integer,
rcmtcnt Integer, tcmtcnt Integer,
month_n integer
);
CREATE TABLE schema_name.geography_name_YYYY_ride_edge_rollup_year_total (
edge_id Integer, athcnt_0 Integer,
rathcnt_0 Integer, actcnt_0 Integer,
ractcnt_0 Integer, tathcnt_0 Integer,
tactcnt_0 Integer, acttime_0 Numeric,
racttime_0 Numeric, cmtcnt_0 Integer,
rcmtcnt_0 Integer, tcmtcnt_0 Integer,
athcnt_1 Integer, rathcnt_1 Integer,
actcnt_1 Integer, ractcnt_1 Numeric,
tathcnt_1 Numeric, tactcnt_1 Integer,
acttime_1 Numeric, racttime_1 Numeric,
cmtcnt_1 Integer, rcmtcnt_1 Integer,
tcmtcnt_1 Integer, athcnt_2 Integer,
rathcnt_2 Integer, actcnt_2 Integer,
ractcnt_2 Numeric, tathcnt_2 Numeric,
tactcnt_2 Integer, acttime_2 Numeric,
racttime_2 Numeric, cmtcnt_2 Integer,
rcmtcnt_2 Integer, tcmtcnt_2 Integer,
athcnt_3 Integer, rathcnt_3 Integer,
actcnt_3 Integer, ractcnt_3 Numeric,
tathcnt_3 Numeric, tactcnt_3 Integer,
acttime_3 Numeric, racttime_3 Numeric,
cmtcnt_3 Integer, rcmtcnt_3 Integer,
tcmtcnt_3 Integer, athcnt_4 Integer,
rathcnt_4 Integer, actcnt_4 Integer,
ractcnt_4 Numeric, tathcnt_4 Numeric,
tactcnt_4 Integer, acttime_4 Numeric,
racttime_4 Numeric, cmtcnt_4 Integer,
rcmtcnt_4 Integer, tcmtcnt_4 Integer,
athcnt Integer, rathcnt Integer, actcnt Integer,
ractcnt Numeric, tathcnt Numeric,
tactcnt Integer, acttime Numeric,
racttime Numeric, cmtcnt Integer,
rcmtcnt Integer, tcmtcnt Integer,
month_range character varying
);
CREATE TABLE schema_name.geography_name_YYYY_ride_node_rollup_month_total (
node_id integer, athcnt_0 integer,
actcnt_0 integer, cmtcnt_0 integer,
pct_0_0 integer, pct_025_0 numeric,
pct_05_0 numeric, pct_075_0 numeric,
pct_1_0 integer, athcnt_1 integer,
actcnt_1 integer, cmtcnt_1 integer,
pct_0_1 integer, pct_025_1 numeric,
pct_05_1 numeric, pct_075_1 numeric,
pct_1_1 integer, athcnt_2 integer,
actcnt_2 integer, cmtcnt_2 integer,
pct_0_2 integer, pct_025_2 numeric,
pct_05_2 numeric, pct_075_2 numeric,
pct_1_2 integer, athcnt_3 integer,
actcnt_3 integer, cmtcnt_3 integer,
pct_0_3 integer, pct_025_3 numeric,
pct_05_3 numeric, pct_075_3 numeric,
pct_1_3 integer, athcnt_4 integer,
actcnt_4 integer, cmtcnt_4 integer,
pct_0_4 integer, pct_025_4 numeric,
pct_05_4 numeric, pct_075_4 numeric,
pct_1_4 integer, athcnt integer, actcnt integer,
cmtcnt integer, pct_0 integer, pct_025 numeric,
pct_05 numeric, pct_075 numeric, pct_1 integer,
month_n integer
)
CREATE TABLE schema_name.geography_name_YYYY_ride_nodes_rollup_year_total (
node_id integer, athcnt_0 integer,
actcnt_0 integer, cmtcnt_0 integer,
pct_0_0 integer, pct_025_0 numeric,
pct_05_0 numeric, pct_075_0 numeric,
pct_1_0 integer, athcnt_1 integer,
actcnt_1 integer, cmtcnt_1 integer,
pct_0_1 integer, pct_025_1 numeric,
pct_05_1 numeric, pct_075_1 numeric,
pct_1_1 integer, athcnt_2 integer,
actcnt_2 integer, cmtcnt_2 integer,
pct_0_2 integer, pct_025_2 numeric,
pct_05_2 numeric, pct_075_2 numeric,
pct_1_2 integer, athcnt_3 integer,
actcnt_3 integer, cmtcnt_3 integer,
pct_0_3 integer, pct_025_3 numeric,
pct_05_3 numeric, pct_075_3 numeric,
pct_1_3 integer, athcnt_4 integer,
actcnt_4 integer, cmtcnt_4 integer,
pct_0_4 integer, pct_025_4 numeric,
pct_05_4 numeric, pct_075_4 numeric,
pct_1_4 integer, athcnt integer, actcnt integer,
cmtcnt integer, pct_0 integer, pct_025 numeric,
pct_05 numeric, pct_075 numeric, pct_1 integer
)
CREATE TABLE schema_name.geography_name_YYYY_ride_od_hourly
(
polygon_id integer,
dest_polygon_id integer,
year integer,
day integer,
hour integer,
minute integer,
commute_count integer,
athlete_count integer,
activity_count integer,
distance numeric,
duration integer
);
CREATE TABLE schema_name.geography_name_YYYY_ride_od_rollup_month_total
(
origin integer,
destination integer,
athcnt_0 integer,
actcnt_0 integer,
cmtcnt_0 integer,
athcnt_1 integer,
actcnt_1 integer,
cmtcnt_1 integer,
athcnt_2 integer,
actcnt_2 integer,
cmtcnt_2 integer,
athcnt_3 integer,
actcnt_3 integer,
cmtcnt_3 integer,
athcnt_4 integer,
actcnt_4 integer,
cmtcnt_4 integer,
athcnt integer,
actcnt integer,
cmtcnt integer,
month_n integer
);
CREATE TABLE schema_name.geography_name_YYYY_ride_od_rollup_total
(
origin integer,
destination integer,
athcnt_0 integer,
actcnt_0 integer,
cmtcnt_0 integer,
athcnt_1 integer,
actcnt_1 integer,
cmtcnt_1 integer,
athcnt_2 integer,
actcnt_2 integer,
cmtcnt_2 integer,
athcnt_3 integer,
actcnt_3 integer,
cmtcnt_3 integer,
athcnt_4 integer,
actcnt_4 integer,
cmtcnt_4 integer,
athcnt integer,
actcnt integer,
cmtcnt integer,
month_range character varying
)