In my last blog post I showed how you could resolve 1:M relationships found in overlapping polygons using the spaghetti and meatballs technique. The model described in that post used used the sample Concatenate Row Values tool available from the Analysis and Geoprocessing Tool Gallery.
The Concatenate Row Values tool works on any table and is useful for a wide variety of situations. But you can also configure the Spatial Jointool to do the same job as Concatenate Row Values by using the power of the Field Map control found in the Spatial Join.
You can download the data and models I used in this blog here.
Figure 1 shows the goal: create non-overlapping polygons and calculate the combination of species within each polygon. In the figure, the legend shows all the unique combinations of species, with each species separated by a hyphen. The labels in the map are ‘stacked’—each species within the polygon is on a separate line (as opposed to a single line as shown in the legend).
This post assumes you’ve read the previous post since I’m going to dive right into the use of Spatial Join.
Using the Field Map in Spatial Join
The previous post showed the details of a model that created spaghetti and meatballs and then used Spatial Join to perform a point-in-polygon overlay. In that model, the Join Operation for Spatial Join was JOIN_ONE_TO_MANY and the result was a point feature class with one point for each overlapping polygon. In addition, each point had the species name (the ENGL_NAME attribute) of the overlapping polygon. Using the Concatenate Row Values tool, the All_Names attribute was calculated by appending the values of ENGL_NAME and separating them with ‘ – ‘ (space/hyphen/space). The result of Concatenate Row Value is shown in Figure 2. This result table was joined the spaghetti polygons by ORIG_ID and the All_Names field was copied over to the spaghetti polygons to create the map shown in Figure 1 above.
The Concatenate Row Values tool works on any table and is useful for a wide variety of situations. But you can also configure the Spatial Join tool to do the same job with its Field Map parameter. Figure 3 shows a model that is exactly the same as the model in the previous blog post but with a different usage of Spatial Join.
This usage of Spatial Join depends upon defining a new output field (All_Names) in the Field Map of Join Features parameter. The first thing to do is to remove all the input fields from the Field Map control by highlighting the field and clicking the remove button. Then follow these steps, all illustrated in figure 4.
- Right-click within the Field Map control and choose Add Output Field. The Output Field Properties dialog box opens.
- In the Output Field Properties dialog box, name the field All_Names, choose Text for the Type of field, make its Length big enough to hold the concatenated field names (I chose 100). Choose Join for the Merge Rule and provide a delimiter (space/hypen/space). Click OK.
- The Field Map will show this new output field. You now have to tell it what fields you want to join by right-clicking All_Names and choosing Add Input Field. This opens the Add Input dialog box.
- In the Add Input dialog box, select the ENGL_NAME attribute and click OK. The Field Map will now show the new field along with the input field that will create the values for the new field.
- For the Join Operation parameter, choose JOIN_ONE_TO_ONE.
In the model, the output of Spatial Join is a new point feature class named Combinations. Figure 5 shows the Combinations attribute table. As you can see, the values of ENGL_NAME are joined together and each name is separated by ‘ – ‘. Spatial Join determined the Bovidea Ranges polygons that enclosed each meatball point, gathered together the values of ENGL_NAME from each of the enclosing polygons, then joined these values together using the delimiter.
The sort issue
In figure 5 above, I highlighted two records, TARGET_FID 51 and 57. Note that they have exactly the same species but they’re listed in different order. This different order means you can’t compare the values of All_Names. Even though they have the same species their All_Names values aren’t equivalent.
This is easy to fix using the Calculate Field tool and a bit of Python code, illustrated in Figure 6.
The value of the Expression parameter is:
The code for the Code Block parameter follows. This code block defines a routine named sortValues that is used in the Expression parameter.
# Ignore null values (artifact polygons)
# Create a Python list by splitting the string on the delimeter
' - '
# Sort the list
# Reassemble the string from the sorted list
" - "
Joining the meatballs back to the spaghetti
With the All_Names field now sorted, the next step is to join the spaghetti polygons and their meatball points and copy the All_Names field over to the spaghetti polygons using the Join Field tool. TARGET_FID is the OBJECTID of the centroid (Meatball) point, which is also the OBJECTID of the spaghetti polygon.
Just as with the previous post, artifact polygons need to be removed. In Figure 5 above, the record where TARGET_FID = 60 is an artifact polygon; there is no polygon in Bovidae Ranges that encloses the point. After joining and copying All_Names to the spaghetti polygons, the artifact polygons can be selected with the expression:
"All_Names" IS NULL
and deleted using the Delete Features tool.
Comparing the two techniques
The previous post used the sample Concatenate Row Values tool while this post used nothing but system tools. Concatenate Row Values works on any table, such as one output by other overlay tools such as Intersect or Identity. The field map control is available in Spatial Join and a few other tools such as Merge, Append, and Feature Class To Feature Class. The choice of which technique to use is up to you. When creating a one-off model to be used with a specific set of data, I tend to use the Field Map method. But when my model is going to be used with a variety of different data with different attributes, I tend to use the Concatenate Row Values tool since it’s easier to change its simple parameters than manipulate a field map.
The next post in this series about overlay shows how to create a street intersection list by overlaying lines to create points at their intersections.
1 Data provided by NatureServe in collaboration with Bruce Patterson, Wes Sechrest, Marcelo Tognelli, Gerardo Ceballos, The Nature Conservancy – Migratory Bird Program, Conservation International – CABS, World Wildlife Fund – US, and Environment Canada – WILDSPACE.