in reply to Merge/Purge address data

I think you need to rethink how you are storing your data in the database. (If it is relational I mean.) If you stored a unique_id for Dr. Balfour, this would be a trivial issue of joining several tables on his id. And, while you might be somewhat successful at doing some complex merge, typos, data-entry and human errors will futher corrupt your best algorithm ( ie. how will you verify the data you distill? -- Hence the old adage about garbage in, garbage out ). I think the answer is much simpler to add keys to your existing data and normalize it so that you can use the power of a relational database. That is the whole reason they were invented IMHO. Cheers, JamesNC

Replies are listed 'Best First'.
Re: Re: Merge/Purge address data
by cleverett (Friar) on Nov 11, 2003 at 12:25 UTC

    Heh,

    No stranger to third normal form am I. I think you miss my point.

    Information coming off a web form where a strict "log in with a password to use the site" policy is NOT in effect will contain tons of blank records and near duplicates. But making users do the extra work of selecting a user name and password raises a barrier, which means we get a lot less data.

    The solution I have in place:

    1. gathers data from a web form into one set of tables
    2. cleans up that data
    3. merges cleaned data into another table for other work

    I'm looking to automate steps 2 and 3.

[reply]
      Depending what your site is offering, you may want to give an option for a user to select a "code" that they can enter to automatically fill in their name and address.

      This of course assumes the user has some reason to fill in address information with some attempt at accuracy.

      Just some thoughts on how dageek might try it. try to create some standardized wording. Split on white space, replace MD with DR in name fields (you will build a list of others). In the Address fields, replace NORTH with N., South and So with S. and so on.

      Now compare for exact match for words and numbers.

      Store "standardized" name and address in main database for future compares.

      You have a good piece of work ahead of you no matter what method you choose - Good Luck!

      dageek

[reply]

In Section Seekers of Perl Wisdom