1
SELECT crashes.cris_crash_id,
2
'CR3'::text AS type,
3
crashes.location_id,
4
crashes.case_id,
5
to_char((crashes.crash_timestamp AT TIME ZONE 'US/Central'::text), 'YYYY-MM-DD'::text) AS crash_date,
6
to_char((crashes.crash_timestamp AT TIME ZONE 'US/Central'::text), 'HH24:MI:SS'::text) AS crash_time,
7
upper(to_char((crashes.crash_timestamp AT TIME ZONE 'US/Central'::text), 'dy'::text)) AS day_of_week,
8
crash_injury_metrics_view.crash_injry_sev_id AS crash_sev_id,
9
crashes.latitude,
10
crashes.longitude,
11
crashes.address_primary,
12
crashes.address_secondary,
13
crash_injury_metrics_view.non_injry_count,
14
crash_injury_metrics_view.nonincap_injry_count,
15
crash_injury_metrics_view.poss_injry_count,
16
crash_injury_metrics_view.sus_serious_injry_count,
17
crash_injury_metrics_view.tot_injry_count,
18
crash_injury_metrics_view.unkn_injry_count,
19
crash_injury_metrics_view.vz_fatality_count,
20
crash_injury_metrics_view.est_comp_cost_crash_based,
21
collsn.label AS collsn_desc,
22
crash_units.movement_desc,
23
crash_units.travel_direction,
24
crash_units.veh_body_styl_desc,
25
crash_units.veh_unit_desc
26
FROM (((crashes
27
LEFT JOIN LATERAL ( SELECT units.crash_pk,
28
string_agg(movt.label, ','::text) AS movement_desc,
29
string_agg(trvl_dir.label, ','::text) AS travel_direction,
30
string_agg(veh_body_styl.label, ','::text) AS veh_body_styl_desc,
31
string_agg(unit_desc.label, ','::text) AS veh_unit_desc
32
FROM ((((units
33
LEFT JOIN lookups.movt movt ON ((units.movement_id = movt.id)))
34
LEFT JOIN lookups.trvl_dir trvl_dir ON ((units.veh_trvl_dir_id = trvl_dir.id)))
35
LEFT JOIN lookups.veh_body_styl veh_body_styl ON ((units.veh_body_styl_id = veh_body_styl.id)))
36
LEFT JOIN lookups.unit_desc unit_desc ON ((units.unit_desc_id = unit_desc.id)))
37
WHERE (crashes.id = units.crash_pk)
38
GROUP BY units.crash_pk) crash_units ON (true))
39
LEFT JOIN LATERAL ( SELECT crash_injury_metrics_view_1.id,
40
crash_injury_metrics_view_1.cris_crash_id,
41
crash_injury_metrics_view_1.unkn_injry_count,
42
crash_injury_metrics_view_1.nonincap_injry_count,
43
crash_injury_metrics_view_1.poss_injry_count,
44
crash_injury_metrics_view_1.non_injry_count,
45
crash_injury_metrics_view_1.sus_serious_injry_count,
46
crash_injury_metrics_view_1.tot_injry_count,
47
crash_injury_metrics_view_1.fatality_count,
48
crash_injury_metrics_view_1.vz_fatality_count,
49
crash_injury_metrics_view_1.law_enf_fatality_count,
50
crash_injury_metrics_view_1.cris_fatality_count,
51
crash_injury_metrics_view_1.motor_vehicle_fatality_count,
52
crash_injury_metrics_view_1.motor_vehicle_sus_serious_injry_count,
53
crash_injury_metrics_view_1.motorcycle_fatality_count,
54
crash_injury_metrics_view_1.motorcycle_sus_serious_count,
55
crash_injury_metrics_view_1.bicycle_fatality_count,
56
crash_injury_metrics_view_1.bicycle_sus_serious_injry_count,
57
crash_injury_metrics_view_1.pedestrian_fatality_count,
58
crash_injury_metrics_view_1.pedestrian_sus_serious_injry_count,
59
crash_injury_metrics_view_1.micromobility_fatality_count,
60
crash_injury_metrics_view_1.micromobility_sus_serious_injry_count,
61
crash_injury_metrics_view_1.other_fatality_count,
62
crash_injury_metrics_view_1.other_sus_serious_injry_count,
63
crash_injury_metrics_view_1.crash_injry_sev_id,
64
crash_injury_metrics_view_1.years_of_life_lost,
65
crash_injury_metrics_view_1.est_comp_cost_crash_based,
66
crash_injury_metrics_view_1.est_total_person_comp_cost
67
FROM crash_injury_metrics_view crash_injury_metrics_view_1
68
WHERE (crashes.id = crash_injury_metrics_view_1.id)
69
LIMIT 1) crash_injury_metrics_view ON (true))
70
LEFT JOIN lookups.collsn ON ((crashes.fhe_collsn_id = collsn.id)))
71
WHERE ((crashes.is_deleted = false) AND (crashes.crash_timestamp >= ((now() - '5 years'::interval))::date))
72
UNION ALL
73
SELECT aab.form_id AS cris_crash_id,
74
'NON-CR3'::text AS type,
75
aab.location_id,
76
(aab.case_id)::text AS case_id,
77
(aab.date)::text AS crash_date,
78
concat(aab.hour, ':00:00') AS crash_time,
79
( SELECT
80
CASE date_part('dow'::text, aab.date)
81
WHEN 0 THEN 'SUN'::text
82
WHEN 1 THEN 'MON'::text
83
WHEN 2 THEN 'TUE'::text
84
WHEN 3 THEN 'WED'::text
85
WHEN 4 THEN 'THU'::text
86
WHEN 5 THEN 'FRI'::text
87
WHEN 6 THEN 'SAT'::text
88
ELSE 'Unknown'::text
89
END AS "case") AS day_of_week,
90
0 AS crash_sev_id,
91
aab.latitude,
92
aab.longitude,
93
aab.address AS address_primary,
94
''::text AS address_secondary,
95
0 AS non_injry_count,
96
0 AS nonincap_injry_count,
97
0 AS poss_injry_count,
98
0 AS sus_serious_injry_count,
99
0 AS tot_injry_count,
100
0 AS unkn_injry_count,
101
0 AS vz_fatality_count,
102
aab.est_comp_cost_crash_based,
103
''::text AS collsn_desc,
104
''::text AS movement_desc,
105
''::text AS travel_direction,
106
''::text AS veh_body_styl_desc,
107
''::text AS veh_unit_desc
108
FROM atd_apd_blueform aab
109
WHERE (aab.date >= ((now() - '5 years'::interval))::date);