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.
