How to find identical street names with consecutively numbered addresses using Arcpy

Question

I am trying to write a python script that will only select multiple rows with the same street name if they have consecutively numbered addresses and then place those street names in a list. For example:

FULLST     | Lowest Address Number (LOR) | Highest Address Number (HIR):
ASH ST     | 100                         | 200
ASH ST     | 201                         | 300
LOUIS RD   | 100                         | 200
LOUIS RD   | 300                         | 400

In this example I need to place ASH ST in the list but leave LOUIS RD out because LOUIS represents a break in addresses for a non-contiguous street.

This is the workflow I imagine but don't know the proper coding:

streetlist = []
rows = arcpy.SearchCursor("G/MSAG/MergedTable")
for row in rows:
    if row.getValue("FULLST") == rows.next(row.getValue("FULLST")) and row.getValue("HIR") == rows.next(row.getValue("LOR"+1)):
        streetlist.append(row.getValue("FULLST"))

---

This is the code that was successful in adding FULLST names to the streetlist:

streetlist = []
sc = arcpy.SearchCursor(STMS, None, None, "FULLST; LOR; HIR; MCN; OEN", None)
addToList=False
previousSt = None
previousHIR = None
previousMCN = None
previousOEN = None
for row in sc:
    if not previousSt:
        previousSt = row.getValue("FULLST")
        previousHIR = row.getValue("HIR")
    else:
        if previousSt == row.getValue("FULLST") and previousMCN == row.getValue("MCN") and previousOEN == row.getValue("OEN"):
            if (row.getValue("LOR")-1) == previousHIR or row.getValue("LOR") < previousHIR:
                addToList = True
                previousHIR = row.getValue("HIR")
            else:
               previousHIR = row.getValue("HIR")
        else:
            if addToList:
                streetlist.append(previousSt)
                addToList = False
            previousSt = row.getValue("FULLST") 
            previousHIR = row.getValue("HIR")  
            previousMCN = row.getValue("MCN")  
            previousOEN = row.getValue("OEN") 

Instead of adding FULLST to the streetlist I need to add a Unique ID number so that it will only select rows that need to be merged while ignoring streets that share the same FULLST but have gaps compared to the consecutive rows. If I change the append line to:

streetlist.append(previousUID)

It will only add the first row in the series to the streetlist. For example: Smith: 1-110 ID:458 / Smith: 141-706 ID:459 / Smith: 707-3500 ID:460. Here only ID:458 is being added to the streetlist. If I change the append line to:

streetlist.append(row.getValue("UID"))

It will add the Unique ID for the row after the series. For the Smith example above, it will skip all of those and add Stone: 1-250 ID:461. Any suggestion on how to select the rows in between these two?

Answer 1

If I understand your question, you would like to create a list of street names that have consecutive numbering. That is, Low Address Number picks up where the previous row's High Street Number left off. I think to do this you need to keep track of the previous street name as you loop through the row. The code below is one way to check, but others may have a better approach.

To begin you probably want to make sure that your data is ordered by street name and low number.

streetlist = []
with arcpy.SearchCursor("G/MSAG/MergedTable",["FULLST","LOR","HIR"],sql_clause=(None, 'ORDER BY FULLST,LOR')) as sc:
    addToList=False
    previousSt = None
    previousHIR = None
    for row in sc:
        if not previousSt:
            previousSt = row[0]
            previousHIR = row[2]
        else:
            if previousSt == row[0]:
                if (row[1]-1) == previousHIR:
                    addToList = True
                    previousHIR = row[2]
                else:
                    pass
                    #I don't know what your data is like, but this leaves open
                    #the possibility that you may have a gap in the LOR and HIR
            else:
                if addToList:
                    streetlist.append(previousSt)
                    addToList = False
                previousSt = row[0]
                previousHIR = row[2]

It's difficult to provide code since I don't know exactly the different scenarios that will happen in the dataset, and like I said this may not be the best approach.

Basically, the code loops through each row of the search cursor preserving the previous row's value to compare it to the current row. If it meets your criteria a special variable addToList changes to True. When you change street names the code checks if the previous name should be added to the list.

EDIT

To account for breaks in the street numbering you could try this for where pass is above.

        if previousSt == row[0]:
            if (row[1]-1) == previousHIR:
                addToList = True
                previousHIR = row[2]
            else:
                previousHIR = row[2]