Snowflake Schema-Based Data Warehouse for Analyzing Crash, Citation, and Warning Traffic Safety Records
Publication: International Conference on Transportation and Development 2023
ABSTRACT
Decision-makers in traffic agencies and police departments require a wide variety of high-quality data to support traffic safety problem identification, program implementation, and result evaluation. A major challenge is integrating traffic safety data collected and managed in different databases, often across multiple agencies. This paper describes the design and implementation of a Wisconsin traffic safety data warehouse that is focused on the six core traffic record data systems identified by the National Highway Traffic Safety Administration for its model performance measures. As a first step, the development of the Wisconsin data warehouse is focused on linking crash records to citations and warnings. The design of the data warehouse is determined by the selection of the data source, description of the data flow architecture, and design of the snowflake schema. The snowflake schema was found to support a wide variety of in-depth and flexible traffic safety analyses and is easily extended for future safety applications.
Get full access to this article
View all available purchase options and get full access to this chapter.
REFERENCES
Abel, S., Lindley, J., and Paniati, J. (2020). Safe System Strategic Plan. Washington, DC: Institute of Transportation Engineers.
Blincoe, L. J., Miller, T. R., Zaloshnja, E., and Lawrence, B. A. (2015, May). The economic and societal impact of motor vehicle crashes, 2010. (Revised). Washington, DC: National Highway Traffic Safety Administration.
Budiawan, W., Saptadi, S., and Arvianto, A. (2018). The Development of Data Warehouse to Support Data Mining Technique for Traffic Accident Prediction.
Council, F. M., Soika, J. P., Lefler, N. X., Pollack, R. A., and Tan, C. H. (2013). Performance Measures for Roadway Inventory Data. Washington, DC: Federal Highway Administration Office of Safety.
Curry, A. E., Pfeiffer, M. R., Metzger, K. B., Carey, M. E., and Cook, L. J. (2021). Development of the integrated New Jersey Safety and Health Outcomes (NJ-SHO) data warehouse: catalysing advancements in injury prevention research. Injury prevention: journal of the International Society for Child and Adolescent Injury Prevention, 27(5), 472–478.
Dahlan, A., and Wibowo, F. (2016). Design of Library Data Warehouse Using SnowFlake Scheme Method: Case Study: Library Database of Campus XYZ. 318–322. https://doi.org/10.1109/ISMS.2016.71.
Evers, T., Thompson, C., and Pabst, D. (2022). “State of Wisconsin Federal Fiscal Year 2022 Highway Safety Plan” <https://www.nhtsa.gov/sites/nhtsa.gov/files/2021-12/wi_fy22_hsp-tag_0.pdf>(Oct. 28, 2022).
IADLEST (International Association of Directors of Law Enforcement). (2021). Data-Driven Approaches to Crime and Traffic Safety (DDACTS 2.0) Operational Guidelines.
Lefler, N., Zhou, Y., Carter, D., McGee, H., Harkey, D., and Council, F. (2010). Model Inventory of Roadway Elements – MIRE 2.0. Washington, DC: Federal Highway Administration Office of Safety.
Levene, M., and Loizou, G. (2003). Why is the snowflake schema a good data warehouse design? Information Systems, 28(3), 225–240.
Mashhadi, M. M. R., Saha, P., and Ksaibati, K. (2017). Impact of traffic Enforcement on Traffic Safety. International Journal of Police Science & Management.
Mohammed, K. I. (2019). Data Warehouse Design and Implementation Based on Star Schema vs. Snowflake Schema. International Journal of Academic Research in Business and Social Sciences, 9(14), 25–38.
National Center for Statistics and Analysis. (2022, April). Early estimate of motor vehicle traffic fatalities in 2021 (Crash•Stats Brief Statistical Summary. ). National Highway Traffic Safety Administration.
NHTSA (National Highway Traffic Safety Administration). (2011). “Model Performance Measures for State Traffic Records Systems” <https://crashstats.nhtsa.dot.gov/Api/Public/ViewPublication/811441> (Oct. 28, 2022).
NHTSA (National Highway Traffic Safety Administration). (2017). “Model Minimum Uniform Crash Criteria –5th Edition.” <https://www.nhtsa.gov/mmucc-1>(Jan. 22, 2022).
NHTSA (National Highway Traffic Safety Administration). (2019). “Data Integration: Linking It All Together” <https://crashstats.nhtsa.dot.gov/Api/Public/ViewPublication/812784>(Oct. 28, 2022).
Patel, J., and Patel, A. (2012). Data Modeling Techniques for Data Warehouse. 2. 240–246.
Peach, K., Brown, R., DeFisher, J., Zhou, Y., Haney, K., Bryson, M., Scopatz, B., and Chestnutt, C. (2019). State Traffic Records Coordinating Committee Strategic Planning Guide. Washington, DC: National Highway Traffic Safety Administration.
Pollack, R. (2010). Crash data improvement program guide. Washington, DC: The Federal Highway Administration.
Rothenberg, H. A., Riessman, R., and Flatten, D. (2005). Development of a statewide highway safety data warehouse: Massachusetts data warehouse and web-based access. Journal of Safety Research, 36(5), 465–467.
Vaisman, A., and Zimányi, E. (2014). Data Warehouse Systems: Design and Implementation.
Information & Authors
Information
Published In
International Conference on Transportation and Development 2023
Pages: 51 - 61
History
Published online: Jun 13, 2023
ASCE Technical Topics:
- Buildings
- Climates
- Engineering fundamentals
- Environmental engineering
- Facilities (by type)
- Hydrologic data
- Hydrologic engineering
- Hydrology
- Infrastructure
- Meteorology
- Models (by type)
- Precipitation
- Snow
- Storage facilities
- Structural engineering
- Structures (by type)
- Traffic accidents
- Traffic analysis
- Traffic engineering
- Traffic management
- Traffic models
- Traffic safety
- Transportation engineering
- Transportation management
- Transportation safety
- Water and water resources
Authors
Metrics & Citations
Metrics
Citations
Download citation
If you have the appropriate software installed, you can download article citation data to the citation manager of your choice. Simply select your manager software from the list below and click Download.