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-frequently, and so do we. The piece that few geocoders hand 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 part 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 none of the three publishers ever built one.

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

Suppose you have a set of records and no key to join them on. A clinic appears in the federal provider registry, again in a state licensing export, and a third time in a funding-program spreadsheet that someone typed by hand. The files share no identifier. The provider number is internal to one publisher, and the facility ID is internal to another. You must judge which records describe the same place for each pair, repeated a few million times.

The market offers two kinds of tools for this. Each solves part of the problem, and neither solves all of it.

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

They are. Now compare these two:

Jyllandsgade 15, 9000 Aalborg
Jyllandsgade 75, 9000 Aalborg

They are different places, 650 meters apart.

A string-similarity matcher scores the first pair, the same place, low because of different punctuation, "Street" vs "St", and a reordered unit. It scores the second pair, different places, at 0.96 because they differ by one character. The usual tool gets both pairs wrong. A threshold change cannot fix this, because string similarity measures the wrong thing.