The Data

There are four data sets shown on this site.

  1. Seattle Department of Transportation (SDOT) Collisions
  2. Economic costs of collision severity
  3. Seattle neighborhood geospatial boundaries
  4. Seattle City Council District geospatial boundaries

Seattle Department of Transportation (SDOT) Collisions

According to description on the SDOT Collisions about page as of Sunday May 21, 2017 10:52pm PST, this data is described as…

“Displays the locations and attributes of collisions that occur within Seattle. All collisions are provided by the Seattle Police Department and Washington State Department of Transportation and are recorded by Traffic Records for the last 10 years. This includes all types of collisions. Collisions are shown at the intersection or mid-block of a street segment that is closest to where it occurred.”

There is a PDF that describes the data fields available. There is also a json feed to the data provided by Socrata. Socrata is the company hired by the city to host their open data. They provide the platform & software. However, it is up to the city to use Socrata, enter the data, make sure the data is accurate, and answer questions about the data.

Building this site required combining the knowledge stored in these two different places. The knowledge stored in the PDF and the knowledge of how to use Socrata’s API. The PDF from SDOT tells us what the data means and Socrata’s developer portal tells us how to access the data and what format it is stored in.

Economic costs of collision severity

Every year the National Safety Council (NSC) publishes their Injury Facts® report. This report includes an estimated “Cost of Motor-Vehicle Injuries”. Here is a sample of a Cost of Motor-Vehicle Injuries report. The National Safety Council categorizes collisions with the following six levels of severity.

  1. Death
  2. Disabling
  3. Evident
  4. Possible
  5. No injury observed
  6. Property damage only

The data from the Seattle Department of Transportation (SDOT) has the following five levels of severity.

  1. Fatality collision (sdot code 3)
  2. Serious injury collision (sdot code 2b)
  3. Injury (sdot code 2)
  4. Unknown (sdot code 0)
  5. Property damage only collision (sdot code 1)

We mapped the NSC severities to the SDOT severities as follows.

  1. Death (nsc) -> Fatality collision (sdot code 3)
  2. Disabling (nsc) -> Serious injury collision (sdot code 2b)
  3. Evident (nsc) -> Injury (sdot code 2)
  4. Possible (nsc) -> Unknown (sdot code 0)
  5. Property damage only (nsc) -> Property damage only collision (sdot code 1)

The National Safety Council also has TWO sets of estimated costs, “economic” and “comprehensive” costs. In the sample report the National Safety Council says the following about the meaning of “Economic Costs”.

“The figures above are appropriate for measuring the economic loss to a community resulting from past motor-vehicle crashes. They should not be used, however, in computing the dollar value of future benefits due to traffic safety measures because they do not include the value of a person’s natural desire to live longer or to protect the quality of one’s life. That is, the economic loss estimates do not include what people are willing to pay for improved safety.”

So, the “economic cost” is basically the cost of the past damage done by collisions where comprehensive costs includes a person’s desires for the future. Notice in the sample report, the economic costs are lower than the comprehensive cost since economic costs do not include benefits gained from safety improvement investments and changes in quality of life of those involved in a collision.

Cathy Tuttle recommended we use the more conservative economic costs number. Below is a table for the economic costs used for the calculations in this application. The headings are the NSC label / SDOT label. It would be nice to have the ability to switch between economic & comprehensive costs. I have this on my to do list.

Seattle neighborhood geospatial boundaries

The data provided by the Seattle Department of Transportation includes latitude & longitude coordinates for a collision. However, we don’t know what neighborhood these coodinates fall in. I wanted to be able to view collisions by neighborhood so I had to find neighborhoood geospatial boundaries. Turns out that Zillow has neighborhood boundaries in shapefiles. However, this does me little good since I need the coordinates in a format that I can store in my database.

After some searching on GitHub I found a repository by Mashvisor that has the Zillow shape files converted into GeoJSON and CSV. The .csv file for Washington State is the one I used to create the multipolygon datatypes in my database.

Seattle City Council geospatial boundaries

Cathy & Gordon mentioned that it would be interesting to see collisions by district. I attened an Open Seattle meetup and Jackson Maxfield introduced me to Seattle.io. I don’t know why I didn’t come across this before! Turns out there are a bunch of boundaries available here including City Council Districts. Yay!

With some refactoring from my code to create the neighborhood boundaries, I imported the City Council District boundaries, created similar API endpoints, and similar views. I’d like to make these district views better. Perhaps show a list of neighborhoods within each district!