3

I'm wondering if anyone can help me brainstorm how to simplify this ETL process? The way I have it written, it just feels very repetitive, but I can't think of a way to avoid the repetition.

I'll eventually be feeding about a dozen feature classes, all with a different schema (different field ordering, field spellings, etc.), into one composite feature class. I've pre-determined the proper index values for each field, and as you can see, the ordering (and as I said, the spelling of field names as well) are wildly different between "fc1" and "fc2," which makes it hard to automate, thus the problem.

I'm afraid that with so many feature classes being added, the code will eventually get unnecessarily long. This example has been abbreviated by quite a lot.

I have modified the code for simplicity and confidentiality:

import arcpy, os
from arcpy import env

arcpy.env.workspace = r"I:\GIS_TOOLS\SDE_connections\gis_sde.sde"
sde = arcpy.env.workspace

masterFC = os.path.join(sde, "sde.SDE.masterFC")
fc1 = os.path.join(sde, "sde.SDE.FC1")
fc2 = os.path.join(sde, "sde.SDE.FC2")

masterFCFieldList = ["Shape", "Id", "Name", "Address", "Zip", "Boro", "Phone"]

# ---- Load feature class 1 ----

insertCursorMasterFC = arcpy.da.InsertCursor(masterFC, masterFCFieldList)
searchCursorFC1 = arcpy.da.SearchCursor(fc1, *)

for row in searchCursorFC1:        
    Shape      =   row[9] 
    Id         =   row[5]
    Name       =   row[1]
    Address    =   row[2]
    Zip        =   row[4]
    Boro       =   row[3]
    Phone      =   row[6] 
    insertCursorMasterFC.insertRow([Shape, Id, Name, Address, Zip, Boro, Phone]) 

del insertCursorMasterFC
del searchCursorFC1

# ---- Load feature class 2 ----

insertCursorMasterFC = arcpy.da.InsertCursor(masterFC, masterFCFieldList)
searchCursorFC2 = arcpy.da.SearchCursor(fc2, *)

for row in searchCursorFC2:        
    Shape      =   row[8] 
    Id         =   row[1]
    Name       =   row[5]
    Address    =   row[3]
    Zip        =   row[7]
    Boro       =   row[2]
    Phone      =   row[4] 
    insertCursorMasterFC.insertRow([Shape, Id, Name, Address, Zip, Boro, Phone]) 

del insertCursorMasterFC
del searchCursorFC2

print "Script Complete."
  • The easy answer is to go get a trial version of FME and do this in about 3 minutes. ;) – Mintx Jan 7 '16 at 22:05
  • I'll have to give this a look as well, "FME" (safe.com/how-it-works) – Kristen G. Jan 8 '16 at 21:14
2

There are a lot of ways to go about organizing something like this. My favorite is to create a dictionary of dictionaries at the top, something like this:

all_fc={
'fc1':{'Shape':9,'Id':5,'Name':1,'etc':'etc'},
'fc2':{'Shape':8,'Id':1,'Name':5,'etc':'etc'}
}

print all_fc['fc1']['Shape']

> 9

You can then loop through the dictionary, which should cut down on some of the redundant code.

  • This was extremely helpful. Thank you. I came up with an answer specifically for my original code that makes use of your suggestion. I'm including it as another answer below. What do you think? – Kristen G. Jan 8 '16 at 20:56
  • Looks good, glad I could help! – Mintx Jan 8 '16 at 21:12
1

This answer provides a simpler alternative to the original code I posted by using @Mintx's helpful suggestion/answer to iterate through a dictionary of dictionaries:

import arcpy, os
from arcpy import env

arcpy.env.workspace = r"I:\GIS_TOOLS\SDE_connections\gis_sde.sde"
sde = arcpy.env.workspace

masterFC = os.path.join(sde, "sde.SDE.masterFC")
fc1 = os.path.join(sde, "sde.SDE.FC1")
fc2 = os.path.join(sde, "sde.SDE.FC2")

masterFCFieldList = ["Shape", "Id", "Name", "Address", "Zip", "Boro", "Phone"]

allFCs = {
    fc1:{'Shape':9, 'Id':5, 'Name':1, 'Address':2, 'Zip':4, 'Boro':3, 'Phone':6},
    fc2:{'Shape':8, 'Id':1, 'Name':5, 'Address':3, 'Zip':7, 'Boro':2, 'Phone':4}
    }

for fc, fields in allFCs.iteritems():

    insertCursorMasterFC = arcpy.da.InsertCursor(masterFC, masterFCFieldList)
    searchCursorFC = arcpy.da.SearchCursor(fc, "*")

    for row in searchCursorFC:

        Shape      =   row[allFCs[fc]['Shape']] 
        Id         =   row[allFCs[fc]['Id']]
        Name       =   row[allFCs[fc]['Name']]
        Address    =   row[allFCs[fc]['Address']]
        Zip        =   row[allFCs[fc]['Zip']]
        Boro       =   row[allFCs[fc]['Boro']]
        Phone      =   row[allFCs[fc]['Phone']]

        insertCursorMasterFC.insertRow([Shape, Id, Name, Address, Zip, Boro, Phone])

    del insertCursorMasterFC
    del searchCursorFC

print "Script Complete."
  • It looks like you will want to move those del commands outside of your for row in searchCursorFC loop, so move them back 1 indent. – Mintx Jan 8 '16 at 21:26
  • Oops, sloppy copy/pasting. Thanks for catching! – Kristen G. Jan 8 '16 at 22:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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