Re: How to find double entries - Mailing list pgsql-sql

Andreas wrote:
> Hi,
> 
> how can I find double entries in varchar columns where the content is not
> 100% identical because of a spelling error or the person considered it
> "looked nicer" that way?
> 
> I'd like to identify and then merge records of e.g.   'google', 'gogle', 
> 'guugle' Then I want to match abbrevations like  'A-Company Ltd.', 'a
> company ltd.', 'A-Company Limited'
> 
> Is there a way to do this? It would be OK just to list candidats up to be
> manually checked afterwards.
> 
> 
This is really tough, whether you use postgreSQL or not. I once worked for a
large regulated monopoly who had to look up stuff in telephone books a lot.
Several of us got a magnetic tape with all the business, professional, and
government listings for a county on Long Island in it; we thought
residential would be too easy and did not have the disk space for it (in
those days, hard drives cost $40,000 and held 40 Megabytes).

One of the things we did was do leading substring partial matching. I.e., we
could look for "J Smith" and find "Smith, John Robert"; we could find him
with "Rob Smit" as well.

This helped because people did not put their names in the right order.
Sometimes they said "Smith, John" and other times they said "John Smith" or
"J Smith" and meant the same guy. Sometimes they said "White St" or "White
Street" when they meant "White Road". And so it went. Sometimes they spelled
her name "Jeannine" when she spelled it "Genine". So the question always
ended up being what did they really mean.

To make matters worse, someone had run a program over the data to spell out
abbreviations, but that generated "42 Saint" instead of "42 Street" and
problems like that. Also, if a field was too big, the data-entry clerks just
kept on typing into the next field, so a lot of entries had, as an address,
"If no entry call"

I stuck in a phonetic matcher (similar to Soundex coding) so that a query
for "Ristorante Italiano" would find "Mom's Pizza Italian Restaurant". It
would also find Genine whey you were looking for Jeannine.

People often got the towns wrong. Around here, there is a town on the map,
but the telephone company had that in another town, and the tax collector
had it in yet another town. So towns were weighted lower than names.

For government listings, there was a separate record for each line in the
telephone book, so you would get entries like this, each line a separate record:

U S Government  Federal Aviations Administration     Kennedy Airport        Pilot Information           Arrivals
  Departures
 

We had to make it find "Pilot Arrivals" so indexing was not trivial until
you figured out how to do it.

But when all was said and done, we put a program on the output that
displayed answers in terms of decreasing goodness of match and stuck the
users with deciding what they wanted. A big trick was to do all this without
doing a sequential search of the database.


--  .~.  Jean-David Beyer          Registered Linux User 85642. /V\  PGP-Key: 9A2FC99A         Registered Machine
241939./()\ Shrewsbury, New Jersey    http://counter.li.org^^-^^ 21:30:01 up 33 days, 2:32, 1 user, load average: 4.06,
4.07,4.11