Nested join table using python arcpy
I am using Generate near tool to find the closest 25 match between unmatched FC & Census FC
the output of the near tool is split into 25 tables with the rank value.
I have: 1- 25 tables (NEAR_1, NEAR_2, NEAR_3...etc) each one have the same columns(IN_FID,NEAR_FID,TOP_RANK)
2- 25 FC Census data (C2015_J1, C2015_J2, C2015_J3...etc)is copies of Census FC
3- the original layers I ran the Near tools on (census FC, unmatched FC)
What I need to do: 1- I want to create a table with all the data from the 25 near tables and the 25 census table
to do that I have to:
a)join the unmatched FC with the NEAR1 table
b)join the result FC of step a) with the census FC
c)repeat the same step for all the near tables and join the census on each one
2- using model builder I used something like the snapshot below
Notice that the output of every line is the input for the next one
3- I am trying to write a python code to do the same but I am really struggling with it, I think it might be something like this:
nearlist = ["C:\Adminstrative\CODES\WAREHOUSE_For_Testing\Matching_Model.gdb\\NEAR_1",\
"C:\Adminstrative\CODES\WAREHOUSE_For_Testing\Matching_Model.gdb\\NEAR_2"]
censuslist = ["C:\Adminstrative\CODES\WAREHOUSE_For_Testing\Matching_Model.gdb\\C2015_J1",\
"C:\Adminstrative\CODES\WAREHOUSE_For_Testing\Matching_Model.gdb\\C2015_J2"]
j=0
k=0
lastjoindtable="C:\Adminstrative\CODES\WAREHOUSE_For_Testing\Matching_Model.gdb\\UNMATCHED" #assign the unmatched FC to lastjoindtable variable
for j in nearlist:
try:
arcpy.env.qualifiedFieldNames = False
# Set local variables
j_inFeatures = lastjoindtable
j_layerName = "J_tembjoin_" + str(j)
j_joinTable = nearlist
j_joinField = "OBJECTID"
j_joinField2= "NEAR_" + str(j+1) + ".IN_FID"
outFeature = "Matching_Model.gdb"
tempjoin= "Matching_Model.gdb" + "\\" + str(layerName)
# Create a feature layer from the lastjoindtable featureclass
arcpy.MakeFeatureLayer_management (j_inFeatures, j_layerName)
# Join the feature layer to a table
arcpy.AddJoin_management(j_layerName, j_joinField, J_joinTable, j_joinField2)
# Copy the layer to a new permanent feature class
arcpy.CopyFeatures_management(j_layerName, outFeature)
j+=1
except Exception as err:
print(err.args[0])
for k in censuslist:
try:
#"the output of the join in the small for will be assigned to last joind table variable which will be used as inbut in the big for"
# Set local variables
k_inFeatures = tempjoin
k_layerName = "k_tembjoin_" + str(k)
k_joinTable = censuslist
k_joinField = "OBJECTID"
k_joinField2= "C2015_J" + str(k+1)+ ".NEAR_FID"
outFeature = "Matching_Model.gdb"
# Create a feature layer from the lastjoindtable featureclass
arcpy.MakeFeatureLayer_management (k_inFeatures, k_layerName)
# Join the feature layer to a table
arcpy.AddJoin_management(k_layerName, k_joinField, k_joinTable, k_joinField2)
# Copy the layer to a new permanent feature class
arcpy.CopyFeatures_management(k_layerName, outFeature)
lastjoindtable= "Matching_Model.gdb"+ "\\" + str(layerName)
k+=1
except Exception as err:
print(err.args[0])
