Match Addresses from 2 Datasets

Question

Hi
I have 2 datasets containing addresses and want to check if they are matching or not.

Set 1:

LocationCityCost110 Ave NDenver10076 Adelaide StreetBuffalo50

Set 2:

LocationQuantity110 Avenue North, Denver576 Adelaide ST,Buffalo,NY10

The words are not an exact match between the data set. 
For Ex: one set address is ST whereas other one its Street. Same for Ave(Avenue) etc

Is there any way to match this quickly without using Fuzzy match?

I am planning to split the city into a different column to only extract the address and street name.

Thanks

Taher

NicholasM · Answer

Hey @tahertalib,

Hmm I bet there is a way to get this accomplished without using a Fuzzy Match, the question more is it worth the trouble? In addition to parsing out the City from the Street address, what you would need to do is create a series of Look-up tables to standardize the Street and Cardinal Direction abbreviations. The process would look something like a series of Find-Replace Tools to correct the address, then a join tool once the addresses from the two datasets look the same.

Sometimes the Fuzzy Match tool gets a bad wrap of being very difficult. I will point out that it is equal parts art and science, however, addresses are some of the easier fuzzy matches to establish. I would recommend trying it if creating the look-up tables prove to be too much work. Here is a great training led by Nick Smith.

Let me know if you have questions

MattBSlalom · Answer

May I ask why you're against using Fuzzy Match?

Other options could be:

* Clean up the addresses in both datasets to a standard value
* Translate the addresses into latitude / longitude then compare these coordinates instead

Both of these solutions can be accomplished either with tools in the Address tool palette if you have the additional license for the CASS dataset (CASS tool & Street Geocoder tool) or by making API calls to Google or Bing Maps.

Here's an article describing the Google Maps API scenario:  https://www.theinformationlab.co.uk/2017/01/23/geocode-addresses-alteryx-google-api/

There are also several Macros available in the Gallery, but I do not have any personal experience with any of them:  Alteryx Analytics Gallery | Search - Address