Extracting automobile fatality data from roadway.report API¶

roadway.report API docs at https://roadway.report/v1/docs

We want to fork over all data available on bicycle / micromobility fatalities in king county¶

We want to filter objects from the ACCIDENT table, based on records in the PERSON table, so we use the endpoint /v1/accidents_by_person

In [1]:
import requests
from time import sleep
API_URL = "https://roadway.report/v1/accidents_by_person"
CRASH_DETAILS_URL = "https://roadway.report/v1/accidents/"

List of "Person Types" found here https://roadway.report/v1/person_type_choices

In [2]:
BICYCLE_PERSON_TYPE = "6" #bicycle
PEDALCYCLE_PERSON_TYPE = "7" # other pedalcycle
PERSONAL_CONVEYANCE_PERSON_TYPE = "8" # Other Personal Conveyance

To find a county_id, click on a death in the correct county on https://roadway.report,

go to the accident_details page (example: https://roadway.report/accidents/2008530079/)

and then edit the URL to see the raw data (add "/v1/" and delete the trailing backslash --- OLD: https://roadway.report/accidents/2008530079/ -> NEW: https://roadway.report/v1/accidents/2008530079)

The county ID is displayed on the raw data page (example: https://roadway.report/v1/accidents/2008530079)

In [3]:
COUNTY_ID = "53033" # king county wa

injury severity choices https://roadway.report/v1/injury_severity_choices

In [4]:
INJURY_SEVERITY = "4" #fatal

Get lists of accidents containing fatally injured persons in the three person types considered "micromobility"

In [5]:
bicycle_fatalities = requests.get(f"{API_URL}?accident__county_id={COUNTY_ID}&person_type={BICYCLE_PERSON_TYPE}&injury_severity={INJURY_SEVERITY}")
pedalcycle_fatalities = requests.get(f"{API_URL}?accident__county_id={COUNTY_ID}&person_type={PEDALCYCLE_PERSON_TYPE}&injury_severity={INJURY_SEVERITY}")
personal_conveyance_fatalities = requests.get(f"{API_URL}?accident__county_id={COUNTY_ID}&person_type={PERSONAL_CONVEYANCE_PERSON_TYPE}&injury_severity={INJURY_SEVERITY}")
In [6]:
pedalcycle_fatalities.json()
Out[6]:
{'items': [{'id': 2008530348,
   'type': 'Feature',
   'properties': {'id': 2008530348,
    'datetime': '2008-08-06T18:53:00Z',
    'st_case': 530348,
    'fatalities': 1,
    'link': "<a href='/accidents/2008530348'>Details Here</a>"},
   'geometry': {'type': 'Point', 'coordinates': [-122.3515167, 47.6152472]}},
  {'id': 2020530530,
   'type': 'Feature',
   'properties': {'id': 2020530530,
    'datetime': '2020-09-14T12:50:00Z',
    'st_case': 530530,
    'fatalities': 1,
    'link': "<a href='/accidents/2020530530'>Details Here</a>"},
   'geometry': {'type': 'Point', 'coordinates': [-122.2125333, 47.2819722]}}],
 'count': 2}

Get the complete accident details for each of these incidents, and write them to a file

In [6]:
with open("king_county_micromobility_fatalities.json", "w") as f:
    f.write('{"type": "FeatureCollection", "features": [')
    for item in bicycle_fatalities.json()['items']:
        sleep(1)
        y = requests.get(f"{CRASH_DETAILS_URL}{item['id']}")
        f.write(y.text)
        f.write(",")
        print(f"bike death: https://roadway.report/v1/accidents/{item['id']}")
    for item in pedalcycle_fatalities.json()['items']:
        sleep(1)
        y = requests.get(f"{CRASH_DETAILS_URL}{item['id']}")
        f.write(y.text)
        f.write(",")
        print(f"other pedal death: https://roadway.report/v1/accidents/{item['id']}")
    for item in personal_conveyance_fatalities.json()['items']:
        sleep(1)
        y = requests.get(f"{CRASH_DETAILS_URL}{item['id']}")
        f.write(y.text)
        f.write(",")
        print(f"personal conveyance death: https://roadway.report/v1/accidents/{item['id']}")
    f.write(']}')

bike death: https://roadway.report/v1/accidents/2001530043
bike death: https://roadway.report/v1/accidents/2001530156
bike death: https://roadway.report/v1/accidents/2001530415
bike death: https://roadway.report/v1/accidents/2002530214
bike death: https://roadway.report/v1/accidents/2002530252
bike death: https://roadway.report/v1/accidents/2002530501
....

A file containing a GeoJSON object with all pertinent Cyclist Fatality data is now written to a file called king_county_micromobility_fatalities.json

In [12]:
with open('king_county_micromobility_fatalities.json', 'r') as f:
    print(f.read(1000))

{"type": "FeatureCollection", "features": [{"id": 2001530043, "type": "Feature", "properties": {"id": 2001530043, "st_case": 530043, "fatalities": 1, "state": {"id": 53, "name": "WASHINGTON"}, "county": {"id": 53033, "name": "KING"}, "city": {"id": 530331960, "name": "SEATTLE"}, "month": 2, "day": 27, "hour": 6, "minute": 0, "datetime": "2001-02-27T06:00:00Z", "latitude": 47.6941583, "longitude": -122.3444611, "number_of_vehicles": 0, "number_of_vehicles_in_transport": 1, "number_of_parked_vehicles": 0, "number_of_persons_in_motor_vehicles": 2, "number_of_persons_in_motor_vehicles_in_transport": 0, "number_of_persons_in_parked_vehicles": 2, "number_of_nonmotorists": 1, "number_of_persons_not_in_motor_vehicles_in_transport": 0, "trafficway_identifier_1": "SR-99", "trafficway_identifier_2": null, "route_signing": 3, "route_signing__display": "State Highway", "rural_urban": 2, "rural_urban__display": "Urban", "functional_system": 3, "functional_system__display": "Other Principal Arterial"