The Oregon Department of Transportation (ODOT) collects and provides data regarding crash information on state and local highways in Oregon. For this lab deliverable, you are to select a highway or local road of your choice, download the crash data, an

1/3

MEMORANDUM

To: CE112 Students

From: Evan Kristof

Date: April 26, 2015

Subject: Lab 2: ODOT Crash Data Analysis

The Oregon Department of Transportation (ODOT) collects and provides data regarding crash

information on state and local highways in Oregon. For this lab deliverable, you are to select a highway

or local road of your choice, download the crash data, and then provide a summary of some of the data in

a memo.

Please follow these instructions to complete your deliverable:

1. Choose and Download Crash Data

a. Go to the ODOT data portal at https://zigzag.odot.state.or.us

i. Note: if you have trouble accessing the site, try using Internet Explorer

ii. Note: if the link asks you to download and install Java, you may choose to either

install it or “continue … with limited site functionality”

b. Click on the “Crash Data System” link (see Figure 1 below).

Figure 1: Link to Crash Data System

c. Select a Highway Segment by selecting a highway from the dropdown box (see below).

You may click on the “Cross-Reference” link to find out the common names for state

highways. You may adjust the milepost ranges if you would like to (see Figure 2 below).

Figure 2: Highway Segment Options

d. You may change the “Roadway Type” and “Date Range” as you see fit. Note that the

more miles and dates included may result in a very large file.

CE112 Lab 2 Instructions Spring 2015

2/3

e. Select “Excel Format” under “Select a Report Format” (see Figure 3 below).

Figure 3: Report Format Options

f. Select “Comprehensive” under “Select a Report” to download the data (See figure 4

below).

Figure 4: Report Selection Options

2. Open and Interpret Crash Test Data

a. Open the Excel file and note the large amount of data column and rows. Many of these

data points have abbreviated or cryptic descriptions. To begin to interpret some of the

descriptions, refer to the links on the left side of the crash data website: “Crash Data

System Code Manual” (see Figure 5 below).

Figure 5: Crash Data Reference Links

CE112 Lab 2 Instructions Spring 2015

3/3

b. You may also download the “Data Fields & Formats” Excel file at the bottom of the

webpage, which includes descriptions for each data column (See Figure 6).

Figure 6: Data Field & Format Link

3. Choose and Analyze Crash Data

a. Choose three data types (columns) to analyze. Examples of data types includes: crash

severity, weather conditions, location, or time.

b. Choose how you want to analyze the data using functionality learned in class. For

example, you could use a function to determine minimum, maximum, frequency/count,

mode, average, etc. of the data. Make the analysis meaningful.

i. The following sections of your Walkenbach textbook may be helpful:

1. Tables (ch. 5)

2. Pivot Tables (chs. 33, 34)

3. Hiding Rows and Columns (p. 66)

4. Printing Row and Column Titles (pp. 182-183)

5. Worksheet Function Reference (App. A)

4. Write a Memo Summarizing Your Analysis and Conclusions

a. Write a memo to the instructor that provides/includes the following:

i. Describe the highway segment, date range, and milepost range that you selected.

Explain why you selected the segment.

ii. Describe the types of data that you analyzed and why.

iii. Explain how you analyzed the data (give specific examples/descriptions of the

functions that you used).

iv. Summarize the conclusions from your analysis.

v. Attach a PDF printout of the Excel file with the data and your analysis to your

memo. Format the table so that it is user-friendly to review and focuses on the

relevant data. Some formatting suggestions are provided below:

1. Format the table using Excel’s built in styles (Walkenbach pp. 105-106)

2. Hide columns with unnecessary information (Walkenbach p. 66)

3. Provide a descriptive header and footer (Walkenbach pp. 186-188)

b. Make sure to follow the memo formatting guidelines when drafting and preparing the

memo.

c. Upload the PDF(s) file to the D2L dropbox by Monday, May 18th at 5pm.