location_crashes_view

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

cris_crash_id

int4

√

null

type

text

2147483647

√

null

location_id

text

2147483647

√

null

atd_txdot_locations.location_id

Implied Constraint

case_id

text

2147483647

√

null

crash_date

text

2147483647

√

null

crash_time

text

2147483647

√

null

day_of_week

text

2147483647

√

null

crash_sev_id

int4

√

null

latitude

numeric

√

null

longitude

numeric

√

null

address_primary

text

2147483647

√

null

address_secondary

text

2147483647

√

null

non_injry_count

int8

√

null

nonincap_injry_count

int8

√

null

poss_injry_count

int8

√

null

sus_serious_injry_count

int8

√

null

tot_injry_count

int8

√

null

unkn_injry_count

int8

√

null

vz_fatality_count

int8

√

null

est_comp_cost_crash_based

numeric

√

null

collsn_desc

text

2147483647

√

null

movement_desc

text

2147483647

√

null

travel_direction

text

2147483647

√

null

veh_body_styl_desc

text

2147483647

√

null

veh_unit_desc

text

2147483647

√

null

Relationships

Close relationships within degrees of separation

View Definition

SELECT crashes.cris_crash_id,
    'CR3'::text AS type,
    crashes.location_id,
    crashes.case_id,
    to_char((crashes.crash_timestamp AT TIME ZONE 'US/Central'::text), 'YYYY-MM-DD'::text) AS crash_date,
    to_char((crashes.crash_timestamp AT TIME ZONE 'US/Central'::text), 'HH24:MI:SS'::text) AS crash_time,
    upper(to_char((crashes.crash_timestamp AT TIME ZONE 'US/Central'::text), 'dy'::text)) AS day_of_week,
    crash_injury_metrics_view.crash_injry_sev_id AS crash_sev_id,
    crashes.latitude,
    crashes.longitude,
    crashes.address_primary,
    crashes.address_secondary,
    crash_injury_metrics_view.non_injry_count,
    crash_injury_metrics_view.nonincap_injry_count,
    crash_injury_metrics_view.poss_injry_count,
    crash_injury_metrics_view.sus_serious_injry_count,
    crash_injury_metrics_view.tot_injry_count,
    crash_injury_metrics_view.unkn_injry_count,
    crash_injury_metrics_view.vz_fatality_count,
    crash_injury_metrics_view.est_comp_cost_crash_based,
    collsn.label AS collsn_desc,
    crash_units.movement_desc,
    crash_units.travel_direction,
    crash_units.veh_body_styl_desc,
    crash_units.veh_unit_desc
   FROM (((crashes
     LEFT JOIN LATERAL ( SELECT units.crash_pk,
            string_agg(movt.label, ','::text) AS movement_desc,
            string_agg(trvl_dir.label, ','::text) AS travel_direction,
            string_agg(veh_body_styl.label, ','::text) AS veh_body_styl_desc,
            string_agg(unit_desc.label, ','::text) AS veh_unit_desc
           FROM ((((units
             LEFT JOIN lookups.movt movt ON ((units.movement_id = movt.id)))
             LEFT JOIN lookups.trvl_dir trvl_dir ON ((units.veh_trvl_dir_id = trvl_dir.id)))
             LEFT JOIN lookups.veh_body_styl veh_body_styl ON ((units.veh_body_styl_id = veh_body_styl.id)))
             LEFT JOIN lookups.unit_desc unit_desc ON ((units.unit_desc_id = unit_desc.id)))
          WHERE (crashes.id = units.crash_pk)
          GROUP BY units.crash_pk) crash_units ON (true))
     LEFT JOIN LATERAL ( SELECT crash_injury_metrics_view_1.id,
            crash_injury_metrics_view_1.cris_crash_id,
            crash_injury_metrics_view_1.unkn_injry_count,
            crash_injury_metrics_view_1.nonincap_injry_count,
            crash_injury_metrics_view_1.poss_injry_count,
            crash_injury_metrics_view_1.non_injry_count,
            crash_injury_metrics_view_1.sus_serious_injry_count,
            crash_injury_metrics_view_1.tot_injry_count,
            crash_injury_metrics_view_1.fatality_count,
            crash_injury_metrics_view_1.vz_fatality_count,
            crash_injury_metrics_view_1.law_enf_fatality_count,
            crash_injury_metrics_view_1.cris_fatality_count,
            crash_injury_metrics_view_1.motor_vehicle_fatality_count,
            crash_injury_metrics_view_1.motor_vehicle_sus_serious_injry_count,
            crash_injury_metrics_view_1.motorcycle_fatality_count,
            crash_injury_metrics_view_1.motorcycle_sus_serious_count,
            crash_injury_metrics_view_1.bicycle_fatality_count,
            crash_injury_metrics_view_1.bicycle_sus_serious_injry_count,
            crash_injury_metrics_view_1.pedestrian_fatality_count,
            crash_injury_metrics_view_1.pedestrian_sus_serious_injry_count,
            crash_injury_metrics_view_1.micromobility_fatality_count,
            crash_injury_metrics_view_1.micromobility_sus_serious_injry_count,
            crash_injury_metrics_view_1.other_fatality_count,
            crash_injury_metrics_view_1.other_sus_serious_injry_count,
            crash_injury_metrics_view_1.crash_injry_sev_id,
            crash_injury_metrics_view_1.years_of_life_lost,
            crash_injury_metrics_view_1.est_comp_cost_crash_based,
            crash_injury_metrics_view_1.est_total_person_comp_cost
           FROM crash_injury_metrics_view crash_injury_metrics_view_1
          WHERE (crashes.id = crash_injury_metrics_view_1.id)
         LIMIT 1) crash_injury_metrics_view ON (true))
     LEFT JOIN lookups.collsn ON ((crashes.fhe_collsn_id = collsn.id)))
  WHERE ((crashes.is_deleted = false) AND (crashes.crash_timestamp >= ((now() - '5 years'::interval))::date))
UNION ALL
 SELECT aab.form_id AS cris_crash_id,
    'NON-CR3'::text AS type,
    aab.location_id,
    (aab.case_id)::text AS case_id,
    (aab.date)::text AS crash_date,
    concat(aab.hour, ':00:00') AS crash_time,
    ( SELECT
                CASE date_part('dow'::text, aab.date)
                    WHEN 0 THEN 'SUN'::text
                    WHEN 1 THEN 'MON'::text
                    WHEN 2 THEN 'TUE'::text
                    WHEN 3 THEN 'WED'::text
                    WHEN 4 THEN 'THU'::text
                    WHEN 5 THEN 'FRI'::text
                    WHEN 6 THEN 'SAT'::text
                    ELSE 'Unknown'::text
                END AS "case") AS day_of_week,
    0 AS crash_sev_id,
    aab.latitude,
    aab.longitude,
    aab.address AS address_primary,
    ''::text AS address_secondary,
    0 AS non_injry_count,
    0 AS nonincap_injry_count,
    0 AS poss_injry_count,
    0 AS sus_serious_injry_count,
    0 AS tot_injry_count,
    0 AS unkn_injry_count,
    0 AS vz_fatality_count,
    aab.est_comp_cost_crash_based,
    ''::text AS collsn_desc,
    ''::text AS movement_desc,
    ''::text AS travel_direction,
    ''::text AS veh_body_styl_desc,
    ''::text AS veh_unit_desc
   FROM atd_apd_blueform aab
  WHERE (aab.date >= ((now() - '5 years'::interval))::date);

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views