At a Glance
City staff were spending a lot of time running reports of upcoming street work and cleaning, reformatting, and emailing the data for use in an existing conflict management tool. Now, the tasks are automatically completed within minutes with a custom version of Airflow, the open-source platform created by Airbnb.
Upkeep of infrastructure, especially streets, is among the top issues that San Diegans say they want the City to focus on when surveyed. But no one likes it when a street is closed to repair a water main, then closed again to repair utility lines, and then closed another time for resurfacing. The City uses a conflict management tool to ensure this doesn’t happen, and one of the departments who contribute to this tool is Transportation and Storm Water.
Every month, TSW staff would run a report to export street repair projects from the pavement management database, perform about 30 steps in Excel to clean and reformat the data, join the tabular data to GIS data, and upload the final output to the conflict management tool. This process was a great first step toward managing conflicts with street work, but it introduced potential human errors and inconsistencies. It also took up a lot of staff time, requiring humans to perform tasks that computers do best.
City of San Diego used/is using a conflict management tool to address this/these challenge(s).
To save valuable staff time, as well as ensure consistency and accuracy in the data, the solution was to automate exporting and cleaning the data and transferring it to the conflict management tool. The City implemented its own version of Airflow, an open-source project originally built by Airbnb as a workflow management platform (see link under "More Reading"). TSW’s pavement management database was one of the first data sources to be added to the City’s Airflow. Airflow now backs all of the data published on the open data portal as well as data for internal analytics projects.
- The automated process replaced a time-consuming manual process, freeing up staff to work on more important tasks.
- The workflow of exporting, cleaning, and formatting the data is now defined in code, which makes the data more reliable and the workflow more manageable.
- Data is now updated daily instead of monthly.
The City of San Diego was the first local government entity to put into production the same workflow management system that large, data-driven companies like Airbnb, Groupon, Nextdoor, Paypal, and Spotify use.
Who Should Consider?
Governments with open data, performance management, and data science programs that need to automate extracting, cleaning, and transferring data.