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.

closed as off-topic by PolyGeo Jul 8 at 21:59

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "When seeking help to debug/write/improve code always provide the desired behavior, a specific problem/error and the shortest code (as formatted text, not pictures) needed to reproduce it in the question body. Providing a clear problem statement and a code attempt helps others to help you." – PolyGeo
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    why will a join not work? – Michael Stimson May 22 '14 at 22:51
  • I am building an address locator from the data and any joined field upsets the locator build and it fails. – user30531 May 22 '14 at 22:56
  • Do you mean the locator build fails with a join active, or it fails even after exporting the join results to a new data set? If the latter it may be a case of bad field names resulting from the join. Related/possible duplicate: gis.stackexchange.com/questions/75311/… – Chris W May 26 '14 at 18:59
  • It was a bad join in modelbuilder that was causing the problem. I rewrote the workflow in arcpy and it worked. Thanks for the help. – user30531 May 29 '14 at 15:01

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]

    Output_Workspace = InData
    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
          gp.AddIndex_management (FCname,"bOID","bOID","NON_UNIQUE", "ASCENDING")
          print("Index: " + FCname)
          print("Unable to index")
          gp.AddIndex_management (JoinDataName,"bOID","bOID","NON_UNIQUE", "ASCENDING")
          print("Index: " + JoinDataName)
          print("Unable to index")

        InFields = gp.listfields(FCname)
        InF = []
        #for f in InFields:
        f = InFields.next()
        while f:
            print("Adding " + f.Name + " to list")
            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 :

                #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")
                elif JField.type == "Integer":
                    gp.AddField_management (FCname,JField.name,"LONG")
                    print("-- " + JField.name + " as LONG")
                elif JField.type == "Single":
                    gp.AddField_management (FCname,JField.name,"FLOAT")
                    print("-- " + JField.name + " as FLOAT")
                elif JField.type == "Double":
                    gp.AddField_management (FCname,JField.name,"DOUBLE")
                    print("-- " + JField.name + " as DOUBLE")
                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))
                elif JField.type == "Date":

                    gp.AddField_management (FCname,JField.name,"DATE")
                    print("-- " + JField.name + " as DATE")
            JField = JoinFields.next()

        gp.MakeFeatureLayer_management (FCname,"Layer")
        print("Feature Layer")
        gp.AddJoin_management ("Layer","bOID",JoinDataName,"bOID","KEEP_COMMON")
        for CalcField in FieldList:
                #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)
                print("Perhaps " + CalcField + " doesn't exist")
        print(JoinDataName + " probably doesn't exist")
        if gp.exists("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.


Depending on the relationships between your various tables, the Join Field tool will do what you want - transfer attributes from one table to another based on a join. Since you're joining tables to a feature class you shouldn't have any trouble with 1:1 or M:1 joins - 1:M or M:M joins of course won't work.

Not the answer you're looking for? Browse other questions tagged or ask your own question.