Include ODOT Crash Data into the Live API

[code-farmhouse]

No description provided.

[bhgrant8]

do we want just the vehicle, participant, crash tables as previously discussed or is there additional tables to include?

[ben-chaney]

These tables include plain English versions of some of the attribute values that could be useful for display and understanding, or could be joined to the main tables prior to the API : Crash_typ Collis_typ Cause Actn Evnt Err Vhcl_typ wthr_cond Partic_typ Thanks, ~ Ben

…

On May 30, 2018, 11:10 AM -0700, Brian Grant ***@***.***>, wrote: do we want just the vehicle, participant, crash tables as previously discussed or is there additional tables to include? — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub, or mute the thread.

[bhgrant8]

Awesome, thanks @ben-chaney. I did create a foreign key relationship for the crash type and a few other fields in the first iteration, but will look at adding some more

[bhgrant8]

Example Crash Object:

{
      "crash_id": 1096439,
      "crash_hour": {
        "crash_hr_no": 20,
        "crash_hr_long_desc": "08:00 PM to 08:59 PM",
        "crash_hr_med_desc": "20:00 to 20:59",
        "crash_hr_short_desc": "8P "
      },
      "crash_severity": {
        "crash_svrty_cd": 4,
        "crash_svrty_long_desc": "Non-Fatal Injury ",
        "crash_svrty_short_desc": "INJ"
      },
      "crash_type": {
        "crash_typ_cd": "H",
        "crash_typ_long_desc": "From opposite direction-one left turn,one straight",
        "crash_typ_med_desc": "OPP - 1 L-TURN 1 STR",
        "crash_typ_short_desc": "O-1 L-TURN"
      },
      "collision_type": {
        "collis_typ_cd": "6",
        "collis_typ_long_desc": "Turning movement",
        "collis_typ_alt_long_desc": "TURNING MOVEMENTS",
        "collis_typ_med_desc": "TURNING ",
        "collis_typ_short_desc": "TURN    "
      },
      "crash_cause_1_cd": {
        "cause_cd": 2,
        "cause_long_desc": "Did not yield right-of-way",
        "cause_med_desc": "FAILED YIELD ROW",
        "cause_short_desc": "NO-YIELD",
        "cause_crash_valid_flg": 1,
        "cause_partic_valid_flg": 1,
        "cause_vhcl_valid_flg": 0
      },
      "crash_cause_2_cd": null,
      "crash_cause_3_cd": null,
      "crash_evnt_1_cd": null,
      "crash_evnt_2_cd": null,
      "crash_evnt_3_cd": null,
      "invstg_agy_cd": {
        "invstg_agy_cd": 3,
        "invstg_agy_long_desc": "City Police - Report received",
        "invstg_agy_short_desc": "CITY  "
      },
      "wthr_cond_cd": {
        "wthr_cond_cd": 1,
        "wthr_cond_long_desc": "Clear  ",
        "wthr_cond_med_desc": "CLEAR",
        "wthr_cond_short_desc": "CLR "
      },
      "ser_no": "00058",
      "crash_dt": "01/05/04 00:00:00",
      "crash_mo_no": 1,
      "crash_day_no": 5,
      "crash_yr_no": 2004,
      "crash_wk_day_cd": 2,
      "cnty_id": 26,
      "cnty_nm": "Multnomah",
      "city_sect_id": 245,
      "city_sect_nm": "Portland SE",
      "urb_area_cd": 57,
      "urb_area_short_nm": "PORTLAND UA    ",
      "fc_cd": 17,
      "fc_short_desc": "U COLL  ",
      "nhs_flg": 0,
      "hwy_no": null,
      "hwy_sfx_no": null,
      "hwy_med_nm": "",
      "rdwy_no": null,
      "hwy_compnt_cd": null,
      "hwy_compnt_short_desc": "",
      "mlge_typ_cd": null,
      "mlge_typ_short_desc": "",
      "rd_con_no": null,
      "lrs_val": "",
      "lat_deg_no": null,
      "lat_minute_no": null,
      "lat_sec_no": null,
      "longtd_deg_no": null,
      "longtd_minute_no": null,
      "longtd_sec_no": null,
      "lat_dd": null,
      "longtd_dd": null,
      "specl_jrsdct_id": null,
      "specl_jrsdct_short_desc": null,
      "jrsdct_grp_cd": null,
      "jrsdct_grp_long_desc": null,
      "agy_st_no": "02702  ",
      "st_full_nm": "SE YAMHILL ST               ",
      "recre_rd_nm": null,
      "isect_agy_st_no": "05030  ",
      "isect_st_full_nm": "SE 30TH AVE                 ",
      "isect_recre_rd_nm": null,
      "isect_seq_no": 1,
      "from_isect_dstnc_qty": 0,
      "cmpss_dir_cd": 9,
      "mp_no": null,
      "post_speed_lmt_val": null,
      "rd_char_cd": 1,
      "rd_char_short_desc": "INTER   ",
      "off_rdwy_flg": 0,
      "isect_typ_cd": 1,
      "isect_typ_short_desc": "CROSS  ",
      "isect_rel_flg": 0,
      "rndabt_flg": 0,
      "drvwy_rel_flg": 0,
      "ln_qty": null,
      "turng_leg_qty": 0,
      "medn_typ_cd": null,
      "medn_typ_short_desc": "",
      "impct_loc_cd": 3,
      "rd_surf_cond_cd": 1,
      "rd_surf_short_desc": "DRY ",
      "lgt_cond_cd": 2,
      "lgt_cond_short_desc": "DLIT",
      "traf_cntl_device_cd": 4,
      "traf_cntl_device_short_desc": "STOP SIGN ",
      "traf_cntl_func_flg": 1,
      "schl_zone_ind": null,
      "wrk_zone_ind": null,
      "alchl_invlv_flg": 0,
      "drug_invlv_flg": 0,
      "crash_speed_invlv_flg": 0,
      "crash_hit_run_flg": 0,
      "pop_rng_cd": 9,
      "pop_rng_med_desc": "Over 200,000",
      "rd_cntl_cd": 1,
      "rd_cntl_med_desc": "PORTLAND CITY STREET",
      "rte_typ_cd": "",
      "rte_id": "",
      "rte_nm": "",
      "reg_id": null,
      "dist_id": "",
      "seg_mrk_id": "",
      "seg_pt_lrs_meas": null,
      "unloct_flg": 0,
      "crash_last_ud_dt": "03/16/16 04:00:11",
      "tot_vhcl_cnt": 2,
      "tot_fatal_cnt": 0,
      "tot_inj_lvl_a_cnt": 0,
      "tot_inj_lvl_b_cnt": 0,
      "tot_inj_lvl_c_cnt": 1,
      "tot_inj_cnt": 1,
      "tot_uninjd_age00_04_cnt": 0,
      "tot_uninjd_per_cnt": 1,
      "tot_ped_cnt": 0,
      "tot_ped_fatal_cnt": 0,
      "tot_ped_inj_lvl_a_cnt": 0,
      "tot_ped_inj_cnt": 0,
      "tot_pedcycl_cnt": 0,
      "tot_pedcycl_fatal_cnt": 0,
      "tot_pedcycl_inj_lvl_a_cnt": 0,
      "tot_pedcycl_inj_cnt": 0,
      "tot_unknwn_cnt": 0,
      "tot_unknwn_fatal_cnt": 0,
      "tot_unknwn_inj_cnt": 0,
      "tot_occup_cnt": 2,
      "tot_per_invlv_cnt": 2,
      "tot_sfty_equip_used_qty": 1,
      "tot_sfty_equip_unused_qty": 0,
      "tot_sfty_equip_use_unknown_qty": 1,
      "tot_psngr_vhcl_occ_unrestrnd_fatal_cnt": 0,
      "tot_mcyclst_fatal_cnt": 0,
      "tot_mcyclst_inj_lvl_a_cnt": 0,
      "tot_mcyclst_inj_cnt": 0,
      "tot_mcyclst_unhelmtd_fatal_cnt": 0,
      "tot_alchl_impaired_drvr_inv_fatal_cnt": 0,
      "tot_drvr_age_01_20_cnt": 0,
      "lane_rdwy_dprt_crash_flg": "N"
    }

[bhgrant8]

@code-farmhouse when you are able could you check the current models/responses and see what you think?

Additional questions:

1. @znmeb mentioned adding geo data, where are we with this? Is it needed (i think so if we are comparing crashes with safety hotline tickets)
2. How do we need to filter, ie important categories, and types of filters,
3. For the participant and vehicle tables, I am returning the api link to the crash object vs. the actual object, does this work.
4. Performance, does take a few seconds to load data, is this efficient enough? I am prefetching related models for some gain, but can only do so much, without caching/getting more complex

[bhgrant8]

@code-farmhouse , @znmeb if we need to look at the crashes vs. safety hotline I believe we need the gis data for this? Will this be included in next db update?

[znmeb]

Yes - the crash data will be geotagged in the next update. Some rows will have a NULL geometry, however.