Informational Guide for State, Tribal, and Local Safety Data Integration

172 Pages·2016·14.6 MB·English

Informational Guide for State,Tribal,
and Local Safety Data Integration
FHWA Safety Program FHWA-SA-16-118
November 2016
http://safety.fhwa.dot.gov
INFORMATIONAL GUIDE FOR STATE, TRIBAL, AND LOCAL SAFETY DATA INTEGRATION

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.
INFORMATIONAL GUIDE FOR STATE, TRIBAL, AND LOCAL SAFETY DATA INTEGRATION
TECHNICAL DOCUMENTATION PAGE

1. Report No. 2. Government Accession 3. Recipient's Catalog No.
No.
FHWA-SA-16-118
4. Title and Subtitle 5. Report Date
Informational Guide for State, Tribal, and Local Safety Data Integration October 31, 2016
6. Performing Organization Code
7.Author(s) 8. Performing Organization Report No.
Scopatz, R.A., Goughnour, E., Abbott, D., Tang, E., Carter, D., Smith, S., and
Salzer, T.
9. Performing Organization Name and Address 10. Work Unit No.
VHB
11. Contract or Grant No.
8300 Boone Blvd., Suite 700
DTFH61-10-D-00022 (VHB)
Vienna, VA 22182-2626
Task 13-006
12. Sponsoring Agency Name and Address 13. Type of Report and Period
Federal Highway Administration: Office of Safety Informational Guide
1200 New Jersey Ave., SE August 2013 – August 2016
Washington, DC 20590
14. Sponsoring Agency Code
FHWA
15. Supplementary Notes
The contract manager for this report was Stuart Thompson.
16. Abstract
This is an Informational Guide for State, Tribal, and local safety data integration. This Informational Guide provides
guidance to States, Tribes, and local agencies on the steps and effective methods of safety data integration. This
Informational Guide documents practices within a nine-step process for data integration. Detailed descriptions of the nine
data integration steps are provide as are recommendations to achieve integration. The steps in this Informational Guide help
agencies meet their needs for a flexible approach to data integration. Readers are encouraged to take those portions most
relevant to their needs in order to develop and implement their own safety data integration projects. A State, Tribe, or local
agency does not have to adopt all the activities described in the Informational Guide.
17. Key Words: 18. Distribution Statement
safety data, data integration, roadway inventory No restrictions.
data, traffic data, crash data
19. Security Classif. (of this report) 20. Security Classif. (of this 21. No. of Pages 22. Price
Unclassified page) Unclassified 169
Form DOT F 1700.7 (8-72) Reproduction of completed pages authorized
INFORMATIONAL GUIDE FOR STATE, TRIBAL, AND LOCAL SAFETY DATA INTEGRATION

ii
INFORMATIONAL GUIDE FOR STATE, TRIBAL, AND LOCAL SAFETY DATA INTEGRATION
TABLE OF CONTENTS

LIST OF TABLES ...................................................................................... vii

LIST OF FIGURES ................................................................................... VIII

ACRONYMS ............................................................................................... X

EXECUTIVE SUMMARY .......................................................................... XI

OVERVIEW .................................................................................................. 1

WHAT IS DATA INTEGRATION? ............................................................................ 2

WHY SHOULD AGENCIES CONSIDER DATA INTEGRATION? ....................... 3

AUDIENCE FOR THE INFORMATIONAL GUIDE ................................................. 5

ORGANIZATION OF THE INFORMATIONAL GUIDE ......................................... 6

Case Studies ................................................................................................................ 7

Pilot Studies and Additional Work Plans ................................................................. 8

DATA INTEGRATION CONCEPTS .......................................................................... 8

Data Program Concepts ............................................................................................ 8

METADATA ................................................................................................................ 11

Data Dictionary ......................................................................................................... 11

Data Flows ................................................................................................................. 14

Data Access Rights ................................................................................................... 15

Data Quality and Standards .................................................................................... 17

Data Retention .......................................................................................................... 18

Multiple Users Benefit from Integration ................................................................ 19

User Support ............................................................................................................. 20

SUMMARY ................................................................................................................... 20

STEP 1 – LAY THE FOUNDATION ....................................................... 21

OBJECTIVES ............................................................................................................... 21

CHALLENGES ............................................................................................................ 23

RECOMMENDED SOLUTIONS ............................................................................... 24

iii
INFORMATIONAL GUIDE FOR STATE, TRIBAL, AND LOCAL SAFETY DATA INTEGRATION
LESSONS LEARNED .................................................................................................. 25

SUMMARY ................................................................................................................... 26

CHECKLIST ................................................................................................................. 26

STEP 2 – CONDUCT GAP ANALYSIS .................................................. 28

OBJECTIVES ............................................................................................................... 28

CHALLENGES ............................................................................................................ 28

RECOMMENDED SOLUTIONS ............................................................................... 30

LESSONS LEARNED .................................................................................................. 31

CHECKLIST ................................................................................................................. 32

SUMMARY ................................................................................................................... 32

STEP 3 – ESTABLISH DATA GOVERNANCE PROCESS .................... 33

OBJECTIVES ............................................................................................................... 33

CHALLENGES ............................................................................................................ 35

RECOMMENDED SOLUTIONS ............................................................................... 37

LESSONS LEARNED .................................................................................................. 40

CHECKLIST ................................................................................................................. 41

SUMMARY ................................................................................................................... 41

STEP 4 – DEVELOP DATA COLLECTION AND INTEGRATION

PLAN .......................................................................................................... 43

OBJECTIVE .................................................................................................................. 43

CHALLENGES ............................................................................................................ 44

RECOMMENDED SOLUTIONS ............................................................................... 45

LESSONS LEARNED .................................................................................................. 47

SUMMARY ................................................................................................................... 48

CHECKLIST ................................................................................................................. 48

STEP 5 – IDENTIFY TRAINING NEEDS FOR DATA COLLECTION,

INTEGRATION, AND ANALYSIS .......................................................... 50

OBJECTIVES ............................................................................................................... 50

iv
INFORMATIONAL GUIDE FOR STATE, TRIBAL, AND LOCAL SAFETY DATA INTEGRATION
CHALLENGES ............................................................................................................ 51

RECOMMENDED SOLUTIONS ............................................................................... 52

LESSONS LEARNED .................................................................................................. 53

SUMMARY ................................................................................................................... 54

CHECKLIST ................................................................................................................. 54

STEP 6 – PERFORM DATA INTEGRATION ......................................... 56

OBJECTIVES ............................................................................................................... 56

CHALLENGES ............................................................................................................ 56

RECOMMENDED SOLUTIONS ............................................................................... 58

LESSONS LEARNED .................................................................................................. 59

SUMMARY ................................................................................................................... 60

CHECKLIST ................................................................................................................. 60

STEP 7 – DEVELOP AND DEPLOY THE EXTRACT, TRANSFORM,

AND LOAD PROCESS ............................................................................. 62

OBJECTIVES ............................................................................................................... 62

Data Extraction Process .......................................................................................... 62

Data Transformation ................................................................................................ 64

Data Load .................................................................................................................. 64

GIS-Based Data Integration .................................................................................... 65

Spatial Data Analysis, Filters, and Extraction ....................................................... 66

CHALLENGES ............................................................................................................ 69

RECOMMENDED SOLUTIONS ............................................................................... 70

LESSONS LEARNED .................................................................................................. 70

CHECKLIST ................................................................................................................. 71

SUMMARY ................................................................................................................... 71

STEP 8 – CONDUCT ANALYSES ........................................................... 74

OBJECTIVES ............................................................................................................... 74

Types of Safety Analyses .......................................................................................... 75

How to Choose the Right Tool? .............................................................................. 76

v
INFORMATIONAL GUIDE FOR STATE, TRIBAL, AND LOCAL SAFETY DATA INTEGRATION
CHALLENGES ............................................................................................................ 76

RECOMMENDED SOLUTIONS ............................................................................... 77

LESSONS LEARNED .................................................................................................. 79

CHECKLIST ................................................................................................................. 79

SUMMARY ................................................................................................................... 79

STEP 9 – PERFORM EFFECTIVENESS EVALUATION ........................ 81

OBJECTIVES ............................................................................................................... 81

CHALLENGES ............................................................................................................ 81

RECOMMENDED SOLUTIONS ............................................................................... 82

LESSONS LEARNED .................................................................................................. 82

CHECKLIST ................................................................................................................. 83

SUMMARY ................................................................................................................... 83

CONCLUSION .......................................................................................... 84

BENEFITS OF USING THIS INFORMATIONAL GUIDE ..................................... 85

REFERENCES ............................................................................................ 86

APPENDIX A – PILOT STUDY REPORTS ............................................ 88

APPENDIX B – ADDITIONAL WORK PLANS ................................... 114

APPENDIX C – ROADWAY SAFETY DATA AND ANALYSIS

TOOLBOX ............................................................................................... 124

APPENDIX D – DATA SHARING AGREEMENT AND

MEMORANDUM OF UNDERSTANDING ........................................... 127

APPENDIX E – SAMPLE SURVEY ........................................................ 139

ACKNOWLEDGEMENTS ...................................................................... 156

vi
INFORMATIONAL GUIDE FOR STATE, TRIBAL, AND LOCAL SAFETY DATA INTEGRATION
LIST OF TABLES

Table 1. Those who may be involved in data integration efforts. ................................ 6

Table 2. MIRE FDEs for non-local (based on functional classification) paved roads.12

Table 3. MIRE FDEs for local paved roads. ................................................................... 13

Table 4. MIRE FDEs for unpaved roads. ........................................................................ 14

Table 5. Data source summary. ..................................................................................... 89

vii
INFORMATIONAL GUIDE FOR STATE, TRIBAL, AND LOCAL SAFETY DATA INTEGRATION
LIST OF FIGURES

Figure 1. Chart. Nine-step process for safety data integration. .................................. xi

Figure 2. Chart. Safety Management Process. ............................................................... 2

Figure 3. Chart. Data driver safety analysis process. ..................................................... 4

Figure 4. Chart. Generalized crash data flow diagram. .............................................. 15

Figure 5. Chart. Step 1: Lay the Foundation. ............................................................... 21

Figure 6. Chart. Step 2: Conduct Gap Analysis ............................................................ 28

Figure 7. Chart. Step 3: Establish Data Governance Process. .................................... 33

Figure 8. Chart. Step 4: Develop Data Collection and Integration Plan. .................. 43

Figure 9. Chart. Step 5: Identify Training Needs. ........................................................ 50

Figure 10. Chart. Step 6: Perform Data Integration. .................................................. 56

Figure 11. Chart. Step 7: Develop and Deploy the ETL Process. .............................. 62

Figure 12. Map. Sample GIS map showing horizontal curve locations. .................... 65

Figure 13. Map. Sample GIS map intersection types and crashes. ............................ 67

Figure 14. Screenshot. Sample crash application filters for performing analysis. .... 68

Figure 15. Chart. Step 8: Conduct Analyses. ................................................................ 74

Figure 16. Chart. Integrating the roadway safety management and project

development process. ..................................................................................................... 75

Figure 17. Chart. Step 9: Perform Effectiveness Evaluation. ...................................... 81

Figure 18. Chart. Interactions among the steps in data integration. ........................ 84

Figure 19. Chart. Process overview. .............................................................................. 90

Figure 20. Screenshot. Data management tool. .......................................................... 92

Figure 21. Chart. Recommended LRS/MIRE road inventory work flow. ................ 111

viii