4 posts tagged with "Record matching"

You've got a hundred thousand addresses to reconcile. Two databases, the same clinics and providers scattered across both, each one spelled a dozen ways: abbreviated here, reordered there, a postcode dropped, a suite number glued to the street. You run them through a geocoder, match on the resolved coordinate, and it works. Mostly. Some fraction land on the wrong building, the wrong block, the wrong town, and the geocoder won't tell you which fraction. It hands back a pin for every row and the same silent confidence for all of them: none.

That's the gap we set out to close. Every geocoder chases accuracy, be-right-more-often, and so do we. The piece almost nobody hands you is the one underneath it: a number on each answer that tells you which ones to trust, so you can keep the good ones and send the rest to a human.

Three public datasets land on your desk. The national provider registry — NPPES, every NPI in the country. A federal telecom-funding file from the FCC's Rural Health Care program, one slice of the Universal Service Fund. A state list of licensed nursing facilities from Texas HHSC. You want to know which records describe the same provider, and not one of the three shares an identifier with the other two. The NPI is internal to NPPES. The funding file keys on its own SPIN. The state list has its own facility ID. There's no crosswalk, because nobody ever built one.

So you do what everyone does: you start a spreadsheet, you sort by name, and you give up around row 400.

You have a pile of records and no key to join them on. A clinic shows up in the federal provider registry, again in a state licensing export, a third time in a funding-program spreadsheet somebody keyed by hand. None of those files share an identifier. The provider number is internal to one publisher, the facility ID to another. So the join you actually want — which of these are the same place — isn't a join at all. It's a judgment call, repeated a few million times.

The market hands you two tools for this, and each one solves a different half of the problem. Neither one finishes.

Here are two addresses. Tell me if they're the same place.

123 Main Street, Suite 400, Springfield IL 62704
123 Main St #400, Springfield, Illinois

Easy — yes. Now these two:

Jyllandsgade 15, 9000 Aalborg
Jyllandsgade 75, 9000 Aalborg

Also easy — no. They're 650 metres apart.

Now imagine a string-similarity matcher looking at those same four lines. The first pair, the same place, scores low: different punctuation, "Street" vs "St", a reordered unit. The second pair, different places, scores 0.96 — one character apart. The tool you'd reach for gets both backwards. This isn't a tuning problem you can threshold your way out of. It's the wrong coordinate system.