pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to find double entries
8+ messages / 7 participants
[nested] [flat]
* How to find double entries
@ 2008-04-16 03:15 Andreas <maps.on@gmx.net>
2008-04-16 03:23 ` Re: How to find double entries Tom Lane <tgl@sss.pgh.pa.us>
2008-04-16 04:22 ` Re: How to find double entries Craig Ringer <craig@postnewspapers.com.au>
2008-04-16 05:05 ` Re: How to find double entries Tena Sakai <tsakai@gallo.ucsf.edu>
2008-04-16 06:15 ` Re: How to find double entries Volkan YAZICI <yazicivo@ttmail.com>
2008-04-20 01:54 ` Re: How to find double entries Jean-David Beyer <jeandavid8@verizon.net>
0 siblings, 5 replies; 8+ messages in thread
From: Andreas @ 2008-04-16 03:15 UTC (permalink / raw)
To: pgsql-sql
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.
Regards
Andreas
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to find double entries
2008-04-16 03:15 How to find double entries Andreas <maps.on@gmx.net>
@ 2008-04-16 03:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-04-16 14:27 ` Re: How to find double entries Vivek Khera <vivek@khera.org>
4 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2008-04-16 03:23 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql
Andreas <maps.on@gmx.net> writes:
> 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.
There are some functions in contrib/fuzzystrmatch that seem like they'd
help you find candidate duplicates. contrib/pg_trgm and text search
might also offer promising tools.
What's really a duplicate sounds like a judgment call here, so you
probably shouldn't even think of automating it completely.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to find double entries
2008-04-16 03:15 How to find double entries Andreas <maps.on@gmx.net>
2008-04-16 03:23 ` Re: How to find double entries Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-04-16 14:27 ` Vivek Khera <vivek@khera.org>
2008-04-16 15:49 ` Re: How to find double entries Craig Ringer <craig@postnewspapers.com.au>
0 siblings, 1 reply; 8+ messages in thread
From: Vivek Khera @ 2008-04-16 14:27 UTC (permalink / raw)
To: pgsql-sql
On Apr 15, 2008, at 11:23 PM, Tom Lane wrote:
> What's really a duplicate sounds like a judgment call here, so you
> probably shouldn't even think of automating it completely.
I did a consulting gig about 10 years ago for a company that made
software to normalize street addresses and names. Literally dozens of
people worked there, and that was their primary software product. It
is definitely not a trivial task, as the rules can be extremely complex.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to find double entries
2008-04-16 03:15 How to find double entries Andreas <maps.on@gmx.net>
2008-04-16 03:23 ` Re: How to find double entries Tom Lane <tgl@sss.pgh.pa.us>
2008-04-16 14:27 ` Re: How to find double entries Vivek Khera <vivek@khera.org>
@ 2008-04-16 15:49 ` Craig Ringer <craig@postnewspapers.com.au>
0 siblings, 0 replies; 8+ messages in thread
From: Craig Ringer @ 2008-04-16 15:49 UTC (permalink / raw)
To: Vivek Khera <vivek@khera.org>; +Cc: pgsql-sql
Vivek Khera wrote:
>
> On Apr 15, 2008, at 11:23 PM, Tom Lane wrote:
>> What's really a duplicate sounds like a judgment call here, so you
>> probably shouldn't even think of automating it completely.
>
> I did a consulting gig about 10 years ago for a company that made
> software to normalize street addresses and names. Literally dozens of
> people worked there, and that was their primary software product. It is
> definitely not a trivial task, as the rules can be extremely complex.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to find double entries
2008-04-16 03:15 How to find double entries Andreas <maps.on@gmx.net>
@ 2008-04-16 04:22 ` Craig Ringer <craig@postnewspapers.com.au>
4 siblings, 0 replies; 8+ messages in thread
From: Craig Ringer @ 2008-04-16 04:22 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: 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?
When doing some near-duplicate elimination as part of converting a
legacy data set to PostgreSQL I found the `fuzzystrmatch' contrib module
immensely helpful.
http://www.postgresql.org/docs/current/static/fuzzystrmatch.html
--
Craig Ringer
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to find double entries
2008-04-16 03:15 How to find double entries Andreas <maps.on@gmx.net>
@ 2008-04-16 05:05 ` Tena Sakai <tsakai@gallo.ucsf.edu>
4 siblings, 0 replies; 8+ messages in thread
From: Tena Sakai @ 2008-04-16 05:05 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; pgsql-sql
Hi,
In a recent linux magazine article (http://www.linux-mag.com/id/5679)
there was a mentioning of Full-Text Search Integration. Which I know
nothing about, but sounded interesting to me. You might want to
check it out.
Regards,
Tena Sakai
tsakai@gallo.ucsf.edu
-----Original Message-----
From: pgsql-sql-owner@postgresql.org on behalf of Andreas
Sent: Tue 4/15/2008 8:15 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] How to find double entries
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.
Regards
Andreas
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to find double entries
2008-04-16 03:15 How to find double entries Andreas <maps.on@gmx.net>
@ 2008-04-16 06:15 ` Volkan YAZICI <yazicivo@ttmail.com>
4 siblings, 0 replies; 8+ messages in thread
From: Volkan YAZICI @ 2008-04-16 06:15 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql
On Wed, 16 Apr 2008, Andreas <maps.on@gmx.net> writes:
> 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.
You can try something similar to below example. (levenshtein(text, text)
function is supplied by fuzzystrmatch module.)
SELECT T1.col, T2.col
FROM tbl AS T1,
INNER JOIN tbl AS T2
ON T1.col <> T2.col AND
levenshtein(T1.col, T2.col) < (length(T1.col) * 0.5)
Regards.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to find double entries
2008-04-16 03:15 How to find double entries Andreas <maps.on@gmx.net>
@ 2008-04-20 01:54 ` Jean-David Beyer <jeandavid8@verizon.net>
4 siblings, 0 replies; 8+ messages in thread
From: Jean-David Beyer @ 2008-04-20 01:54 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2008-04-20 01:54 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-04-16 03:15 How to find double entries Andreas <maps.on@gmx.net>
2008-04-16 03:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-04-16 14:27 ` Vivek Khera <vivek@khera.org>
2008-04-16 15:49 ` Craig Ringer <craig@postnewspapers.com.au>
2008-04-16 04:22 ` Craig Ringer <craig@postnewspapers.com.au>
2008-04-16 05:05 ` Tena Sakai <tsakai@gallo.ucsf.edu>
2008-04-16 06:15 ` Volkan YAZICI <yazicivo@ttmail.com>
2008-04-20 01:54 ` Jean-David Beyer <jeandavid8@verizon.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox