Issue with basic field mappings for spatial join

[DanielGermroth]

Hi all,

My script is working except the field mappings. When I remove that parameter from the spatial join, it runs fine (but doesn't include the desired field in the output). When I add the fieldMappings parameter to the spatial join, the script throws the beloved ERROR 999999: Error executing function.

Thus, I know the problem isolated to how I am imputing the fieldMappings parameter which also makes me believe it is a basic misunderstanding on my part regarding how to utilize field mappings.

Here is a code snippet of the issue:

mapFields = ["ITEM_ID", "PRIME_GEO"]
fieldMappings = arcpy.FieldMappings()
for mapField in mapFields:
    fieldMap = arcpy.FieldMap()
    fieldMap.addInputField(aoi, mapField)
    fieldMappings.addFieldMap(fieldMap)

arcpy.SpatialJoin_analysis(aoi, "buildingLyr", buildingOutput, "JOIN_ONE_TO_ONE", "KEEP_COMMON", fieldMappings, "COMPLETELY_WITHIN", "", "")

PS. Of the two tables involved in the join, I only really need to retain the ITEM_ID (and resulting count) field after the join. If there is a better way to do this than field mapping, please share your idea!

[DuncanHornby]

I have to admit I have never done a spatial join with field mapping but looking at the Help file it appears you create a fieldMappings object then add the tables. In your code you are not doing this, may be this is the source of the error?

Duncan

[DanielGermroth]

Thanks for the input! I also have never used field mappings before which is why I have a hunch I am making a laughably simple mistake. I have scoured every help file and blog post relating to the subject and still have come up empty.

addTable was a good tip, but I am still getting the 999999 error. Back to the drawing board for me!

[MathewCoyle]

Your code works for me. Are you sure the two fields exist in your source table?

[DanielGermroth]

If it helps, here is how my fields come mapped out of model builder:

##arcpy.SpatialJoin_analysis(AOI, BUILDING_P_-_StructurePnt, v_CC__AOI_build, "JOIN_ONE_TO_ONE", "KEEP_COMMON", "ITEM_ID \"ITEM_ID\" true true false 150 Text 0 0 ,First,#,C:\\Users\\\\Documents\\Units.mdb\\AOI,ITEM_ID,-1,-1", "INTERSECT", "", "")

[DanielGermroth]

Your code works for me. Are you sure the two fields exist in your source table?

Wow- after reading your post, I closed down ArcMap and my python editor, reopened Arc, ran it and it worked. And even better, it worked as desired! Normally I like to follow up with what fixed the issue, but I don't think I can in this case.

Thank you all for taking the time to read the thread.

[MathewCoyle]

I would avoid using mdbs at all costs. You will run into nothing but trouble unless you need them for a very specific purpose. That may or may not be the cause of your current issues but it is a good place to start.

[DanielGermroth]

Here is the complete section of code just in case it helps anyone in the future:

#Create field map for spatial joins
arcpy.AddMessage("Mapping fields for future use in iteration")
mapFields = ["ITEM_ID", "PRIME_GEO"]
fieldMappings = arcpy.FieldMappings()
for mapField in mapFields:
    fieldMap = arcpy.FieldMap()
    fieldMap.addInputField(aoi, mapField)
    fieldMappings.addFieldMap(fieldMap)


#Begin iterations for each country
arcpy.AddMessage("Beginning loop")
for country in countries:
    cc = str(countries)
        
    #Feed in featureclasses
    arcpy.AddMessage("Reading input featureclasses into in-memory layers")
    arcpy.MakeFeatureLayer_management(countryGdbLTDS + "/StructurePnt", "buildingLyr", dqBuilding)    

    #define output location for temp building tables
    buildingOutput = scratch + "/" + cc + "_AOI_Building"


    #Spatially join buildings
    arcpy.AddMessage("Testing for existing building output files. Any found will be deleted")
    if arcpy.Exists(buildingOutput):
        arcpy.Delete_management(buildingOutput)
        arcpy.AddMessage("Deleted")
        arcpy.AddMessage("Performing Spatial join on Building points")
        arcpy.SpatialJoin_analysis(aoi, "buildingLyr", buildingOutput, "JOIN_ONE_TO_ONE", "KEEP_COMMON", fieldMappings,"INTERSECT", "", "")        
    else:
        arcpy.AddMessage("None Found to delete")
        arcpy.AddMessage("Performing Spatial join on Building points")
        arcpy.SpatialJoin_analysis(aoi, "buildingLyr", buildingOutput, "JOIN_ONE_TO_ONE", "KEEP_COMMON", fieldMappings, "INTERSECT", "", "")
    appendList.append(buildingOutput)

[DanielGermroth]

I would avoid using mdbs at all costs. You will run into nothing but trouble unless you need them for a very specific purpose. That may or may not be the cause of your current issues but it is a good place to start.

I agree completely. Unfortunately my company for uses them far too much for data storage and querying (and haven't upgraded the old data to gdbs) so I have to adapt to it.