0

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?

3
  • 2
    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.
    – John
    Jul 31, 2023 at 20:55
  • 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
    Aug 1, 2023 at 13:14
  • 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%.
    – JTstone
    Aug 11, 2023 at 15:26

1 Answer 1

2

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.

1
  • 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.
    – JTstone
    Aug 1, 2023 at 13:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.