0

I'm looking for duplicate records. I have a Property table with the fields street, number, city, state, county and zip. They get geo-coded based on location, but there are some holes in the data. Problem is if they make a simple typing error or omit certain fields, they won't come up as matches.

As of now a straight = comparison and LIKE aren't really doing a very good job. But Jaro Winkler and similar edit distance algorithms are running with extremely poor performance.

whoblitz
  • 1,065
  • 1
  • 11
  • 17
  • Yep, sounds about right. Also take a look at levenshtein distance formula. Implemented one of those for fuzzy string matching a couple years back. Works great for matching up requests with existing data, but really needs human intervention. – Fosco Jun 10 '11 at 17:03
  • If they are US addresses, you might be able to normalize them using USPS Address Standardization API: http://www.usps.com/webtools/address.htm – krubo Jun 11 '11 at 17:14

1 Answers1

0

The CASS-Certified Scrubbing Service from SmartyStreets offers deduplication as part of their address verification process. Just upload the data in a delimited text file and the duplicates will be marked on the output file you download. There's always a free preview for each file you process so you don't have to purchase anything before you're satisfied with the results. I'm a software developer for SmartyStreets and helped write the application. I'm pretty pleased with both its functionality and ease of use. We also have an API you could use but the deduplication would be your responsibility (just compare the full, 12-digit Delivery Point Barcode, which serves as a unique identifier for addresses).

Michael Whatcott
  • 5,603
  • 6
  • 36
  • 50