Data QA · dashboard · 2021

Automated QC for a statewide road network

An FME pipeline that validates the Arizona Department of Transportation’s Esri Roads and Highways geodatabase against its own data rules and route-network checks, and feeds a dashboard that shows managers which errors are coming in, how often, from whom and where.

Organization
Gistic Research, Inc.
Presented
AGIC 2021 · Esri RHUG 2021 · FME in Transportation SIG
Year
2021
Files
1 download
105
feature classes validated against live domains
4M → 250
rows in the core join after sampling
60 days
default look-back for editor feedback

Results

  • Picks up new or removed domains and tables with no workspace changes.
  • Filters by agency, editor, layer, field, value and edit date.
  • Refreshes daily, so managers see each new domain violation by editor, date, record and value.
  • Submitted for FME Certified Professional.

Stack

  • FME
  • SQL Server
  • SDE enterprise geodatabase
  • Esri Roads & Highways (LRS)
  • Google Sheets
  • Google Data Studio
Navigating roads to data visualization (AGIC 2021, with Sage Donaldson, ADOT)
Visualizing Esri Roads & Highways errors (RHUG 2021, with Shaun Perfect, ADOT)
ArcGIS domains: using FME where Esri falls short (FME in Transportation SIG)

The problem

Geodatabase domains are meant to keep bad values out, but they’re easy to get around. Scripts, direct SQL, FME and even Esri’s own Field Calculator and Append tools can write values outside a field’s coded list or range, and nothing flags it. In a statewide linear referencing system edited by several agencies and contractors, those values pile up.

How it works

  1. Query the Roads and Highways registry for every registered table and its type: route network, linear event or point event. A user-supplied ignore list drops tables or fields that shouldn’t be checked.
  2. Generate one SQL statement per stream from shared logic, so a field change is made in one place, and read only the features edited inside the look-back window.
  3. Read the coded-value and range domains straight from the geodatabase metadata, so validation always uses the current rules.
  4. Explode each record into field/value pairs, sample one set per feature class and field for the domain join, then split into range checks and coded-value checks. Unmatched values are violations.
  5. Truncate and reload a Google Sheet with four tabs (domain violations, LRS event errors, LRS route errors and a QC summary of each run), which powers the dashboard.
The dashboard: violations by agency, editor, feature class, field and date.
The dashboard: violations by agency, editor, feature class, field and date.

What it checks

  • Domain violations: values outside a field’s coded-value list or range.
  • Linear event checks: gaps, overlaps, duplicates, short events, measures that don’t increase along the route (monotonicity) and cardinality.
  • Route checks on the network the events are measured along.

Around the dashboard

I also helped build a shared library of about 59 reusable FME tools for linear referencing, reviewed and standardized the team’s FME workspaces, and made the case for running them on FME Server.

Downloads