Roadway Safety Data Program
 

ARIZONA IMPORTING LOCAL AND TRIBAL DATA FOR
 

SAFETY ANALYSIS
 

ROADWAY SAFETY DATA AND ANALYSIS
 

CASE STUDY
 

FHWA-SA-16-061
 

Prepared for
 

Federal Highway Administration Office of Safety
 

Roadway Safety Data Program
 

http://safety.fhwa.dot.gov/rsdp/
 

October 27, 2016
 
IMPORTING LOCAL AND TRIBAL DATA FOR SAFETY ANALYSIS 

Notice 

This document is disseminated under the sponsorship of the U.S. Department of 

Transportation in the interest of information exchange. The U.S. Government assumes no 

liability for the use of the information contained in this document. 

The U.S. Government does not endorse products or manufacturers. Trademarks or 

manufacturers’ names appear in this report only because they are considered essential to the 

objective of the document. 

Quality Assurance Statement 

The Federal Highway Administration (FHWA) provides high-quality information to serve 

Government, industry, and the public in a manner that promotes public understanding. 

Standards and policies are used to ensure and maximize the quality, objectivity, utility, and 

integrity of its information. FHWA periodically reviews quality issues and adjusts its programs 

and processes to ensure continuous quality improvement. 

i
 

IMPORTING LOCAL AND TRIBAL DATA FOR SAFETY ANALYSIS 

TECHNICAL DOCUMENTATION PAGE
 

1. Report No. 

FHWA-SA-16-061 

2. Government Accession 

No. 

3. Recipient's Catalog No. 

4. Roadway Safety Data and Analysis Case Study: Arizona Importing Local and 

Tribal Data for Safety Analysis 

5. Report Date 

October 2016 

6. Performing Organization Code 

7.Author(s) 

Richard Brown and Robert Scopatz 

8. Performing Organization Report No. 

9. Performing Organization Name and Address 

VHB 

8300 Boone Blvd., Suite 700 

Vienna, VA 22182-2626 

10. Work Unit No. 

11. Contract or Grant No. 

DTFH61-10-D-00022 

Task Order T-13-003 

12. Sponsoring Agency Name and Address 

Federal Highway Administration Office of Safety 

1200 New Jersey Ave., SE 

Washington, DC 20590 

13. Type of Report and Period 

Case Study 

14. Sponsoring Agency Code 

FHWA 

15. Supplementary Notes 

The contract manager for this report was Esther Strawder. 

16. Abstract

 The Arizona Department of Transportation (ADOT) and the Federal Highway Administration (FHWA) Office of Safety 

completed a pilot project that allows the State to acquire roadway data elements from local and Tribal agencies and load the 

data into the Arizona Transportation Information System (ATIS) database. ADOT created a new process that allowed 

business users to import the data from local agencies, export combined State and local/tribal data, and load data into 

AASHTOWare Safety AnalystTM for analysis. ADOT used contractor support to establish data integration procedures for 

crash, roadway, and traffic volume data for both roadway segments and intersections. The results from the pilot created 

processes for the data submissions to ADOT, data integration in ATIS, the ETL process for bringing data into 

AASHTOWare Safety AnalystTM, and quality control checks prior to analysis. 

17. Key Words: 

safety data, roadway inventory data, traffic data, 

data integration 

18. Distribution Statement 

No restrictions. 

19. Security Classif. (of this report) 

Unclassified 

20. Security Classif. (of this 

page) Unclassified 

21. No. of Pages

 ## 

22. Price 

Form DOT F 1700.7 (8-72) Reproduction of completed pages authorized 

ii
IMPORTING LOCAL AND TRIBAL DATA FOR SAFETY ANALYSIS 

TABLE OF CONTENTS
 

INTRODUCTION ....................................................................................... 1
 

BACKGROUND .......................................................................................... 1
 

OBJECTIVE.................................................................................................. 2
 

AUDIENCE .................................................................................................. 3
 

DEVELOPMENT OF THE ETL PROCESS ............................................... 3
 

LOCAL AGENCY DATA COLLECTION AND EVALUATION............ 4
 

BENEFITS TO USING AN ETL PROCESS FOR DATA IMPORT......... 7
 

TECHNICAL BARRIERS ............................................................................ 7
 

RESULTS...................................................................................................... 8
 

NEXT STEPS............................................................................................... 8
 

REFERENCES .............................................................................................. 9
 

AGENCY CONTACT INFORMATION.................................................... 9
 

iii
List of Figures 

 

IMPORTING LOCAL AND TRIBAL DATA FOR SAFETY ANALYSIS 

List of Tables
 

Table 1. Local agency summary of segment data submissions. ...................................6
 

Table 2. Ramp Configuration Table ................................................................................9
 

Figure 1. Extract, Transform, Load (ETL) process for Arizona Safety Data..............4
 

iv
 
 

IMPORTING LOCAL AND TRIBAL DATA FOR SAFETY ANALYSIS 

ACRONYMS 

ADOT Arizona Department of Transportation 

ATIS Arizona Transportation Information System 

BIA Bureau of Indian Affairs 

CSV Comma separated value 

ETL Export, transform, and load 

LTAP Local Technical Assistance Program 

MAG Maricopa Association of Governments 

MPO Metropolitan Planning Organization 

Navajo DOT Navajo Division of Transportation 

PAG Pima Association of Governments 

RCI Roadway Characteristics Inventory 

SQL Structured query language 

TTAP Tribal Technical Assistance Program 

v
 

IMPORTING LOCAL AND TRIBAL DATA FOR SAFETY ANALYSIS 

EXECUTIVE SUMMARY
 

The Arizona Department of Transportation (ADOT) and the Federal Highway Administration 

(FHWA) Office of Safety completed a pilot project that allows the State to acquire roadway 

data elements from local and Tribal agencies and load the data into the Arizona Transportation 

Information System (ATIS) database. ADOT created a new process that allowed business users 

to import the data from local agencies, export combined State and local/tribal data, and load 

data into AASHTOWare Safety AnalystTM for analysis. ADOT used contractor support to 

establish data integration procedures for crash, roadway, and traffic volume data for both 

roadway segments and intersections. The results from the pilot created processes for the data 

submissions to ADOT, data integration in ATIS, the process for bringing data into 

AASHTOWare Safety AnalystTM, and quality control checks prior to analysis. 

vi
 

 

IMPORTING LOCAL AND TRIBAL DATA FOR SAFETY ANALYSIS 

INTRODUCTION
 

The Arizona Department of Transportation (ADOT) and the Federal Highway Administration 

(FHWA) Office of Safety completed a pilot project that allows the State to acquire roadway 

data elements from local and Tribal agencies and load the data into the Arizona Transportation 

Information System (ATIS) database. Using a previous plan developed for ADOT (A Feasibility 

Study for Arizona’s Roadway Safety Management Process Using the Highway Safety Manual and Safety 

Analyst), the project team created a new process that allowed business users to import the data 

from local agencies and export combined State and local/tribal data for analysis. 

For this project, the State identified the following four agencies that agreed to supply data for 

the integration effort: 

1. Bureau of Indian Affairs (BIA) 

2. Maricopa Association of Governments (MAG) 

3. Navajo Division of Transportation 

4. Pima Association of Governments (PAG) 

It is important to note that of the four agencies identified, MAG and PAG are Metropolitan 

Planning Organizations (MPOs), meaning they do not own data, but instead receive data from 

local agencies in their jurisdiction. BIA is a federal agency and is part of the United States 

Department of the Interior. The Navajo DOT is also a federal agency (all land-based Tribes are 

sovereign nations), but functions similarly to a State DOT. 

Out of the four, PAG was the only agency with data suitable for import and integration. The 

project was designed to integrate the local agencies’ data with existing State data, then export, 

transform, and load (ETL) the integrated data into AASHTOWare Safety AnalystTM. In the end, 

PAG data were integrated with the State’s data. Recommendations for integrating other local 

and Tribal data were provided to the State for future action. 

ADOT used contractor support to establish data integration procedures for crash, roadway, 

and traffic volume data for both roadway segments and intersections. The results from the pilot 

created processes for the data submissions to ADOT, data integration in ATIS, the ETL process 

for bringing data into AASHTOWare Safety AnalystTM, and quality control checks prior to 

analysis. 

BACKGROUND 

This project arose as a pilot study sponsored by FHWA’s Office of Safety as part of the 

Integration of State and Local Safety Data project. ADOT submitted a successful application for 

1
 
 

 

 

IMPORTING LOCAL AND TRIBAL DATA FOR SAFETY ANALYSIS 

the pilot study and received technical assistance from a consultant team consisting of IT project 

managers, GIS specialists, and database managers with data integration expertise. ADOT 

identified partner agencies based on past experiences with them and their own interests in 

sharing data with the State. The Navajo DOT was involved in a concurrent pilot study 

integrating data from multiple agencies on Navajo Tribal lands and agreed to share the results 

with ADOT. 

The four contributing agencies represent a range of capabilities and abilities to supply data to 

ADOT in a specified format. By design, the project makes note of barriers encountered as part 

of the attempts to integrate, upload, and analyze safety data. The ADOT project lead and 

consultant team worked with representatives from each of the four agencies to obtain the 

source data. ADOT sent the partner agencies a spreadsheet requesting information about road 

segments, traffic volume counts, intersections and ramps, and crash data. ADOT was 

responsible for data validation and corrections within their own system, and providing 

additional resources such as an intersection and ramp inventory. The consultant team was 

responsible for data integration, ETL, and validation within AASHTOWare Safety AnalystTM. 

The contributing agencies were responsible for addressing their own data quality issues where 

possible. Most of the local and Tribal data quality issues were not resolved as part of the pilot. 

Where errors persisted and blocked selected data from being used in the pilot, the contractor 

team documented the deficiencies and suggested methods for addressing the problems in the 

future. 

OBJECTIVE 

The ADOT pilot project was designed to achieve the following: 

•	 Create a method to integrate data from local agencies into the statewide crash, 

roadway, and traffic volume databases. 

•	 Create a method for data to be extracted from multiple sources, transformed, and 

loaded into AASHTOWare Safety AnalystTM. 

•	 Create an ETL process that requires very little interaction by the user. The State 

desired a solution that was as automated as possible, and with as few steps as possible 

so that it would be both reliable and repeatable without extensive training or staff time. 

•	 Develop a customized ETL process and software tools that would work with ADOT’s 

existing databases. 

•	 Establish methods for data validation that would satisfy the data quality requirements of 

AASHTOWare Safety AnalystTM and make the best use of the integrated data for 

network screening. 

2
 
IMPORTING LOCAL AND TRIBAL DATA FOR SAFETY ANALYSIS 

AUDIENCE 

This case study applies to the following audiences: 

•	 State Departments of Transportation: Roadway Data System Managers, Safety
 

Engineers, GIS specialists, and IT project managers.
 

•	 Local Agencies: Regional Planning Commissions, Metropolitan Planning Organizations, 

cities, and counties, plus Local Technical Assistance Programs (LTAPs). 

•	 Tribal Agencies: including Tribal governments, public agencies responsible for 

transportation safety data in Tribal areas, and Tribal Technical Assistance Programs 

(TTAPs). 

•	 Consultants and private businesses involved with safety data analysis and GIS. 

DEVELOPMENT OF THE ETL PROCESS 

An ETL process takes data from one or more sources and transforms it into a required format 

for other uses (e.g., performing analyses). AASHTOWare Safety AnalystTM includes a very strict 

set of data validations for data import. The ADOT pilot effort sought to create a process that 

an end user could complete with very few steps and with minimal interaction. The consultant 

team accomplished this by using structured query language (SQL) views. SQL views can act as a 

virtual table that can be manipulated instead of making changes to the real database tables. 

Through scripting, these functions can be performed automatically for the user. The user only 

needs to know the desired data source for the analysis (e.g., a specific date range or portion of 

the State) in order to create the necessary data extract and obtain a desired analytic output. 

Figure 1 displays the ETL process for Arizona’s ATIS data with integrated local data using 

AASHTOWare Safety AnalystTM as the analytic tool. 

3
 
 

IMPORTING LOCAL AND TRIBAL DATA FOR SAFETY ANALYSIS 

Figure 1. Chart. Extract, Transform, Load (ETL) process for Arizona Safety Data. 

The ETL process is demonstrated as follows: 

•	 The Data Extraction and Transformation component collects the data elements from 

the ATIS database. The view converts the value to one that AASHTOWare 

SafetyAnalystTM recognizes. In Figure 1, the view uses the location field values (i.e. 

RouteID, FromMeasure, and ToMeasure) for every row identified in the ATIS table. 

•	 The Overlay component merges the segments for each attribute for the events and 

creates a union of the inputs. 

•	 The Aggregate component standardizes the road inventory data and ensures proper 

segmentation of the roadway. 

•	 The data originally from ATIS and the local data is compiled into comma separated value 

(CSV) files based on feature type. 

•	 The data is formatted and loaded for import into AASHTOWare Safety AnalystTM 

LOCAL AGENCY DATA COLLECTION AND EVALUATION 

The data integration step in Figure 1 brought local agency and Tribal data into a CSV file 

suitable for uploading into AASHTOWare Safety AnalystTM. The CSV file included State data 

4
 
 

IMPORTING LOCAL AND TRIBAL DATA FOR SAFETY ANALYSIS 

from ATIS and data from the local and Tribal agencies. Data included segment identifiers and 

inventory attributes, traffic volume, and crash data.. 

Table 1 presents a portion of the Local Agency summary of responses for the data integration 

pilot. This summary document provides a snapshot of the data the four agencies provided and 

what is useful for import. The four agencies differed widely in their capabilities and in the 

success with which the project team could integrate their data with the State’s data. While 

MAG, Navajo DOT, and BIA provided roadway information, PAG had a more comprehensive 

file that better met the needs for integration and analysis. Most importantly for ease of 

integration, PAG uses Route/Milepost for its location referencing system as compared to 

Route/Roadway Section. PAG’s location coding is similar to the way that ATIS references 

locations, which greatly simplified the task of integrating this local dataset with the State data. 

Also the PAG dataset included a number of required fields such as road name, classification, 

category, and travel direction that were missing in the others. However, the PAG data set 

lacked traffic volume data, which are useful for analysis. Since their data was more complete and 

better aligned with the State location coding system, PAG was the only local agency used for 

the ETL process in the attempt to load integrated data into AASHTOWare Safety AnalystTM. 

5
 
 

 

 

 

IMPORTING LOCAL AND TRIBAL DATA FOR SAFETY ANALYSIS 

Table 1. Local agency summary of segment data submissions. 

Item General Description SaA 

Required? 

SaA Data 

Import 

Ref. 

SaA Data 

Type 

# of 

Agencies 

providing 

the data 

element 

Segment 

Identifier 

Unique identifier for a section 

of roadway 

Yes 2.1.1 varchar(128) 3 

Route 

Number 

Signed route number Yes 2.1.4 varchar(128) 4 

Major Road 

Name 

Route or street name, where 

different from Route Number 

No 2.1.12 varchar(128) 4 

Location 

System 

Location reference method Yes 2.1.2 varchar(128) n/a 

Begin 

Segment 

Location 

Location reference Yes 2.1.7 varchar(128) 4 

End Segment 

Location 

Location of the end of the 

segment 

Yes 2.1.8 varchar(128) 4 

Route Type Category of the route 

segment 

Yes 2.1.3 varchar(128) 0 

Area Type Characterize the area as rural 

or urban 

Yes 2.1.17 varchar(128) 0 

Segment 

Length 

Length of the roadway 

segment in miles 

Yes 2.1.13 numeric 3 

Direction of 

Travel 

Direction of travel on 

unidirectional segments 

Yes 2.1.45 varchar(128) 2 

Functional 

Class 

Functional class of the 

segment 

No 2.1.19 varchar(128) 2 

Median Type Type of median Yes 2.1.30 varchar(128) 0 

Access 

Control 

Degree of access control Yes 2.1.40 varchar(128) 2 

One/TwoWay Whether the segment 

operates as a one- or twoway roadway 

Yes 2.1.44 varchar(128) 0 

Ownership Type of governmental 

ownership 

No 2.1.16 varchar(128) 3 

Number of 

Through 

Lanes 

Separately for each direction 

of travel, the number of 

through lanes, excluding turn 

lanes and auxiliary lanes 

Yes 2.1.20 integer 1 

Interchange 

Influence 

Whether segment is within 

interchange influence area 

Yes 2.1.49 varchar(128) 0 

*SaA is AASHTOWare Safety AnalystTM 

6
 
IMPORTING LOCAL AND TRIBAL DATA FOR SAFETY ANALYSIS 

BENEFITS TO USING AN ETL PROCESS FOR DATA IMPORT 

The requirement for data formatting within AASHTOWare Safety AnalystTM yields itself to 

using an ETL process. Here are the benefits uncovered in creating the integrated data set: 

•	 Provides the roadway data system with a structured approach for taking data sets from 

multiple sources and populating a single system, in this case AASHTOWare Safety 

AnalystTM. 

o	 2009 to 2014 source files including State and PAG data yielded the following 

total numbers of records imported: 

 26,038 Roadway segments totaling 17,452 mi. 

 156,228 Traffic volume records. 

 213,802 Accident records. 

o	 51,361 road segments from the PAG data were imported into the system with 

only 2 warnings. 

•	 Creates a standardized platform that will allow future integration of other data sets as 

they become available. 

•	 Requires very little user effort for loading very large data sets and integrating them into 

the statewide system. 

•	 Simplifies the data integration processes so users can easily understand the steps and 

data movements. 

•	 Identifies errors on import which centralizes the error checking / warning into a single 

location and step in the process. 

TECHNICAL BARRIERS 

The largest technical barrier was preparing the integrated State and local data to the point that 

AASHTOWare Safety AnalystTM would accept it without errors. The software validates each 

file in its entirety and rejects the entire dataset if there are unresolved errors. As a result, all 

data cleansing must happen within the State’s database. Data analysis can only be conducted 

when the dataset passes all validation checks. Some unanticipated errors were found in each of 

the source files. For ADOT’s data, roadway segmentation was initially derived from the 

Roadway Characteristics Inventory (RCI). Data integration, however, caused segment 

problems, necessitating a switch to ATIS as the source file. ADOT had to correct the source to 

eliminate overlaps in start and end points of adjacent segments and after that data cleansing, the 

majority of segments passed the validation checks. The consultant team was unable to use 

intersection and ramp data because key data elements were missing or in the wrong format. 

The consultant team provided ADOT with a plan for how to improve those data sources. 

7
 
 

IMPORTING LOCAL AND TRIBAL DATA FOR SAFETY ANALYSIS 

After data cleansing of the segment information, AASHTOWare Safety AnalystTM reported few 

errors, but thousands of warnings. The State’s project team categorized these errors into 

categories that simplified the process of updating and rechecking through the SQL Views and 

scripts that bound the ETL process. These revisions helped to inform the State’s data 

management for segment definitions and roadway attributes so that the file can be maintained 

more effectively in the future. 

RESULTS 

The ETL process was able to import the roadway segment, traffic, and accident data into 

AASHTOWare SafetyAnalystTM. The combined database contained 26,038 segments of 

roadway data totaling 17,472 miles. Crash data was combined with only those road segments 

with associated traffic data. The result was 213,802 crash records available for analysis. For the 

local data integration, SafetyAnalyst accepted 51,136 segments’ data from PAG. This is, 

however, in addition to PAG segments previously integrated into ATIS. PAG data can serve as a 

model for future attempts to integrate data from other local or Tribal agencies. 

NEXT STEPS 

The ETL process developed in this pilot project creates a standardized platform for future data 

sets. The successful integration of one local data source indicates that integration is possible for 

others as well, using the same ETL process. The State also learned that the ETL process will 

work for intersections but that the following data elements will be required: 

•	 The point location of at-grade intersections geocoded in the same linear referencing 

system as the roadway segment data; 

•	 Identification of two distinct routes that make up the intersection, with designation of 

which route is the major route and which is the minor route; 

•	 Traffic volume (annual average daily traffic) for each of the two routes at the intersection; 

•	 Intersection type using AASHTOWare Safety AnalystTM enumerated values, indicating 

whether the intersection is configured as a “T”, “Y”, 4-leg, 5 or more legs, or roundabout; 

•	 A unique identifier for the intersection. 

To import ramp data, an additional field needs to be added to ATIS for the ramp configuration. 

Table 2 displays the ramp configuration codes and values needed in ATIS. 

8
 
IMPORTING LOCAL AND TRIBAL DATA FOR SAFETY ANALYSIS 

Table 2. Ramp Configuration Table 

Code Ramp Configuration 

1 Diamond 

2 Parclo loop 

3 Free-flow loop 

4 Free-flow outer connection 

5 Direct or semi-direct connection 

6 C-D road or other connection 

0 Other 

99 Unknown 

Acquiring the necessary data for other local and Tribal agency segments will require additional 

cooperative agreements between ADOT and the participating agencies. Obtaining the necessary 

intersection and ramp data statewide is a large undertaking that ADOT will need to plan for the 

future. 

REFERENCES 

The following resources were used in development of this case study: 

Cambridge Systematics. A Feasibility Study for Arizona’s Roadway Safety Management Process Using 

the Highway Safety Manual and SafetyAnalyst. 2012. 

AGENCY CONTACT INFORMATION 

Pradeep Tiwari 

Transportation Safety Engineer 

Traffic Safety Section, ITD 

1615 W. Jackson St, Mail Drop 065R 

Phoenix, AZ 85007 

Phone: 602.712.4472, Fax: 602.712.3243 

9