Spaghetti, meatballs, and the one-to-many problem

Question

I am trying to use the Spaghetti and Meatballs overlay technique to create a fire history layer from Cal Fire FRAP's fire perimeter data. I am following the technique Dale Honeycutt describes in his ESRI blog post. However, at the spatial join part, instead of doing a one to one join, I am performing a one to many join. This then gives me multiple points with the same "Target FID" value, but different values for Name and Year of Fire. His next blog then goes into detail on how to combine the points with repeating "Target FID" values into one point with a string of values for both the Name and Year of Fire columns. However, that blog seems to no longer exist.

Essentially what I am trying to do is take data that looks like this:

Target_FID   Fire_Name       Fire_Year
2              Otay            1953
2              Wheeler         1989
2              Thomas          2017

To look like this:

Target_FID   Fire_Name                 Fire_Year
2            Otay_Wheeler_Thomas       1953_1989_2017

Possible duplicate of Counting overlapping polygons from one Shapefile using ArcGIS Desktop? — fatih_dur, Nov 17 '18 at 04:03

score 1 · Answer 1 · answered Nov 16 '18 at 20:21

Here is one way you can accomplish this:

Dissolve the fires feature class by Target_FID
Add two new text fields to the dissolved output (Fire_Name, Fire_Year)

Execute a python script to update these fields with values from the original feature class

import arcpy
from arcpy import env
env.workspace = r"C:\Default.gdb"

origFC = "Fires"
dissovledFC = "Fires_Dissolve"

## Get a list of Target_FIDs
idList = []
with arcpy.da.SearchCursor(dissovledFC, ["Target_FID"]) as cursor:
    for row in cursor:
        idList.append(row[0])
del cursor

## Iterate through each Target_FID
for id in idList:
    ## Use a search cursor to query only the individual Target_FID
    with arcpy.da.SearchCursor(origFC, ["Fire_Name", "Fire_Year"], "Target_FID = {0}".format(id)) as cursor:
        for row in cursor:
            ## Use an update cursor to update the dissolved feature class fields
            with arcpy.da.UpdateCursor(dissovledFC, ["Fire_Name", "Fire_Year"], "Target_FID = {0}".format(id)) as cursor2:
                for row2 in cursor2:
                    if row2[0] == None or row2[0] == '':
                        row2[0] = row[0] + "_"
                        row2[1] = row[1] + "_"

                    else:
                        row2[0] = row2[0] + row[0] + "_"
                        row2[1] = row2[1] + row[1] + "_"

                    cursor2.updateRow(row2)
    del cursor, cursor2

    ## Remove the trailing underscores in the fields
    with arcpy.da.UpdateCursor(dissovledFC, ["Fire_Name", "Fire_Year"], "Target_FID = {0}".format(id)) as cursor:
        for row in cursor:
            row[0] = row[0].rstrip('_')
            row[1] = row[1].rstrip('_')
            cursor.updateRow(row)
    del cursor

score 1 · Accepted Answer · answered Nov 17 '18 at 07:29

In fact this is a variation of https://gis.stackexchange.com/a/280415/28687, except it requires an additional step and is also answered elsewhere. For completeness sake, I am copying here the Luke's answer:

Union the shapefile with itself (introduce only one input, which is the feature class/layer that you are processing);
Convert multipart output to single part;
Use the spatial join tool by use the ARE_IDENTICAL_TO match option and [Additional step to merge the attributes] by using field mapping option. In your case make sure Fire_Name and Fire_Year fields are String field type and set a relatively large field length (based on the length of the string of attributes once concatenated), say 500. Lastly set your mapping Merge Rule to Join and define underscore as the delimiter (see below a snip from https://esriaustraliatechblog.files.wordpress.com/2015/05/spjoin_05.jpg where the delimiter is ,|).

score 0 · Answer 3 · answered Nov 19 '18 at 19:15

I think a simple workaround for this would be to merge the point feature class with a copy of itself. The Data Management > General > Merge tool would allow you to set the Field Map properties so as to join (concatenate) the desired data in a specified attribute field, which could also be a newly created one.

Be aware, merging the point feature class with its own copy will result in a feature class containing twice the amount of features, so you will have to delete the unnecessary duplicates. In this case it should be easy to identify them though, just filter them by the field "ORIG_FID" and keep only one of each ID occurrences (the one with the lowest "OBJECTID" value).

I'm writing this answer in a bit of a rush. Please let me know if you need a more detailed explanation. — user99006, Nov 19 '18 at 19:16

Spaghetti, meatballs, and the one-to-many problem

3 Answers3

Linked