Most Travelled Corridor Dashboard - Code Enforcement
An end-to-end pipeline that moves EnerGov SQL Server code enforcement cases into a GIS-ready feature dataset, performs geocoding + QA/QC, publishes an updated ArcGIS Online feature layer, and drives daily operational dashboards.
Business Problem
EnerGov is the system of record for Code Enforcement, but case data is often non-spatial or inconsistently geocoded. Manual GIS updates are slow and can introduce errors—leading to outdated dashboards and reduced field visibility.
- Missing or inconsistent coordinates (lat/long)
- Address formatting issues that reduce geocode match quality
- Dashboards lag behind operational reality
- Manual refresh workflows don’t scale
Solution
A fully automated ETL + geocoding pipeline loads EnerGov updates into a GIS database, performs coordinate validation and address geocoding, flags exceptions for review, overwrites the AGOL feature layer daily, and powers an internal operations dashboard.
- Standardized text (Unicode) for reliable geocoding
- XY-to-Point when coordinates already exist
- Locator-based geocoding when coordinates are missing
- QC checks and error flagging before publishing
Workflow (End-to-End)
Staff enter new cases and updates in EnerGov throughout the day (system of record).
SSIS extracts updated cases via a SQL view, converts text to Unicode for consistency, and reloads a GIS DB table to maintain a clean daily snapshot.
Records are checked for existing lat/long. If present, XY To Point creates geometry; otherwise addresses are geocoded using a locator. Results are validated and low-quality matches are flagged for review.
Validated results are converted into a feature layer and loaded to an AGOL feature dataset. The hosted layer is overwritten to ensure schema consistency and current data without duplicates.
Python scripts run daily via Windows Task Scheduler to perform geocoding, overwrite the AGOL layer, and push dashboard updates automatically.
An internal AGOL dashboard is shared with Code Enforcement staff and supervisors to monitor case distribution, workload, and status trends for daily decision-making.
Impact
- Eliminated manual GIS refresh and reduced operational lag
- Improved spatial reliability with QA/QC + exception flagging
- Delivered consistent daily publishing to AGOL for dashboards
- Improved visibility for staff and leadership (monitoring + tracking)