How We Modernized Our Road Log and Collision Data With CRAB

How King County is transitioning from a legacy, non-spatial road log and collisions data source to a controlled, GIS-native workflow built around authoritative feature data provided by the Washington State County Road Administration Board (CRAB).

Key Takeaways

• King County is modernizing its road log and collision data management by integrating authoritative CRAB feature data into Roads GIS geodatabase, enabling county systems to rely on a governed, spatially managed source.

• Automated quality-control routines validate schema alignment, record counts, geometry integrity, and attribute consistency both before and after every export, ensuring reliable data migration and reducing the risk of structural or spatial discrepancies

• The automated workflow is supported by a controlled Azure DevOps promotion path, providing a lower-risk, minimally disruptive transition for existing dynamic segmentation and linear referencing systems as they move from legacy, non-spatial processes to a GIS-native environment.

For government agencies, road log and collision data form a critical foundation for transportation management. At King County, these datasets support essential Department of Local Services (DLS) – Road Services functions such as asset tracking, regulatory requirements, roadway modelling and the operational workflows that staff rely on every day. When that information is tied to an older, non-spatial source, maintaining accuracy and adapting to evolving business and technology requirements become increasingly challenging.

To strengthen that foundation, King County developed CRAB2RoadsGIS, a project that transitions road log data from a legacy non-spatial source into a GIS-native workflow built around authoritative feature data from the CRAB. The new approach better supports DLS Roads business needs while improving how road information is managed, validated, and used across GIS applications and downstream workflows.

The result is more than a data refresh. CRAB2RoadsGIS establishes a structured, validated, and operationally hardened process for moving road data from a feature service into a geodatabase, with built-in safeguards and a transition path for existing consumers.

Road log data in King County, shown by Federal Functional Classification, now flows into Roads GIS geodatabase through the CRAB2RoadsGIS workflow.

Moving Beyond a Legacy Data Source

Before this project, CRAB data flowed into county systems through views tied to an on-premises SQL Server database. That legacy source was non-spatial, difficult to validate, and increasingly misaligned with the county’s evolving practices for managing and consuming GIS-based information.

The CRAB2RoadsGIS workflow directly addresses these limitations. By transitioning to authoritative CRAB feature data hosted in ArcGIS Enterprise, the county now relies on spatially enabled, well-maintained datasets delivered through a geodatabase. This shift establishes a cleaner and more sustainable data pipeline for CRAB data, aligning with contemporary needs in mapping, spatial analysis, and application support.

This kind of modernization is not just about replacing one source with another. It is about building a workflow the team can trust operationally and sustain over time.

CRAB2RoadsGIS replaces a legacy non-spatial dependency with a validated GIS workflow.

Building a Controlled GIS Workflow

CRAB2RoadsGIS establishes a configuration-driven ETL (Extract, Transform, Load) workflow designed to reliably move authoritative CRAB feature data into the Roads GIS geodatabase. The process connects to the correct portal content, identifies the configured source feature layers, and loads them into the appropriate target feature classes. Because the workflow is environmentally aware, the same configuration supports development, test, and production environments with settings that are specific to each stage.

A core strength of the implementation is that data loading is governed by explicit quality control checkpoints rather than assumptions of success. Before any export occurs, the workflow iterates through each configured layer and runs targeted pre-process QC checks.

When a target feature class exists, the process validates geometry according to its type, verifying shape length for polyline layers and confirming valid point geometry for point layers. Schema alignment ensures that field names and types match, and record-count checks confirm that the source contains data. Optional count-change thresholds, read from the configuration file, act as safeguards against unexpected pre-load variations. These thresholds evaluate the percentage change between source and target counts and stop the load if the change exceeds the allowable limit. When the target does not exist, the workflow performs geometry-only QC and skips checks that require a pre-existing dataset. Geometry filters can also be disabled when needed.

After exporting the features from the source service into the target feature classes, the workflow performs a second validation stage. Post-process QC includes record-count reconciliation and a field-by-field attribute comparison for all matched records to verify that the exported dataset is structurally and materially consistent with expectations.

This two-phase validation model reduces operational risk in a practical and measurable way. Instead of assuming that the data load succeeded simply because processing completed, the workflow demonstrates through independent pre-process and post-process checks that the resulting dataset meets the expected structure, geometry, and attribute integrity. Configurable safeguards, such as count-change thresholds, allow the team to prevent unintended overwrites during routine updates while still accommodating large, planned imports. The result is a controlled, transparent, and repeatable GIS workflow that supports long-term data integrity.

CRAB2RoadsGIS applies two stages of validation before and after export to ensure that each run meets defined quality and consistency standards.

Supporting a Lower-Risk Transition

Modernizing the data source did not require every dependent system to change immediately. To maintain continuity, the project provides an SQL compatibility view that redirects existing road log and collision views to the new GIS-backed feature classes while preserving the familiar column structure already used by downstream consumers.

On the deployment side, the repository includes Azure DevOps pipeline steps that validate pull requests before promotion. These checks confirm branch naming conventions, the presence of required files, configuration consistency, and successful test execution. With reviewer approval requirements, validation gates, and automated deployment across development, test, and production, the ETL process and its configuration move through a controlled and traceable release pipeline.

Unit tests reinforce this reliability. The suite covers configuration handling, environment detection, export logic, quality-control behavior, and end-to-end workflow outcomes. Combined with the configurable threshold checks, these tests help stakeholders view CRAB2RoadsGIS as production-grade software rather than a one-time script.

Azure DevOps pipeline gates validate each change before promotion, keeping the workflow controlled across all three environments.

Operational Reliability Matters

CRAB2RoadsGIS gives King County a stronger foundation for managing road log and collision data within its GIS environment. By adopting an authoritative GIS data source, validating every run, and preserving a compatibility path for existing consumers, the county is improving both data stewardship and operational continuity.

The project also illustrates the value of treating GIS automation as a production system rather than a one-time script. CRAB2RoadsGIS incorporates structured logging, summary email notifications, secure credentials, automated validation, and continuous integration pipeline support for testing and controlled deployment.

These engineering practices matter because they make the workflow reliable in production. GIS teams often manage critical data flows with minimal tolerance for failure. A project designed in this manner demonstrates how GIS-specific logic can be combined with disciplined software engineering methods to produce automated workflows that teams can trust and sustain over time.

CRAB2RoadsGIS sends a summary email with QC results and runtime, allowing the team to confirm success immediately.

A Practical Model for GIS Modernization

CRAB2RoadsGIS provides a practical and replicable model for modernizing GIS data workflows without disrupting existing business systems. It strengthens the authoritative source of road log and collision data, validates each data movement, and offers a compatibility layer that supports a phased transition for downstream consumers.

For government agencies and GIS teams working to replace legacy tabular or non-spatial dependencies with GIS-native workflows, CRAB2RoadsGIS demonstrates a clear pattern:

• Modernize the authoritative source.

• Control and validate the ETL.

• Protect existing consumers during the transition.

• Treat the pipeline as production software from day one.

Together, these principles transform a technical upgrade into a durable operational improvement and help make GIS data infrastructure something organizations can rely on.

About the Author

Ahmed Ahmouda is a Senior GIS Engineer with more than a decade of experience across the public, private, and academic sectors. He joined the King County GIS Center in 2025, where he focuses on data engineering, GIS automation, and application development that supports the county’s efforts to manage and modernize its geospatial data infrastructure.

Ahmed holds a Bachelor of Science in Civil Engineering, a Master of Science in Geographic Information Systems from the University of Redlands, and a Ph.D. in Geomatics with a concentration in GIS from the University of Florida. His work focuses on advancing reliable, scalable, and production-ready GIS systems for enterprise environments, with an emphasis on data engineering, automation, and modern geospatial infrastructure.