• 0

[SQL] Compare Addresses


Question

Hello gang,

We are having an issue where we need to compare some address data to insure that we are not entering a duplicate record. (The data is coming from multiple outside sources, so an identity/GUID won't work... but thanks for thinking of that)

Consider the following

Company Name

Address

City

State

ZIP Code

If the first person was to send us:

"Bob's Restaurant"

"123 Main Street"

"New York"

"NY"

"10018"

and the second person sends us:

"Bobs Restaurant"

"123 Main St."

"New York"

"NY"

"10018"

Notice that the second restaurant does not have the appostrophy and the street name is abreviated. So an exact match is SQL would not work.

We're looking at Latitude and Longitude, and that should get us there, but in case anyone has any other thoughts..... thanks.

Link to comment
Share on other sites

8 answers to this question

Recommended Posts

  • 0

I'm not sure what database server you are using, but how about something like soundex()? If you can compile a list of common ways to abbreviate parts of an address, and could be reasonably sure that all occurances of St. really equals Street (I'm not sure how your data is stored, if you have city in one column, state in one column, street address in one column, name of location in one column, etc. then this would probably doable), then this would work for you:

DROP TEMPORARY TABLE IF EXISTS t1;
CREATE TEMPORARY TABLE t1 
(
  address text
);

INSERT INTO t1 (address) 
VALUES 
('Bob\'s Restaurant 123 Main Street New York NY 10018'),
('Bobs Restaurant 123 Main St. New York NY 10018');

UPDATE t1 SET address = REPLACE(address, 'St.', 'Street');

SELECT address, 
		   soundex(address) 
FROM t1;

This returns this data set, so, you can see that it is a duplicate address:

+----------------------------------------------------+-------------------+
| address											| soundex(address)  |
+----------------------------------------------------+-------------------+
| Bob's Restaurant 123 Main Street New York NY 10018 | B2623653523635625 |
| Bobs Restaurant 123 Main Street New York NY 10018  | B2623653523635625 |
+----------------------------------------------------+-------------------+

In practice, the way I would do this is to create a stored procedure that takes an address as a parameter or set of parameters, one param for each part of the address. Then you could run a set of replaces against the parameter to normalize it to the rest of the data already in the database (replace St. with Street, or, Street with St., whichever you want, Dr. with Drive, Ln. with Lane, etc.). So, assuming your sp took a param named p_address, and you have already ran your replaces to normalize the string, you could run a query like this:

DECLARE p_is_duplicate tinyint(1) NOT NULL DEFAULT 0;

SET p_is_duplicate = EXISTS(SELECT * FROM addresses WHERE soundex(address) = soundex(p_address);

IF NOT p_is_duplicate THEN 
  INSERT INTO address SET address = p_address;
END IF;

Hope this helps, latitude and longitude will probably be more accurate in the long run, but something like this may work for you depending on what dbms you use and how your data is stored. :)

Link to comment
Share on other sites

  • 0

Drakja,

Thanks! I completely forgot about SoundEx. I will look into that today.

We are using SQL Server 2000, but using Integration Services is prob not going to happen <sigh>

Link to comment
Share on other sites

  • 0

UPDATE:

There is an issue with SoundEx where is does not acurately compare. The two examples below both return values that state they are the same even though they are not (different city, state and zip)

I understand that I can break out the street while doing my compare, and I may do that, I simply wanted to alert other developers of this issue.

SELECT SOUNDEX ('Bob 500 1st St. Seattle, WA 98109'), SOUNDEX ('Bobby 500 First St New York, NY')

SELECT DIFFERENCE ('Bob 500 1st St, Seattle 98109', 'Bobby 500 First St New York, NY 10036')

I may actually may use a DLL that allows me to create lat/long and compare these values....

Thanks again for the feedback and idea.

Link to comment
Share on other sites

  • 0

I knew there was another SoundEx example that I wanted to mention:

SELECT SOUNDEX ('500 First Street'), SOUNDEX ('400 First Street')

Both of these come back as a match. So be aware that soundex is prob not the best solution for address compare. I've moved on to (attempting) to work with lat/long. (It's the getting of these values that is killing me)

Have a great weekend.

Link to comment
Share on other sites

  • 0
I'm not sure what database server you are using, but how about something like soundex()? If you can compile a list of common ways to abbreviate parts of an address, and could be reasonably sure that all occurances of St. really equals Street (I'm not sure how your data is stored, if you have city in one column, state in one column, street address in one column, name of location in one column, etc. then this would probably doable), then this would work for you:

DROP TEMPORARY TABLE IF EXISTS t1;
CREATE TEMPORARY TABLE t1 
(
  address text
);

INSERT INTO t1 (address) 
VALUES 
('Bob\'s Restaurant 123 Main Street New York NY 10018'),
('Bobs Restaurant 123 Main St. New York NY 10018');

UPDATE t1 SET address = REPLACE(address, 'St.', 'Street');

SELECT address, 
		   soundex(address) 
FROM t1;

This returns this data set, so, you can see that it is a duplicate address:

+----------------------------------------------------+-------------------+
| address											| soundex(address)  |
+----------------------------------------------------+-------------------+
| Bob's Restaurant 123 Main Street New York NY 10018 | B2623653523635625 |
| Bobs Restaurant 123 Main Street New York NY 10018  | B2623653523635625 |
+----------------------------------------------------+-------------------+

In practice, the way I would do this is to create a stored procedure that takes an address as a parameter or set of parameters, one param for each part of the address. Then you could run a set of replaces against the parameter to normalize it to the rest of the data already in the database (replace St. with Street, or, Street with St., whichever you want, Dr. with Drive, Ln. with Lane, etc.). So, assuming your sp took a param named p_address, and you have already ran your replaces to normalize the string, you could run a query like this:

DECLARE p_is_duplicate tinyint(1) NOT NULL DEFAULT 0;

SET p_is_duplicate = EXISTS(SELECT * FROM addresses WHERE soundex(address) = soundex(p_address);

IF NOT p_is_duplicate THEN 
  INSERT INTO address SET address = p_address;
END IF;

Hope this helps, latitude and longitude will probably be more accurate in the long run, but something like this may work for you depending on what dbms you use and how your data is stored. :)

Soundex performs a match based on the pronumciation of the first four constanants it encounters (I had to look this up before because I ran into a similar situation). So "Bob's R" and "Bobs R" would be used for comparison and thus match.

It's also a good habit to ALWAYS replace input quotation marks and apostrophes with "`" or "``". You're open to SQL injection attacks if you don't.

Link to comment
Share on other sites

  • 0

Try using a CHECKSUM instead:

SELECT * FROM ADDRESS WHERE CHECKSUM(company + address + city + state + zip) = CHECKSUM(@company + @address + @city + @state + @zip)

Be sure to encase any fields with an isnull() to default the value out to '' if any of those fields can be null. EG

SELECT * FROM ADDRESS WHERE CHECKSUM(isnull(company, '') + isnull(address, '') + isnull(city, '') + isnull(state, '') + isnull(zip, '')) = CHECKSUM(@company + @address + @city + @state + @zip)

If the query returns any rows, you have a duplicate.

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.