0

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?

  • Welcome to GIS SE! As a new user be sure to take the Tour. For questions that involve code we ask that you show us where you are stuck with your own code by including a code snippet in your question. There is an edit button beneath your question which will enable you to do that and a {} button that enables you to format any highlighted code nicely. – PolyGeo Apr 19 '16 at 3:46
  • I re-worded the question to be more specific I think. I also added a code snippet of what direction I am trying to go in, but I am very new to Python and the coding is of course incomplete. – DanMilRiv Apr 19 '16 at 16:30
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]
  • Actually this code works great for the most part. I had to make a few changes because I can never get "with" commands to work, maybe it's my version or something. I also had to make sure a couple of more fields shared the same data along with FULLST. The one exception involved a street name that appears three times: Smith: 1-110, Smith: 141-706, Smith 707-3529. It did not add Smith to the street list. It should ignore the first two instances since there is a gap between 110 and 141, but do you have any suggestions as to why it then misses the link between 706 and 707? – DanMilRiv Apr 21 '16 at 16:22
  • If it makes a difference I changed the "with" command line to: "sc = arcpy.SearchCursor(InTable, None, None, "FULLST; LOR; HIR; MCN; OEN", None)" – DanMilRiv Apr 21 '16 at 16:27
  • With tends to work with the new arcpy.da module. I use it out of habit, but it may not be available in your version of arcpy. As for the Smith street example, you could rework the code where I put pass to maybe change the previousHIR variable to the most recent one. Currently it would keep checking the smith streets against the first HIR 110, instead of updating. I'll add an edit so that it is clearer. – dslamb Apr 21 '16 at 18:48
  • Thank you very much, that worked. However, I am going to have to rethink the original use for this product. Previously I was going to use the streetlist to select out the consecutive duplicates and then merge them into one row. Unfortunately, when there is a gap like Smith, I will need two rows, one for 1-110 and the other for 141-706. Using the 'what's in the streetlist method' is merging all the Smith rows. I think I will need to rework this as an UpdateCursor that only flags the consecutive rows in a new column and then run a Statistical Analysis based off the new row. Thanks again. – DanMilRiv Apr 22 '16 at 19:12
  • Try giving each row a unique id and keep track of that instead. – dslamb Apr 22 '16 at 19:19

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.