Spatial joins when join fields don't match exactly

AngelaSchirck — Thu, 10 Aug 2023 18:43:48 GMT

Hey everyone,

I have a parcels layer and a related table with info (like owners, last sale date, etc). I need to create a join based on parcel ids. Here's the problem. For parcels where there is a Condo (for example), there are multiple units and I need all the records from the info table to join to that parcel. (sounds easy, but wait...)

My problem is for single unit parcels I have parcel numbers like this: 74-43-43-22-12-008-0010 (dashes are removed in the field, I put them here for clarification). For condos there is a parcel number like this 74-43-43-22-12-008 (missing four digits at the end) and then each unit will be something like 74-43-43-22-12-008-0001, 74-43-43-22-12-008-0002, 74-43-43-22-12-008-0003 etc...

If I join my INFO table by Parcel ID the Condo parcels with fewer digits have no matches. I need to do something like this:

If (first 13 digits of PID in join table match first 13 digits of PID in target table) join the records. So I would have a one-to-one for single units and a one-to-many for condos and town homes, etc.

Is there anyway to join this way? I am not afraid to use Python Script or Arcade if it will get the job done. And I cannot alter the tables to add a new field to use as join.

topic Spatial joins when join fields don't match exactly in Data Management Questions

Spatial joins when join fields don't match exactly