I have some data in a relational database structure within a geodatabase i.e a feature class with several lookup tables. I need to flatten this data so that certain fields from the lookup tables are appended to the feature class attribute table. A join will not work, neither will using domains and a query table can only do an inner join which is no good.
I know this can be done in python but where do I begin?
I am using ArcGIS 10.0 and the dataset is large - 1.2 million records.
Here is some code that might help. It adds the missing fields, joins and then calculates. This one does each feature class in a workspace and it's 9.3 (still works in 10) but it shows the general idea. In this case the join field is bOID (backup OID).
import sys,os,string,arcgisscripting
gp = arcgisscripting.create(9.3)
InData = sys.argv[1]
JData = sys.argv[2]
try:
Output_Workspace = InData
except:
gp.AddError("Unspecified database")
if len(Output_Workspace) > 1:
gp.workspace = Output_Workspace
FcList = gp.listfeatureclasses("*")
FeatClass = FcList.next()
while FeatClass:
print("#################" + FeatClass)
JoinDataName = JData + "\\" + FeatClass + "_orig"
FCname = InData + "\\" + FeatClass
try:
gp.AddIndex_management (FCname,"bOID","bOID","NON_UNIQUE", "ASCENDING")
print("Index: " + FCname)
except:
print("Unable to index")
try:
gp.AddIndex_management (JoinDataName,"bOID","bOID","NON_UNIQUE", "ASCENDING")
print("Index: " + JoinDataName)
except:
print("Unable to index")
try:
InFields = gp.listfields(FCname)
#print("InFields")
InF = []
#for f in InFields:
f = InFields.next()
while f:
print("Adding " + f.Name + " to list")
InF.append(string.upper(f.Name))
f = InFields.next()
#print("Made list")
JoinFields = gp.listfields(JoinDataName)
#print("Join Fields")
FieldList = []
#for JField in JoinFields:
JField = JoinFields.next()
while JField:
print("> " + string.upper(JField.name))
if not string.upper(JField.name) in InF :
#print(JField.type)
#SmallInteger, Integer, Single, Double, String, Date, OID, Geometry, BLOB
if JField.type == "SmallInteger":
gp.AddField_management (FCname,JField.name,"SHORT")
print("-- " + JField.name + " as SHORT")
FieldList.append(JField.name)
elif JField.type == "Integer":
gp.AddField_management (FCname,JField.name,"LONG")
print("-- " + JField.name + " as LONG")
FieldList.append(JField.name)
elif JField.type == "Single":
gp.AddField_management (FCname,JField.name,"FLOAT")
print("-- " + JField.name + " as FLOAT")
FieldList.append(JField.name)
elif JField.type == "Double":
gp.AddField_management (FCname,JField.name,"DOUBLE")
print("-- " + JField.name + " as DOUBLE")
FieldList.append(JField.name)
elif JField.type == "String":
#print("-- Adding " + JField.name + " as TEXT " + str(JField.Length))
gp.AddField_management (FCname , JField.name ,"TEXT","", "", JField.Length)
print("--- " + JField.name + " as TEXT " + str(JField.Length))
FieldList.append(JField.name)
elif JField.type == "Date":
gp.AddField_management (FCname,JField.name,"DATE")
print("-- " + JField.name + " as DATE")
FieldList.append(JField.name)
JField = JoinFields.next()
gp.MakeFeatureLayer_management (FCname,"Layer")
print("Feature Layer")
gp.AddJoin_management ("Layer","bOID",JoinDataName,"bOID","KEEP_COMMON")
print("Joined")
for CalcField in FieldList:
try:
#BarrierPoints_View__3_, "BarrierPoints.featid", "[BarrierPoints_orig.featid]", "VB", ""
#gp.CalculateField_management("Layer",FeatClass + "." + CalcField,"\"" + FeatClass + "_orig" + "." + CalcField +"\"","VB","")
gp.CalculateField_management("Layer",FeatClass + "." + CalcField,"[" + FeatClass + "_orig" + "." + CalcField +"]","VB","")
print("Calculated " + CalcField)
except:
print("Perhaps " + CalcField + " doesn't exist")
gp.delete("Layer")
except:
print(JoinDataName + " probably doesn't exist")
if gp.exists("Layer"):
gp.delete("Layer")
FeatClass = FcList.next()
This should help you create your own script to add fields, join and calculate; consider the section about adding indexes, with 1.2 MRecords it will speed things up a lot.
