AnsweredAssumed Answered

Sorting in a dictionary for a newbie

Question asked by mattbeyers on Jun 30, 2017
Latest reply on Jul 6, 2017 by blake.terhune

I'm pretty much a newbie to Python in ArcGIS. I have a table of origins and destinations and their distance. I want to be able to choose the closest n destinations for each origin and write that to a new table. I had been using cursors with SQL and OrderBy but that was taking way too long. I found Richard Fairhurst's Blog posts about using dictionaries (Turbo Charging Data Manipulation with Python Cursors and Dictionaries) but I'm afraid the logic is beyond me right now.

 

I've put the relevant piece of code below. 'neighborconstraint' is the n closest destinations that the user has specified. 'DistanceSort' is the table that we're writing to. 'Matrix' is the input table. The second table, 'NeighborLimit,' selects the nearest n distances.

 

Any help?

#Have table of Origin (BID) to Destination (SID) distances (Distance). Want to select a neighbor limit of shortest distances.
DistanceSort = "DistanceSort"
#Create empty output table, where insert cursor will put distances in ascending order, grouped by BID
arcpy.CreateTable_management(outpath, DistanceSort)
arcpy.AddField_management(outpath + DistanceSort, "BID","LONG")
arcpy.AddField_management(outpath + DistanceSort, "SID","LONG")
arcpy.AddField_management(outpath + DistanceSort, "Distance","DOUBLE")
arcpy.AddMessage("Empty distance sort table created")

#Faster to use dictionary that sorts distance in ascending order, then insert???
#Prepare insert cursor to insert BID, SID, and Distance according to to SQL clause. Inserted into the the DistanceSort table
iCursor = arcpy.da.InsertCursor(outpath + DistanceSort, ["BID","SID","Distance"])          
with arcpy.da.SearchCursor(outpath + Matrix, ["BID","SID","Distance"], sql_clause=(None, 'ORDER BY BID, DISTANCE')) as cursor:
    for row in cursor:
        iCursor.insertRow(row)
del iCursor
arcpy.AddMessage("Insert cursor created to insert sorted distances, SID, and BID")

#Create empty output table, where insert cursor will put distances under the Neighborlimit.
arcpy.AddMessage("Calculating neighbor constraint")
NeighborLimit = "NeighborLimit"                   
arcpy.CreateTable_management(outpath, NeighborLimit)
arcpy.AddField_management(outpath + NeighborLimit, "BID","LONG")
arcpy.AddField_management(outpath + NeighborLimit, "SID","LONG")
arcpy.AddField_management(outpath + NeighborLimit, "Distance","DOUBLE")
arcpy.AddMessage("Empty neighborlimit table created")

BIDList = sorted({row[0] for row in cursor})
arcpy.AddMessage("sorted BID list created")

iCursor = arcpy.da.InsertCursor(outpath + NeighborLimit, ["BID","SID","Distance"])
for BID in BIDList:
    expression = arcpy.AddFieldDelimiters(outpath + DistanceSort, "BID") + ' = ' + str(BID)
    with arcpy.da.SearchCursor(outpath + DistanceSort, ["BID", "SID", "Distance"], where_clause=expression) as cursor:  #or keep fields as  ["BID","SID","Distance"]?
        x = 1
        for row in cursor:
            if x < int(neighborconstraint) + 1:
                iCursor.insertRow(row)
                x = x + 1                       
del iCursor

Outcomes