Merced County, California - Geocoding Automation

• Merced County is a county located in the northern San Joaquin Valley section of the Central Valley in California
• The County was founded in 1855
• As of the 2020 Decennial Census, Merced County had a population of 281,202

County Website:  https://www.co.merced.ca.us/ 

GIS Portal:  https://geostack-mercedcounty.opendata.arcgis.com/ 

“Developing coordinate data in GIS from non-spatial table data has always been a challenge” says Gene Barrera, GIS Manager for the County of Merced in California’s San Joaquin Valley. As a local government, the County creates and maintains much data that if 'geo-enabled' can provide top level decision makers, management and staff with a unique perspective on spatial data that would otherwise be available on a spreadsheet. Nowhere is this better exemplified than with data from the County’s Environmental Health Department."  

Having formed a long standing partnership with Sidwell, Gene contacted our Research and Development team to identify how the County’s Environmental Health data could be converted to coordinate information. The resulting solution would transform non-spatial, non-standardized permit data associated with domestic and agricultural water wells, to a GIS point feature class.

“Sidwell’s expertise in developing automation processes using python scripting was our first choice in determining how best to leverage this data for use in GIS” - Gene Barrera, GIS Manager, Merced County

• Esri's ArcGIS Pro
• ModelBuilder
• Python, ArcPy
• Windows Task Scheduler

To develop a new method of converting Merced County's non-spatial tables into GIS features, there were many challenges to overcome, including:

• Reconciling data entered in non-standard formats with no domains, and sometimes no spatial locations
• Developing the workflow in which the tables would be geocoded to maximize accurate matches
• Building the geocoder with the correct parameters
• Scripting the workflow to be scheduled on a nightly basis

“We recognized early on this would be a heavy lift for the County, as much of the data has been collected in various ways and over many decades - Gene Barrera, GIS Manager, Merced County

Not having a consistent method in collecting the non-spatial data resulted in developing a multi-pronged approach in converting it for GIS use. Recognizing this issue, the Sidwell team met this challenge by developing a script that would scan through the data to find records containing site address information, assessor parcel number data or latitude/longitude data if available. 

To account for records that had no spatial information to geocode, a "dummy" set of latitude and longitude coordinates was assigned to it in a location just outside of the County boundary. This made it so that all records could be geocoded, and the points outside of the County would serve as a visual indicator of those that needed to be cleaned up.

“Having various potential data candidates that could be used to develop coordinate geometry for the well data provide us with an opportunity to extending this data into GIS. Sidwell’s programming team and their expertise in data mining helped uncover how the well records could be geocoded for use in mapping. - Gene Barrera, GIS Manager, Merced County

To ensure the highest quality of data possible, the Sidwell team worked with Merced County's own authoritative parcel and addressing data to create the internal geocoders used for processing. Where previously the County had relied on outdated data from the U.S. Census API geocoder, this method gave them certainty that the transformed data would be of the highest accuracy possible.

To complete the project, Sidwell used the new geoprocessing scheduling function within Esri's ArcGIS Pro to run the script and schedule it. When the script was scheduled through the tools available in ArcGIS Pro, a subsequent task was automatically added with the same parameters to the Windows Task Scheduler, bridging the gap between GIS and Windows to accomplish the nightly process.

The diagram below outlines the steps taken by the Sidwell team in developing this new automated process:

According to Merced County's Environmental Health Department, "the conversion of data that had been collected over many years for administrative and programmatic field work into GIS has changed the way staff are able to read and use this data to make everyday decisions regarding permitting and other environmental work. The ability to utilize one tool to collate and view important information such as data sets, geographical designations, etc. that cross multiple disciplines for decision-making allows for staff to streamline our processes."

"A great example of this would be that our well permitting staff are able to verify multiple components of well permit applications received from contractors and property owners. Our staff are able to verify property lines, Assessor’s Parcel Numbers, property owners, community water/sewer designations, along with our unique data set from Accela, with just one tool instead of visiting multiple websites to verify the same information that is captured through the use of GIS."

Through the collaborative efforts of Gene Barrera at Merced County and the team at Sidwell, an efficient and replicable automated process was created to achieve the County's goal of creating geospatial data for use by their Environmental Health Department. The County will now be able to use their internal geocoders for many other processes within their organization, resulting in more accurate GIS data.

Sidwell’s work on this project is a game changer for the Environmental Health Department’s use of this information and provides other County Departments who rely on this information for land use, property assessment valuation and other needs with a unique view on how this data and its relationship to other GIS information can make more informed decisions. - Gene Barrera, GIS Manager, Merced County

Sidwell is the leading provider of GIS technology and related mapping services for local business and government needs. Sidwell’s mission is to create and deliver outstanding products and provide excellent services to our clients, while developing close, trusting, and enduring professional relationships with them. Among the comprehensive products and services offered by the company are GIS planning, design and implementation; data conversion and cadastral database development; aerial photography; software development; photogrammetric services including topographic, planimetric and digital orthophoto mapping; training; technical support services; and web hosting for GIS data. Sidwell is an Authorized ESRI Business Partner, developer, reseller, and consultant, and is also an Authorized dealer of Magellan navigational equipment.