Asset Record Matching and Database Joining
A Python workflow that linked more than 4,000 locations in each of two databases despite inconsistent names, inaccurate coordinates, and duplicates. The goal was to match each white circle to the right green square.
Outcomes
- Each record gained region, city, county, routes, alternative names, linear and physical mileposts, and distance and direction from the route centerline.
- A weighted score on distance, names, routes, and mileposts separated the correct site from a nearby unrelated one.
- An estimated 90–95% matched, linking UDOT Fiber As-Built data with ATOM. Ambiguous matches still needed review.
The two databases described many of the same sites and shared no identifier. Differences in names, coordinate accuracy, and completeness made name matching and nearest-point matching unreliable. Some sites appeared in only one database. Others had several records, with different names and coordinates.
Each record was enriched before matching: organizational region, city, county, primary and secondary routes, alternative route names, linear-reference and physical mileposts, distance from the route centerline, and direction from the centerline to the site.
A weighted comparison used those fields together. Distance, location names, routes, and mileposts carried the most weight, which separated the correct site from a nearby but unrelated one.
An estimated 90–95% of locations matched. That figure is from project review, not a formal benchmark. The workflow cut the manual effort of reconciling more than 6,000 records and linked UDOT’s Fiber As-Built data with the ATOM asset system. Ambiguous matches and source-data problems still needed review.
