Using Arcmap to Join two tables based on a field with similar values

Question

I am working in Arcmap and attempting to understand a way to join two tables based on similar values.

In Service_Points I have a field called Address where the value is 1862 Waterfall Rd

In Customer_Parcels I have a field called Customer_Address where the value is 1862 Waterfall Road

There are barely any perfect matches since every road type is written long instead of abbreviated. Such as Lane and Ln, Drive and Dr, Parkway and Pkwy, etc.

I was successfully able to complete my task by creating new fields for each table using the first 7 characters and joining based off of that.

I'd like to learn from this time-consuming endeavor and understand if there was a better way.

Would there have been a more efficient approach to this?

• John: Depending on how many tables or how often you need to do things, you can use the Find and Replace to change all Rd values to Road, Ln to Lane, etc. rather easily by keeping the form open and doing through your list. I'm assuming there are a limited number of changes needed. If more it probably could be scripted.
• JTstone: Thank you, this had been my approach in the beginning. I had been manually calculating each variation. I didn't realize the Find and Replace existed.
• JTstone: Just a follow up. The troublesome points where the addresses didn't quite match, turns out was more than just Ln, Rd, Blvd, etc. Addresses such as "123 Sunchase Rd" on the parcel, were "123 Sun Chase Rd" on the points. I ended up creating some short address fields on each and calculated the address to 8 length and matched what I could. Then varied the length over and over until I got close to 100%.

Answer 1

By text as you have done I would say probably not, it's a classic problem with address information.

If your data has a CONSISTENT spatial relationship such as your service point is ALWAYS within the parcel, you could give points a unique ID and do a spatial join to pass the ID field over, then you could use that to do a table join. But it is rarely that simple as there are always cases where the logic is broken.

• JTstone: Great idea, thank you. I have about 500,000 points and half of them are consistently placed in the parcels. The other half are in the street or the complete wrong location and this is the selection I was trying to join by the address field.