拼写检查街道地址的最佳方法是什么？

Question

When importing new addresses to my DB, I do a spellchek to see if the street already exists (the new street is only spelled wrong).

We are currently usingthe Levenshtein method in MySQL query to find similar street names. The problem is street numbers. Having street nmbers in the address really slows down the similarity search / spellcheking.

Example:

Street abc 34
Street abc 37
Street abc 39

These street names is spelled correctly, but the Levenshtein method thinks they are misspelled because of the street numbers.

We have develope a PHP function that takes anything after (and including) the first digit and puts it in another column.

This works fine for streets having the street number at the end, but will not work for countries having the street numbers at the start.

I'm wondering if anyaone else have worked on similar problems?

Update
The solution is for a store locator web site and I'm currently working on the module that will import store lists.

One solution is using Google Maps API and see if it returns geo address.

Answer 1

Uh-oh, generic address is an extremely hard problem. My suggestion is that you perform the minimal amount of validation you can tolerate.

If this is for shipping purposes, for instance, just use dropdowns for the stuff that's going to determine shipping costs, for example. If you have different shipping costs for different countries, just provide a free-form text area with no validation and a countries dropdown. If the user can't spell their address, tough luck. You can have whomever that handles shipping verify the address "humanly". Delivery companies and post companies mostly can deliver parcels to misspelled addresses (Randomcountry's post company probably knows their street names better than you, anyway).

If you really need precise addresses, try to find a third-party solution for this. Using Google Maps API might work, and there exist paid solutions for this.

Considering your algorithm, though, the following solution springs to mind; just use a regex to strip numbers (or even non-letters). However, keep in mind that there are correct street names which are numbers (i.e. NY's 9th Avenue).