Overlaps and Gaps in Street Ranges

[ColinLang1]

I am trying to write a script that would detect overlaps and gaps in address range attributes on a line feature class for streets.  An example would be one segment of "MAIN STREET" has a range of 100-190 on the left and 101 to 191 on the right, and then the segment for the next block would have 200-230, 201-231 (GAP Scenario) or 188-230, 189-231 (OVERLAP Scenario).  I'm working with a province wide dataset with over 68,000 line segments, so any solution I come up with needs to work fairly efficiently.  I am aware that there are tools in the Roads and Highways toolbox, but that is for ArcGIS 10.3 and greater and I am still stuck using 10.2.2 for a little while longer.  

My first thought was to do some processing to buffer and spatial join the segments, and then dissolve to find the all segments belonging to the same named road, and link them together in order, OR to use the linear referencing tools but we have some unusual scenarios like a T-intersection where all directions are part of the same named street, or sections where there is only a range on one side and the other side has 0-0 and I can't seem to consistently get the ranges to order correctly.  We also have roads where the range may be 1-99 on a segment, then there is a bridge with a 0-0 range and then the range resumes after the bridge at 101-151 for example.

I was wondering if anyone else had tried to solve the problem of detecting range gaps and overlaps and if they had, if they would be willing to provide the logical steps behind their code, or a code sample. 

I'm not going to include code in my question, as I'm not sure my approach so far is the most efficient way to do it, and I'm ready to scrap what I've done and try something new.

[BruceHarold]

Hi Colin, I did have a sample out there that checked for underlaps and overlaps, it was complex and hard to maintain.

If you can share some data - no need for all of it - I might be able to take a look.

[DanPatterson_Retired]

Joe Borgione‌ didn't you have this address range issue last year that we looked at or was it a different one?

[JoeBorgione]

We did, but it was a different sort of issue...

[ColinLang1]

It seems I was trying to make this much harder than it had to be.  I've got a functional bit of logic now with a search cursor using a distinct sql clause to find all the unique combinations of street name and community name, and then inside that I loop through all the left side address attributes (with an order by sql clause on the cursor), and then I loop through all the right side address attributes.   It's not extremely fast, but I can set this up as a scheduled task to run overnight, so I'm not too concerned if it takes 45 minutes to an hour to run.  It gets me the result I need, so I can move forward.

[ColinLang1 · Accepted solution]

For those who may benefit from it, this is the code I'm going with as slow, but workable:

## f1 is temporary feature class with todays date, output by other processes.
f1 = "RSE20190328"
fields = ['L_StName','L_StDir','L_StTypP','L_StTypS','L_PlName'] ## street name, street direction, street type prefix, street type suffix and place name
with arcpy.da.SearchCursor(f1,fields, sql_clause=('DISTINCT','ORDER BY L_PlName, L_StName, L_StTypS')) as cursor1:
   for row1 in cursor1:
      street_name = row1[0]
      street_direction = row1[1]
      street_prefix = row1[2]
      street_suffix = row1[3]
      street_place_name = row1[4]
      ## some street names have apostrophes in them - make the search replace that with a % because no combination I tried of escaping or backslashing the quote seemed to work correctly
      where2 = "L_StName = '" + street_name.replace("'","%") + "'"
      if street_direction is not None:
         where2 += " and L_StDir = '" + street_direction + "' "
      if street_prefix is not None:
         where2 += " and L_StTypP = '" + street_prefix + "'"
      if street_suffix is not None:
         where2 += " and L_StTypS = '" + street_suffix + "'"
      ## some place names have apostrophes in them - make the search replace that with a % because no combination I tried of escaping or backslashing the quote seemed to work correctly
      if street_place_name is not None:
         where2 += " and L_PlName = '" + street_place_name.replace("'","%") + "'"

      ## work with left and right sides separately as they may have 0,0 ranges in different spots.

      fields2 = ['L_LADD','L_HADD','ID'] ## left low address and left high address
      list2 = [] ## put results into list so we can easily compare each row to the previous row -- hard to do in a cursor
      with arcpy.da.SearchCursor(f1, fields2, where2, sql_clause=(None,'ORDER BY L_LADD, L_HADD')) as cursor2:
         for row2 in cursor2:
            if row2[0] == 0 and row2[1] == 0: ## ignore 0,0 ranges for my purposes
            continue
            list2.append([row2[0], row2[1], row2[2]])

      for i in range (0, len(list2)-1):
         if list2[1] == list2[i+1][0] - 2: ## if there is a gap of exactly 2 there is no gap or overlap
            ##print "1: Good"
            continue
         elif list2[1] <= list2[i+1][0]:
            print "2:Gap" ## print results for now - eventually I will put the results into a separate list, and do an update cursor on them to
            print street_name + " " + street_suffix + " " + street_place_name + ": " + str(list2)
            continue
         elif list2[1] > list2[i+1][0] - 2 :
            print "3:Overlap"
            print street_name + " " + street_suffix + " " + street_place_name + ": " + str(list2)
            continue

      fields3 = ['R_LADD','R_HADD','ID'] ## right low address and right high address
      list3 = []
      with arcpy.da.SearchCursor(f1, fields3, where2, sql_clause=(None,'ORDER BY R_LADD, R_HADD')) as cursor3:
         for row3 in cursor3:
            if row3[0] == 0 and row3[1] == 0: ## ignore 0,0 ranges for my purposes
               continue
            list3.append([row3[0], row3[1], row3[2]])

      for i in range (0, len(list3)-1):
         if list3[1] == list3[i+1][0] - 2:
            ##print "1: Good"
            continue
         elif list3[1] <= list3[i+1][0]:
            print "2:Gap"
            print street_name + " " + street_suffix + " " + street_place_name + ": " + str(list3)
            continue
         elif list3[1] > list3[i+1][0] - 2 :
            print "3:Overlap"
            print street_name + " " + street_suffix + " " + street_place_name + ": " + str(list3)
            continue

[HeatherWidlund]

This was extremely helpful and I was able to modify it for my purposes. However, there is an error in the script posted. When you loop through the "for i in range" part, you have to put in [i] as the index. I am reposting the script with my fix.

## f1 is temporary feature class with todays date, output by other processes.
f1 = "RSE20190328"
fields = ['L_StName','L_StDir','L_StTypP','L_StTypS','L_PlName'] ## street name, street direction, street type prefix, street type suffix and place name
with arcpy.da.SearchCursor(f1,fields, sql_clause=('DISTINCT','ORDER BY L_PlName, L_StName, L_StTypS')) as cursor1:
   for row1 in cursor1:
      street_name = row1[0]
      street_direction = row1[1]
      street_prefix = row1[2]
      street_suffix = row1[3]
      street_place_name = row1[4]
      ## some street names have apostrophes in them - make the search replace that with a % because no combination I tried of escaping or backslashing the quote seemed to work correctly
      where2 = "L_StName = '" + street_name.replace("'","%") + "'"
      if street_direction is not None:
         where2 += " and L_StDir = '" + street_direction + "' "
      if street_prefix is not None:
         where2 += " and L_StTypP = '" + street_prefix + "'"
      if street_suffix is not None:
         where2 += " and L_StTypS = '" + street_suffix + "'"
      ## some place names have apostrophes in them - make the search replace that with a % because no combination I tried of escaping or backslashing the quote seemed to work correctly
      if street_place_name is not None:
         where2 += " and L_PlName = '" + street_place_name.replace("'","%") + "'"

      ## work with left and right sides separately as they may have 0,0 ranges in different spots.

      fields2 = ['L_LADD','L_HADD','ID'] ## left low address and left high address
      list2 = [] ## put results into list so we can easily compare each row to the previous row -- hard to do in a cursor
      with arcpy.da.SearchCursor(f1, fields2, where2, sql_clause=(None,'ORDER BY L_LADD, L_HADD')) as cursor2:
         for row2 in cursor2:
            if row2[0] == 0 and row2[1] == 0: ## ignore 0,0 ranges for my purposes
            continue
            list2.append([row2[0], row2[1], row2[2]])

      for i in range (0, len(list2)-1):
         if list2[i][1] == list2[i+1][0] - 2: ## if there is a gap of exactly 2 there is no gap or overlap
            ##print "1: Good"
            continue
         elif list2[i][1] <= list2[i+1][0]:
            print "2:Gap" ## print results for now - eventually I will put the results into a separate list, and do an update cursor on them to
            print street_name + " " + street_suffix + " " + street_place_name + ": " + str(list2)
            continue
         elif list2[i][1] > list2[i+1][0] - 2 :
            print "3:Overlap"
            print street_name + " " + street_suffix + " " + street_place_name + ": " + str(list2)
            continue

      fields3 = ['R_LADD','R_HADD','ID'] ## right low address and right high address
      list3 = []
      with arcpy.da.SearchCursor(f1, fields3, where2, sql_clause=(None,'ORDER BY R_LADD, R_HADD')) as cursor3:
         for row3 in cursor3:
            if row3[0] == 0 and row3[1] == 0: ## ignore 0,0 ranges for my purposes
               continue
            list3.append([row3[0], row3[1], row3[2]])

      for i in range (0, len(list3)-1):
         if list3[i][1] == list3[i+1][0] - 2:
            ##print "1: Good"
            continue
         elif list3[i][1] <= list3[i+1][0]:
            print "2:Gap"
            print street_name + " " + street_suffix + " " + street_place_name + ": " + str(list3)
            continue
         elif list3[i][1] > list3[i+1][0] - 2 :
            print "3:Overlap"
            print street_name + " " + street_suffix + " " + street_place_name + ": " + str(list3)
            continue