Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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 unsure where to begin. Some guidance would be greatly appreciated. (I am using ArcGIS 10.0 and the dataset is large - 1.2 million records)

share|improve this question
1  
why will a join not work? –  Michael Miles-Stimson May 22 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 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 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 at 15:01

2 Answers 2

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.

share|improve this answer
    
Thanks, I will check this out. –  user30531 May 22 at 23:07

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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