ArcGIS: Error 999999 in spatial join

Question

I am trying to assign attributes from one point layer to another point layer based on closest location.

So I am trying to use a spatial join and generate the ArcPy code in order to ensure reproducibility.

I have tried two ways to do a spatial join.

First, "right click on the layer file in the table of contents" > joins and relates > join > ... . This works but does not generate Python code.

Second, Analysis toolbox > Overlay toolset > spatial join. This generates Python code but also an "Error 999999: An expected Field was not found or could not be retrieved properly. Failed to execute (SpatialJoin)." (the second sentence of the error message appears roughly 20 times). I played around with different "Field Maps", "Match options", "Search Radius" and "Distance Field name" without success.

Running the following python code has also generate the same error:

import arcpy


arcpy.env.workspace = "Path_to_workspace"
arcpy.SpatialJoin_analysis ("dataset1", "dataset2","Path_to_folder_with_output_file/output_file.shp","JOIN_ONE_TO_ONE", "KEEP_ALL", "#", "CLOSEST", "#", "#")

Do you know how I can generate Python code from the first approach or avoid the error in the second code?

Solution

I think the issue arose because "dataset1" and "dataset2" where generated from CSV files using coordinates and the MakeXYEventLayer_management command. So once I saved them as a shapefile and used the shapefile for the spatial join it worked. It is not obvious to me why this is the case, so feel free to add intuition.

I solved the issue using the following code:

import arcpy


arcpy.env.workspace = "Path_to_workspace"

# Define projection 
spRef = "path_to_projection/projection.prj"

# Data set 1
arcpy.MakeXYEventLayer_management("path_to_table/table_dataset1.txt", 'x', 'y', 'dataset1',spRef)
arcpy.FeatureClassToFeatureClass_conversion('dataset1', 'Path_to_folder_with_input_file', 'dataset1.shp')
# Data set 2   
arcpy.MakeXYEventLayer_management("path_to_table/table_dataset2.txt", 'x', 'y', 'dataset2',spRef)
arcpy.FeatureClassToFeatureClass_conversion('dataset2, 'Path_to_folder_with_input_file', 'dataset2.shp')

arcpy.SpatialJoin_analysis ("Path_to_folder_with_input_file/dataset1.shp", "Path_to_folder_with_input_file/dataset2.shp","Path_to_folder_with_output_file/output_file.shp","JOIN_ONE_TO_ONE", "KEEP_ALL", "#", "CLOSEST", "#", "#")

Answer 1

Make sure that your parameters have been correctly input.

This code will join the points layers, and join the attributes from the closest feature. It will also create a new field called "distance" to identify how far this feature is.

Try:

import arcpy
ws = arpcy.env.workspace = r"path/to/workspace"

pl_1 = r"path/to/dataset"
pl_2 = r"path/to/dataset"

arcpy.SpatialJoin_analysis(pl_1, pl_2, "Point_join.shp", "JOIN_ONE_TO_MANY", "KEEP_ALL","", "CLOSEST", "", "distance")

Answer 2

A join via the drop down (basic Join) in the Table of Contents and the Spatial Join tool are not really the same process.

The Spatial Join tool is joining records based on a the spatial location (relationship) of the features. The basic Join is joining the records based on matching field values.

I do not believe there is a way to produce python via the basic Join drop down, however there are other Join tools in arctoolbox. If the drop down join is solving your problem (producing desired results) then you do not need to do a spatial join. You can use the "Add Join" tool which is the same behavior as your basic Join. Or you can use the "Join Field" tool which will produce a separate output Feature Class of the joined data. I suggest using the Join Field, unless you have a good reason not to, because the add join will create additional locks on the interacting Feature Classes that may cause further processes to not behave properly.

If you need to use the spatial join then I suspect the specific error you are getting has to do with your field mappings. Possibly mismatched or nulled out field, it is hard to say without viewing the data. I would direct you to the tool help which has thorough explanations of how the tool is working and sample code if you are python savvy.

Answer 3

Solution

I think the issue arose because "dataset1" and "dataset2" where generated from csv files using coordinates and the MakeXYEventLayer_management command. So once I saved them as a shape file and used the shape file for the spatial join it worked. It is not obvious to me why this is the case, so feel free to add intuition.

I solved the issue using the following code:

import arcpy
arcpy.env.workspace = "Path_to_workspace"

# Define projection 
spRef = "path_to_projection/projection.prj"

# Data set 1
arcpy.MakeXYEventLayer_management("path_to_table/table_dataset1.txt", 'x', 'y', 'dataset1',spRef)
arcpy.FeatureClassToFeatureClass_conversion('dataset1', 'Path_to_folder_with_input_file', 'dataset1.shp')
# Data set 2   
arcpy.MakeXYEventLayer_management("path_to_table/table_dataset2.txt", 'x', 'y', 'dataset2',spRef)
arcpy.FeatureClassToFeatureClass_conversion('dataset2, 'Path_to_folder_with_input_file', 'dataset2.shp')

arcpy.SpatialJoin_analysis ("Path_to_folder_with_input_file/dataset1.shp", "Path_to_folder_with_input_file/dataset2.shp","Path_to_folder_with_output_file/output_file.shp","JOIN_ONE_TO_ONE", "KEEP_ALL", "#", "CLOSEST", "#", "#")