Avoiding duplicate addresses in a database table

Question

I'm trying to avoid reinventing the wheel when it comes to storing street addresses in a table only once. Uniqueness constraints won't work in some common situations:

100 W 5th Ave
100 West 5th Ave
100 W 5th

200 N 6th Ave Suite 405
200 N 6th Ave  #405

I could implement some business logic or a trigger to normalize all fields before inserting and use uniqueness constraints across several fields in the table, but it would be easy to miss some cases with something that varies as much as street addresses.

What would be best would be a universal identifier for each address, perhaps based on GPS coordinates. Before storing a new address look up its GUID and see if the GUID already exists in the Address table.

An organization like Mapquest, the Postal Serice, FedEx, or the US government probably has a system like this.

Has anyone found a good solution to this?

Here's my Address table now (generated by JPA):

CREATE TABLE address
(
  id bigint NOT NULL,
  "number" character varying(255),
  dir character varying(255),
  street character varying(255),
  "type" character varying(255),
  trailingdir character varying(255),
  unit character varying(255),
  city character varying(255),
  state character varying(255),
  zip integer,
  zip4 integer,
  CONSTRAINT address_pkey PRIMARY KEY (id)
)

Answer 1

Look up the address in Google maps and use the spelling they use.