7

I'm writing an automated script to do some data processing of address points. One stage of the process is to retrieve 19 fields of information from various polygon layers that the Address Points are in, and populate pre-created fields in the Address feature class. Of the 19 fields, most are Text, but a few are Double.

Rather than hard-code all 19, I'm using a SearchCursor to cycle through a table of all 19 fields, each with its type, length, source feature class, and source field name. The SearchCursor first creates the field to specifications, and then runs code (below) to retrieve the field from the source features. The source feature classes are spread across an SDE and a geodatabase purpose-built for this process.

Out of these 19 fields I'm joining, 9 fields are failing, and they're all failing in the same way - the initial Spatial Join is returning a Null. This should return a point feature class with the address point's FID and the target field value in a field called "Scratch_Da". On the problem fields' Spatial Join outputs, "Scratch_Da" is NULL all the way down. Most of the problem data sources are in the source SDE, but moving them to the geodatabase did not solve the issues, and two problem fields are coming from the same data source as a field that works fine. I have verified that most of the address points are within the subject polygons and all the field names and types are correct.

Has anyone seen an ArcPy Spatial Join act this way before?

Here's the Spatial Field Retrieval loop:

with arcpy.da.SearchCursor("X:\AddressStage.gdb\SFRFields", SFRList) as cursor:
    for row in cursor:
            fname = row[0]
            ftype = row[1]
            fleng = row[2]
            fscal = row[3]
            fsrc = row[4]
            lyr = row[5]
            fsfld = row[6]
            #create fields with parameters given
            arcpy.AddField_management("CPLayer", fname, ftype, fleng, fscal, fleng)
            #if field source value is PARSE:
            if fsrc == "PARSE":
                #use continue to start next iteration
                continue
            elif fsrc!= "BLANK":
                #Begin spatial field retrieval code
                #Start by selecting CPLayer features with proper spatial relationship
                arcpy.SelectLayerByLocation_management("CPLayer", "WITHIN", fsrc)
                loopStart = time.time()
                runTime = loopStart - startTime
                log.write("{0}:\n".format(fname))
                log.write("{0} seconds elapsed in script.\n".format(runTime))
                print("Beginning retrieval of field {0}. {1} seconds elapsed in script.".format(fname, runTime))
                #perform SFR on nested field, using parameters in SFRFields
                #Layer to be calculated
                InLayer = "CPLayer"
                #InField: Layer which will receive final data
                InField = fname
                #SourceLayer: Layer which contributes data.
                SourceLayer = fsrc
                #SourceField: source field
                SourceField = fsfld
                #SpatShip = spatial relationship - same as Spatial Join tool
                SpatShip = "WITHIN"
                #MergeRule: How to handle one-to-many relationships
                if ftype == "TEXT":
                    MergeRule = "FIRST"
                elif ftype == "DOUBLE":
                    MergeRule = "SUM"
                #SearchDist: search distance
                SearchDist = 0

                #parse name of SJ features
                SJFeat = str("SJ_" + lyr + "_" + fname)
                log.write("Output feature class: {0}\n".format(SJFeat))

                #Create a field map that changes the InField name to Scratch_Da, and
                #uses Sourcefield as input.
                #This is so the eventual spatial join features have only the TARGET_FID
                #and the target data.
                InpFM = arcpy.FieldMap()
                ScratchFMS = arcpy.FieldMappings()

                InFieldList = arcpy.ListFields(InLayer)
                for field in InFieldList:
                    if field.name == InField:
                        InpFM.addInputField(SourceLayer, SourceField)
                        InpFM.mergeRule = MergeRule
                        #set output field name to Scratch_Data
                        scratch_name = InpFM.outputField
                        scratch_name.name = "Scratch_Da"
                        InpFM.outputField = scratch_name
                        ScratchFMS.addFieldMap(InpFM)

                #spatial join to scratch features
                sjTime = time.time()
                arcpy.SpatialJoin_analysis(InLayer, SourceLayer, SJFeat, "JOIN_ONE_TO_ONE",
                                          "KEEP_ALL", ScratchFMS, SpatShip, SearchDist)
                runTime = time.time() - sjTime
                log.write("Spatial join completed. {0} seconds elapsed in spatial join.\n".format(runTime))

                #create dictionary object for join purposes.
                #the key will be the Target FID, and the value is the target field value.
                JoinDict = {}
                noneCount = 0
                with arcpy.da.SearchCursor(SJFeat, ("TARGET_FID","Scratch_Da")) as cursor:
                    for row in cursor:
                        fid = row[0]
                        val = row[1]
                        if val == None and ftype == "DOUBLE":
                            val = 0
                            noneCount += 1
                        elif ftype == "DOUBLE" and val <> None:
                            val = float(row[1])
                        elif ftype == "TEXT" and val <> None:
                            val = str(row[1])[:fleng]
                        elif ftype == "TEXT" and val == None:
                            val = "FAIL"
                            noneCount += 1
                        JoinDict[fid] = val

                #Update cursor, hinges on dictionary
                with arcpy.da.UpdateCursor(InLayer, ("OID@", InField)) as cursor:
                    #reach into dictionary using FID values
                    for row in cursor:
                        #Search for dictionary item with feature's FID as key
                        val = JoinDict[row[0]]
                        row[1] = str(val)
                        cursor.updateRow(row)

                #delete ScratchSJ file.
                #arcpy.Delete_management("ScratchSJ")
                loopTime = time.time() - loopStart
                log.write("Field retrieval completed. {0} seconds elapsed in loop.\n".format(loopTime))
                log.write("{0} None values retrieved.\n\n".format(noneCount))
                #end of SFR loop

Update 03/21/2016: The script is still producing the NULL behavior. Of the problem fields:

  • 4 are text, of lengths from 4 to 20 characters. Input and output lengths match.
  • 4 are double. Length and precision match output specifications, but the inputs are short integers and I can't change that. However, the inclusion of text fields among the problem field lead me to believe that the field type or length is not a problem.
  • 2 fields, both text, are coming from the same data source as another text field that works perfectly.
  • I have exported a single problem field's row from the table that controls the Spatial Field Retrieval loop, and ran it through the same code. The NULL behavior persisted when I did this with both Text and Double fields.
11
  • 1
    I did this sort of thing but Unioned all the polygon layers first. I just thought that might be a good tip for you, since it simplifies the number of layers being accessed allowing you to do the spatial join only once. Often with Union, Intersect, Spatial Join, you get collisions with fields of the same name having different types or lengths and that could be an issue for you here. Mar 18, 2016 at 17:14
  • That's why the field map outputs to a single field with a predictable name. The SFR loop code is taken from a custom Toolbox tool I wrote that does the same thing. I thought about trying to group them loops, or Union-ing the polygon layers, but that would throw the weight on the field mapping side, and I'd much rather run a single loop 19 times than have fields get in each other's way. Mar 18, 2016 at 17:24
  • I found that spatial join does crazy things with databases. It always works as expected with shapefiles though
    – FelixIP
    Mar 19, 2016 at 0:32
  • 1
    You give no indication about which kinds of fields (Double or Text) are failing. The spatial join does not do crazy things with my file geodatabases, so I blame your program and not ArcMap unless you can show me that your problem happens independently and has nothing to do with the way you are manipulating these file geodatabase fields. Direct use of SDE data may have issues, depending on the resources the SDE administrator has allocated for you to use during geoprocessing. For example, they can impose resource restrictions that affect the maximum selection set size you can use. Mar 19, 2016 at 15:51
  • 1
    At the moment you are presenting much more than an MCVE. Can you reproduce the same behaviour just on one field and its field type?
    – PolyGeo
    Mar 20, 2016 at 22:54

2 Answers 2

4
+100

I have recently had some troubles getting my spatial join to work, running into similar issues as you were. I am not familiar with the field mapping enough to know if the way you are field mapping is possible or not. All I know is, this is the way I got field mapping (with a merge rule on multiple fields) to work for me.

    # Copy features to a new layer
    arcpy.CopyFeatures_management(target,outputfeatureAPT,config_keyword="#",spatial_grid_1="0",spatial_grid_2="0",spatial_grid_3="0")
    arcpy.CopyFeatures_management(joinFeatureSTUD,outputfeatureSTUD,config_keyword="#",spatial_grid_1="0",spatial_grid_2="0",spatial_grid_3="0")

    # Delete fields in copy features before you spatial join
    arcpy.DeleteField_management(outputfeatureAPT,drop_field="APT;ADDRESS;PA;TYPE;STATUS;LIU;MRU;UNITS;CLASS;LOW_INCOME;APP_NUM;ELEM;INT_;MID;HIGH;ELEM_STUD;INT_STUD;MID_STUD;HIGH_STUD;TOT_STUD;ELEM_YLD;INT_YLD;MID_YLD;HIGH_YLD;TOT_YLD;LAST_EDIT;NOTES")

    # The following inputs are layers or table views: "stud_0915_1"
    arcpy.DeleteField_management(outputfeatureSTUD,drop_field="Loc_name;Status;Score;Match_type;X;Y;Match_addr;Side;Pct_along;ARC_Street;ARC_Zip;ARC_City;ARC_Postal;ISD;STUD_ID;NAME;F_NAME;L_NAME;ADDRESS;ZIP;CITY;CAMPUS_ID;SCHOOL;GRADE;ETHNICITY;ED;BIL;PA;RESIDE;CLASS;SUB;EE_PK;EE;PK;KG;GRD1;GRD2;GRD3;GRD4;GRD5;GRD6;GRD7;GRD8;GRD9;GRD10;GRD11;GRD12;AMER_IND;ASIAN;BLACK;HISPANIC;WHITE;PACIFIC;MULTI;BIL_EE;BIL_PK;BIL_KG;BIL_GRD1;BIL_GRD2;BIL_GRD3;BIL_GRD4;BIL_GRD5;BIL_GRD6;BIL_ELEM;BIL_INT;BIL_MID;BIL_HIGH;ED_ELEM;ED_INT;ED_MID;ED_HIGH;AMER_IND_ELEM;AMER_IND_INT;AMER_IND_MID;AMER_IND_HIGH;ASIAN_ELEM;ASIAN_INT;ASIAN_MID;ASIAN_HIGH;BLACK_ELEM;BLACK_INT;BLACK_MID;BLACK_HIGH;HISPANIC_ELEM;HISPANIC_INT;HISPANIC_MID;HISPANIC_HIGH;WHITE_ELEM;WHITE_INT;WHITE_MID;WHITE_HIGH;PACIFIC_ELEM;PACIFIC_INT;PACIFIC_MID;PACIFIC_HIGH;MULTI_ELEM;MULTI_INT;MULTI_MID;MULTI_HIGH;SF;MF;EXEMPT_65;RENTER")

    # Create a new fieldmappings and add the two input feature classes.
    fms = arcpy.FieldMappings()
    fms.addTable(outputfeatureAPT)
    fms.addTable(outputfeatureSTUD)
    ELEMSumIndex = fms.findFieldMapIndex("ELEM")
    INT_SumIndex = fms.findFieldMapIndex("INT_")
    MIDSumIndex = fms.findFieldMapIndex("MID")
    HIGHSumIndex = fms.findFieldMapIndex("HIGH")
    TOT_STUDSumIndex = fms.findFieldMapIndex("TOT_STUD")

    # Create the required FieldMap and FieldMappings objects
    fm_apt_poly_APT_ID = arcpy.FieldMap()
    fm_stud_ELEM = fms.getFieldMap(ELEMSumIndex)
    fm_stud_INT_ = fms.getFieldMap(INT_SumIndex)
    fm_stud_MID = fms.getFieldMap(MIDSumIndex)
    fm_stud_HIGH = fms.getFieldMap(HIGHSumIndex)
    fm_stud_TOT_STUD = fms.getFieldMap(TOT_STUDSumIndex)
    fms = arcpy.FieldMappings()

    # Create the required FieldMap and FieldMappings objects
    fm_apt_poly_APT_ID = arcpy.FieldMap()
    fm_stud_ELEM = arcpy.FieldMap()
    fm_stud_INT_ = arcpy.FieldMap()
    fm_stud_MID = arcpy.FieldMap()
    fm_stud_HIGH = arcpy.FieldMap()
    fm_stud_TOT_STUD = arcpy.FieldMap()
    fms = arcpy.FieldMappings()

    # Get the field names for both original files
    apt_poly_APT_ID = "APT_ID"
    stud_ELEM = "ELEM"
    stud_INT_ = "INT_"
    stud_MID = "MID"
    stud_HIGH = "HIGH"
    stud_TOT_STUD = "TOT_STUD"

    # Add fields to their corresponding FieldMap objects
    fm_apt_poly_APT_ID.addInputField(outputfeatureAPT, "APT_ID")
    fm_stud_ELEM.addInputField(outputfeatureSTUD, "ELEM")
    fm_stud_INT_.addInputField(outputfeatureSTUD, "INT_")
    fm_stud_MID.addInputField(outputfeatureSTUD, "MID")
    fm_stud_HIGH.addInputField(outputfeatureSTUD, "HIGH")
    fm_stud_TOT_STUD.addInputField(outputfeatureSTUD, "TOT_STUD")

    # Set the output field properties for both FieldMap objects
    apt_poly_APT_ID = fm_apt_poly_APT_ID.outputField
    apt_poly_APT_ID.name = "APT_ID"
    fm_apt_poly_APT_ID.outputField = apt_poly_APT_ID

    stud_ELEM = fm_stud_ELEM.outputField
    stud_ELEM.name = "ELEM"
    stud_ELEM.aliasName = "ELEM"
    fm_stud_ELEM.outputField = stud_ELEM

    stud_INT_ = fm_stud_ELEM.outputField
    stud_INT_.name = "INT_"
    stud_INT_.aliasName = "INT_"
    fm_stud_INT_.outputField = stud_INT_

    stud_MID = fm_stud_MID.outputField
    stud_MID.name = "MID"
    stud_MID.aliasName = "MID"
    fm_stud_MID.outputField = stud_MID

    stud_HIGH = fm_stud_HIGH.outputField
    stud_HIGH.name = "HIGH"
    stud_HIGH.aliasName = "HIGH"
    fm_stud_HIGH.outputField = stud_HIGH

    stud_TOT_STUD = fm_stud_TOT_STUD.outputField
    stud_TOT_STUD.name = "TOT_STUD"
    stud_TOT_STUD.aliasName = "TOT_STUD"
    fm_stud_TOT_STUD.outputField = stud_TOT_STUD

    # Set the merge rule to sum and then replace the old field map in the mappings object with the updated one
    fm_stud_ELEM.mergeRule = "SUM"
    fm_stud_INT_.mergeRule = "SUM"
    fm_stud_MID.mergeRule = "SUM"
    fm_stud_HIGH.mergeRule = "SUM"
    fm_stud_TOT_STUD.mergeRule = "SUM"
    fms.addFieldMap(fm_apt_poly_APT_ID)
    fms.addFieldMap(fm_stud_ELEM)
    fms.addFieldMap(fm_stud_INT_)
    fms.addFieldMap(fm_stud_MID)
    fms.addFieldMap(fm_stud_HIGH)
    fms.addFieldMap(fm_stud_TOT_STUD)

    # Spatial Join Sample Code
    # SpatialJoin_analysis (target_features, join_features, out_feature_class, {join_operation}, {join_type}, {field_mapping}, {match_option}, {search_radius}, {distance_field_name})
    # Process: Spatial Join
    arcpy.AddMessage("Performing Student Spatial Join")
    arcpy.SpatialJoin_analysis(outputfeatureAPT, outputfeatureSTUD, output2, "JOIN_ONE_TO_ONE", "KEEP_ALL", fms, "INTERSECT", "", "")
    arcpy.AddMessage("Just performed Student Spatial Join")

    # Join the output to your apt_poly_xxxx by the APT_ID field
    # JoinField_management Sample Code
    # arcpy.JoinField_management(Input Feature, "Field Name", "join table", "join field", {["fields"]})
    arcpy.AddJoin_management(target, "APT_ID", output2, "APT_ID","")
    arcpy.AddMessage("Just performed Student Table Join")

    # Calculate Field_management Sample Code
    # arcpy.CalculateField_management(Input Feature, "Field Name", expression, "expression type", code block)

    # Process: Calculate Field, with a string index in the expression used to isolate the feature class name
    arcpy.CalculateField_management(target, targetname + ".ELEM_STUD", '!'+output2[74:]+'.ELEM!', "PYTHON_9.3")
    # Process: Calculate Field (2)
    arcpy.CalculateField_management(target, targetname + ".INT_STUD", '!'+output2[74:]+'.INT_!', "PYTHON_9.3")
    # Process: Calculate Field (3)
    arcpy.CalculateField_management(target, targetname + ".MID_STUD", '!'+output2[74:]+'.MID!', "PYTHON_9.3")
    # Process: Calculate Field (4)
    arcpy.CalculateField_management(target, targetname + ".HIGH_STUD", '!'+output2[74:]+'.HIGH!', "PYTHON_9.3")
    # Process: Calculate Field (5)
    arcpy.CalculateField_management(target, targetname + ".TOT_STUD", '!'+output2[74:]+'.TOT_STUD!', "PYTHON_9.3")

    # Remove the Join
    arcpy.RemoveJoin_management(target, "")

I eneded up deleting all fields in my target and join feature exept the onese that I needed, and then manually field mapped the rest. I was working with 6 fields and it was a pain, so I understand the idea of trying to work around doing it this way with 19 fields. But here's how it worked for me.

4
  • Thank you for answering. I have some questions about how this code works. Why do you have two sections beginning with #Create the required FieldMap and FieldMappings objects? It looks like you're creating a field map with fms, and then overwriting it with a blank one with the line fms = arcpy.FieldMappings(). This is the code that fixed your problem? Mar 21, 2016 at 19:50
  • 1
    I have been working on this section of code off and on for a few months now, so I forgot to update my comments on the process. But yes this is the code that works, and that is the location that I decided to index the fields that were going to have a merge rule applied to it later on. Mar 21, 2016 at 20:02
  • This worked! I don't get the difference between mine and yours, but yours worked. All 19 fields correctly populated with no errors, and comparable run time to the previous attempts. I'll post another answer showing my variant on your code for future users, but as soon as I can award bounty (16 hours) I'll award it to you. Thank you so very much. Mar 21, 2016 at 20:25
  • 1
    Excellent! Glad I can help. I labored over this piece of code for multiple days, but got it working for my needs early last week! I am very glad that it is helping you out too. Thanks! Mar 21, 2016 at 20:27
0

This successful code was inspired by code posted by @forrestchev in their answer. Please direct any upvotes to their answer—I intend this answer more as a comment on theirs rather than an answer in itself; I just couldn't get a multi-line code snippet to be legible in a comment.

I do not yet understand exactly why this worked successfully, but this code, replacing the 17 lines of code following #Create a field map... in my question's code, populated the field successfully with no null values:

#Create field map à la forrestchev
ScratchFMS = arcpy.FieldMappings()
ScratchFMS.addTable(SourceLayer)
SourceIndex = ScratchFMS.findFieldMapIndex(SourceField)
SourceFM = ScratchFMS.getFieldMap(SourceIndex)
ScratchFMS = arcpy.FieldMappings()
SourceFM.addInputField(SourceLayer, SourceField)
SourceFM.mergeRule = MergeRule

ScratchFMS.addFieldMap(SourceFM)

And the spatial join code:

arcpy.SpatialJoin_analysis(
    InLayer,
    SourceLayer,
    SJFeat,
    "JOIN_ONE_TO_ONE",
    "KEEP_ALL",
    ScratchFMS,
    SpatShip,
    SearchDist
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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