SJ Vision Zero Project

San Jose joined the effort of cities across the United States to eliminate severe and fatal traffic related accidents. The Vision Zero Initiative is a bold new way of solving a long standing issue. Reduce traffic related injuries by upgrading street design to better safeguard transportation activity.

Goal

The data collected from traffic accidents will serve as a guide to monitor and evaluate safety outcomes.

Outcome

Adding the corridor map view was an improvement to the insights gathering process. Through this I was able to identify several other corridors with frequently occurring traffic related injuries.

Overview

Role: Data Analyst

Requirements: Develop a vision zero dashboard

Timeline: Approximately 120 hours

Tools: Apache Airflow, Python, Jupyter Lab, QGIS, SQL, Azure Data Studio, Affinity Design, Tableau

Plan

Extract

Analyze

Visualize

Plan (4 Steps)

1. Extract and Load
   • Pull crash data from San Jose Open Data Portal
   • Import dataframes into SQL Postgres Database
2. Analyze data in SQL
   • Feature selection
   • Transform data, add moving average/ rolling total
   • Import into SQL Postgres Database
3. Update shapefile with QGIS
   • Add more high KSI corridors using vector tool
   • Calculate length for added corridor rows
   • Create boolean field – non-priority or priority corridor
4. Design Tableau dashboard
   • Overview with aggregate crash data
   • Filters to drill-down into data
   • Corridor heat map
   • Additional filters for corridor selection
   • information about Vision Zero Initiative (Refrences)

Extract, Transform, and Load

• Data source: Crash Locations
• Three different datasets were collected: Crash Locations, Crash Vehicles Involved, and Safety Corridors
• Crash Locations dataset features: date, age, lighting, weather, involved, narrative, violation
• Crash Vehicles Involved dataset features: date, direction, damage, violation
• Safety Corridors dataset features: streetname, limits, grantamount, notes

Apache Airflow

• Build three DAG functions to auto extract and load data into Postgresql database
• Set Airflow to run every week to update database

Analyze Data

• Feature selection - Azure Data Studio

QGIS

• Data source: Vision Zero Corridor dataset
• Vector tool - Add corridors
• Calculate - Update length field

Evaluate Results

• Design - Affinity Design
• Visualization - Tableau

Tableau Dashboard Shapshot 📸

Conclusion

Downtown San Jose has always been a troublesome area for bicyclists, pedestrians, and motorists. Because of its busy streets and dense population the chances of being involved in a traffic related accident is more likely. In the last three year 7,728 traffic related injuries were recorded, most were minor injuries, but a significant amount were severe and even fatal. The Vision Zero Initiative aims to target these most troublesome streets and reimagine its use with safety as the priority.

While the San Jose Vision Zero Initiative has done a good job of targeting and redesigning several corridor streets labeling them as priority, other injury prone streets have not made the list. 10th street and Santa Clara street has a record of multiple severe and fatal injuries from motorists running red lights. Capital avenue and McKee road has multiple pedestrian injuries severe and fatal caused from jaywalking. A stretch of San Carlos from Race to Bascom has multiple severe and fatal injuries by pedestrians failing to yield to cars. The opportunity to improve yet more unsafe street designs still exists.

In this project we gathered traffic accident data to evaluate the safety outcomes of San Jose Vision Zero. The results of which revealed eleven corridors of interests for safety redesign in addition to the existing nineteen corridors that are already priority. For San Jose to achieve it objective and fully eliminate severe and fatal injures from traffic accidents the progress of previous redesign efforts must be focused towards safeguarding other corridors. If your interested visit https://www.sanjoseca.gov to learn more about current action plans for San Jose Vision Zero.

View Dashboard