Sometimes the job of Chief Data Officer is as simple as figuring out how to count things. The challenge is that counting things is not always quite as easy as it seems. With processes documented on paper, information not standardized, or not documented at all, the task of figuring out how much stuff happens on a day-to-day basis in city government can be a challenge.
The City of Syracuse has a lot of potholes. With infrastructure that has mostly passed its useful life and snow and plows that give the roads a beating each year, roads show their damage by the time spring comes around.
Our task was to try and figure out where and on which roads potholes were being filled most. We also wanted to report how many potholes were filled overall so people understood how much work our public works staff did each year to keep the roads smooth.
Unfortunately, the pothole filling crews did not record when they filled potholes in any easy format. We did have all of the pothole complaints that people reported to the Department of Public Works, but we were told, and observed, that the crews would often times fill about 10 other potholes on the way to filling the pothole that was reported. Crews would record some of the pothole locations on paper, but this would not be easy to analyze. Still the task remained.
Instead of buying an expensive piece of software to solve the problem, we worked with the crews to test a few different options:
- Excel spreadsheet with columns for date the pothole was filled and address nearest to where it was filled. The good part was that the data would be digitized. The problem with this was there was still a lot of data entry, we had no guarantee the crews would actually fill out the spreadsheet, the addresses and dates could be inaccurate, and the time spent with data entry meant less time spent filling potholes.
- Do Button with If This, Then That. This solution let crews take a picture which automatically logged GPS information, date, and an image into a Google spreadsheet. While the data would be digitized and less data entry was required, the reality was that crews were not guaranteed to have a smart phone in the field, remembering to take a picture after the pothole was fixed was not guaranteed, and there was an increased risk of the phone getting broken while being used in the field.
After doing tests with the options above, we realized that we needed a fully automated option as it would guarantee data entry and not distract crews from their core job: filling potholes.
We realized that we could leverage the GPS units on the trucks to record the data. Potholes are primarily filled using a Durapatcher truck with has an attached hose that shoots asphalt. By connecting a sensor to the hose, when the crews pulled the trigger to engage the trigger on the hose, a row of data could be logged that indicated GPS coordinates as well as the date and time the sensor was triggered. This was the fully automated option we sought. From there we could pull data in near real time, map the information, and report on it.
The outcome was that we could now count how much work was occurring each day and in which parts of the city. Also, instead of paying for an app or a different software solution, we were able to test, understand the possibilities and limitations for the crews, and leverage existing technology to count better.
Sidewalk Curb Corners
Another challenge we constantly face is tracking how many assets we have and are responsible for in the city. One example was sidewalk curb corners. There are specific requirements set by the Americans With Disabilities Act about how these curb corners should be constructed, and the City was responsible for presenting a plan for when all of the existing curb corners would be updated to be in line with current requirements of the law.
With thousands of curb corners in the city, and none of them mapped, and few of the attributes known, this would be a big project. Sending staff to collect data would be too time consuming.
The resolution was to work with student interns to log the information. First, we worked with the Engineering Department to understand what attribute types were critical to record: color change in the curb, raised bumps on the slope of the curbs so people who are visually impaired could sense when the sidewalk was ending, and more. Then, using a data collection product from ArcGIS — a mapping platform most municipal governments use, we programmed the tool with drop down menus that would be easy for the students to complete.
We split the city into square zones, and assigned each zone to a student. Then, the students used Google Maps and Street View to look at each curb corner in their area. They would click on the ArcGIS map, a popup would appear with all of the drop down fields they needed to complete, and once they filled in all the information, the data point would appear on the map.
In all, the students logged more than 7,000 curb corners in a semester. Pieces of data were certainly missing or inaccurate, but this was less expensive and less time consuming for our staff. We also got the information we needed to complete the project and create a plan while also building a partnership with Syracuse University.
City staff now have access to the map and can make updates and additions. Any of the data that was inaccurate can be fixed as city staff go to different curb corners to assess and monitor, but at least the baseline is already there.