-
Notifications
You must be signed in to change notification settings - Fork 8
/
collisions_involved.sql
236 lines (228 loc) · 10.7 KB
/
collisions_involved.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
230
231
232
233
234
235
236
-- Script for creating a table of individual-level variables from collisions.acc.
-- View: collisions_replicator.involved
-- DROP MATERIALIZED VIEW IF EXISTS collisions_replicator.involved;
CREATE MATERIALIZED VIEW IF NOT EXISTS collisions_replicator.involved
TABLESPACE pg_default
AS
WITH involved_desc AS (
SELECT a_1."ACCNB"::bigint AS accnb,
a_1."REC_ID"::bigint AS rec_id,
date_part('year'::text, a_1."ACCDATE"::date) AS accyear,
CASE
WHEN btrim(a_1."PER_NO"::text) ~ '^[0-9]+$'::text THEN btrim(a_1."PER_NO"::text)::integer
ELSE NULL::integer
END AS person_no,
CASE
WHEN btrim(a_1."VEH_NO"::text) ~ '^[0-9]+$'::text THEN btrim(a_1."VEH_NO"::text)::integer
ELSE NULL::integer
END AS vehicle_no,
upper(btrim(i.description)) AS vehicle_class,
upper(btrim(j.description)) AS initial_dir,
upper(btrim(k.description)) AS impact_location,
upper(btrim(l.description)) AS event1,
upper(btrim(m.description)) AS event2,
upper(btrim(n.description)) AS event3,
upper(btrim(o.description)) AS involved_class,
CASE
WHEN btrim(a_1."INVAGE"::text) ~ '^[0-9]+$'::text THEN btrim(a_1."INVAGE"::text)::integer
ELSE NULL::integer
END AS involved_age,
upper(btrim(p.description)) AS involved_injury_class,
upper(btrim(q.description)) AS safety_equip_used,
upper(btrim(r.description)) AS driver_action,
upper(btrim(s.description)) AS driver_condition,
upper(btrim(t.description)) AS pedestrian_action,
upper(btrim(u.description)) AS pedestrian_condition,
upper(btrim(v.description)) AS pedestrian_collision_type,
upper(btrim(w.description)) AS cyclist_action,
upper(btrim(x.description)) AS cyclist_condition,
upper(btrim(y.description)) AS cyclist_collision_type,
upper(btrim(z.description)) AS manoeuver,
a_1."POSTED_SPEED"::integer AS posted_speed,
a_1."ACTUAL_SPEED"::integer AS actual_speed,
CASE
WHEN a_1."FAILTOREM" = 1 THEN true
ELSE false
END AS failed_to_remain,
a_1."BIRTHDATE" AS birthdate,
a_1."USERID" AS validation_userid,
a_1."TS" AS time_last_edited
FROM ( SELECT acc_safe_copy."ACCNB",
acc_safe_copy."RELACCNB",
acc_safe_copy."ACCDATE",
acc_safe_copy."DAY_NO",
acc_safe_copy."ACCTIME",
acc_safe_copy."PATAREA",
acc_safe_copy."STNAME1",
acc_safe_copy."STREETYPE1",
acc_safe_copy."DIR1",
acc_safe_copy."STNAME2",
acc_safe_copy."STREETYPE2",
acc_safe_copy."DIR2",
acc_safe_copy."STNAME3",
acc_safe_copy."STREETYPE3",
acc_safe_copy."DIR3",
acc_safe_copy."PER_INV",
acc_safe_copy."VEH_INV",
acc_safe_copy."MUNICIPAL",
acc_safe_copy."LOCCOORD",
acc_safe_copy."IMPCTAREA",
acc_safe_copy."ACCLASS",
acc_safe_copy."ACCLOC",
acc_safe_copy."TRAFFICTL",
acc_safe_copy."DRIVAGE",
acc_safe_copy."VEH_NO",
acc_safe_copy."VEHTYPE",
acc_safe_copy."TOWEDVEH",
acc_safe_copy."INITDIR",
acc_safe_copy."IMPACTYPE",
acc_safe_copy."IMPLOC",
acc_safe_copy."EVENT1",
acc_safe_copy."EVENT2",
acc_safe_copy."EVENT3",
acc_safe_copy."PER_NO",
acc_safe_copy."INVTYPE",
acc_safe_copy."INVAGE",
acc_safe_copy."INJURY",
acc_safe_copy."SAFEQUIP",
acc_safe_copy."DRIVACT",
acc_safe_copy."DRIVCOND",
acc_safe_copy."PEDCOND",
acc_safe_copy."PEDACT",
acc_safe_copy."CHARGE",
acc_safe_copy."CHARGE2",
acc_safe_copy."CHARGE3",
acc_safe_copy."CHARGE4",
acc_safe_copy."VISIBLE",
acc_safe_copy."LIGHT",
acc_safe_copy."RDSFCOND",
acc_safe_copy."VEHIMPTYPE",
acc_safe_copy."MANOEUVER",
acc_safe_copy."ENTRY",
acc_safe_copy."GEOCODE",
acc_safe_copy."FACTOR_ERR",
acc_safe_copy."REP_TYPE",
acc_safe_copy."BADGE_NO",
acc_safe_copy."POSTAL",
acc_safe_copy."XCOORD",
acc_safe_copy."YCOORD",
acc_safe_copy."PRECISE_XY",
acc_safe_copy."LONGITUDE",
acc_safe_copy."LATITUDE",
acc_safe_copy."CHANGED",
acc_safe_copy."SENT_UNIT",
acc_safe_copy."SENT_DATE",
acc_safe_copy."STATUS",
acc_safe_copy."CITY_AREA",
acc_safe_copy."USER_ID",
acc_safe_copy."CRC_UNIT",
acc_safe_copy."COMMENTS",
acc_safe_copy."MTP_DIVISION",
acc_safe_copy."POLICE_AGENCY",
acc_safe_copy."SUBMIT_BADGE_NUMBER",
acc_safe_copy."SUBMIT_DATE",
acc_safe_copy."BIRTHDATE",
acc_safe_copy."PRIVATE_PROPERTY",
acc_safe_copy."PERSON_ID",
acc_safe_copy."USERID",
acc_safe_copy."TS",
acc_safe_copy."ROAD_CLASS",
acc_safe_copy."SYMBOL_NUM",
acc_safe_copy."ROTATION_NUM",
acc_safe_copy."PX",
acc_safe_copy."DISTRICT",
acc_safe_copy."QUADRANT",
acc_safe_copy."FAILTOREM",
acc_safe_copy."YEAR",
acc_safe_copy."REC_ID",
acc_safe_copy."PEDTYPE",
acc_safe_copy."CYCLISTYPE",
acc_safe_copy."SIDEWALKCYCLE",
acc_safe_copy."CYCACT",
acc_safe_copy."CYCCOND",
acc_safe_copy."MVAIMG",
acc_safe_copy."WARDNUM",
acc_safe_copy."FATAL_NO",
acc_safe_copy."DESCRIPTION",
acc_safe_copy."TAB_REPORT",
acc_safe_copy."ACTUAL_SPEED",
acc_safe_copy."POSTED_SPEED",
acc_safe_copy."TRAFCTLCOND"
FROM collisions_replicator.acc_safe_copy
WHERE acc_safe_copy."ACCDATE"::date >= '1985-01-01'::date AND acc_safe_copy."ACCDATE"::date <= 'now'::text::date) a_1
LEFT JOIN ( SELECT DISTINCT ON (vehtype.vehtype) vehtype.vehtype,
vehtype.description
FROM collision_factors.vehtype
ORDER BY vehtype.vehtype, (char_length(vehtype.description))) i ON a_1."VEHTYPE"::text = i.vehtype
LEFT JOIN collision_factors.initdir j ON a_1."INITDIR"::text = j.initdir
LEFT JOIN collision_factors.imploc k ON a_1."IMPLOC"::text = k.imploc
LEFT JOIN collision_factors.event1 l ON a_1."EVENT1"::text = l.event1
LEFT JOIN collision_factors.event2 m ON a_1."EVENT2"::text = m.event2
LEFT JOIN collision_factors.event3 n ON a_1."EVENT3"::text = n.event3
LEFT JOIN collision_factors.invtype o ON a_1."INVTYPE"::text = o.invtype
LEFT JOIN collision_factors.injury p ON a_1."INJURY"::text = p.injury
LEFT JOIN collision_factors.safequip q ON a_1."SAFEQUIP"::text = q.safequip
LEFT JOIN collision_factors.drivact r ON a_1."DRIVACT"::text = r.drivact
LEFT JOIN collision_factors.drivcond s ON a_1."DRIVCOND"::text = s.drivcond
LEFT JOIN collision_factors.pedact t ON a_1."PEDACT"::text = t.pedact
LEFT JOIN collision_factors.pedcond u ON a_1."PEDCOND"::text = u.pedcond
LEFT JOIN collision_factors.pedtype v ON a_1."PEDTYPE"::text = v.pedtype
LEFT JOIN collision_factors.cycact w ON a_1."CYCACT"::text = w.cycact
LEFT JOIN collision_factors.cyccond x ON a_1."CYCCOND"::text = x.cyccond
LEFT JOIN collision_factors.cyclistype y ON a_1."CYCLISTYPE"::text = y.cyclistype
LEFT JOIN collision_factors.manoeuver z ON a_1."MANOEUVER"::text = z.manoeuver
)
SELECT b.collision_no,
a.rec_id,
a.person_no,
a.vehicle_no,
a.vehicle_class,
a.initial_dir,
a.impact_location,
a.event1,
a.event2,
a.event3,
a.involved_class,
CASE
WHEN a.involved_age > 0 THEN a.involved_age
WHEN a.involved_age = 0 AND a.birthdate IS NOT NULL THEN a.involved_age
ELSE NULL::integer
END AS involved_age,
a.involved_injury_class,
a.safety_equip_used,
a.driver_action,
a.driver_condition,
a.pedestrian_action,
a.pedestrian_condition,
a.pedestrian_collision_type,
a.cyclist_action,
a.cyclist_condition,
a.cyclist_collision_type,
a.manoeuver,
a.posted_speed,
a.actual_speed,
a.failed_to_remain,
a.validation_userid,
a.time_last_edited
FROM involved_desc a
JOIN collisions_replicator.collision_no b USING (accnb, accyear)
ORDER BY b.collision_no
WITH DATA;
ALTER TABLE IF EXISTS collisions_replicator.involved
OWNER TO collision_admins;
COMMENT ON MATERIALIZED VIEW collisions_replicator.involved
IS 'Individual-level variables in collisions_replcator.acc_safe_copy. Data refreshed daily at 3am.';
GRANT SELECT ON TABLE collisions_replicator.involved TO kchan;
GRANT SELECT ON TABLE collisions_replicator.involved TO bdit_humans;
GRANT SELECT ON TABLE collisions_replicator.involved TO rsaunders;
GRANT SELECT ON TABLE collisions_replicator.involved TO ksun;
GRANT ALL ON TABLE collisions_replicator.involved TO collision_admins;
GRANT SELECT ON TABLE collisions_replicator.involved TO data_collection;
CREATE INDEX collision_involved_idx
ON collisions_replicator.involved USING btree
(collision_no)
TABLESPACE pg_default;
CREATE UNIQUE INDEX inv_rec_id_idx
ON collisions_replicator.involved USING btree
(rec_id)
TABLESPACE pg_default;