socrata_export_crashes_view

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

int4

√

null

cris_crash_id

int4

√

null

case_id

text

2147483647

√

null

address_primary

text

2147483647

√

null

address_secondary

text

2147483647

√

null

is_deleted

bool

√

null

latitude

numeric

√

null

longitude

numeric

√

null

rpt_block_num

text

2147483647

√

null

rpt_street_name

text

2147483647

√

null

rpt_street_sfx

text

2147483647

√

null

crash_speed_limit

int4

√

null

road_constr_zone_fl

bool

√

null

is_temp_record

bool

√

null

crash_sev_id

int4

√

null

sus_serious_injry_cnt

int8

√

null

nonincap_injry_cnt

int8

√

null

poss_injry_cnt

int8

√

null

non_injry_cnt

int8

√

null

unkn_injry_cnt

int8

√

null

tot_injry_cnt

int8

√

null

law_enf_fatality_count

int8

√

null

death_cnt

int8

√

null

onsys_fl

bool

√

null

private_dr_fl

bool

√

null

units_involved

text

2147483647

√

null

motor_vehicle_death_count

int8

√

null

motor_vehicle_serious_injury_count

int8

√

null

bicycle_death_count

int8

√

null

bicycle_serious_injury_count

int8

√

null

pedestrian_death_count

int8

√

null

pedestrian_serious_injury_count

int8

√

null

motorcycle_death_count

int8

√

null

motorcycle_serious_injury_count

int8

√

null

micromobility_death_count

int8

√

null

micromobility_serious_injury_count

int8

√

null

other_death_count

int8

√

null

other_serious_injury_count

int8

√

null

years_of_life_lost

int8

√

null

crash_timestamp

text

2147483647

√

null

crash_timestamp_ct

text

2147483647

√

null

point

text

2147483647

√

null

crash_fatal_fl

bool

√

null

Relationships

Close relationships within degrees of separation

View Definition

WITH unit_aggregates AS (
         SELECT crashes_1.id,
            string_agg(DISTINCT mode_categories.label, ' & '::text) AS units_involved
           FROM ((crashes crashes_1
             LEFT JOIN units ON ((crashes_1.id = units.crash_pk)))
             LEFT JOIN lookups.mode_category mode_categories ON ((units.vz_mode_category_id = mode_categories.id)))
          GROUP BY crashes_1.id
        )
 SELECT crashes.id,
    crashes.cris_crash_id,
    crashes.case_id,
    crashes.address_primary,
    crashes.address_secondary,
    crashes.is_deleted,
    crashes.latitude,
    crashes.longitude,
    crashes.rpt_block_num,
    crashes.rpt_street_name,
    crashes.rpt_street_sfx,
    crashes.crash_speed_limit,
    crashes.road_constr_zone_fl,
    crashes.is_temp_record,
    cimv.crash_injry_sev_id AS crash_sev_id,
    cimv.sus_serious_injry_count AS sus_serious_injry_cnt,
    cimv.nonincap_injry_count AS nonincap_injry_cnt,
    cimv.poss_injry_count AS poss_injry_cnt,
    cimv.non_injry_count AS non_injry_cnt,
    cimv.unkn_injry_count AS unkn_injry_cnt,
    cimv.tot_injry_count AS tot_injry_cnt,
    cimv.law_enf_fatality_count,
    cimv.vz_fatality_count AS death_cnt,
    crashes.onsys_fl,
    crashes.private_dr_fl,
    unit_aggregates.units_involved,
    cimv.motor_vehicle_fatality_count AS motor_vehicle_death_count,
    cimv.motor_vehicle_sus_serious_injry_count AS motor_vehicle_serious_injury_count,
    cimv.bicycle_fatality_count AS bicycle_death_count,
    cimv.bicycle_sus_serious_injry_count AS bicycle_serious_injury_count,
    cimv.pedestrian_fatality_count AS pedestrian_death_count,
    cimv.pedestrian_sus_serious_injry_count AS pedestrian_serious_injury_count,
    cimv.motorcycle_fatality_count AS motorcycle_death_count,
    cimv.motorcycle_sus_serious_count AS motorcycle_serious_injury_count,
    cimv.micromobility_fatality_count AS micromobility_death_count,
    cimv.micromobility_sus_serious_injry_count AS micromobility_serious_injury_count,
    cimv.other_fatality_count AS other_death_count,
    cimv.other_sus_serious_injry_count AS other_serious_injury_count,
    cimv.years_of_life_lost,
    to_char(crashes.crash_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS'::text) AS crash_timestamp,
    to_char((crashes.crash_timestamp AT TIME ZONE 'US/Central'::text), 'YYYY-MM-DD"T"HH24:MI:SS'::text) AS crash_timestamp_ct,
        CASE
            WHEN ((crashes.latitude IS NOT NULL) AND (crashes.longitude IS NOT NULL)) THEN (((('POINT ('::text || (crashes.longitude)::text) || ' '::text) || (crashes.latitude)::text) || ')'::text)
            ELSE NULL::text
        END AS point,
    COALESCE((cimv.crash_injry_sev_id = 4), false) AS crash_fatal_fl
   FROM ((crashes
     LEFT JOIN LATERAL ( SELECT crash_injury_metrics_view.id,
            crash_injury_metrics_view.cris_crash_id,
            crash_injury_metrics_view.unkn_injry_count,
            crash_injury_metrics_view.nonincap_injry_count,
            crash_injury_metrics_view.poss_injry_count,
            crash_injury_metrics_view.non_injry_count,
            crash_injury_metrics_view.sus_serious_injry_count,
            crash_injury_metrics_view.tot_injry_count,
            crash_injury_metrics_view.fatality_count,
            crash_injury_metrics_view.vz_fatality_count,
            crash_injury_metrics_view.law_enf_fatality_count,
            crash_injury_metrics_view.cris_fatality_count,
            crash_injury_metrics_view.motor_vehicle_fatality_count,
            crash_injury_metrics_view.motor_vehicle_sus_serious_injry_count,
            crash_injury_metrics_view.motorcycle_fatality_count,
            crash_injury_metrics_view.motorcycle_sus_serious_count,
            crash_injury_metrics_view.bicycle_fatality_count,
            crash_injury_metrics_view.bicycle_sus_serious_injry_count,
            crash_injury_metrics_view.pedestrian_fatality_count,
            crash_injury_metrics_view.pedestrian_sus_serious_injry_count,
            crash_injury_metrics_view.micromobility_fatality_count,
            crash_injury_metrics_view.micromobility_sus_serious_injry_count,
            crash_injury_metrics_view.other_fatality_count,
            crash_injury_metrics_view.other_sus_serious_injry_count,
            crash_injury_metrics_view.crash_injry_sev_id,
            crash_injury_metrics_view.years_of_life_lost,
            crash_injury_metrics_view.est_comp_cost_crash_based,
            crash_injury_metrics_view.est_total_person_comp_cost
           FROM crash_injury_metrics_view
          WHERE (crashes.id = crash_injury_metrics_view.id)
         LIMIT 1) cimv ON (true))
     LEFT JOIN LATERAL ( SELECT unit_aggregates_1.id,
            unit_aggregates_1.units_involved
           FROM unit_aggregates unit_aggregates_1
          WHERE (crashes.id = unit_aggregates_1.id)
         LIMIT 1) unit_aggregates ON (true))
  WHERE ((crashes.is_deleted = false) AND (crashes.in_austin_full_purpose = true) AND (crashes.private_dr_fl = false) AND (crashes.crash_timestamp < (now() - '14 days'::interval)))
  ORDER BY crashes.id;

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views