ArcGIS Pro Join One to Many Join by Attribute

[DavidStone1]

I am interested in doing a join of spatial data to a csv in which one geographic feature is joined to multiple values from the csv. However, the only apparent option for a join from a spatial file to a csv is a one to one, and I need to do a one to many (in the same way that this is an option for a spatial join). I know the relate function is a potential solution but I will eventually need to export the data as a shapefile so the increased entries have to be exportable. Has anyone found a workaround specific to ArcGIS Pro?

[DanPatterson_Retired]

David the same options exist in Pro, relate is your option, but if the geometry is point geometry then you switch the join if you calculate the X, Y coordinates in the table first.  You can then re-add the data as an event layer.  If you have point/polygon geometry, then relates are your only options

[DavidStone1]

Thanks for the reply. The original spatial data is a polygon geometry file. I was under the impression that if I create a relate, the relate wouldn't remain if I extracted the data (e.g. the "Feature Class to Shapefile" tool would not capture the related data). Is this the case?

[DanPatterson_Retired]

They aren't permanent, they have to be re-established.  Joins can be made permanent, but that is just a shortcut to adding a new field, calculating values from it using the joined data.

Nothing gets carried down to the shapefile level, so be careful of things like your attribute field names etcetera.

[KentaOkuyama · Accepted solution]

Hi David, 

I have just come across the same challenge, and feel like I figured that out, so positing my solution here. 

You can use Make Query Table for executing one-to-many join.

How To: Create a one-to-many join in ArcMap 

The site above is an instruction for ArcMap to Make Query Table, but the same tool was found in ArcGIS Pro for mine. Using Python would be more helpful if you want to export the temporal layer created by Make Query Table as a permanent gdb file or shapefiles. 

I'm pasting my work for an example.

Goal: Join polygon feature class which has 59 city names and table which has temporal population value (2006-2010) for 59 city names.

Kyuson_H12 

• Polygon feature class
• 59 rows
• Field "Kyuson" has 59 different city names (This will be used for matching with table)

agg15male

• Table
• 590 rows
• Field "Kyuson" has 59 different city names  (This will be used for matching with Polygon feature class)
• The data is long-format temporal data (2006-2015), so there are 590 rows

import arcpy,os
from arcpy import env
env.overwriteOutput = True
env.workspace = r"C:\Users\kenta\Documents\ArcGIS\Projects\MyProject2\Rdata.gdb"

# list the polygon feature class and table that want to be joined

tableList = [ r"C:\Users\kenta\Documents\ArcGIS\Projects\MyProject2\Rdata.gdb\Kyuson_H12",\
r"C:\Users\kenta\Documents\ArcGIS\Projects\MyProject2\Rdata.gdb\agg15male"]

# define the query for matching

whereClause = "Kyuson_H12.Kyuson = agg15male.Kyuson"

# name the temporary layer name created by MakeQueryTable

lyrName = "Kyuson_layer"

# name the output fc name

outFeatureClass = "agg15male_poly"

arcpy.MakeQueryTable_management(tableList, lyrName,"USE_KEY_FIELDS", "", "", whereClause)

# since lyrName created by MakeQueryTable is temporal, save it as parmanent gdb file

arcpy.CopyFeatures_management(lyrName, outFeatureClass)

****Points you need to be careful***

1. Feature class and table that you want to join have to be in the same file geotabase. So, you need to convert csv file to gdb table first and locate them in the same file geotabase as your feature class.
2. MakeQueryTable generate temporary layer, so you need to export it as your desired file by CopyFeatures_management.

You might have finished this task a while ago, but hoping it will be helpful for anyone running into the same problems in the future.

Best,

Kenta

[LoganAshmore1]

"I have just come across the same challenge, and feel like I figured that out, so positing my solution here. "

I think you have figured it out; worked perfectly for my project joining 19 years of Quarterly data to 68 Districts. 

[LSantos]

Thank you!

[TedHallum]

Kenta, you saved my bacon with this one. Thank you for posting this solution!!!

[XuanZhang]

I have another possible solution (Kenta's method worked for me as well). Using Pro, I had exactly the same issue for one to multiple join when the table was a CSV file and the other was a shapefile, but the join worked when both spatial data and table are in the same geodatabase. People who have the same issue may want to make them in the same geodatabase and try for another time. 

: )

[Bethpc9]

This is a much simpler solution with no coding required! Thank you Xuan 🙂