topic Re: ArcGIS Pro Join One to Many Join by Attribute in ArcGIS Pro Questions

ArcGIS Pro Join One to Many Join by Attribute

DavidStone1 — Wed, 17 Jan 2018 21:57:08 GMT

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?

Re: ArcGIS Pro Join One to Many Join by Attribute

DanPatterson_Retired — Thu, 18 Jan 2018 00:57:02 GMT

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

Re: ArcGIS Pro Join One to Many Join by Attribute

DavidStone1 — Thu, 18 Jan 2018 01:23:26 GMT

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?

Re: ArcGIS Pro Join One to Many Join by Attribute

DanPatterson_Retired — Thu, 18 Jan 2018 01:31:14 GMT

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.

Re: ArcGIS Pro Join One to Many Join by Attribute

KentaOkuyama — Wed, 19 Sep 2018 06:55:09 GMT

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***

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.
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

Re: ArcGIS Pro Join One to Many Join by Attribute

LoganAshmore1 — Mon, 03 Jun 2019 23:27:20 GMT

"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.

Re: ArcGIS Pro Join One to Many Join by Attribute

LSantos — Mon, 12 Aug 2019 22:43:29 GMT

Thank you!

Re: ArcGIS Pro Join One to Many Join by Attribute

TedHallum — Sat, 04 Jul 2020 15:31:55 GMT

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

Re: ArcGIS Pro Join One to Many Join by Attribute

XuanZhang — Thu, 23 Jul 2020 03:58:48 GMT

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.

: )

Re: ArcGIS Pro Join One to Many Join by Attribute

Bethpc9 — Tue, 09 Feb 2021 08:04:55 GMT

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

Re: ArcGIS Pro Join One to Many Join by Attribute

MatthewMalone — Fri, 25 Jun 2021 19:41:30 GMT

I realize this is almost a year later, but thank you to Xuan for helping me stop beating my head against the wall trying to solve this issue! The join validation (using Pro 2.8) kept stating one-to-one when I knew it should've read one-to-many. Lo and behold, I had the source and join table in separate geodatabases. Exported the source feature class into the same default.gdb created with the project and it worked perfectly. Thanks again! 👍

Re: ArcGIS Pro Join One to Many Join by Attribute

DaltonAiken1 — Tue, 06 Jul 2021 17:05:27 GMT

Worked for me too, great suggestion!

Re: ArcGIS Pro Join One to Many Join by Attribute

JI_Tampa — Thu, 29 Jul 2021 14:35:16 GMT

I found an alternate solution, for anyone that might be referencing this post later. I had a table with data by month for different routes (1155 features) but no spatial or location information and route polygon areas (229 features) that I needed to join to show the attributes that vary by month for each route. Make query table created a multiplication of my 1155*229 features but did not maintain the attributes needed to display this spatially without additional processes. This was my workflow in ArcPro:

Start with the polygon and table both having a uniqueID field for joins. You may have to create a field that has a relevant uniqueID, I used a day and route number.

Export the table and polygon/ point layer in the same gdb, this will not work on an excel brought in.

Run add ‘attribute index’ tool on these fields.

Convert the polygon to a point using the’ Feature to Point’ tool and check the box for ‘inside’ in ArcPro- I am using 2.7

Run the tool called ‘calculate geometry attributes’ tool, I used WGS 84 and linear US feet and square feet for the parameters.

Note: creating fields and calculating geometry didn’t work for me when I went to display x,y date because of required field type errors.

Now you have a point file with and x and y field and a unique ID field ready to join.

The many to one join:

Select the table (the ‘many’) and add join, join to the point layer(the one) with the x,y fields. Use the UniqueID field for the join and keep all records.

Export this joined table to the gdb. (Display x,y tool will fail on a joined layer.)

Take the new table and right click to run the ‘display x,y’ tool. Check the attribute count, it should match the original table, if not check the join and fields.

Re: ArcGIS Pro Join One to Many Join by Attribute

Barry_Zickuhr — Mon, 22 Aug 2022 17:21:35 GMT

Hi David,

I know this is old, but another less code-y solution is to save the CSV as a geodatabase table IN THE SAME geodatabase as the feature layer.

Join then picks up the one-to-many immediately. But as noted, the table and the layer must be in the same geodatabase. Otherwise it just does a one-to-one.

Barry

Re: ArcGIS Pro Join One to Many Join by Attribute

JesseD80 — Fri, 15 Sep 2023 18:38:38 GMT

Your reply should honestly be marked as the solution. Way easier to do this. I just tried it too and it works beautifully.

Re: ArcGIS Pro Join One to Many Join by Attribute

quillaja — Tue, 05 Mar 2024 08:32:08 GMT

Yes, this is the better solution. I couldn't join a csv to a feature class until the csv was brought into the geodatabase. After that, both Make Query Table and normal join (Create Join) worked fine, so I went with the simpler normal join.

Re: ArcGIS Pro Join One to Many Join by Attribute

VictoriaGotthardt — Fri, 20 Sep 2024 03:02:25 GMT

Wow, worked! Cheers, Xuan!
Shows how important it is to keep the layers, or a copy in the same geodatabase for a smooth process.