SEO

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

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/curre...ystrmatch.html

--
Craig Ringer

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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

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.

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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.


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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.

From what little I've personally seen of others' addressing handling,
some (many/most?) people who blindly advocate full normalisation of
addresses either:

(a) only care about a rather restricted set of address types ("ordinary
residential addresses in <my country>", though that can be bad enough);
or
(b) don't know how horrible addressing is .... yet ... and are going to
find out soon when their highly normalized addressing schema proves
incapable of representing some address they've just been presented with.

with most probably falling into the second category.

Overly strict addressing, without the associated fairly extreme
development effort to get it even vaguely right, seems to lead to users
working around the broken addressing schema by entering bogus data.


Personally I'm content to provide lots of space for user-formatted
addresses, only breaking out separate fields for the post code
(Australian only), the city/suburb, the state, and the country - all
stored as strings. The only DB level validation is a rule preventing the
entry of invalid & undefined postcodes for Australian addresses, and
preventing the entry of invalid Australian states. The app is used
almost entirely with Australian addresses, and there's a definitive, up
to date list of australian post codes available from the postal
services, so it's worth a little more checking to protect against basic
typos and misunderstandings.

The app provides some more help at the UI level for users, such as
automatically filling in the state and suburb if an Australian post code
is entered. It'll warn you if you enter an unknown Australian
suburb/city for an entry in Australia. For everything else I leave it to
the user and to possible later validation and reporting.

I've had good results with this policy when working with other apps that
need to handle addressing information, and I've had some truly horrible
experiences with apps that try to be too strict in their address checking.

--
Craig Ringer

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql