How to compare differently formatted addresses (php / mysql)

Question

I am working on an established php mysql web application with some fairly big data.

As part of the data management routines new data on addresses are added to the database (import) from two different sources.

All of the addresses in this data are from the UK.

The application has used addressRecord.address1 = address1 AND addressRecord.postcode = postcode as a collision detection before inserting a new address, or associating a record with an existing address.

The trouble is the collision test is not conclusive. As the two different sources of data have provided somewhat different address formats.

source 1

    address1 = 'FLAT N, RICHMOND HILL GATE, 1'

    address2 = 'RICHMOND HILL DRIVE'

    address3 = 'BOURNEMOUTH'

    postcode = 'BH2 6LT'

source 2

    address1 = 'Flat N'

    address2 = 'Richmond Hill Gate'

    address3 = '1 Richmond Hill Drive'

    postcode = 'BH2 6LT'

Because this is an established application this duplication already exists in the address table, which I must deal with, but also new data is continually imported and must be related to an address record.

So I am looking for a conclusive (almost conclusive might do) way to compare addresses with slightly different formatting, that is also performant (10's millions of rows of data).

I have so far considered calculating an identification field, which can be augmented to the existing data, and calculated for the new imports, that may for example be the 3 address fields concatenated, with all punctuation removed, or perhaps just the numbers. or ... Any ideas gratefully received.

Answer 1

You will have to convert all addresses to a normalized address format like @chris said. I don't think you will be able to do that 'on-the-fly' in your database. You will be depending on code to do that and store the results in the database. The biggest problem you have there is that the data you have is missing integrity: The same address with or without zip code; Addresses with the same zip code but with a different street name or city; the same address with a different zip code; Street names that are written differently, 'W Churchill Ln' vs 'Winston Churchill Lane'. You will need fuzzy logic to let a computer decide what is right. Data from various sources might be consistent in formatting per source, you might gain from this in some way. The other thing is that one source will be more reliable than the others, you will also be able to use this to your advantage.