This month, the Know Spokane team set out to rate Spokane's average street quality by neighborhood, and look at the quality of our most heavily-used arterials.
You can read the results here, but for you wonks out there, here's an explanation of what we did.
The City of Spokane keeps a GIS file of its roads, with the most recent pavement ratings attached. Those ratings are called a pavement condition index, or PCI, and range from 0-100.
They're based on annual summer surveys of the roads, where city employees chart cracking, patches, weather damage and other road quality issues, then give the street a grade. Arterials are rated every two years, and residential streets every four, though ratings are also updated when improvement work is done on a street.
The city's data
We requested a copy of the city's PCI data, which included this spreadsheet detailing the ratings, block by block. The "Street" column showed the street being rated, while columns C and D describe the block.
Each of these city blocks is in a neighborhood, but the city didn't track that, so we had to add it by hand. We scrolled through, street by street, using the city's map of neighborhood division boundaries at maps.spokanecity.org and put each block in the neighborhood it belonged in.
(If you're wondering, that took two people about two and a half weeks, spending a few hours per day on this.)
In cases where a given block was the boundary line between two or three neighborhoods (which happened on a handful of streets), we made extra columns, so all boundary streets were counted in each place. That means if you add up the total number of blocks in each neighborhood, you'll get a total higher than the actual number of blocks in the city.
Once we had the blocks grouped into neighborhoods, we had to figure out the average pavement score for each neighborhood.
Rather than doing a simple block-by-block average, we decided to do a weighted average based on the length of each block. If one street was twice as long as another, it should count twice as much toward the neighborhood's total.
We split each neighborhood into its own file, then ran those through a Python script to calculate a weight for each block and determine a total score.
We divided the length of each block by the total length of all blocks in the neighborhood to come up with a weight, then multiplied the weight by that block's PCI. Once we had a weighted score for each block, we added them all together to get the neighborhood total.
We did the same with major arterials or groups of arterials, and the city as a whole. You can read more about what we found on our main post.
The city rates all unpaved roads with a PCI of -1. We debated whether to include those roads in the averages.
On the one hand, we were concerned that might skew a neighborhood unfairly negative because of a few unpaved roads around otherwise good streets. On the other hand, driving over an unpaved road, particularly in winter, is not a pleasant experience, and overall quality scores should reflect that.
We ultimately ran the scores both ways and found there was less than a 0.3 point difference for every neighborhood between the two methods. The neighborhood rankings were the same regardless of the method used. The scores were cited in our article included unpaved roads.
Shortcomings of the data
Though scores are updated with road maintenance, it's inevitable that some of the scores in the city database aren't current. Roads deteriorate in the time since they were measured, and the brutal freeze/thaw winter we just had sprung up a fresh crop of potholes all over the city.
New road maintenance won't show up in the database until the city has closed the project, which means all finishing work has been done and invoices are paid. Often, that's well after the road work has been finished. Late last year, the city did significant work on Monroe Street between Main and Riverside, smoothing over ruts and depressions. But because the project isn't closed, that block still has a PCI of 2 in the city's database.
We're hopeful that the relative scores are fairly accurate, since worse quality roads are more prone to potholes and other weather damage. But if a city rating seems vastly wrong for a block you're familiar with, that might be why.
We were diligent in checking roads and finding correct neighborhoods, and checking for errors after our initial data entry, but it's possible we misclassified a block of two. In a database of more than 11,800 blocks, it's unlikely that would make much of a difference in overall scores.
Shortcomings of our analysis
There are all kinds of ways you might decide which neighborhoods have the worst roads. We picked one here that was straightforward, easy to analyze and didn't push the boundaries of our mathematical and statistical skills.
If we had all the time in the world, we'd love to do an analysis that weights arterials more heavily or takes into account actual traffic numbers, since the impact of a major arterial having bad pavement is much higher than the impact for a small residential street.
North Division Street is one of the most well-traveled streets in the city, and we'd be willing to bet most city residents care about its pavement quality, whether they live nearby or not.