Now that our Vision Zero data collection is mostly complete, we’re starting to move onto the analysis phase of the project. Since our home is in San Antonio, TX, we figured that examining the Alamo City’s crash data would be a good jumping off point for this part of the project.
We first went to San Antonio’s Open Data Portal to see if there was a decent crash dataset available. We found what we thought were two promising data files from Public Works identifying pedestrian and bicyclist severe injury areas of the city. Unfortunately, neither of these data files had all the information we needed.
The data only contained crashes from 2017 and 2020 and only displayed how many people suffered incapacitated injuries or were killed. We wanted data that was more detailed and inclusive — while we mainly focus on pedestrian and cyclist dangers when it comes to San Antonio, knowing how many cars crash into other cars is helpful in determining the overall roadway safety for everyone. It is a bit of a head-scratcher why the city would publish data for two separate years instead of the range of years. I’m not sure I see the logic behind this, since without 2018 & 2019 data it’s pretty much impossible to determine any sort of trend in dangerous areas of the city. But we could use this data in future analyses to confirm high danger areas. and possibly find new ones.
I had to go to the TxDOT Crash Query tool to get crash data that I could work with, and was able to get crash data for the years 2013–2022. One minor irritation about the Crash Query tool is that it limits queries to 50,000 rows. San Antonio is the 7th largest city in the country…there’s a lot of car accidents. This didn’t affect the quality of the results, it was just a minor frustration when collecting the data.
Once I collected data for 2013–2022, I combined all the separate data frames into one large one. It ended up being over 1 million rows, so I knew something was up within the data itself. San Antonio is big, but over 1 million crashes seemed strange.
Sure enough, there were duplicates. But not just any duplicates — confusing duplicates! The way TxDOT organizes the data assigns a row to each individual person involved in the crash but use the same CrashID. For example, CrashID 13051989 is one crash incident that involved 5 people: a driver, a passenger of the car, and 3 pedestrians. Can you guess who got injured in this crash? The way CRIS populates the rows as well is a bit confusing at first glance. The “Crash Not Injured Count” is 2 for all rows of this crash, but we need to look two rows over to “Person Total Injury Count” to see that the driver and passenger of the vehicle weren’t injured…the pedestrians were.
In this first run through analysis, I wanted to see how many pedestrians and cyclists have been killed in the 10 years of data I have. To do this, I filtered the whole data down to two separate data frames — one for pedestrians and one for cyclists.
From 2013–2022, a total of 569 pedestrians have been killed in San Antonio, 6% of total crashes involving pedestrians.
From 2013–2022, a total of 40 bicyclists have been killed in San Antonio.
At first glance, those numbers don’t seem like a lot. Over 8,000 pedestrians involved in car crashes and only 569 died? A 6% death rate doesn’t seem too bad. The death rate for cyclists is even lower! But try telling that to the over 600 families and friends that have lost someone who was trying to walk or bike in San Antonio.
I know the above is more emotional than analytical, but when the stated goal of cities pledged to Vision Zero is to reduce the number of fatalities and severe injuries to pedestrians and cyclists to 0 (hint: it’s in the name), even 1 is too many.
