2

I'm working with building routes for a lit of technician's stops. In some cases, they have two "stops" at the same customer location -- turning on two types of service at a house, for example, is listed as two jobs even though it's at the same house.

Each point has a start time (when they're supposed to arrive), a duration (how long the work should take to complete), and task type. I need to remove the duplicate geographies, because the resulting route maps aren't as clear as they could be -- the frequent question in meetings is "why isn't there a Stop #1?" and answering "because it's at the same place as Stop #2 and that marker is on top of it" tends to confuse non-GIS colleagues. However, I also:

  1. want to preserve the earlier start time for Task 1, not the later start time of Task 2
  2. need to sum the task durations, so if Task 1 takes 5 minutes and Task 2 takes 10 minutes, the combined single-stop task takes 15 minutes
  3. would like to keep task types around (concatenating the strings could work)

So something like the following example, Joe's first two stops need to be combined into one:

1  8:00  8:24  24  -81.12346  31.54642  DNP  Joe Smith
2  8:24  8:37  13  -81.12346  31.54642  LCT  Joe Smith
3  8:45  8:55  10  -81.65487  31.45121  ABC  Joe Smith

The ideal result is:

1  8:00  8:37  37  -81.12346  31.54642  DNP; LCT  Joe Smith
2  8:45  8:55  10  -81.65487  31.45121  ABC       Joe Smith

I considered dissolving on a lat/long and technician fields, but that wouldn't preserve the other attributes.

I can find the duplicates and build a list of OBJECTIDs that need to be merged with a search cursor, but I am confused how to proceed after that. I've drawn out a sequence of search and update cursor loops which would probably work, but seems inelegant and inefficient (requires looping through the entire feature class many times). Is there a better approach?

  • 2
    The way to accomplish this in a relational database is to avoid compound fields and use two tables, Stops and Stop_Tasks, with a one-to-many relationship and a view that sums the task times. ArcGIS has two different options to implement 1:M relationships. resources.arcgis.com/en/help/main/10.1/index.html#/… – Vince Oct 23 '14 at 16:28
  • I don't have control over the database structure, as it's sent to me in a CSV data dump every morning. I agree that a more sensible schema would greatly improve this problem, but I'm stuck with other people's bad decisions :) – Erica Oct 23 '14 at 17:01
  • If it's sent in CSV, you have complete control over how the tables you use are laid out; you just need to come up with an import sequence to support it. Do you have access to a database to do the manipulation? It changes potential answers. – Vince Oct 23 '14 at 17:30
  • 1
    Dictionary will do. Key equal str (x) + str (y). Data is list of lists. Create it in 1 go, summarise/concatenate values in a second one – FelixIP Oct 24 '14 at 3:38
  • 1
    @SaraBarnes Actually yes; I don't have time today to post the solution (the code's at work) but I will try to get to it tomorrow. Essentially, I used FelixIP's approach of dictionaries :) – Erica Dec 4 '14 at 20:03
0

Based on FelixIP's suggestion, and using the Find Identical tool, I came up with the following solution.

# find duplicate stops
outdbf = os.path.join(scriptPath, "tbl.dbf")
if os.path.isfile(outdbf):
    os.remove(outdbf)
# if lat, long, and technician all match, the rows go into the duplicate stop table
result = arcpy.FindIdentical_management(fcStops, outdbf, ["Longitude", "Latitude", "Tech"])

# build duplicate stops list from the duplicate stops table
out_records = []
for row in arcpy.SearchCursor(result.getOutput(0), fields="IN_FID; FEAT_SEQ"):
    out_records.append([row.IN_FID, row.FEAT_SEQ])
# records with same FEAT_SEQ value will be in the same group (i.e., identical)
identicals_iter = itertools.groupby(out_records, operator.itemgetter(1))
# make a list of identical groups - each group in a list.
identical_groups = [[item[0] for item in data] for (key, data) in identicals_iter]

for listDups in identical_groups:
    if len(listDups) > 1:
        stopData = {}
        sumWorkTime = 0
        OBJID1 = listDups[0]
        OBJID2 = listDups[1]
        searchFields = ["OBJECTID", "StartTime", "Duration"]
        with arcpy.da.SearchCursor(fcStops, searchFields) as cursor:
            for row in cursor:
                if row[0] == OBJID1:
                    stopData[OBJID1] = [row[1], row[2]]
                if row[0] == OBJID2:
                    stopData[OBJID2] = [row[1], row[2]]
        Start1 = stopData[OBJID1][0]
        Start2 = stopData[OBJID2][0]
        if Start1 > Start2:
            firstStartTime = stopData[OBJID2][0]
            firstStop = OBJID2
            keepStop = OBJID1
        else:
            firstStartTime = stopData[OBJID1][0]
            firstStop = OBJID1
            keepStop = OBJID2
        with arcpy.da.UpdateCursor(fcStops, searchFields) as cursor:
            for row in cursor:
                if row[0] == keepStop:
                    row[1] = firstStartTime
                    row[2] = int(stopData[OBJID1][1]) + int(stopData[OBJID2][1])
                    cursor.updateRow(row)
                if row[0] == firstStop:
                    cursor.deleteRow()
        # print "{} duplicates; merging data, deleting OBJECTID {}".format(listDups, firstStop)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.