Hi this is Jackson Porterfield, presenting on behalf of Marcus King, myself, and Nicholas Yu. I’ll be presenting our capstone project from Geography 469, in which student groups were matched to sponsor organizations to complete a 10-week mapping task. Our task was critical infrastructure mapping for Snohomish County.
Our sponsor for the project was the Snohomish County Department of Emergency Management, or DEM. The DEM is responsible for responding to emergencies across eighteen city and tribal jurisdictions within Snohomish County, as listed on the left. These emergencies include severe weather, climate disasters, and public health hazards, among others.
Our primary objective for the project was to update the DEM’s geodatabase of critical water infrastructure in the county. This includes feature layers for water distribution, sewers, and stormwater drains. Our secondary objective was to automate the geodatabase update, to minimize maintainance time for the DEM going forward.
We were able to break our approach down into three questions. First, how do we acquire the data? Our sponsor was not able to provide us with data sources, but instead provided a list of the county’s nineteen water and sewer districts. In order to acquire the data, we researched each district online to see whether their data is public. For some of the larger districts public geodata portals exist, but for many the data is not readily available. We reached out to these districts to acquire the data, but weren’t able to acquire some of the datasets.
Our next question was, what transformations need to be made? Our sponsor had some specific criteria for the attributes of the geodatabase. Some files needed to be converted to shapefiles, at which point we revised their attribute data and combined them into a collective geodatabase.
The final question was, how can we make the process repeatable? Because much of the data required emails, calls, and request forms, we weren’t able to automatically extract the data. However, once the data collection is complete, we were able to write a script to instantly clean and combine the files. Every step of the process is documented in detail for the DEM to replicate.
So, this is an overview of the data processing workflow for a single layer. We begin with the input data file we intend to add on the left, and the collective geodatabase at the bottom. The input file is converted, if necessary, and its attribute data is rewritten. These processes return a cleaned file which is merged to the geodatabase, and this process is repeated for each input file.
Here’s a more detailed look at our ArcPy script. Since each input file stores similar attributes under different field names, our script recognizes these names and rewrites the data into a single column shared by the entire database. It then neatly combines the files into folders for each district.
And here is a look at our results. The map on the right shows the updates we’ve made to the geodatabase. Lines in black are from the geodatabase we were originally given. The lines in color are the districts we were able to update or add. The map on the left gives a better view of what the infrastructure data looks like.
A further addition to this project could provide an online data storage platform where the county’s water districts would upload their geodata periodically, or as changes are made. This could completely absolve the DEM of any maintainance work, as the input file paths in our Python script could be replaced with the location of these files online. The pitfall of this approach is that it would require complete cooperation with all 19 districts. This may not be attainable, as many districts might not want to add the data for privacy concerns, or just to avoid the hassle.
And before I conclude, we would like to give a thank you Drew Schwitters, our primary point of contact at the DEM, Suzanne Withers and Alyson Mabie, our mentors during the course of this project, and all of the professionals in Snohomish County that collaborated with us for this project. Thanks for watching!