Skip navigation
All People > rfairhur24 > Richard Fairhurst's Blog

I work for an agency that maintains a Land Management System (LMS) for tracking land use cases, like subdivisions and permits, processed by our Planning and Building and Safety Departments.  For 20 years our LMS system has been integrated with GIS based on the Assessor’s Parcel numbers entered by our users.  The parcel shapes associated with these parcel numbers are used to transfer information from GIS into our LMS and to create GIS features representing our LMS cases.  However, our reliance on Assessor’s Parcel Numbers alone has produced far from perfect results due to many factors.  We recently replaced our LMS and now have a system that also integrates the Address Points feature class that my agency maintains.  This will help us solve many problems for our new cases as we go forward, but we did not attempt to create a direct association of our historic LMS address data with our GIS address points during conversion.   Fortunately I have found that there is third integration option for solving these historic LMS/GIS problems:  Subdivision and Lot descriptions.

 

When it applies, the Assessors of my jurisdiction enters subdivision and lot information that describes each parcel and normally my agency’s LMS users have entered the subdivision and lot number descriptions of the cases that fall on these parcels.  I have been discovering ways I can efficiently apply this data relationship to dramatically improve my agency’s historic LMS case shapes on a large scale.  This Blog will provide an overview of one of the processes I have developed for leveraging this data relationship where it exists to significantly transform my LMS case shapes in GIS.

 

An example of what can be accomplished in an afternoon using my techniques is shown below.


Case shapes based on original parcel numbers - 26,105 parcels countywide

 

Case shapes based on subdivision and lot description parcels - 124,643 parcels countywide

 


A Basic Overview of the Process

 

Without going into a lot of detail, here are the broad steps I followed to transform the case shapes below that were created from parcels that existed at the time of application before the property was subdivided.  None of the shapes below represent the true boundaries of the case applications.

 

1.   For the Assessor’s parcel feature class, which must be within a geodatabase, I concatenated the separate fields that make up the subdivision and lot description or each parcel into a single field using a standardized description format

 

2.   Extract the LMS case number and field(s) containing each case’s subdivision and lot descriptions into a file geodatabase table

 

3.   For the LMS data select the sets of records that contain the most common forms of a subdivision and lot description from within a field that may contain one.

 

4.   Calculate the subdivision and lot portions of the description into separate fields using Python parsing methods.

 

5.    Summarize all unique values in the field containing the lot portions of the description into a new summary table in the geodatabase containing the LMS data.

 

6.    Add the set of pairs of fields required to store all of the lot ranges that can be parsed from the original lot description and parse the range values into them. This is probably the hardest step, depending on the number of lot descriptions you have and the format variations you encounter.

 

7.   Run a script (see the end of this post for the script code) that populates a new table stored within the LMS geodatabase with the expanded set of records required to populate a new field with each individual lot number defined in the ranges of every lot description field pair in the summary table.  This step makes all of the time spend doing the previous step well worth the effort.

 

8.   Summarize the original LMS case data into a new table using the Summary Statistics tool so that it only has one record per case containing the case number and the legal description fields added in step 4 at minimum, although you can include as many fields in the output that you want so long as they do not cause more than one record per case to be created in the output.

 

9.   Make the summarized LMS case data created in step 8 into a Table view and join its lot description field populated in step 4 with same field contained in the expanded table populated by the script in step 7.

 

10.    Export the joined LMS case data table into a new table stored in the same geodatabase as the parcel feature class. With ArcGIS 10.3.1 and above this will cause the case records to expand to cover all of the records required to describe each individual lot for all of the case subdivision and lot descriptions.

 

11.   Add a new field to the export output of step 10 and calculate the concatenation of the subdivision name with the field containing the individual lot values of each record into a new subdivision and lot concatenation field. These concatenated descriptions must match the format of the Assessors subdivision and lot concatenation field populated in step 1.

 

12.   Make the Assessor’s parcel feature class into a layer and join its concatenated subdivision and lot field to the new concatenated subdivision and lot field of the expanded case record calculated in step 11.

 

13.   Export the joined Parcel features to a new feature class to expand the set of parcel features to cover every individual lot in every joined case.

 

14.   Here is the final result.  This is a true representation of the parcels and property each case actually should have covered.

 

The outcome is an LMS case feature class that has features in the shape of each and every parcel that matched the lots described in the case’s original overall subdivision and lot description. The more cases I need to be process, the more efficient this process becomes.  I have applied it to nearly 1 million LMS cases in an afternoon and created a parcel output that would have required approximately 5 years of man hours to manually enter all of them individually into the LMS system.

 

Based on questions and requests posted to this Blog I will add sections to this post to give further details that explain how I approached any step I have listed .  There is a great deal more I could say about each aspect of this process, but for now I will post these general steps as a template that can at least help me make the process even more efficient or automated in the future.

 

Below is the script I use in step 7.

from time import strftime  
 
print( "Start script: " + strftime("%Y-%m-%d %H:%M:%S")  )
 
import arcpy 
 
sourceFC = r"\\agency\AgencyDFS\Tran\FILES\GISData\rfairhur\Layers\Plus_Conversion_Data\Plus_Conversion_Data.gdb\PLUS_LOT_FIELD"
sourceFieldsList = ["LOT","START_1","END_1","START_2","END_2","START_3","END_3","START_4","END_4","START_5","END_5","START_6","END_6","START_7","END_7","START_8","END_8","START_9","END_9","START_10","END_10","START_11","END_11"]

insertFC = r"\\agency\AgencyDFS\Tran\FILES\GISData\rfairhur\Layers\Plus_Conversion_Data\Plus_Conversion_Data.gdb\PLUS_LOT_RANGES_FULL" 
insertFieldsList = ["LOT","RANGE_COUNT","EACH_LOT","START_1","END_1","START_2","END_2","START_3","END_3","START_4","END_4","START_5","END_5","START_6","END_6","START_7","END_7","START_8","END_8","START_9","END_9","START_10","END_10","START_11","END_11"]

arcpy.TruncateTable_management(insertFC)

icursor = arcpy.da.InsertCursor(insertFC, insertFieldsList)

countDict = {}
with arcpy.da.SearchCursor(sourceFC, sourceFieldsList) as sourceRows: 
    for sourceRow in sourceRows:
        for j in range(0,22,2):
            if j == 0 and sourceRow[1] == None:
                # print(sourceRow[j+1])
                l1 = [sourceRow[0],0,sourceRow[0]]
                l = l1 + list(sourceRow[1:])
                # print(l)
                icursor.insertRow(l)
                del l1
                del l
                if sourceRow[0] in countDict:
                    countDict[sourceRow[0]] += 1
                else:
                    countDict[sourceRow[0]] = 1
            elif sourceRow[j+1] != None:
                # print(sourceRow[j+1])
                for i in range(int(sourceRow[j+1]),int(sourceRow[j+2])+1):
                    l1 = [sourceRow[0],0,str(i)]
                    l = l1 + list(sourceRow[1:])
                    # print(l)
                    icursor.insertRow(l)
                    del l1
                    del l
                    if sourceRow[0] in countDict:
                        countDict[sourceRow[0]] += 1
                    else:
                        countDict[sourceRow[0]] = 1

del icursor

print( "Expanded Lot Records: " + strftime("%Y-%m-%d %H:%M:%S")  )

with arcpy.da.UpdateCursor(insertFC, insertFieldsList) as updateRows:
    for updateRow in updateRows:
        updateRow[1] = countDict[updateRow[0]]
        updateRows.updateRow(updateRow)

sourceFieldsList = ["LOT","RANGE_COUNT"]

with arcpy.da.UpdateCursor(sourceFC, sourceFieldsList) as updateRows:
    for updateRow in updateRows:
        updateRow[1] = countDict[updateRow[0]]
        updateRows.updateRow(updateRow)

print( "Set Range_Count field: " + strftime("%Y-%m-%d %H:%M:%S")  )

print( "Finish script: " + strftime("%Y-%m-%d %H:%M:%S")  )

I have developed a tool for splitting address ranges and have attached it.  Below is a description of how it works.

 

The toolbar is tied to an Editor Extension that must be enabled in order for the tools to work.  It is using the Python Editor extension which can slow performance when doing edit selections, so you generally should leave the extension off and only turn it on when you need to do address range splits or manipulations.  To turn the extension on or off click on the Customize menu->Extensions… submenu and when the extension dialog opens check or uncheck the item outlined in red shown below:

 

SplitRange1.png

The tools on the toolbar only become enabled when you begin an Editor session.  You need to select the polyline layer you want the tools to act on in the table of contents window (the layer name must be highlighted)

 

SplitRange2.png

 

The toolbar is shown below and has five groups of tools.

 

SplitRange3.png

 

The first tools flip line geometry without affecting address range values.  The first two tools are the same as the Edit Vertices tool and the Flip tool for a single selected line you are familiar with in ArcMap.  The third button uses the Flip Line tool which will flip all selected lines, but it requires an Advanced license to use it.

 

The second set of tools swap address values between the different address range fields. The first 3 buttons swap dual address pairs. The six other buttons swap single address pairs.  With these 9 buttons all swap combinations can be done either by a single button or combining two or three buttons.

 

The next drop down is a list of field names that match the address range fields in your polylines.  The tool should detect this if your layer uses one of the listed set of field names and select the one that matches.  If it does not match you field names tell me what they are and I will add you address range field names to the list.  You can manually type the names in, but I prefer to make the list cover all user configurations.

 

The next two drop downs control the snap tolerance affecting whether or not a line will split and whether that split will occur on a vertex or just on a line edge, respectively.  The units are in the native units of your layer.

 

The last two tools split lines and address ranges.  It is critical that your line geometry and ranges are aligned together properly before you use these tools.  The first button is like the ArcMap Split tool where you must select a single line from the highlighted layer and click on the map to split the geometry and address ranges proportionally.  The second button is like the Planarize tool and requires that you select two or more intersecting lines from the highlighted layer that will split the geometry and proportional ranges where they intersect each other.

 

Let me know if this works for you or if you have any problems.  There are some known limitations, particularly if you are editing SDE data.

1. There is a lack of ArcGIS/Python resources that support the manipulation of Date Field values using the Field Calculator

 

The ArcGIS software provides good built-in support and documentation that let users take full advantage of the data that they have stored in Text fields or Numeric fields (Short, Long, Float, and Double).  This includes built-in support in the Field Calculator that provides quick access to Python methods that let users parse, manipulate and transform the values of an existing field into another field that will hold reformatted data that is more suitable for analysis and presentation.  Being able to take existing text and numeric data and store it in alternative forms is often vital for working with this information through symbology, labeling, geoprocessing, and other common operations.

 

However, when it comes to Date or Date-Time fields, the Python methods provided by the Field Calculator are limited to manipulating the current system date or time or the parsed date components (year, month, day, hour, minute, second, AM/PM) that make up a date, not a date itself.   The methods provided are of no real use for parsing, manipulating and transforming dates or times that have been stored in a Date/Date-Time field.  The help documentation for the Field Calculator provides numerous examples of existing text and numeric fields, but is completely silent on how to apply any of the available Python date methods to dates stored in a date field.  As a result, beyond using the actual date value itself, few users know how to extract information from the values stored in a date field that would let them fully analyze and present their date-based data in the most effective way through the ArcGIS software.

 

The purpose of this Blog is to try to tackle the challenges of parsing, manipulating and transforming values stored in date fields or text fields holding date-based data using Python and the ArcGIS Field Calculator.  Some challenges in tackling date field values are specific to the use of Python in the Field Calculator, and could be handled relatively easily by using a VB Script Field Calculation alternative or exporting to Excel and using the formulas of that program.  However, this Blog will limit itself to offering a pure ArcGIS/Python solution, even where the ArcGIS/Python solution is a fair amount more difficult to implement than the VB Script calculation or an Excel formula.

 

2. Why is it important to be able to manipulate Date-Time fields and text fields with date-based data in ArcGIS?

 

Since ArcGIS 10.1, all products of the GIS software have included the ability to apply time awareness to user data.  However, often this data does not comply with the standards required to make good use of the time aware features of ArcGIS.

 

For example, time aware data must naturally sort in its native type according to datetime sorting rules, and often an alphabetical sort of data stored as text violates the patterns of a true datetime value sort.  Even if you have your data stored in a Date-Time field and it can be sorted, it is most often stored as the local datetime value of the data provider.  However, if that data ever needs to be compiled with data in other time zones it will need to be standardized to a universal datetime, such as UTC, before it can be integrated with data coming from outside of that local datetime representation and used with the time aware features of ArcGIS.

 

Also, although the ArcGIS software continues to expand the available geoprocessing tools that can make your data more suitable for use with its time aware capabilities, much of the processing still only takes places on the fly in conjunction with the ArcGIS time slider.  But in many cases, it is often more convenient if the datetime data has been recalculated into a field that has converted the datetime into periods of different multiples or divisions of years, quarters, months, days of the month, etc, so that the date-based data can be easily summarized into a table format for reports or easily used within a geoprocessing workflow.

 

The time aware features of ArcGIS also do not readily handle all datetime periods, such as divisions of a year into pay period cycles, the natural seasons of winter, summer, spring and autumn, lunar cycles, etc. all of which can be derived from a datetime value rather than typed by a user.  Being able to convert a datetime value into custom or natural time period divisions or groupings can be essential to fully analyzing hidden relationships within time-based data and invaluable to let you take advantage of the full range of ArcGIS capabilities for presenting that data.

 

3. When is a Date or a Date-Time Field Value neither a Date nor a DateTime?

 

Unfortunately, the answer to this section's question is "when it is a Unicode String", which is what is returned by a Date or Date-Time field when the Field Calculator is set to use the Python parser option.  This is a peculiarity of a Python field calculation, since If you are using a cursor in a Python script the value returned by a date field will be a date or datetime variable and not a Unicode string  The most likely reason a Unicode String is returned is that the field calculator interface is closely related to the labeling expression interface.  When a date field is used to create a label the returned formatted date string ensures that a user-friendly date will appear in the label without using any other Python code.  If a Python Date or DateTime variable was returned, the label would not appear in a user-friendly format without specifying a format through a method like datetime.strptime().

 

At least in the case of Windows-based systems, the format of the date string is specified in the OS Settings and is controlled by one or both of the OS Short Date and OS Long Time settings.  Users of other operating systems will have to verify for themselves where the setting that controls the date format string can be found.  The OS short date setting controls both the way the date will appear in the Table Window and the format of the string provided from a date field to a Python Field Calculation.

 

Because different cultures use different short date and long time formats and each culture has multiple short date and long time formats to choose from, the conversion of the string to an actual date or datetime variable that can be easily manipulated to extract date information is the biggest challenge that has to be overcome in order to use the Field Calculator with the Python option.

 

 

4. Windows and Python format codes used to parse date and/or time strings

 

The format strings for the US & Canada region default Windows short date format is "M/d/yyyy" which equates to the Python format u'%m/%d/%Y' (i.e, u'12/25/2016').  The format strings for the US & Canada region default Windows long time format is 'h:mm:ss tt' which equates to the Python format u'%I:%M:%S %p' (i.e., u'1:52:20 PM').  The Python formats would need to change if you have selected an incompatible alternative format, or if you have regional settings that use a different format setting than the US & Canada default short date and long time formats.

 

The Windows and Python equivalent formats for the common date or time components are:

 

MM or M = %m (Month number with or without a leading zero)

MMM = %b (Month as locale’s abbreviated name)

MMMM = %B (Month as locale’s full name)

dd or d = %d (Day of the month number with or without a leading zero)

ddd = %a (Weekday as locale’s abbreviated name)

dddd = %A (Weekday as locale’s full name)

yy or y = %y (Year number without century with or without a leading zero)

yyyy = %Y (Year number with century)

hh or h = %I (Hour (12-hour clock) with or without a leading zero)

HH or H = %H (Hour (24-hour clock) with or without a leading zero)

mm or m = %M (Minute number with or without a leading zero)

ss or s = %S (Second number with or without a leading zero)

tt = %p (Locale’s equivalent of either AM or PM)

/, -, : = /, -, : (literal date or time separator characters)

 

Be sure to pay attention to the case of each Windows or Python code, since uppercase codes and lowercase codes parse different date or time components.  If you are using an OS other than Windows you will have to determine the equivalents between the format codes your OS uses and those Python uses to match your OS' short date and long time formats.

 

The full set of Windows US & Canada short date and long time codes and their Python code equivalents are shown below:

 

 

5. A Field Calculator calculation for parsing a formatted date and/or time in a Text field or a Date or Date-Time field and convert it into a Python datetime variable.

 

The date value formats that are provided to a Python Field Calculation from a Date or Date-Time field include Null values (Python==None), short date, short date and time, and time only (which occurs if the date is 12/30/1899 in a file gdb).  This also often applies to text fields that store a date and/or time if users have been well trained or use a front end validation interface to enter data.  The code I have designed is intended to deal with each possibility.  To do the parsing I will be using datetime.strptime method which attempts to parse a Unicode string into a datetime based on the specified date and time format strings.

 

Open the Field Calculator on a blank date field (right-click the field name in the output column in a table and choose Field Calculator from the context menu) and set the Field Calculator to the following settings:

 

Parser: Python

 

Show Codeblock: Checked

 

Pre-Logic Script Code:

def unicodeToDate( uniDate, dateFormat=u'%m/%d/%Y', timeFormat=u'%I:%M:%S %p' ):
  datetimeFormat = '{0} {1}'.format(dateFormat, timeFormat)
  if uniDate == None:
    return None
  try:
    return datetime.datetime.strptime(uniDate, dateFormat)
  except:
    try:
      return datetime.datetime.strptime(uniDate, datetimeFormat)
    except:
      try:
        # Python parses time only values as 1/1/1900 so subtract 2 days to match 12/30/1899, the date a file gdb uses.
        return datetime.datetime.strptime(uniDate, timeFormat) + timedelta(days=-2)
      except:
        return None

 

Expression: unicodeToDate( !Your_Date_Field!, dateFormat = u'%m/%d/%Y' , timeFormat = u'%I:%M:%S %p' )

 

Be sure to replace !Your_Date_Field! with a real Date/Date-Time/Time field or Text field containing dates and/or times.  The number of embedded try-except blocks can be expanded to include other formats.  This can be useful when attempting to convert a text field with multiple date and/or time formats into a true date field.  The conversion of all of the formats can be done by a single calculation as long as the order of the try-except blocks are arranged to test for the most common formats to the least common formats from top to bottom.

 

6. A Field Calculation that can return many different derived date components from the datetime variable returned by the calculation in Step 5.

 

Date fields have many components that can derived from the datetime values, and I prefer to use a single calculation to get at them all rather than many separate calculations.  This saves me from having to load many different .cal files and replace the date field used by each calculation to get a different date component.  The Calculation below uses a partIndex value ranging from 0-25 to get any of 26 components from any date field that can be parsed by the calculation in step 5.  (These calculations work for Python 2.7, but some may need to be changed to work for Python 3.4.x used by the latest version of ArcGIS Pro.)

 

Open the Field Calculator on a blank numeric field with a date component name like Year_ (right-click the field name in the output column in a table and choose Field Calculator from the context menu) and set the Field Calculator to the following settings:

 

Parser: Python

 

Show Codeblock: Checked

 

Pre-Logic Script Code:

def dateFieldPart( uniDate , partIndex = 8 , dateFormat = u'%m/%d/%Y' , timeFormat = u'%I:%M:%S %p' , offsetStartYear = 0 ):  

  # use the def defined in the previous field calculation which is included in this new calculation  
  theDate = unicodeToDate( uniDate, dateFormat=u'%m/%d/%Y', timeFormat=u'%I:%M:%S %p' )  

  # if the unicode could not be parsed to a datetime return None, otherwise use the partIndex specified by the user to return one of many different derived components of a datetime.  
  if theDate == None:  
    return None  
  try:  
    if partIndex == 0: # century (100-year periods-returns period start year-default is 00)
      return theDate.year - ((theDate.year + (100 - (offsetStartYear % 100) % 100)) % 100)
    elif partIndex == 1: # half-century (50-year periods-returns period start year-defaults end in 00 and 50)  
      return theDate.year - ((theDate.year - (50 - (100 - (offsetStartYear % 50)) % 50)) % 50)  
    elif partIndex == 2: # quarter-century (25 year periods-returns period start year-defaults end in 00, 25, 50, 75)  
      return theDate.year - ((theDate.year - (25 - (100 - (offsetStartYear % 25)) % 25)) % 25)  
    elif partIndex == 3: # decade (10 year periods-returns period start year-defaults end in 00, 10, 20, 30, ... 90)  
      return theDate.year - ((theDate.year - (10 - (100 - (offsetStartYear % 10)) % 10)) % 10)  
    elif partIndex == 4: # lustrum (5-year periods-returns period start year-defaults end in 00, 05, 10, 15, ... 95)
      return theDate.year - ((theDate.year - (5 - (100 - (offsetStartYear % 5)) % 5)) % 5)  
    elif partIndex == 5: # quadrennium  (4-year periods-returns period start year-defaults end in 00, 04, 08, 12, ... 96)  
      return theDate.year - ((theDate.year - (4 - (100 - (offsetStartYear % 4)) % 4)) % 4)  
    elif partIndex == 6: # triennium  (3-year periods-returns period start year-defaults end in 00, 03, 06, 09, ... 97 over a 300 year cycle)
      return theDate.year - ((theDate.year - (3 - (300 - (offsetStartYear % 3)) % 3)) % 3)  
    elif partIndex == 7: # biennium  (2-year periods-returns period start year-defaults end in 00, 02, 04, 06, ... 98)  
      return theDate.year - ((theDate.year - (2 - (100 - (offsetStartYear % 2)) % 2)) % 2)  
    elif partIndex == 8: # year  
      return theDate.year  
    elif partIndex == 9: # fiscal year (year-1 for January-June and year for July-December)  
      return theDate.year-(abs(theDate.month-13))//7  
    elif partIndex == 10: # semiannual  (six-month periods, with first 6 months = 1 and second 6 months = 2)  
      return (theDate.month - 1)//6 + 1  
    elif partIndex == 11: # calendar quarter  
      return (theDate.month - 1)//3 + 1  
    elif partIndex == 12: # fiscal quarter  
      return ((theDate.month + 5)%12)//3 + 1  
    elif partIndex == 13: # month of year  
      return theDate.month  
    elif partIndex == 14: # month of year percent (January=0% and 99%<December<100%)  
      return float(float(theDate.month - 1) / 12) * 100.0  
    elif partIndex == 15: # day of year number (1-365 for non-leap years and 1-366 for leap years)  
      return theDate.timetuple().tm_yday  
    elif partIndex == 16: # day of year percent (January 1 = 0% and 99% < December 31 < 100%)  
      return float(float(theDate.timetuple().tm_yday - 1) / datetime.datetime(theDate.year, 12, 31).timetuple().tm_yday) * 100.0  
    elif partIndex == 17: # day of month  
      return theDate.day  
    elif partIndex == 18: # ISO week of the year (1-52 or 1-53)  
      return theDate.isocalendar()[1]  
    elif partIndex == 19: # day is weekday/weekend (0=Weekday and 1=Weekend)  
      return theDate.weekday()//5  
    elif partIndex == 20: # day of week (0=Monday to 6=Sunday)  
      return theDate.weekday()  
    elif partIndex == 21: # day of week (0=Sunday to 6=Saturday)  
      return (theDate.weekday()+1) % 7  
    elif partIndex == 22: # season northern hemisphere  
      return get_season(theDate)  
    elif partIndex == 23: # season southern hemisphere  
      return (get_season(theDate) + 1) % 4 + 1  
    elif partIndex == 24: # moonlight percent approximate (0=no moonlight to 100=max. moonlight),   
      return moon_light(theDate, 0)  
    elif partIndex == 25: # moon phase approximate   
      return moon_light(theDate, 1)  
  except:  
    return None  

def unicodeToDate( uniDate, dateFormat=u'%m/%d/%Y', timeFormat=u'%I:%M:%S %p' ):  
  datetimeFormat = '{0} {1}'.format(dateFormat, timeFormat)  
  if uniDate == None:  
    return None  
  try:  
    return datetime.datetime.strptime(uniDate, dateFormat)  
  except:  
    try:  
      return datetime.datetime.strptime(uniDate, datetimeFormat)  
    except:  
      try:  
        # Python parses time only values as 1/1/1900 so subtract 2 days to match 12/30/1899, the date a file gdb uses. 
        return datetime.datetime.strptime(uniDate, timeFormat) + datetime.timedelta(days=-2)
      except:  
        return None  

# Approximate ranges defining seasons only.  Not precise. Use a site-package like PyEphem for precision.  
Y = 2000 # dummy leap year to allow input X-02-29 (leap day)  
seasons = [(1, (datetime.date(Y,  1,  1),  datetime.date(Y,  3, 20))),  
           (2, (datetime.date(Y,  3, 21),  datetime.date(Y,  6, 20))),  
           (3, (datetime.date(Y,  6, 21),  datetime.date(Y,  9, 22))),  
           (4, (datetime.date(Y,  9, 23),  datetime.date(Y, 12, 20))),  
           (1, (datetime.date(Y, 12, 21),  datetime.date(Y, 12, 31)))]  

def get_season(dDate):  
  dDate = datetime.date(Y, dDate.month, dDate.day)  
  return next(season for season, (start, end) in seasons  
                if start <= dDate <= end)  

# Approximate moon light and phase only.  Not precise. Use a site-package like PyEphem for precision.  
def moon_light(dDate, moon_type=0):  
  # moon_type is: 0=moonlight percent, 1=moon phase  
  month = dDate.month  
  day = dDate.day  
  year = dDate.year  
  ages = [18, 0, 11, 22, 3, 14, 25, 6, 17, 28, 9, 20, 1, 12, 23, 4, 15, 26, 7]  
  offsets = [-1, 1, 0, 1, 2, 3, 4, 5, 7, 7, 9, 9]  
  description = ['New (Totally dark)',
                 'Waxing Cresent (Increasing to full)',
                 'In its first quarter (Increasing to full)',
                 'Waxing Gibbous (Increasing to full)',
                 'Full (Full light)',
                 'Waning Gibbous (Decreasing from full)',
                 'In its last quarter (Decreasing from full)',
                 'Waning Cresent (Decreasing from full)']  
  months = ["Jan", "Feb", "Mar", "Apr", "May", "Jun",  
            "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]  
  if day == 31:  
    day = 1  
  days_into_phase = ((ages[(year + 1) % 19] +  
                      ((day + offsets[month-1]) % 30) +  
                      (year < 1900)) % 30)  
  index = int((days_into_phase + 2) * 16/59.0)  
  if index > 7:  
    index = 7  
  status = description[index]  
  # light should be 100% 15 days into phase  
  light = int(2 * days_into_phase * 100/29)  
  if light > 100:  
    light = abs(light - 200);  
  date = "%d%s%d" % (day, months[month-1], year)  
  if moon_type == 0:  
    return light  
  elif moon_type == 1:  
    return index
  elif moon_type == 2:
    return status

 

Expression:  dateFieldPart( !Your_Date_Field! , partIndex = 8 , dateFormat = u'%m/%d/%Y' , timeFormat = u'%I:%M:%S %p' , offsetStartYear = 0 )

 

Be sure to replace !Your_Date_Field! with a real Date/Date-Time field or Text field containing dates or date-times.  Although the calculation above will convert a real Time field or Text field containing time, only values derived from the date associated with the time value (i.e., 12/30/1899) will be returned.

 

The partindex parameter determines the kind of data that will be derived from the date and returned by the calculation.  Since the partIndex = 8 in this example, the Expression above will convert the field date to a Python datetime variable and return the Year of that value in each record.  If you change the partIndex to another number a different date component will be outputted.  For example, if you change the partIndex parameter to  partIndex = 0 the calculation will convert the field date to a Python datetime variable and return the Century of that value in each record.

 

The offsetStartYear parameter affects the year grouping values returned by partIndexes 0 through 7.  It adjusts the start year that begins the period interval to a custom starting year.  This can be used to satisfy those who need to mark the start of centuries from year 1, and regard the 21st century as having started on 2001, not 2000. By default the function returns centuries based on a year 0 starting year due to the mathematical simplicity of computing that value and its popular usage.  To make centuries aligned to the Year 1 as the century starting point instead, use partIndex = 0 and offsetStartYear = 1 as your parameters.  Other less traditional starting years can also be used.  For example, by setting partIndex = 0 and offsetStartYear = 50 you can define periods of 100 years (centuries) that extend from one mid-century to the next mid-century according to popular reckoning, rather than a traditional century.  Year 1 advocates can similarly use offsetStartYear = 51 to align their custom century groupings to the year that they regard as the mid-century start year.

 

Other calculations are possible, for example I used a very simple method of estimating seasons and moon phases, but those methods could be replaced with methods that use a site package like PyEphem to return very precise values.  Or I could add an option for a custom 2 week pay period cycle, primary seasonal year so that December to March seasons avoid being separated across different calendar years, or other custom groupings or divisions of years, months, day, week, hours, minutes, seconds, etc.

 

7. A sample time-based animation demo video showing some of what you can do using the calculation.

 

Here is a link to a YouTube video of a time based animation demo I created that shows the importance of being able to manipulating date fields to create alternative formats and date groupings.  This time based animation was only possible after I used my calculation to transform my original Applied_Date field into an Applied_Fiscal_Year Long field for some of my building permits data  The new field let me symbolize each Fiscal Year with a distinct color using categorized symbols.  The new field also let me Dissolve the permits to get a Count of the Permits in each Fiscal Year.  The Dissolve allowed me to create labels on the map showing the counts and a time based graph of the permit activity by Fiscal Year.  The Dissolve also dramatically improved the redraw performance over my original data.

 

Below are a three screenshots from the demo that shows different stages of building permit activity over time using both a time enabled map and a linked bar chart graph.

 

 

8. Please let me know any feedback you may have.

 

If you discover any errors in the formulas I have used in my calculations or can suggest an alternative formula that is more Pythonic or that makes the code more efficient/flexible, please let me know, so that I can incorporate your corrections/suggestions into the calculations of this Blog for the benefit of everyone.  Also if you identify any changes that need to be made to allow these calculations to be compatible with ArcGIS Pro/Python 3.4.x and above, please let me know.  I hope to eventually update this Blog with additional versions of the calculations that have been tested for compatibility and optimized for use with ArcGIS Pro/Python 3.4.x and above.

 

I hope this helps you uncork your Time in a Bottle.

 

 

Revisions:

Corrected formula for the case when the only the timeFormat parses the date on 7/2/2016 at 8:26 AM PST.

Corrected offsetStartYear formulas on 7/2/2016 8:26 AM PST.

Corrected descriptions of formulas that use offsetStartYear on 7/2/2016 12:50 PM PST and 7/3/2016 8:06 AM PST.

Google has added 25 million buildings in the United States this year to their Map view.  These building outlines can be converted to polygons in ArcGIS if you have Photoshop and do the following steps:

1.      Locate an area with buildings in Google Maps with the Map view on.  Maximize the browser window.  Zoom until you are at least at the second level zoom above the minimum level to make the buildings appear to get enough resolution. 
2.      Press the Alt+Prt Scr buttons on the keyboard to get a screen shot (or use your favorite screen shot software).

Google1.png
3.      Open Photshop and create a new project (on the menu use File -> New or press Ctrl+N).
4.      Paste the screenshot into the Photoshop project (on the menu use Edit -> Paste or Ctrl+V).


5.      From the Menu choose Select -> Color Range… (I added a custom keyboard shortcut of Alt+Shift+Ctrl+R)
6.      With the Select option set to Sampled Color click the eyedropper cursor on the center of a building.  Check the inverse option.  Press the OK button.


7.      With the selection still active create a new layer (on the menu use Layer -> New -> Layer… or press Shift+Ctrl+N).  Name the layer Building Background and press the OK button.


8.      Add a fill to the layer (from the menu use Edit -> Fill… or press Shift+F5). Choose the Mode “Black” and press the OK button.


9.      Deselect everything by pressing Ctrl+D.  From the menu choose Select -> Color Range... (I added a custom keyboard shortcut of Alt+Shift+Ctrl+R).  With the Select option set to Sampled Color click the eyedropper cursor on the center of a building.  Press the OK button.


10.  With the selection still active create a new layer (on the menu use Layer -> New -> Layer… or press Shift+Ctrl+N).  Name the layer Building Foreground and press the OK button.


11.  Add a fill to the layer (from the menu use Edit -> Fill… or press Shift+F5). Choose the Mode “White” and press the OK button.


12.  You should now have a Black and White image.  The outlines of the roads will have areas with white pixels that need to be painted out.  Set the palette color to Black.  Right click on the map and choose a paint brush size that fits comfortably within the roadway (about 40 pixels).  Paint out the small white pixels.  Also paint out partial buildings on the edges of the map.
13.  On the menu choose Image -> Mode -> Grayscale… (I added a custom keyboard shortcut to the Grayscale option of Alt+Shift+Ctrl+G) and then choose to not Flatten the image and to Discard the Color information.


14.  Save the photoshop project as a BMP.  Use 8 Bit depth (if it says higher depths you have a problem).
15.  Open a new ArcMap session and just add aerials or a satellite basemap.
16.  Right click the Date Frame and chose Date Frame Properties.  On the Freme tab choose the background to be black.
17.  Locate the same set of buildings on the aerial.
18.  Add the BMP you saved in step 14.  Say OK to the warning about the image not being spatially referenced.
19.  Change the BMP layer transparency to 50%.
20.  Right click the BMP layer and Zoom to Layer.
21.  Open the Georeferencing toolbar.


22.  Press the Add Control Points button and choose a corner of a building that you can match to the aerial.


23.  Press the Back button to return to the aerial and set the control point at the corner of the same building on the aerial.
24.  Set another control point that is vertical or horizontal to the previous point, not diagonal.
25.  Set additional control points if necessary, but not more than 4 total points.
26.  When you are satisfied with the georeferencing position on the toolbar choose Georeferencing -> Update Georeferencing.


27.  Set the BMP transparency to 0% and turn off the aerials.


28.  Open the Toolboxes -> System Toolboxes -> Conversion Tools Toolbox -> From Raster Toolset -> Raster to Polygon tool.
29.  Choose the bmp as the input and set the output location and name to a geodatabase feature class and press OK.
30.  Start an editor session on the polygon layer you just created and delete the background polygon.
31.  Open the Table View for the new polygon feature class and sort by shape area. Choose any very small polygons that are not buildings that you did not paint out and delete them.

32.  Set up a permanent feature class that you will append all new buildings to or append this set of buildings to the feature class you set up previously.  The result shown below created 115 building outline polygons (the polygons are set to hollow with a red outline that is 3 points thick).

I have wanted tools in ArcMap that let me do more things with Standalone Tables.   For example, there is no tool that will let me duplicate records in a Standalone Table or insert a row directly into an existing Standalone Table that came from the tabular portion of a feature.  I cannot Copy/Paste rows or features into a Standalone Table at all like I can with features in a Feature Class.  Even for features using Copy/Paste is slow and can only be done in an edit session.  The Append tool works for table to table inserts or feature class to feature inserts, but only if the input and output are different from each other.  The Append tool also will not let me take the tabular data of a feature and append it into an existing Standalone Table.

 

I wanted a tool that would quickly do inserts and work for Feature Class to Feature Class, Feature Class to Table, or Table to Table inserts.  I also wanted the selection behavior to be similar to the Copy/Paste behavior.  I wanted the tool to only work when records are first selected, and I wanted the selected records of the input to be cleared and the newly inserted records in the target to be selected when the tool finishes.  That is especially useful when the input and the target are the same table or feature class.  I also wanted the tool to automatically match fields like Copy and Paste does.

 

The field matching behavior is similar to using the default field map that appears in the Append tool when the NO TEST option is chosen, so the schemas don't have to match exactly.  However, unlike the Append tool, the Insert Selected Features or Rows tool does not support multiple inputs and does not support customized field mapping.

 

An edit session should not be active if the data is versionsed, since arcpy cannot connect to an active edit session.  The tool will internally start and stop an edit session when versioned data is involved.  If data in not versioned an edit session may be active, but is not necessary.  When an edit session is active for unversioned data, if the edit session is stopped before saving the inserts then the inserts will disappear without warning and will not be saved.

 

The tool lets you specify the number of copies of each feature or row you want to insert into the target layer or table view.  The minimum number of copies is 1, and 1 is the default value.

 

NOTE:  If the table view of the target is open when the tool is run, then the Append tool and my tool are affected by an Esri bug.  The records are inserted, but the total record count of the table view does not update and the newly inserted records cannot be accessed or seen when Show all records view is active.  However, since the inserted records are selected by my tool, after the tool completes the selected records count of the table view is updated and the new records do appear when the Show selected records view is active.  To fully refresh the table view to show the new records in the Show all records view mode either the table view has to be closed and reopened or a field has to have its values sorted.

 

The attached python toolbox includes the code for the tool and the tool help files.  I recommend that you save the attached toolbox in your "%APPDATA%\ESRI\Desktop10.2\ArcToolbox\My Toolboxes" directory. The toolbox also Includes my Multiple Field Key to Single Field Key tool for 10.2.  For more on that tool see my blog here.

 

The code for the tool is shown below:

 

# Author: Richard Fairhurst
# Date:   July 2015


import arcpy

class Toolbox(object):
    def __init__(self):
        """Define the toolbox (the name of the toolbox is the name of the
        .pyt file)."""
        self.label = "Field Match Tools"
        self.alias = ""

        # List of tool classes associated with this toolbox
        self.tools = [InsertSelectedFeaturesOrRows]

class InsertSelectedFeaturesOrRows(object):
    def __init__(self):
        """Define the tool (tool name is the name of the class)."""
        self.label = "Insert Selected Features or Rows"
        self.description = ""
        self.canRunInBackground = False


    def getParameterInfo(self):
        """Define parameter definitions"""
        # First parameter
        param0 = arcpy.Parameter(
            displayName="Source Layer or Table View",
            name="source_layer_or_table_view",
            datatype="GPTableView",
            parameterType="Required",
            direction="Input")


        # Second parameter
        param1 = arcpy.Parameter(
            displayName="Target Layer or Table View",
            name="target_layer_or_table_view",
            datatype="GPTableView",
            parameterType="Required",
            direction="Input")


        # Third parameter
        param2 = arcpy.Parameter(
            displayName="Number of Copies to Insert",
            name="number_of_copies_to_insert",
            datatype="GPLong",
            parameterType="Required",
            direction="Input")


        param2.value = 1        


        # Fourth parameter
        param3 = arcpy.Parameter(
            displayName="Derived Layer or Table View",
            name="derived_table",
            datatype="GPTableView",
            parameterType="Derived",
            direction="Output")


        param3.parameterDependencies = [param1.name]
        param3.schema.clone = True


        params = [param0, param1, param2, param3]


        return params


    def isLicensed(self):
        """Set whether tool is licensed to execute."""
        return True


    def updateParameters(self, parameters):
        """Modify the values and properties of parameters before internal
        validation is performed.  This method is called whenever a parameter
        has been changed."""
        return


    def updateMessages(self, parameters):
        """Modify the messages created by internal validation for each tool
        parameter.  This method is called after internal validation."""
        if parameters[1].value:
            insertFC = parameters[1].value
            strInsertFC = str(insertFC)
            if parameters[0].value and '<geoprocessing Layer object' in strInsertFC:
                FC = parameters[0].value
                strFC = str(FC)
                if not '<geoprocessing Layer object' in strFC:
                    print("Input FC must be a layer if output is a layer")
                    parameters[0].setErrorMessage("Input must be a feature layer if the Output is a feature layer!")
                else:
                    dscFCLyr = arcpy.Describe(FC)
                    dscinsertFCLyr = arcpy.Describe(insertFC)
                    # add the SHAPE@ field if the shapetypes match
                    if dscFCLyr.featureclass.shapetype != dscinsertFCLyr.featureclass.shapetype:
                        print("Input and Output have different geometry types!  Geometry must match!")
                        parameters[0].setErrorMessage("Input and Output do not have the same geometry")
                    
                    if dscFCLyr.featureclass.spatialReference.name != dscinsertFCLyr.featureclass.spatialReference.name:
                        print("Input and Output have different Spatial References!  Spatial References must match!")
                        parameters[0].setErrorMessage("Input and Output do not have the same Spatial References!  Spatial References must match!")
        if parameters[2].value <= 0:
            parameters[2].setErrorMessage("The Number of Row Copies must be 1 or greater")
        return


    def execute(self, parameters, messages):
        """The source code of the tool."""
        try:
            mxd = arcpy.mapping.MapDocument(r"CURRENT")
            df = arcpy.mapping.ListDataFrames(mxd)[0]


            FC = parameters[0].value
            insertFC = parameters[1].value


            strFC = str(FC)
            strInsertFC = str(insertFC)


            FCLyr = None
            insertFCLyr = None


            for lyr in arcpy.mapping.ListLayers(mxd, "", df):
                # Try to match to Layer
                if '<geoprocessing Layer object' in strFC:
                    if lyr.name.upper() == FC.name.upper():
                        FCLyr = lyr
                if '<geoprocessing Layer object' in strInsertFC:
                    if lyr.name.upper() == insertFC.name.upper():
                        insertFCLyr = lyr
            if FCLyr == None or insertFCLyr == None:
                # Try to match to table if no layer found
                if FCLyr == None:
                    tables = arcpy.mapping.ListTableViews(mxd, "", df)
                    for table in tables:
                        if table.name.upper() == strFC.upper():
                            FCLyr = table
                            break
                if insertFCLyr == None:
                    tables = arcpy.mapping.ListTableViews(mxd, "", df)
                    for table in tables:
                        if table.name.upper() == strInsertFC.upper():
                            insertFCLyr = table
                            break


            # If both layers/tables are found then process fields and insert cursor
            if FCLyr != None and insertFCLyr != None:
                dsc = arcpy.Describe(FCLyr)         
                       
                selection_set = dsc.FIDSet


                # only process layers/tables if there is a selection in the FCLyr
                if len(selection_set) > 0:
                    print("{} has {} {}{} selected".format(FCLyr.name, len(selection_set.split(';')), 'feature' if '<geoprocessing Layer object' in strInsertFC else 'table row', '' if len(selection_set.split(';')) == 1 else 's'))
                    arcpy.AddMessage("{} has {} {}{} selected".format(FCLyr.name, len(selection_set.split(';')), 'feature' if '<geoprocessing Layer object' in strInsertFC else 'table row', '' if len(selection_set.split(';')) == 1 else 's'))
                    
                    FCfields = arcpy.ListFields(FCLyr)
                    insertFCfields = arcpy.ListFields(insertFCLyr)


                    # Create a field list of fields you want to manipulate and not just copy    
                    # All of these fields must be in the insertFC    
                    manualFields =  []
                    matchedFields = []
                    for manualField in manualFields:
                        matchedFields.append(manualField.upper())
                    for FCfield in FCfields:
                        for insertFCfield in insertFCfields:
                            if (FCfield.name.upper() == insertFCfield.name.upper() and
                                FCfield.type == insertFCfield.type and
                                FCfield.type <> 'Geometry' and
                                insertFCfield.editable == True and
                                not (FCfield.name.upper() in matchedFields)):    


                                matchedFields.append(FCfield.name)    
                                break
                            elif (FCfield.type == 'Geometry' and
                                  FCfield.type == insertFCfield.type):


                                matchedFields.append("SHAPE@")
                                break
                            elif insertFCfield.type == "OID":
                                oid_name = insertFCfield.name
                   
                    if len(matchedFields) > 0:
                        # Print the matched fields list
                        print("The matched fields are: {}".format(matchedFields))
                        arcpy.AddMessage("The matched fields are: {}".format(matchedFields))


                        copies = parameters[2].value
                        print("Making {} {} of each {}".format(copies, 'copy' if copies == 1 else 'copies', 'feature' if '<geoprocessing Layer object' in strInsertFC else 'table row'))
                        arcpy.AddMessage("Making {} {} of each {}".format(copies, 'copy' if copies == 1 else 'copies', 'feature' if '<geoprocessing Layer object' in strInsertFC else 'table row'))


                        oid_list = []
                        # arcpy.AddMessage(oid_name)
                        dscInsert = arcpy.Describe(insertFCLyr)
                        if '<geoprocessing Layer object' in strInsertFC:
                            oid_name = arcpy.AddFieldDelimiters(dscInsert.dataElement, oid_name)
                        else:
                            oid_name = arcpy.AddFieldDelimiters(dscInsert, oid_name)
                        rowInserter = arcpy.da.InsertCursor(insertFCLyr, matchedFields)
                        print("The output workspace is {}".format(insertFCLyr.workspacePath))
                        arcpy.AddMessage("The output workspace is {}".format(insertFCLyr.workspacePath))
                        if '<geoprocessing Layer object' in strInsertFC:
                            versioned = dscInsert.featureclass.isVersioned
                        else:
                            versioned = dscInsert.table.isVersioned
                        
                        if versioned:
                            print("The output workspace is versioned")
                            arcpy.AddMessage("The output workspace is versioned")
                            with arcpy.da.Editor(insertFCLyr.workspacePath) as edit:
                                with arcpy.da.SearchCursor(FCLyr, matchedFields) as rows:       
                                    for row in rows:       
                                        for i in range(copies):
                                            oid_list.append(rowInserter.insertRow(row))
                        else:
                            print("The output workspace is not versioned")
                            arcpy.AddMessage("The output workspace is not versioned")
                            with arcpy.da.SearchCursor(FCLyr, matchedFields) as rows:       
                                for row in rows:       
                                    for i in range(copies):
                                        oid_list.append(rowInserter.insertRow(row))
                        del row       
                        del rows       
                        del rowInserter  
                        if len(oid_list) == 1:
                            whereclause = oid_name + ' = ' + str(oid_list[0])
                        elif len(oid_list) > 1:
                            whereclause = oid_name + ' IN (' + ','.join(map(str, oid_list)) + ')'
                        if len(oid_list) > 0:
                            # arcpy.AddMessage(whereclause)
                            # Switch feature selection
                            arcpy.SelectLayerByAttribute_management(FCLyr, "CLEAR_SELECTION", "")
                            arcpy.SelectLayerByAttribute_management(insertFCLyr, "NEW_SELECTION", whereclause)
                            print("Successfully inserted {} {}{} into {}".format(len(oid_list), 'feature' if '<geoprocessing Layer object' in strInsertFC else 'table row', '' if len(selection_set.split(';')) == 1 else 's', insertFCLyr.name))
                            arcpy.AddMessage("Successfully inserted {} {}{} into {}".format(len(oid_list), 'feature' if '<geoprocessing Layer object' in strInsertFC else 'table row', '' if len(selection_set.split(';')) == 1 else 's', insertFCLyr.name))
                    else:
                        print("Input and Output have no matching fields")
                        arcpy.AddMessage("Input and Output have no matching fields")
                else:
                    print("There are no features selected")
                    arcpy.AddMessage("There are no features selected")


                     
            # report if a layer/table cannot be found
            if FCLyr == None:
                print("There is no layer or table named '{}' in the map".format(FC))
                arcpy.AddMessage("There is no layer or table named '" + FC + "'")
            if insertFCLyr == None:
                print("There is no layer or table named '{}' in the map".format(insertFC))
                arcpy.AddMessage("There is no layer or table named '{}' in the map".format(insertFC))


            arcpy.RefreshActiveView()                             
            return
        except Exception as e:     
            # If an error occurred, print line number and error message     
            import traceback, sys     
            tb = sys.exc_info()[2]     
            print("Line %i" % tb.tb_lineno)
            arcpy.AddMessage("Line %i" % tb.tb_lineno)
            print(e.message)
            arcpy.AddMessage(e.message)

Several discussions in the old forum referenced the Concave Hull Estimator script tool by esri's Bruce Harold, but during the conversion and website migrations all of the links to that tool were broken.  I had made an enhanced version of the tool which I have attached.  The bulk of the code is by Bruce, but the code that allows a user to select a case field was added by me.  The case field creates selection sets of points for processing based on the case field values without having to manually create each set and run the tool separately for each.

 

An example output of the tool is shown below (I don't recall the k factor I used).  All of the hulls were created using a case field number as shown.

ConcaveHull2Result.PNG

Anyway, while the real credit for this tool belongs to Bruce, his original post has been referenced recently on several websites with each commenting that it the link has been lost.  I wanted to revive the link to a version of that tool (with my own contribution thrown in).  Interestingly, this tool included the use of a dictionary before I understood anything about what dictionaries did.

 

Here are pictures I just processed on a set of address points for three Subdivisions. For comparison the original parcels are shown. The starting k factor for this tool run was set to 3, but the tool iterated each point set to at least a k factor of 6 before creating each polygon (a k factor of 9 was used for one of them). The tool created the new hull feature class and all 3 hulls in under 35 seconds.  The presence of somewhat regularly distributed points that fill the interior of the hull helps to create a more accurate hull outline than just using the set of points that ought to define the outline.

 

Hull 1 Original Parcels.png

Hull 2 Concave Hulls.png

Hull 3 Overlay of Hulls on Parcels.png

I have created a Python toolbox tool that converts the set of unique key values found in multiple fields that relate two layers/tables into a set of related sequential numbers in a CASE_ID field populated in both layers/tables.  This allows a user to create a standard join or relate between the two layers/tables on the CASE_ID field that is equivalent to creating a multi-field join or relate.

 

This tool was inspired by the ArcInfo Workstation's FREQUENCY command which could optionally add a numeric case field to the source and output that would maintain a single field relationship when the frequency was based on more than one case field.  That capability was lost in the Desktop Frequency tool.  However, the tool I have created is actually more flexible than what Workstation provided, since it can be applied to any pair of layers/tables, even when the Frequency tool had nothing to do with how they were created or related.

 

The two zipped python toolboxes attached are designed for ArcGIS 10.3 (Field Match Tools.pyt.zip) and ArcGIS 10.2 (Field Match Tools v10.2.pyt.zip) .  The toolboxes should be placed in the "%APPDATA%\ESRI\Desktop10.[3]\ArcToolbox\My Toolboxes" folder to show up in your My Toolbox under ArcCatalog (modify the items in brackets to fit your Desktop version).

 

I use python lists and itertools to get my sorted unique list of multiple field key values and to generate the sequential numbers associated with each key, but I convert the list into a dictionary prior to running the updateCursor so that I gain the speed or dictionary random access matching when I am writing the Case ID number values back to the data sources.  Dictionary key matching is much faster than trying to locate matching items in a list.  Here is the interface:

 

 

The validation works to make sure that the two input data sources are not the same and that the fields used in the case field list are actually in both sources.  The user can choose as many fields as they want to make up their unique multiple field case value keys.  The field names do not have to be the same in both data sources.  The position of the fields in the list will control the sort priority of the Case Fields (highest priority = top field) and the Sort Order column controls whether the values in each field are sorted in Ascending or Descending order.  The sort order of the Case field values controls the Case ID number sequencing.  The arrangement of the fields can be different from the field arrangement actually used in the sources.

 

There are three options for creating sequential numbers in the CASE_ID field output.  The first operates like a standard Join, where all unique key values in the Primary table are numbered, but only matching key values in the Secondary table are numbered.  All unmatched values in the Secondary table are given a CASE_ID of -1.  The second option is an union, where the sequential numbers are based on the complete set of key values in the combination of the Primary and Secondary layers/tables.  The third option is an intersection, where only key values found in both layers/tables receive positive sequential numbers.  All unmatched values of either table not found in the other table received a CASE_ID of -1.

 

The 10.3 version tool works the best and has the best features, However, I have provided a 10.2 version, but in order for the tool to work under the limitations of 10.2, the tool has fewer capabilities and a somewhat less intuitive interface.  The 10.2 version of the tool does not support controlling the sort order of the fields, so their sequential number values are always based on the use of an ascending order for the field values.  To match the fields in the two layers/tables you must click on fields in two lists, but those fields only appear in the separate field string text box when you click somewhere outside of the field lists.  The tool sidebar help provides more detail on how to use it.

 

Here is my code for the 10.3 version of the tool:

 

import arcpy


class Toolbox(object):
    def __init__(self):
        """Define the toolbox (the name of the toolbox is the name of the
        .pyt file)."""

        self.label = "Field Match Tools"
        self.alias = "FieldMatchTools"

        # List of tool classes associated with this toolbox
        self.tools = [MultiToSingleFieldKey, InsertSelectedFeaturesOrRows]


class MultiToSingleFieldKey(object):
    def __init__(self):
        """Define the tool (tool name is the name of the class)."""
        self.label = "Multiple Field Key To Single Field Key"
        self.description = ""
        self.canRunInBackground = False

    def getParameterInfo(self):
        """Define parameter definitions"""

        # First parameter
        param0 = arcpy.Parameter(
            displayName="Input Primary Table",
            name="in_prim_table",
            datatype="DETable",
            parameterType="Required",
            direction="Input")

        # Second parameter
        param1 = arcpy.Parameter(
            displayName="Input Secondary Table",
            name="in_sec_table",
            datatype="DETable",
            parameterType="Required",
            direction="Input")

        # Third parameter
        param2 = arcpy.Parameter(
            displayName="Case Fields",
            name="case_fields",
            datatype="GPValueTable",
            parameterType="Required",
            direction="Input")

        param2.columns = [['GPString', 'Primary Case Field'], ['GPString', 'Secondary Case Field'], ['GPString', 'Sort Order']]
        param2.filters[0].type="ValueList"
        param2.filters[0].list = ["X"]
        param2.filters[1].type="ValueList"
        param2.filters[1].list=["x"]
        param2.filters[2].type="ValueList"
        param2.filters[2].list=["Ascending", "Descending"]
        param2.parameterDependencies = [param0.name]

        # Fourth parameter
        param3 = arcpy.Parameter(
            displayName="Case ID Field Name",
            name="in_Case_ID_field",
            datatype="GPString",
            parameterType="Required",
            direction="Input")

        param3.value = "CASE_ID"

        # Fifth parameter
        param4 = arcpy.Parameter(
            displayName="The created unique Case ID numbers form this Primary/Secondary relationship:",
            name="case_key_combo_type",
            datatype="GPString",
            parameterType="Required",
            direction="Input")

        param4.filter.type = "valueList"
        param4.filter.list = ["Left Join","Full Join","Inner Join"]
        param4.value = "Left Join"

        newField = arcpy.Field()
        newField.name = param3.value
        newField.type = "LONG"
        newField.precision = 10
        newField.aliasName = param3.value
        newField.isNullable = "NULLABLE"

        # Sixth parameter
        param5 = arcpy.Parameter(
            displayName="Output Primary Table",
            name="out_prim_table",
            datatype="DETable",
            parameterType="Derived",
            direction="Output")

        param5.parameterDependencies = [param0.name]
        param5.schema.clone = True

        param5.schema.additionalFields = [newField]

        # Seventh parameter
        param6 = arcpy.Parameter(
            displayName="Output Secondary Table",
            name="out_sec_table",
            datatype="DETable",
            parameterType="Derived",
            direction="Output")

        param6.parameterDependencies = [param1.name]
        param6.schema.clone = True

        param6.schema.additionalFields = [newField]

        # Eighth parameter
        param7 = arcpy.Parameter(
            displayName="String comparisons are Case:",
            name="case_sensitive_combo_type",
            datatype="GPString",
            parameterType="Required",
            direction="Input")

        param7.filter.type = "valueList"
        param7.filter.list = ["Insensitive","Sensitive"]
        param7.value = "Insensitive"

        # Ninth parameter
        param8 = arcpy.Parameter(
            displayName="String ends trimmed of whitespace:",
            name="whitespace_combo_type",
            datatype="GPString",
            parameterType="Required",
            direction="Input")

        param8.filter.type = "valueList"
        param8.filter.list = ["Both", "Left", "Right", "None"]
        param8.value = "Both"

        params = [param0, param1, param2, param3, param4, param5, param6, param7, param8]

        return params

    def isLicensed(self):
        """Set whether tool is licensed to execute."""
        return True

    def updateParameters(self, parameters):
        """Modify the values and properties of parameters before internal
        validation is performed.  This method is called whenever a parameter
        has been changed."""


        if parameters[0].value:
            # Return primary table 
            tbl = parameters[0].value 
            desc = arcpy.Describe(tbl) 
            fields = desc.fields 
            l=[] 
            for f in fields: 
                if f.type in ["String", "Text", "Short", "Long", "Float", "Single", "Double", "Integer","OID", "GUID"]: 
                    l.append(f.name) 
            parameters[2].filters[0].list =
        if parameters[1].value: 
            # Return secondary table 
            tbl = parameters[1].value 
            desc = arcpy.Describe(tbl) 
            fields = desc.fields 
            l=[] 
            for f in fields: 
                if f.type in ["String", "Text", "Short", "Long", "Float", "Single", "Double", "Integer","OID", "GUID"]: 
                    l.append(f.name) 
            parameters[2].filters[1].list =
        if parameters[2].value != None:
            mylist = parameters[2].value
            for i, e in list(enumerate(mylist)):
                if mylist[i][2] != "Descending":
                    mylist[i][2] = "Ascending"
            parameters[2].value = mylist
        return

    def updateMessages(self, parameters):
        """Modify the messages created by internal validation for each tool
        parameter.  This method is called after internal validation."""

        if parameters[3].value and parameters[0].value and parameters[1].value:
            desc = arcpy.Describe(parameters[0].value) 
            fields = desc.fields
            in_primary = False
            is_primary_error = False
            is_primary_uneditable = False
            for f in fields:
                if parameters[3].value.upper() == f.name.upper():
                    in_primary = True
                    if f.type != "Integer":
                        is_primary_error = True
                    elif not f.editable:
                        is_primary_uneditable = False
            desc2 = arcpy.Describe(parameters[1].value) 
            fields2 = desc2.fields
            in_secondary = False
            is_secondary_error = False
            is_secondary_uneditable = False
            for f2 in fields2:
                if parameters[3].value.upper() == f2.name.upper():
                    in_secondary = True
                    if f2.type != "Integer":
                        is_secondary_error = True
                    elif not f2.editable:
                        is_secondary_uneditable = False
            newField = arcpy.Field()
            newField.name = parameters[3].value
            newField.type = "LONG"
            newField.precision = 10
            newField.aliasName = parameters[3].value
            newField.isNullable = "NULLABLE"
            fields1 = []
            fields2 = []
            order = []
            for item in parameters[2].value:
                fields1.append(item[0].upper())
                fields2.append(item[1].upper())
                order.append(item[2])
            if str(parameters[0].value).upper() == str(parameters[1].value).upper():
                parameters[1].setErrorMessage("The Input Secondary Table {0} cannot be the same as the Input Primary Table {1} ".format(parameters[1].value, parameters[0].value))
            else:
                parameters[1].clearMessage()
            if in_primary and in_secondary:
                if is_primary_error and is_secondary_error:
                    parameters[3].setErrorMessage("{0} exists and is not a Long Integer field in both the Input Primary and Secondary Tables".format(parameters[3].value.upper()))
                elif is_primary_error:
                    parameters[3].setErrorMessage("{0} exists and is not a Long Integer field in the Input Primary Table".format(parameters[3].value.upper()))
                elif is_secondary_error:
                    parameters[3].setErrorMessage("{0} exists and is not a Long Integer field in the Input Secondary Table".format(parameters[3].value.upper()))
                elif parameters[3].value.upper() in fields1 and parameters[3].value.upper() in fields2:
                    parameters[3].setErrorMessage("{0} is used as a Case Field for both the Input Primary and Secondary Tables".format(parameters[3].value.upper()))
                elif parameters[3].value.upper() in fields1:
                    parameters[3].setErrorMessage("{0} is used as a Case Field for the Input Primary Table".format(parameters[3].value.upper()))
                elif parameters[3].value.upper() in fields2:
                    parameters[3].setErrorMessage("{0} is used as a Case Field for the Input Secondary Table".format(parameters[3].value.upper()))
                elif is_primary_uneditable and is_secondary_uneditable:
                    parameters[3].setErrorMessage("{0} exists and is not editable in both the Input Primary and Secondary Tables".format(parameters[3].value.upper()))
                elif is_primary_uneditable:
                    parameters[3].setErrorMessage("{0} exists and is not editable in the Input Primary Table".format(parameters[3].value.upper()))
                elif is_secondary_uneditable:
                    parameters[3].setErrorMessage("{0} exists and is not editable in the Input Secondary Table".format(parameters[3].value.upper()))
                else:
                    parameters[3].setWarningMessage("{0} will be overwritten in both the Input Primary and Secondary Tables".format(parameters[3].value.upper()))
            elif in_primary:
                parameters[6].schema.additionalFields = [newField]
                if is_primary_error:
                    parameters[3].setErrorMessage("{0} exists and is not a Long Integer field in the Input Primary Table".format(parameters[3].value.upper()))
                elif is_primary_uneditable:
                    parameters[3].setErrorMessage("{0} exists and is not editable in the Input Primary Table".format(parameters[3].value.upper()))
                else:
                    parameters[3].setWarningMessage("{0} will be overwritten in the Input Primary Table".format(parameters[3].value.upper()))
            elif in_secondary:
                parameters[5].schema.additionalFields = [newField]
                if is_secondary_error:
                    parameters[3].setErrorMessage("{0} exists and is not a Long Integer field in the Input Secondary Table".format(parameters[3].value.upper()))
                elif is_secondary_uneditable:
                    parameters[3].setErrorMessage("{0} exists and is not editable in the Input Secondary Table".format(parameters[3].value.upper()))
                else:
                    parameters[3].setWarningMessage("{0} will be overwritten in the Input Secondary Table".format(parameters[3].value.upper()))
            else:
                parameters[5].schema.additionalFields = [newField]
                parameters[6].schema.additionalFields = [newField]
                parameters[3].clearMessage()
        return

    def stringCaseTrim(self, parameters, value):
        tempstr = None
        if parameters[7].value.upper() == 'Sensitive'.upper():
            tempstr = value
        else:
            tempstr = value.upper()
        if parameters[8].value.upper() == 'None'.upper():
            return tempstr
        if parameters[8].value.upper() == 'Left'.upper():
            return tempstr.lstrip()
        if parameters[8].value.upper() == 'Right'.upper():
            return tempstr.rstrip()
        else:
            return tempstr.strip()

    def execute(self, parameters, messages):
        """The source code of the tool."""
        try:
            desc = arcpy.Describe(parameters[0].value) 
            fields = desc.fields
            in_primary = False
            for f in fields:
                if parameters[3].value.upper() == f.name.upper():
                    in_primary = True
            if not in_primary:
                arcpy.AddField_management(parameters[0].value, parameters[3].value.upper(), "Long", 10)
                arcpy.AddMessage("Added a Case ID field to the Input Primary Table") 
            desc2 = arcpy.Describe(parameters[1].value) 
            fields2 = desc2.fields
            in_secondary = False
            for f2 in fields2:
                if parameters[3].value.upper() == f2.name.upper():
                    in_secondary = True
            if not in_secondary:
                arcpy.AddField_management(parameters[1].value, parameters[3].value.upper(), "Long", 10)
                arcpy.AddMessage("Added a Case ID field to the Input Secondary Table") 
            tbl1 = parameters[0].value
            tbl2 = parameters[1].value
            fields1 = []
            fields2 = []
            order = []
            for item in parameters[2].value:
               fields1.append(item[0])
               fields2.append(item[1])
               order.append(item[2])
            arcpy.AddMessage("Primary Case Fields are {0}".format(str(fields1)))
            arcpy.AddMessage("Secondary Case Fields are {0}".format(str(fields2)))
            arcpy.AddMessage("Sort Orders are {0}".format(str(order)))
            import itertools
            k = []
            arcpy.AddMessage("Strings Comparisons Are {0}".format(parameters[7].value))
            k = list(tuple([self.stringCaseTrim(parameters, value) if str(type(value)) in ("<class 'str'>", "<type 'unicode'>") else value for value in r]) for r in arcpy.da.SearchCursor(tbl1, fields1))
            arcpy.AddMessage("Case Values have been read from the Input Primary Table")
            if parameters[4].value == "Full Join":
                j = []
                j = list(tuple([self.stringCaseTrim(parameters, value) if str(type(value)) in ("<class 'str'>", "<type 'unicode'>") else value for value in r]) for r in arcpy.da.SearchCursor(tbl2, fields2))
                k = k + j
                j = None
                arcpy.AddMessage("Case Values have been appended from the Input Secondary Table")
            from operator import itemgetter 
            for i, e in reversed(list(enumerate(order))):
                if order[i] == "Descending":
                    k.sort(key=itemgetter(i), reverse=True)
                else:
                    k.sort(key=itemgetter(i))
            k = list(k for k,_ in itertools.groupby(k))
            if parameters[4].value == "Inner Join":
                j = list(tuple([self.stringCaseTrim(parameters, value) if str(type(value)) in ("<class 'str'>", "<type 'unicode'>") else value for value in r]) for r in arcpy.da.SearchCursor(tbl2, fields2))
                arcpy.AddMessage("Case Values have been read from the Input Secondary Table")
                l = []
                for item in k:
                    if tuple(item) in j:
                        l.append(item)
                j = None
                k = l
                l = None
                arcpy.AddMessage("Case Values have been matched to the Input Secondary Table")
            arcpy.AddMessage("A list of sorted and unique Case Values has been created")
            dict = {}
            fields1.append(parameters[3].value)
            fields2.append(parameters[3].value)
            for i in xrange(len(k)):
                dict[tuple(k[i])] = i + 1
            k = None
            arcpy.AddMessage("A dictionary of unique Case Value keys with Case ID number values has been created")
            with arcpy.da.UpdateCursor(tbl1, fields1) as cursor:
                for row in cursor:
                    caseinsensitive = tuple([self.stringCaseTrim(parameters, value) if str(type(value)) in ("<class 'str'>", "<type 'unicode'>") else value for value in row[0:len(fields2)-1]])
                    if caseinsensitive in dict:
                        row[len(fields1)-1] = dict[caseinsensitive]
                    else:
                        row[len(fields2)-1] = -1
                    cursor.updateRow(row)
            del cursor
            arcpy.AddMessage("{0} values have been updated for Input Primary Table".format(parameters[3].value))
            with arcpy.da.UpdateCursor(tbl2, fields2) as cursor2:
                for row2 in cursor2:
                    caseinsensitive = tuple([self.stringCaseTrim(parameters, value) if str(type(value)) in ("<class 'str'>", "<type 'unicode'>") else value for value in row2[0:len(fields2)-1]])
                    if caseinsensitive in dict:
                        row2[len(fields2)-1] = dict[caseinsensitive]
                    else:
                        row2[len(fields2)-1] = -1
                    cursor2.updateRow(row2)
            del cursor2
            arcpy.AddMessage("{0} values have been updated for Input Secondary Table".format(parameters[3].value))
        except Exception as e: 
            messages.addErrorMessage(e.message)

        return



class InsertSelectedFeaturesOrRows(object):
    def __init__(self):
        """Define the tool (tool name is the name of the class)."""
        self.label = "Insert Selected Features or Rows"
        self.description = ""
        self.canRunInBackground = False

    def getParameterInfo(self):
        """Define parameter definitions"""
        # First parameter
        param0 = arcpy.Parameter(
            displayName="Source Layer or Table View",
            name="source_layer_or_table_view",
            datatype="GPTableView",
            parameterType="Required",
            direction="Input")

        # Second parameter
        param1 = arcpy.Parameter(
            displayName="Target Layer or Table View",
            name="target_layer_or_table_view",
            datatype="GPTableView",
            parameterType="Required",
            direction="Input")

        # Third parameter
        param2 = arcpy.Parameter(
            displayName="Number of Copies to Insert",
            name="number_of_copies_to_insert",
            datatype="GPLong",
            parameterType="Required",
            direction="Input")

        param2.value = 1       

        # Fourth parameter
        param3 = arcpy.Parameter(
            displayName="Derived Layer or Table View",
            name="derived_table",
            datatype="GPTableView",
            parameterType="Derived",
            direction="Output")

        param3.parameterDependencies = [param1.name]
        param3.schema.clone = True

        params = [param0, param1, param2, param3]

        return params

    def isLicensed(self):
        """Set whether tool is licensed to execute."""
        return True

    def updateParameters(self, parameters):
        """Modify the values and properties of parameters before internal
        validation is performed.  This method is called whenever a parameter
        has been changed."""

        return

    def updateMessages(self, parameters):
        """Modify the messages created by internal validation for each tool
        parameter.  This method is called after internal validation."""

        if parameters[1].value:
            insertFC = parameters[1].value
            strInsertFC = str(insertFC)
            if parameters[0].value and '<geoprocessing Layer object' in strInsertFC:
                FC = parameters[0].value
                strFC = str(FC)
                if not '<geoprocessing Layer object' in strFC:
                    print("Input FC must be a layer if output is a layer")
                    parameters[0].setErrorMessage("Input must be a feature layer if the Output is a feature layer!")
                else:
                    dscFCLyr = arcpy.Describe(FC)
                    dscinsertFCLyr = arcpy.Describe(insertFC)
                    # add the SHAPE@ field if the shapetypes match
                    if dscFCLyr.featureclass.shapetype != dscinsertFCLyr.featureclass.shapetype:
                        print("Input and Output have different geometry types!  Geometry must match!")
                        parameters[0].setErrorMessage("Input and Output do not have the same geometry")
                   
                    if dscFCLyr.featureclass.spatialReference.name != dscinsertFCLyr.featureclass.spatialReference.name:
                        print("Input and Output have different Spatial References!  Spatial References must match!")
                        parameters[0].setErrorMessage("Input and Output do not have the same Spatial References!  Spatial References must match!")
        if parameters[2].value <= 0:
            parameters[2].setErrorMessage("The Number of Row Copies must be 1 or greater")
        return

    def execute(self, parameters, messages):
        """The source code of the tool."""
        try:
            mxd = arcpy.mapping.MapDocument(r"CURRENT")
            df = arcpy.mapping.ListDataFrames(mxd)[0]

            FC = parameters[0].value
            insertFC = parameters[1].value

            strFC = str(FC)
            strInsertFC = str(insertFC)

            FCLyr = None
            insertFCLyr = None

            for lyr in arcpy.mapping.ListLayers(mxd, "", df):
                # Try to match to Layer
                if '<geoprocessing Layer object' in strFC:
                    if lyr.name.upper() == FC.name.upper():
                        FCLyr = lyr
                if '<geoprocessing Layer object' in strInsertFC:
                    if lyr.name.upper() == insertFC.name.upper():
                        insertFCLyr = lyr
            if FCLyr == None or insertFCLyr == None:
                # Try to match to table if no layer found
                if FCLyr == None:
                    tables = arcpy.mapping.ListTableViews(mxd, "", df)
                    for table in tables:
                        if table.name.upper() == strFC.upper():
                            FCLyr = table
                            break
                if insertFCLyr == None:
                    tables = arcpy.mapping.ListTableViews(mxd, "", df)
                    for table in tables:
                        if table.name.upper() == strInsertFC.upper():
                            insertFCLyr = table
                            break

            # If both layers/tables are found then process fields and insert cursor
            if FCLyr != None and insertFCLyr != None:
                dsc = arcpy.Describe(FCLyr)        
                      
                selection_set = dsc.FIDSet

                # only process layers/tables if there is a selection in the FCLyr
                if len(selection_set) > 0:
                    print("{} has {} {}{} selected".format(FCLyr.name, len(selection_set.split(';')), 'feature' if '<geoprocessing Layer object' in strInsertFC else 'table row', '' if len(selection_set.split(';')) == 1 else 's'))
                    arcpy.AddMessage("{} has {} {}{} selected".format(FCLyr.name, len(selection_set.split(';')), 'feature' if '<geoprocessing Layer object' in strInsertFC else 'table row', '' if len(selection_set.split(';')) == 1 else 's'))
                   
                    FCfields = arcpy.ListFields(FCLyr)
                    insertFCfields = arcpy.ListFields(insertFCLyr)

                    # Create a field list of fields you want to manipulate and not just copy   
                    # All of these fields must be in the insertFC   
                    manualFields =  []
                    matchedFields = []
                    for manualField in manualFields:
                        matchedFields.append(manualField.upper())
                    for FCfield in FCfields:
                        for insertFCfield in insertFCfields:
                            if (FCfield.name.upper() == insertFCfield.name.upper() and
                                FCfield.type == insertFCfield.type and
                                FCfield.type <> 'Geometry' and
                                insertFCfield.editable == True and
                                not (FCfield.name.upper() in matchedFields)):   

                                matchedFields.append(FCfield.name)   
                                break
                            elif (FCfield.type == 'Geometry' and
                                  FCfield.type == insertFCfield.type):

                                matchedFields.append("SHAPE@")
                                break
                            elif insertFCfield.type == "OID":
                                oid_name = insertFCfield.name
                  
                    if len(matchedFields) > 0:
                        # Print the matched fields list
                        print("The matched fields are: {}".format(matchedFields))
                        arcpy.AddMessage("The matched fields are: {}".format(matchedFields))

                        copies = parameters[2].value
                        print("Making {} {} of each {}".format(copies, 'copy' if copies == 1 else 'copies', 'feature' if '<geoprocessing Layer object' in strInsertFC else 'table row'))
                        arcpy.AddMessage("Making {} {} of each {}".format(copies, 'copy' if copies == 1 else 'copies', 'feature' if '<geoprocessing Layer object' in strInsertFC else 'table row'))

                        oid_list = []
                        # arcpy.AddMessage(oid_name)
                        dscInsert = arcpy.Describe(insertFCLyr)
                        if '<geoprocessing Layer object' in strInsertFC:
                            oid_name = arcpy.AddFieldDelimiters(dscInsert.dataElement, oid_name)
                        else:
                            oid_name = arcpy.AddFieldDelimiters(dscInsert, oid_name)
                        rowInserter = arcpy.da.InsertCursor(insertFCLyr, matchedFields)
                        print("The output workspace is {}".format(insertFCLyr.workspacePath))
                        arcpy.AddMessage("The output workspace is {}".format(insertFCLyr.workspacePath))
                        if '<geoprocessing Layer object' in strInsertFC:
                            versioned = dscInsert.featureclass.isVersioned
                        else:
                            versioned = dscInsert.table.isVersioned
                       
                        if versioned:
                            print("The output workspace is versioned")
                            arcpy.AddMessage("The output workspace is versioned")
                            with arcpy.da.Editor(insertFCLyr.workspacePath) as edit:
                                with arcpy.da.SearchCursor(FCLyr, matchedFields) as rows:      
                                    for row in rows:      
                                        for i in range(copies):
                                            oid_list.append(rowInserter.insertRow(row))
                        else:
                            print("The output workspace is not versioned")
                            arcpy.AddMessage("The output workspace is not versioned")
                            with arcpy.da.SearchCursor(FCLyr, matchedFields) as rows:      
                                for row in rows:      
                                    for i in range(copies):
                                        oid_list.append(rowInserter.insertRow(row))
                        del row      
                        del rows      
                        del rowInserter 
                        if len(oid_list) == 1:
                            whereclause = oid_name + ' = ' + str(oid_list[0])
                        elif len(oid_list) > 1:
                            whereclause = oid_name + ' IN (' + ','.join(map(str, oid_list)) + ')'
                        if len(oid_list) > 0:
                            # arcpy.AddMessage(whereclause)
                            # Switch feature selection
                            arcpy.SelectLayerByAttribute_management(FCLyr, "CLEAR_SELECTION", "")
                            arcpy.SelectLayerByAttribute_management(insertFCLyr, "NEW_SELECTION", whereclause)
                            print("Successfully inserted {} {}{} into {}".format(len(oid_list), 'feature' if '<geoprocessing Layer object' in strInsertFC else 'table row', '' if len(selection_set.split(';')) == 1 else 's', insertFCLyr.name))
                            arcpy.AddMessage("Successfully inserted {} {}{} into {}".format(len(oid_list), 'feature' if '<geoprocessing Layer object' in strInsertFC else 'table row', '' if len(selection_set.split(';')) == 1 else 's', insertFCLyr.name))
                    else:
                        print("Input and Output have no matching fields")
                        arcpy.AddMessage("Input and Output have no matching fields")
                else:
                    print("There are no features selected")
                    arcpy.AddMessage("There are no features selected")

                    
            # report if a layer/table cannot be found
            if FCLyr == None:
                print("There is no layer or table named '{}' in the map".format(FC))
                arcpy.AddMessage("There is no layer or table named '" + FC + "'")
            if insertFCLyr == None:
                print("There is no layer or table named '{}' in the map".format(insertFC))
                arcpy.AddMessage("There is no layer or table named '{}' in the map".format(insertFC))

            arcpy.RefreshActiveView()                            
            return
        except Exception as e:    
            # If an error occurred, print line number and error message    
            import traceback, sys    
            tb = sys.exc_info()[2]    
            print("Line %i" % tb.tb_lineno)
            arcpy.AddMessage("Line %i" % tb.tb_lineno)
            print(e.message)
            arcpy.AddMessage(e.message)

I am gathering some of the posts and code I have written over the years that I regularly want to reference and reposting them under my Blog to hopefully make them easier for me to find.  I generally have difficulty finding these posts, because they were responses to questions by other users and the original post title they can be found under often is poorly related to the post I wrote.

 

Here is how to calculate the MMonotonicity of a feature.  MMonotonicity describes the trend of the M values as you traverse the polyline's vertices.  This is seen in the Identify Route Locations tool in the Measure Values section, but is more useful as a field value to allow you to select routes where measures do not continuously increase.  Anything other than a 1 (Strictly Increasing) indicates a complex route design that may cause problems.

 

The MMonotonicy values and their domain translations are:

 

1 = Strictly Increasing
2 = All Measures are Level
3 = Increasing with Levels
4 = Strictly Decreasing
5 = Increasing and Decreasing
6 = Decreasing with Levels
7 = Increasing and Decreasing with Levels
8 = All Measures are NaN
9 = Increasing with NaN
10 = Measures with Levels and NaN only
11 = Increasing with Levels and NaN
12 = Decreasing with NaN
13 = Increasing and Decreasing with NaN
14 = Decreasing with Levels and NaN
15 = Increasing and Decreasing with Levels and NaN

 


Here is the calculation:

 

Parser:  Python


Show Codeblock:  Checked


Pre-Logic Script Code:

import numpy  
def MMonotonicity(feat):  
 partnum = 0  
  
 # Count the number of points in the current multipart feature  
 partcount = feat.partCount  
 pntcount = 0  
 mmonotonicity = 0  
 lastM = -100000000  
  
 # Enter while loop for each part in the feature (if a singlepart feature  
 # this will occur only once)  
 #  
 while partnum < partcount:  
  part = feat.getPart(partnum)  
  pnt = part.next()  
  
  # Enter while loop for each vertex  
  #  
  while pnt:  
   pntcount += 1  
   if lastM < pnt.M and lastM != -100000000:  
    mmonotonicity = mmonotonicity | 1  
   if lastM == pnt.M and lastM != -100000000:  
    mmonotonicity = mmonotonicity | 2  
   if lastM > pnt.M and lastM != -100000000:  
    mmonotonicity = mmonotonicity | 4  
   if numpy.isnan(pnt.M):  
    mmonotonicity = mmonotonicity | 8  
   lastM = pnt.M  
   pnt = part.next()  
     
   # If pnt is null, either the part is finished or there is an   
   # interior ring  
   #  
   if not pnt:   
    pnt = part.next()  
  partnum += 1  
  return mmonotonicity

 

Expression: MMonotonicity( !Shape!)

Performance Issue of Past Solutions

 

The subject of creating labels that include data from related feature classes/tables in a One-To-Many or Many-To-Many relationship has come up many times over the years, and while there have been a few solutions proposed, all have suffered from poor performance.  However, I have discovered a way to overcome the performance issues after further experimentation with the techniques I described in my Turbo Charging Data Manipulation with Python Cursors and Dictionaries.

 

Previous solutions were slow, because they kept processing queries for each label being generated by building an SQL expression from the relate value of each feature being labeled to return the set of related records one label at a time from the related feature class/table.  This is an extremely inefficient and slow way to process look-ups against a relate.

 

Solving Performance Issues by Using Global Dictionaries for Related Data

 

Dictionaries are the perfect solution for handling relate look-ups.  The reason is that the insert/delete/look-up time of items in the dictionary is amortized constant time - O(1) - which means no matter how big the dictionary gets, the time it takes to find something remains relatively constant. This is highly desirable for high-speed look-ups.  Therefore it is much more efficient to read an entire related feature class/table into a dictionary and process the related value of each label against a dictionary key value than it is to repeatedly process SQL queries against the related feature class/table for each label relationship.

 

However, a dictionary would also be no good if the entire related feature class/table had to be reloaded into the dictionary as each label was being processed.  Fortunately there is no need to do that, and the entire related feature class/table can be loaded into the dictionary once when the first label is created.  To do that the dictionary is created as a global variable of the label function that is only loaded by the first label.  All other labels just check to see if the dictionary has already been loaded.  If it has, the related feature class/table will not be queried again.  As long as the labels are being generated, all subsequent labels will just use the already loaded global dictionary to get the related feature class/table data.

 

Each time the map is refreshed, the global dictionary is rebuilt for just the first label again and then that dictionary is used until all of the labels are refreshed.  Therefore, edits to the related feature class/table will be reflected each time the labels are refreshed.  Editing a related feature class will cause the map to refresh as edits are posted.  However, editing a related standalone table will not cause the map to automatically refresh the labels as each edit is posted.  You will have to manually refresh the map to see the standalone table updates.  However, I consider that a good thing, since waiting for map refreshes after editing one record at a time is very time consuming and there is no need to refresh the labels at all if the related standalone table is only edited in fields that do not participate in the label expression.

 

Example 1:  The Fundamental Code to Create Labels Using this Technique

 

The labels shown in this picture were created by the code below.  The related intersection event table used to create these labels contains over 130,000 records, all of which are read into the dictionary when the first label is processed.  These labels took less than 9 seconds to draw.

Labels_with_1_To_M_Relate.png

 

To use the code below I went to the Label tab of the Routes and checked the Label check box.  Then I pressed the Expression button for the labels, I changed the Label Parser from VBScript to Python.  After checking the Advanced expression option, I placed the code below in the expression editor.  This expression creates a label for my layer of linear referenced Routes shown above and includes data from a related table of intersection linear reference events.  It lists the Route ID (ROUTE_NAME) in the header of the label in 12 point Bold font.  Under that I show a summary count value of the number of intersection cross street names contained in the related table in 10 point Bold font.  Then I list the related cross street names and their event measure value for each intersection in the regular 8 point font.  The list of cross streets is sorted as a list based on the measures so that they appear in driving order going in the direction of the route's measure orientation.

 

# Initialize a global dictionary for a related feature class/table
relateDict = {}
def FindLabel ( [ROUTE_NAME] ):
  # declare the dictionary global so it can be built once and used for all labels
  global relateDict
  # only populate the dictionary if it has no keys
  if len(relateDict) == 0:
    # Provide the path to the relate feature class/table
    relateFC = r"C:\Users\OWNER\Documents\ArcGIS\Centerline_Edit.gdb\CL_INTERSECTIONS_PAIRS"
    # create a field list with the relate field first (ROUTE_NAME),
    # followed by sort field(s) (MEASURE), then label field(s) (CROSS_STREET)
    relateFieldsList = ["ROUTE_NAME", "MEASURE", "CROSS_STREET"]
    # process a da search cursor to transfer the data to the dictionary
    with arcpy.da.SearchCursor(relateFC, relateFieldsList) as relateRows:
      for relateRow in relateRows:
        # store the key value in a variable so the relate value
        # is only read from the row once, improving speed
        relateKey = relateRow[0]
        # if the relate key of the current row isn't found
        # create the key and make it's value a list of a list of field values
        if not relateKey in relateDict:
          # [searchRow[1:]] is a list containing
          # a list of the field values after the key.
          relateDict[relateKey] = [relateRow[1:]]
        else:
          # if the relate key is already in the dictionary
          # append the next list of field values to the
          # existing list associated with the key
          relateDict[relateKey].append(relateRow[1:])
    # delete the cursor, and row to make sure all locks release
    del relateRows, relateRow
  # store the current label feature's relate key field value
  # so that it is only read once, improving speed
  labelKey = [ROUTE_NAME]
  # start building a label expression.
  # My label has a bold key value header in a larger font
  expression = '<FNT name="Arial" size="12"><BOL>{}</BOL></FNT>'.format(labelKey)
  # determine if the label key is in the dictionary
  if labelKey in relateDict:
    # sort the list of the list of fields
    sortedList = sorted(relateDict[labelKey])
    # add a record count to the label header in bold regular font
    expression += '\n<FNT name="Arial" size="10"><BOL>Cross Street Count = {}</BOL></FNT>'.format(len(sortedList))
    # process the sorted list
    for fieldValues in sortedList:
       # append related data to the label expression
       # my label shows a list of related
       # cross streets and measures sorted in driving order
       expression += '\n{} - {:.4f}'.format(fieldValues[1], fieldValues[0])
    # clean up the list variables after completing the for loop
    del sortedList, fieldValues
  else:
    expression += '\n<FNT name="Arial" size="10"><BOL>Cross Street Count = 0</BOL></FNT>'
  # return the label expression to display
  return expression

 

Example 2: Adapting the Code to Produce Table Style Labels

 

The labels shown represent only one of the possible ways I could have summarized and/or listed the related feature class/table data.  A semi-tabular presentation is possible if I use a fixed-spaced font like Courier New.  A method for making a tabular style label was given in this post by Jennifer Horsman; however, her code used VBScript and used an inefficient search cursor algorithm.  Below I have adapted her code to use Python and the much more efficient dictionary algorithm shown above.

 

# Initialize a global dictionary for a related feature class/table
relateDict = {}
def FindLabel ( [ROUTE_NAME] ):
  # declare the dictionary global so it can be built once and used for all labels
  global relateDict
  # only populate the dictionary if it has no keys
  if len(relateDict) == 0:
    # Provide the path to the relate feature class/table
    relateFC = r"C:\Users\OWNER\Documents\ArcGIS\Centerline_Edit.gdb\CL_INTERSECTIONS_PAIRS"
    # create a field list with the relate field first (ROUTE_NAME),
    # followed by sort field(s) (MEASURE), then label field(s) (CROSS_STREET)
    relateFieldsList = ["ROUTE_NAME", "MEASURE", "CROSS_STREET"]
    # process a da search cursor to transfer the data to the dictionary
    with arcpy.da.SearchCursor(relateFC, relateFieldsList) as relateRows:
      for relateRow in relateRows:
        # store the key value in a variable so the relate value
        # is only read from the row once, improving speed
        relateKey = relateRow[0]
        # if the relate key of the current row isn't found
        # create the key and make it's value a list of a list of field values
        if not relateKey in relateDict:
          # [searchRow[1:]] is a list containing
          # a list of the field values after the key.
          relateDict[relateKey] = [relateRow[1:]]
        else:
          # if the relate key is already in the dictionary
          # append the next list of field values to the
          # existing list associated with the key
          relateDict[relateKey].append(relateRow[1:])
    # delete the cursor, and row to make sure all locks release
    del relateRows, relateRow
  # store the current label feature's relate key field value
  # so that it is only read once, improving speed
  labelKey = [ROUTE_NAME]


  # variables to adjust table cell sizes
  iMaxLbl1Sz = 0 
  iMaxLbl2Sz = 0 
  iSpace = 5 


  # determine if the label key is in the dictionary
  if labelKey in relateDict:
    # sort the list of the list of fields
    sortedList = sorted(relateDict[labelKey])
    # process the sorted list to determine cell spacing
    for fieldValues in sortedList:
      strLabel1 = fieldValues[1] 
      strLabel2 = '{:.4f}'.format(fieldValues[0]) 
      if (len(strLabel1) > iMaxLbl1Sz):
        iMaxLbl1Sz = len(strLabel1) 
      if (len(strLabel2) > iMaxLbl2Sz):
        iMaxLbl2Sz = len(strLabel2) 
    # clean up the fieldValues variable once the for loop is done
    del fieldValues


    # My label has a key value header followed by a record count
    expression = labelKey
    expression += '\n<UND>Cross Street Count = {}</UND>'.format(len(sortedList)) + '_' * (iMaxLbl1Sz + iMaxLbl2Sz + iSpace + 1 - len('Cross Street Count = {}'.format(len(sortedList))))
    # process the sorted list
    for fieldValues in sortedList:
      strLabel1 = fieldValues[1] 
      strLabel2 = '{:.4f}'.format(fieldValues[0]) 
      k1 = (iMaxLbl1Sz - len(strLabel1)) + 2
      k2 = iSpace + (iMaxLbl2Sz - len(strLabel2)) - 3
      # append related data to the label expression
      # my label shows a list of related
      # cross streets and measures sorted in driving order
      expression += '\n' + strLabel1 + "." * k1
      expression += "|"
      expression += "." * k2 + strLabel2 + "|"
    # clean up all list variables after completing the for loops
    del sortedList, fieldValues
  else:
    # My label has a key value header followed by a record count
    expression = labelKey
    expression += '\n<UND>Cross Street Count = 0</UND>'
  # return the label expression to display
  return expression

 

The code above results in this output:

 

Labels_with_1_To_M_Relate_Table.png

 

Example 3: Another Table Style Label Alternative

 

Here is an alternative table style layout.  The code below includes all of the code shown in the previous example through line 36, but replaces the code that began in line 37 in the code for example 2 as follows:.

 

  # variables to adjust table cell sizes  
  iMaxLbl1Sz = 0   
  iMaxLbl2Sz = 0   
  iSpace = 5   
 
 
  # determine if the label key is in the dictionary 
  if labelKey in relateDict: 
    # sort the list of the list of fields 
    sortedList = sorted(relateDict[labelKey]) 
    # process the sorted list to determine cell spacing 
    for fieldValues in sortedList: 
      strLabel1 = fieldValues[1]   
      strLabel2 = '{:.4f}'.format(fieldValues[0])   
      if (len(strLabel1) > iMaxLbl1Sz): 
        iMaxLbl1Sz = len(strLabel1)   
      if (len(strLabel2) > iMaxLbl2Sz): 
        iMaxLbl2Sz = len(strLabel2)   
    # clean up the fieldValues variable once the for loop is done 
    del fieldValues 
 
 
    # My label has a key value header followed by a record count 
    expression = "<CLR red='255' green='255' blue='255'>_</CLR>" + labelKey + "<CLR red='255' green='255' blue='255'>" +  '_' * (iMaxLbl1Sz + iMaxLbl2Sz + iSpace + 2 - len("_"+labelKey)) + "</CLR>"
    expression += "\n_<UND>Cross Street Count = {}</UND>".format(len(sortedList)) + '_' * (iMaxLbl1Sz + iMaxLbl2Sz + iSpace + 2 - len('_Cross Street Count = {}'.format(len(sortedList)))) 
    # process the sorted list 
    for fieldValues in sortedList: 
      strLabel1 = fieldValues[1]   
      strLabel2 = '{:.4f}'.format(fieldValues[0])   
      k1 = (iMaxLbl1Sz - len(strLabel1)) + 2 
      k2 = iSpace + (iMaxLbl2Sz - len(strLabel2)) - 3 
      # append related data to the label expression 
      # my label shows a list of related 
      # cross streets and measures sorted in driving order 
      expression += '\n_<UND>' + strLabel1 + "." * k1 
      expression += "|" 
      expression += "." * k2 + strLabel2 + "</UND>_" 
    # clean up all list variables after completing the for loops 
    del sortedList, fieldValues 
  else: 
    # My label has a key value header followed by a record count 
    expression = "<CLR red='255' green='255' blue='255'>_</CLR>" + labelKey  
    expression += '\n_<UND>Cross Street Count = 0</UND>' 
  # return the label expression to display 
  return expression

 

With some adjustments to the label style and using the Maplex Label Engine, the example 3 code variation can produce an output that looks like the example below:

LabelTableSytle2.png

 

Considering Other Possibilities Supported by this Technique

 

More complex relationship primary and foreign keys can also be handled by modifying the code above.  A dictionary works for almost anything where exact values shared between the parent feature class and related feature class/table can be looked-up, even when a join or relate is not possible in ArcMap.  For example, the dictionary key could be used to do look-ups based on a sub-string from a field or on many fields in the parent feature class and/or related feature class/table to create a multi-field key look-up without having to parse or concatenate the values of those fields into a new field in the original feature classes/tables.  Look-ups based on portions of dates or numeric calculations could also be done without creating fields to hold those values, as long as an exact match between the two sources can be made.

 

A limited set of spatial look-ups can also be handled by a dictionary, such as finding exact matches or duplicates of shapes or finding extracted coordinates that are shared by the two geometries (for example, a dictionary look-up of points can be done against the from or to end point coordinates of a line where the points overlap the line ends).  However, dictionaries cannot be used to speed up inexact value matching or near proximity spatial relationships.

 

Additionally, I could have used multiple global dictionaries to build labels from more than one relate look-up.  This is useful in situations where the parent feature class has several fields relating to more than one related feature class/table, or where multi-level feature class/table relationships exist (i.e., the parent feature class relates to a child feature class/table, and the child feature class/table relates to another child feature class/table through one or more of its fields).

 

Situations Where the Technique Shown Might Not Work and Possible Solutions

 

The primary reason that this technique may not work will occur when the related feature classes/tables are too large to fit in memory after being loaded into a dictionary.  In these cases, applying some sort of query filter on the related feature class/table based on the entire set of parent features in the current map extent would be required before loading it into the dictionary to keep the dictionary from becoming too large.  While it may be possible to do this with arcpy mapping code, I have not tried that to see if it works, but that is something that I may look into later.

 

Memory management of this code is also important to avoid memory leaks, since this code can bypass the normal memory management processes of the label engine.  For example, memory issues may occur after several label refreshes if variables used to process the lists of listed fields in for loops are not deleted after the loops complete and the list variables are no longer needed.

 

How I Configured the Labels Shown in the Examples

 

In case you like the label style layout shown in my screen shot above, here are the settings I used:

 

I used the Standard Label Engine.  The initial label symbol was set to be the Bullet Leader symbol from the predefined symbol list.  I modified the font from 10 point to 8 point.  For the labels in screen shot 1 I used Arial font and for the table style labels in screen shot 2 I used Courier New (or some other fixed space font).  The Placement Properties are set to Horizontal Placement.  I pressed the Symbol button, then I pressed the Edit Symbol button, then I chose the Advanced Text tab and pressed the Text Background Properties Button.  On the Text Background Properties dialog I changed the leader style to the angled line type associated with the fifth radio button.  I pressed the Symbol button under the Leader check box and changed the line and arrow symbol shown as a dot to red.  After returning to the Text Background Properties dialog, I checked the Border option for the background and then pressed the Symbol button under the Border check box to set the border fill and border line style to No Fill.  After pressing OK on all child dialogs, I set the horizontal alignment on the General tab on the first Symbol dialog to Left.

The Problems with Using the Field Calculator, Join and Summary Statistics tools

 

The field calculator is a great tool and easy to set up, but it has several limitations that can seriously slow down scripted routines when you need to do a lot of data manipulation with large data sets.  The most obvious limitation is that only one field can be calculated at a time.  This often means having to process every record in the feature class multiple times in order to update several fields.  If you need to do data transfers between feature classes using a join it is critical to index the join field in both feature classes to avoid calculations that take forever to process, and even then the speed is significantly slower when a join is involved.  Also, if you actually need to use multiple fields to create a join that correctly matches records between feature classes your only option is to create a new field in both feature classes that concatenates those field values to make a join that works.  Also, often before these joins can be created a summary table has to be created, which takes more time.

 

Fortunately if you have ArcGIS Desktop 10.1 or later there is an alternative that can speed scripts like this up dramatically and make data transfers between feature classes both flexible and incredibly fast.  The solution is to replace the field calculator with one or more data access cursors (da cursors) and joins and creating summary tables with one or more python dictionaries built using a da SearchCursor.

 

Using a da UpdateCursor to Replace a Field Calculation within a Single Feature Class:

 

Below is a simple script that will use a da UpdateCursor on a feature class to fill in a URL field with a hyperlink that is built using a value taken from another field.  The strftime method is used to print out the time when the script starts and the time when it finishes.  To adapt this script to your own data you only need to change the Feature Class path and name in line 7, the field names in the field list in line 9 and the URL string in line 14.  This should perform about 7 times faster than a similar field calculation.

 

from time import strftime

print "Start script: " + strftime("%Y-%m-%d %H:%M:%S")

import arcpy

updateFC = r"C:\Path\FeatureClass"

updateFieldsList =  ["URLField", "ValueField"] # Example fields that will build a URL based on another field

with arcpy.da.UpdateCursor(updateFC, updateFieldsList) as updateRows:
    for updateRow in updateRows:
        # updateRow[0] is the URL field and updateRow[1] is the value field
        updateRow[0] = r"http://pic.asrclkrec.com/view_ParcelMaps.aspx?ParcelNumber=" + str(updateRow[1])
        updateRows.updateRow(updateRow)

print "Finished APN_MAP field update: " + strftime("%Y-%m-%d %H:%M:%S")

 

Using a da UpdateCursor to Replace Several Field Calculations within a Single Feature Class:

 

Now lets say that we actually need to process 2 more field calculations to complete the update of this feature class.  One calculation needs to add two other fields together, and the other needs to convert the values in a field from capitalized values to title case values.  So if there are 100K records, then all of those records have to be calculated 3 times.

 

All of this can be handled in a single pass of one da UpdateCursor through the 100K records.  Because the records only have to changed once and the addition of more fields for the UpdateCursor to read or calculate takes very little additional time to process, the speed gain over 3 field calculations is much faster that the previous script.  As a result, the more field calculations you can replace with a single pass of a da UpdateCursor, the more dramatically the speed of the script will improve.

 

Below is the script that can do all three field updates (assume that there are no Null values in the fields being added together or changing case).  To use this script with your own data you still only need to change lines 7, 9, and 14 (optionally change the calculations in lines 16 and 18).

 

from time import strftime

print "Start script: " + strftime("%Y-%m-%d %H:%M:%S")

import arcpy

updateFC = r"C:\Path\FeatureClass"

updateFieldsList =  ["URLField", "ValueField", "SumField", "NumberField1", "NumberField2", "TitleCaseField"] # Example fields that will build a URL based on another field

with arcpy.da.UpdateCursor(updateFC, updateFieldsList) as updateRows:
    for updateRow in updateRows:
        # updateRow[0] is the URL field and updateRow[1] is the value field
        updateRow[0] = r"http://pic.asrclkrec.com/view_ParcelMaps.aspx?ParcelNumber=" + str(updateRow[1])
         # updateRow[2] is the Sumfield and updateRow[3] and updateRow[4] are the NumberField1 and NumberField2 fields. 
        updateRow[2] = updateRow[3] + updateRow[4]
         # updateRow[5] is the TitleCaseField that is having its Capitalized values replaced with Title Case values.
        updateRow[5] = updateRow[5].title()
        updateRows.updateRow(updateRow)

print "Finished APN_MAP field update: " + strftime("%Y-%m-%d %H:%M:%S")

 

Using a Python Dictionary Built using a da SearchCursor to Replace a Join Connecting Two Feature Classes:

 

Another common data manipulation involves joining one feature class to another in order to transfer values from the Joined feature class to the Target feature class using the Field Calculator.  However, features loaded into a Python Dictionary using a Search Cursor are much faster at matching join field values in one feature class to another than a Join.  Combined with using a da UpdateCursor to replace the Field Calculator, the speed of these kinds of data manipulations can be even more dramatic than data manipulations on a single feature class.

 

Example 1 - Transfer of a Single Field Value between Feature Classes

 

Below is the code required to transfer the value of one feature class to another based on a common join value in another field.  To adapt this script to your own data change lines 7 and 14 to the path and name of the source (Join) and update (target) Feature Class to match the feature classes you would normally join, change lines 9 and 16 to replace the field list with the name of the Join field and the name of the field being transferred.

 

from time import strftime

print "Start script: " + strftime("%Y-%m-%d %H:%M:%S")

import arcpy

sourceFC = r"C:\Path\SourceFeatureClass"

sourceFieldsList = ["JoinField", "ValueField"]

# Use list comprehension to build a dictionary from a da SearchCursor
valueDict = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(sourceFC, sourceFieldsList)}

updateFC = r"C:\Path\UpdateFeatureClass"

updateFieldsList = ["JoinField", "ValueField"]

with arcpy.da.UpdateCursor(updateFC, updateFieldsList) as updateRows:
    for updateRow in updateRows:
        # store the Join value of the row being updated in a keyValue variable
        keyValue = updateRow[0]
         # verify that the keyValue is in the Dictionary
        if keyValue in valueDict:
             # transfer the value stored under the keyValue from the dictionary to the updated field.
            updateRow[1] = valueDict[keyValue][0]
            updateRows.updateRow(updateRow)

del valueDict

print "Finished script: " + strftime("%Y-%m-%d %H:%M:%S")

 

Example 2 - Transfer of Multiple Field Values between Feature Classes where there is a 1:1 Match between Field Sets

 

Just like the Field Calculator the more fields being transferred from one feature class to the other, the more your script will speed up.  If the transfer is a simple field for field transfer then the transfer fields can be handled in a for loop.  Below is an example of a transfer of 5 matching fields between two feature classes.  To adapt this code to your data you should again change the feature classes in lines 7 and 14 and the field lists in lines 9 and 16.

 

from time import strftime

print "Start script: " + strftime("%Y-%m-%d %H:%M:%S")

import arcpy

sourceFC = r"C:\Path\SourceFeatureClass"

sourceFieldsList = ["JoinField", "ValueField1", "ValueField2", "ValueField3", "ValueField4", "ValueField5" ]

# Use list comprehension to build a dictionary from a da SearchCursor
valueDict = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(sourceFC, sourceFieldsList)}

updateFC = r"C:\Path\UpdateFeatureClass"

updateFieldsList = ["JoinField", "ValueField1", "ValueField2", "ValueField3", "ValueField4", "ValueField5"]

with arcpy.da.UpdateCursor(updateFC, updateFieldsList) as updateRows:
    for updateRow in updateRows:
        # store the Join value of the row being updated in a keyValue variable
        keyValue = updateRow[0]
        # verify that the keyValue is in the Dictionary
        if keyValue in valueDict:
            # transfer the values stored under the keyValue from the dictionary to the updated fields.
            for n in range (1,len(sourceFieldsList)):
                updateRow[n] = valueDict[keyValue][n-1]
            updateRows.updateRow(updateRow)

del valueDict

print "Finished script: " + strftime("%Y-%m-%d %H:%M:%S")
print "Finished script: " + strftime("%Y-%m-%d %H:%M:%S")

 

Example 3 - Transfer of Multiple Field Values between Feature Classes involving Complex Field Manipulations

 

If you need to do more complex field transfers that do more than a simple transfer of data from one table to another you can do this using code similar to the example field calculations that were done on a single table.  Here are the 3 field updates of my second field calculation example done between two separate tables with common join values instead of all within a single table.

 

from time import strftime

print "Start script: " + strftime("%Y-%m-%d %H:%M:%S")

import arcpy

sourceFC = r"C:\Path\SourceFeatureClass"

sourceFieldsList = ["JoinField", "ValueField", "NumberField1", "NumberField2", "CapitalizedField" ]

# Use list comprehension to build a dictionary from a da SearchCursor
valueDict = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(sourceFC, sourceFieldsList)}

updateFC = r"C:\Path\UpdateFeatureClass"

updateFieldsList = ["JoinField", "UrlField1", "SumField", "TitleCaseField"]

with arcpy.da.UpdateCursor(updateFC, updateFieldsList) as updateRows:
    for updateRow in updateRows:
        keyValue = updateRow[0]
        # verify that the keyValue is in the Dictionary
        if keyValue in valueDict:
             # updateRow[1] is the URL field and valueDict[keyValue][0] is the value field
             updateRow[1] = r"http://pic.asrclkrec.com/view_ParcelMaps.aspx?ParcelNumber=" + str(valueDict[keyValue][0])
             # updateRow[2] is the Sumfield and valueDict[keyValue][1] and valueDict[keyValue][2] are the NumberField1 and NumberField2 fields. 
             updateRow[2] = valueDict[keyValue][1] + valueDict[keyValue][2]
             # updateRow[3] is the TitleCaseField and valueDict[keyValue][3] is the CapitalizedField values.
             updateRow[3] = valueDict[keyValue][3].title()
             updateRows.updateRow(updateRow)

del valueDict

print "Finished script: " + strftime("%Y-%m-%d %H:%M:%S")

 

Creating a Multi-Field Python Dictionary Key to Replace a Concatenated Join Field:

 

Handling Multi-field join values that match records between two feature classes is also possible using dictionaries and cursors without having to do concatenation of separate fields into a new field.  The concatenation is still necessary, but it can be handled on the fly in memory, which is much faster than calculating a field.  Below shows an example of the single field transfer, but this time matching records using 3 separate fields to define the unique values between the feature classes used to match the records between the feature classes.

 

from time import strftime

print "Start script: " + strftime("%Y-%m-%d %H:%M:%S")

import arcpy

sourceFC = r"C:\Path\SourceFeatureClass"

sourceFieldsList = ["JoinField1", "JoinField2", "JoinField3", "ValueField"]

# Use list comprehension to build a dictionary from a da SearchCursor where the key values are based on 3 separate feilds
valueDict = {str(r[0]) + "," + str(r[1]) + "," + str(r[2]):(r[3:]) for r in arcpy.da.SearchCursor(sourceFC, sourceFieldsList)}

updateFC = r"C:\Path\UpdateFeatureClass"

updateFieldsList = ["JoinField1", "JoinField2", "JoinField3", "ValueField"]

with arcpy.da.UpdateCursor(updateFC, updateFieldsList) as updateRows:
    for updateRow in updateRows:
        # store the Join value by combining 3 field values of the row being updated in a keyValue variable
        keyValue = updateRow[0]+ "," + str(updateRow[1]) + "," + str(updateRow[2]
        # verify that the keyValue is in the Dictionary
        if keyValue in valueDict:
            # transfer the value stored under the keyValue from the dictionary to the updated field.
            updateRow[3] = valueDict[keyValue][0]
            updateRows.updateRow(updateRow)

del valueDict

print "Finished script: " +strftime("%Y-%m-%d %H:%M:%S")


 

Replacing Successive Joins with Simultaneous Processing of Several Python Dictionaries Built using da SearchCursors

 

When several joins have to be created and removed to do data manipulation between tables, the speed gains can be increased even more, since you can create several dictionaries and then process all of the join relationships simultaneously during a single pass of the update cursor on the updated feature class.  All source tables should be read into dictionaries before  processing the update cursor to achieve this benefit.  The more joins that you can replace this way, the more your script speed will improve.

 

Below is an example where two feature classes that would normally be joined to the updated feature class are replaced by dictionaries and the processed simultaneously by a single UpdateCursor operation.

 

from time import strftime

print "Start script: " + strftime("%Y-%m-%d %H:%M:%S")

import arcpy

sourceFC = r"C:\Path\SourceFeatureClass1"

sourceFieldsList = ["JoinField1", "ValueField", "NumberField1", "NumberField2" ]

# Use list comprehension to build a dictionary from a da SearchCursor
valueDict1 = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(sourceFC, sourceFieldsList)}

sourceFC = r"C:\Path\SourceFeatureClass2"

sourceFieldsList = ["JoinField2", "CapitalizedField" ]

# Use list comprehension to build a dictionary from a da SearchCursor
valueDict2 = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(sourceFC, sourceFieldsList)}

updateFC = r"C:\Path\UpdateFeatureClass"

updateFieldsList = ["JoinField1", "UrlField1", "SumField", "JoinField2", "TitleCaseField"]

with arcpy.da.UpdateCursor(updateFC, updateFieldsList) as updateRows:
    for updateRow in updateRows:
        keyValue1 = updateRow[0]
        # verify that the keyValue1 is in the First Dictionary
        if keyValue1 in valueDict1:
             # updateRow[1] is the URL field and valueDict1[keyValue1][0] is the value field
             updateRow[1] = r"http://pic.asrclkrec.com/view_ParcelMaps.aspx?ParcelNumber=" + str(valueDict1[keyValue1][0])
             # updateRow[2] is the Sumfield and valueDict1[keyValue1][1] and valueDict1[keyValue1][2] are the NumberField1 and NumberField2 fields. 
             updateRow[2] = valueDict1[keyValue1][1] + valueDict1[keyValue1][2]
        keyValue2 = updateRow[3]
        # verify that the keyValue2 is in the Second Dictionary
        if keyValue2 in valueDict2:
             # updateRow[4] is the TitleCaseField and valueDict2[keyValue2][0] is the CapitalizedField values.
             updateRow[4] = valueDict2[keyValue2][0].title()
        updateRows.updateRow(updateRow)

del valueDict1
del valueDict2

print "Finished script: " + strftime("%Y-%m-%d %H:%M:%S")

 

Using a Python Dictionary Built using a da SearchCursor to Replace a Summary Statistics Output Table

 

Another common need is to create a summary of a feature class for the unique values of a field or fields that cover multiple records.  The Python Dictionary can be used instead of a Summary Table output to accomplish this.  The benefit is that the output is stored in memory and is much faster to create than outputting an actual summary table.  Below is an example building a summary dictionary and using it to update the source table with sum values matching that field and create a percentage field in the source table for each record that built the summary.

 

from time import strftime

print "Start script: " + strftime("%Y-%m-%d %H:%M:%S")

import arcpy

sourceFC = r"C:\Path\UpdateFeatureClass"

sourceFieldsList = ["UniqueValuesField", "NumberField"]

# Build a summary dictionary from a da SearchCursor with unique key values of a field storing a list of the sum of that value and the record count.
valueDict = {}
with arcpy.da.SearchCursor(sourceFC, sourceFieldsList) as searchRows:
    for searchRow in searchRows:
        keyValue = searchRow[0]
        if not keyValue in valueDict:
             # assign a new keyValue entry to the dictionary storing a list of the first NumberField value and 1 for the first record counter value
            valueDict[keyValue] = [searchRow[1], 1]
        # Sum the last summary of NumberField value with the current record and increment the record count when keyvalue is already in the dictionary  
        else:
            valueDict[keyValue][0] += searchRow[1]
            valueDict[keyValue][1] += 1  

updateFC = r"C:\Path\UpdateFeatureClass"

updateFieldsList = ["UniqueValuesField", "NumberField", "PercentField", "NumberSumField", "MeanField"]

with arcpy.da.UpdateCursor(updateFC, updateFieldsList) as updateRows:
    for updateRow in updateRows:
        # store the Join value of the row being updated in a keyValue variable
        keyValue = updateRow[0]
        # verify that the keyValue is in the Dictionary
        if keyValue in valueDict:
            # divide the current record's NumberField value by the sum of the NumberField to get a percentage value
            updateRow[2] = updateRow[1] / valueDict[keyValue][0] 
            # transfer the sum of the NumberField stored under the keyValue from the dictionary.
            updateRow[3] = valueDict[keyValue][0]
            # divide the sum of the NumberField value by the record count of the NumberField to get a mean value
            updateRow[4] = valueDict[keyValue][0] / valueDict[keyValue][1]
            updateRows.updateRow(updateRow)

del valueDict

print "Finished script: " + strftime("%Y-%m-%d %H:%M:%S")

 

Conclusion

 

Once the patterns are understood it is possible to do virtually everything that you can do with the Field Calculator, a Join or a Summary table using an update cursor or a python dictionary.   Using these coding techniques I have been able to rewrite several scripts that did bulk Data Manipulations so that rather than taking more than an hour each to process, they now only take 3 to 10 minutes to process.  The more of these operations that you can replace using code like this, the greater the savings in processing time you will achieve.  The performance gains can be huge making it possible to keep data up to date without extensive waits or consuming large portions of your after hours batch processing time.  It also solves the problems that arise when you cannot alter the source data schema to add indexes or field concatenations and making copies of the data is not practical.

 

if you find any errors in the sample code, please let me know and I will post an update to this Blog to point out the correction. Also if you need more comments added to the code to help you understand what is going on, let me know and I will try to comment the code more fully.  These code examples are designed to work on shapefiles, file geodatabase feature classes, and unversioned SDE feature classes.  Additional code modifications are required to make them work with versioned SDE feature classes.

 

I want to express my thanks to Chris Synder who first demonstrated the usefulness of these techniques to me through the Forum and helped me understand how to apply them.

I have been able to assign address ranges to centerlines based on actual address points placed on houses.  I have written several posts on the ESRI User Forum, but found that my ideas evolved after writing each post as I continued working on the problem.  Unfortunately I could not edit posts after 24 hours had passed from the initial post time, so outdated and even incorrect information would become part of the thread that I could not revise, correct or update.  I wrote this paper in a Google Doc originally in an attempt to bring my documentation up to date and allow me to continue to develop better techniques and procedures that achieve the goal of creating address ranges on centerlines.

In the instructions below I suggest feature class and table names for the outputs of each process which are assumed in the SQL selections and field calculations.  These names are the suggested output base names and can be used as proposed if your jurisdiction is small enough to process all of the addresses at one time.  However, due to performance issues at some steps, I generally recommend processing only a maximum of 50,000 address points at one time and dividing a jurisdiction up if that limit would be exceeded.  My jurisdiction is too large to do all 650,000+ addresses at one time.  Therefore, in my example screen shots I am processing a single area plan, known as the Jurupa area plan, which is within the limit of the above criterion.  I create a separate file geodatabase for the processing outputs of each area, so that I will not have to rename the output files created using these methods or overwrite them or rename them when I process the addresses of a different area.

What Do You Need to Use this Approach?

In order to use this approach you will need:

1.       ArcMap 10.0 or above with a Basic (ArcView) License.  Where an Advanced license would let you more easily perform certain tasks the alternative approach with that license will be noted.

2.       You will need a Centerline polyline network covering your jurisdiction(s) of interest.  The Centerline Feature Class will need to include the following fields or their equivalent:

a.       Street Name fields.  It is recommended that you have fields for the Full Street Name and the Street Name Components, especially the Base Street Name (the primary street name excluding any pre- or post- compass directions or suffixes, like Ave, Blvd, Rd, St, Way, etc.)

b.      Address Range Fields.  In this paper these fields will be assumed to be named L_F_ADD (Left From Address), L_T_ADD (Left To Address), R_F_ADD (Right From Address), and R_T_ADD (Right To Address).  These fields can be text, long or double fields.

c.       A Route ID or Route Name field for a Linear Referencing Route is recommended, and required if you have never built a Linear Referencing Route network from your centerlines.  If your Route ID/Name is based on the Street Name it is recommended that the name component fields to build a Route Name would be included.  For example, I have a required Area_Plan_Abbreviation field and an optional Subroute field that hold values that are appended to the street name in the Route_Name field to make my Routes group by area and so that they form simple routes.

3.       You will need an Address Point layer covering the same area of interest covered by the Centerlines.  The Address Point Layer will at a minimum contain the following fields or their equivalent:

a.       Street Name fields, particularly the Base Street Name of the components of a full street name.  A Full Street Name field is optional.

b.      A House Number field.  This field should be numeric, but it can be text.

c.   A unique ID field for each address point that in a field other than the ObjectID.  A Long field that duplicates the ObjectID is fine.

4.       An optional Linear Referenced Route Network.  The network should be built from your centerlines.  It should be monotonic, meaning that there are no branching routes and that measures on the routes would increase with each successive vertex only, never decrease, or duplicate a prior measure.  If a route has not been created a method for creating one is included in this paper.

5.       I strongly recommend using a file geodatabase for this task over using shapefiles and/or dbf tables.  The field names generated will get much longer than the 10 character limit of a shapefile/dbf table during this exercise and field name truncation will not be used in any examples provided in this paper.  All SQL will be based on the assumption that you are using a file geodatabases.  Also, performance, precision and many other factors make a file geodatabase much better for this exercise than a set of shapefile and dbf tables.  Modifying outputs will be a common exercise so file geodatabases are preferable to SDE for speed and flexibility in making such modifications and SDE provides no real benefits during the many geoprocessing steps involved.  So start by creating and porting your data to a file geodatabase if you have not already done so and set that file geodatabase as the default output location in the geoprocessing environment settings of the MXD map document file you will be using.

Constructing a Linear Referencing Route Network

This step is only required for those who have never previously created a Route Network based on their centerline network before.  If you already have such a network you can skip to the next section.

1.       The Centerlines should have a Route ID or Route Name field included.  If you have no particular system for defining such routes in your state you can initially define the Route ID/Name as the Street Name.  However, I normally create a separate ROUTE_NAME field on my centerlines so I can modify the original street names with area designations or unique numbers that help better define sets of centerlines that should be grouped as a single route, especially for large networks that may have widely separated unrelated streets that have the same street names.  it should have enough characters in the ROUTE_NAME field to allow the name to be changed to keep routes from having huge gaps or including branches.  I recommend that you include a separate field for coordinate priority, which you can initially calculate to be equal to LL (for Lower Left, see step 2).

2.       Initially build the route network using the Create Routes tool in the Linear Referencing Tools toolbox.  The settings for the tool should use the centerlines as the input line network, the Route ID/Name field of the centerlines, Length as the measure source option, and LOWER_LEFT as the coordinate priority and use the NO_IGNORE option for measuring gaps.

3.       The measures environments settings should permit fractional tolerance and resolution values.  In the case of my particular data, I use a tolerance of .001 and resolution of .0001 on my routes which have measure lengths based on feet.

4.       The measures should be based on feet or meters rather than miles or kilometers and if necessary use the optional measure factor to make the conversion.  See the conversion of units entry on Wikipedia for measure conversion factor values.

5.       If you have a very large centerline network consider using model builder to construct the routes by areas or street name groups and append them together using the Append and/or Merge tools in the Data Management Tools toolbox -> General toolset.  Try not to build more than 10,000 separate routes at once, since performance of the Create Routes tool can drop dramatically when too many routes are being built at once.

6.       Add four double fields called PARTS, FROM_MEAS, TO_MEAS and MEAS_LENGTH to the routes output and calculate them using the Python parser of the Field Calculator to be equal to !Shape.PartCount!, !Shape.FirstPoint.M!, !Shape.LastPoint.M! and !Shape.LastPoint.M! - !Shape.FirstPoint.M! respectively.  Select the routes that have a PARTS value greater than 1.

7.       Use the Multipart to Singlepart tool in the Data Management Tools toolbox -> Features toolset on the route selection.  Add two fields called PART_FROM_MEAS and PART_TO_MEAS and calculate them using the Python parser of the Field Calculator to be !Shape.FirstPoint.M! and !Shape.LastPoint.M! respectively.

8.       On the last output use the Sort tool in the Data Management Tools toolbox -> General toolset if you have an Advanced license or the Dissolve tool in the Data Management Tools toolbox -> Generalization toolset for a lower license with the Route ID/Name fields, PART_FROM_MEAS, and PART_TO_MEAS fields as the sort/case fields (and for Dissolve get the Sum of the length field as the Summary field).

9.       Examine the output for large jumps in measures or overlapping measures to discover the parts that should be broken into new routes, particularly if measures overlap.

a.       Create a relate back to the Multipart to Singlepart output to select and examine the actual segment locations that appear to be odd.

b.      Edit the underlying centerline Route ID/Name fields to separate unrelated or branching parts into new routes.

c.       Alter the coordinate priority for routes that orient diagonally perpendicular to the Lower Left corner to UL (more easterly/westerly routes) or LR (more northerly/southerly routes) and separately construct those routes with the Create Route tool for appending as described in step 5.

d.      Consider fixing the Centerline network itself using topology if a lot of small gaps exist between centerlines that are not meaningful, since these can cause routes to be constructed strangely if their ends go past each other.

e.      Consider creating new Route ID/Names at jurisdictional boundaries if address ranges will be reset at those boundaries.  It will eliminate many steps involved in creating ranges where two or more range sets occur on the same street name.

10.   Repeat steps 2 through 9 until all routes are either single part or only have small gaps and no branch pairs connect.

Converting and Validating Address Points as Linear Referenced Events

1.       If you intend to process your addresses in groups, such as by City or area, you should create a separate geodatabase for each address set.  This will allow you to use the default table or feature class names used in this document, which will make using the field calculations easier when your join data.  You should also change the Workspace Environment settings under the Geoprocessing Menu to use that database for all tool outputs.

2.    Your Address Points should have a UniqueID value for each point in a field other than the ObjectID field.  A Long field that duplicates the ObjectID is fine.  You Address Points should also have two double fields preferably named X_COORDINATE and Y_COORDINATE that you have calculated to hold the original coordinates of the address point using the Geometry Calculator.

3.       Use the Locate Features Along Routes tool in the Linear Referencing Tools toolbox to convert the address points to linear referenced events.   Here are the settings I use:

a.       Input Features:  Address Points

b.     In Routes: My Road Network Routes

c.     RouteID field: ROUTE_NAME

d.     Radius:  1000 (feet)

e.     Out Table: Default name assigned by tool

f.      Out Event Properties: RID, POINT, MEAS, “”

g.     Route Locations: All

h.     Distance Field: DISTANCE

i.      Zero Length Events: ZERO

j.      In Fields: NO FIELDS

k.     M Direction Offsetting:  M DIRECTION

With these settings it took approximately an 1 ½ hours to process 83,485 address points along 5,211 routes.  It outputted 1,342,451 point events, which will be reduced to the correct number of matches in subsequent steps.  I chose the radius of 1,000 feet after observing that this set of addresses included several large areas of rural roads with many addresses at that offset distance, but the large number of events generated was due to letting the tool find all route locations with this radius being applied to roads in densely addressed areas.  It is important to get all address locations, and not just the closest route event, because corner lots often have side roads closer to their address point than the addressing street when they are located in the center of a parcel.  The amount of processing time the tool took was not significantly reduced when I used a lower radius setting; however, the larger the radius the larger the number of false matches.  The side Offset Distance field is important for later steps. If your addresses have no reliable unique value field other the ObjectID, use the NO FIELDS option to preserve the ObjectID of each address in a field called INPUTOID, and join the Address Points ObjectID field to the output’s INPUTOID field and export it to get the rest of the address fields transferred.

4.       It takes several steps to extract the correct matches from the output of step 3 above.  The first of these steps is to select all features where the base street name of the Address is within the ROUTE_NAME (since my routes include the actual road name), or within a RouteID table that associates a full street name to each of your RouteID values.  I use the following expression to do the selection and then export the selection to a new event table called ADDRESS_POINTS_Matched:

a.       ROUTE_NAME LIKE Concat(Concat(‘%’, STREET_NAME), ‘%’)

5.       Since it is possible for the base street name to be contained within more than one full street name (i.e., the base street name “CEDAR” is in CEDAR AVE, CEDAR CT, CEDAR CREST DR, CEDARWOOD DR, RED CEDAR RD, etc.), the Summary Statistics tool, located in the Analysis toolbox in the Statistics toolset, should be used to summarize all of the addresses in the ADDRESS_POINT_Matched table to find all matches with more than one location for the address.  This will also help isolate addresses that were matched to several equidistant point locations along the interior bend of a curved road.  Include the Base Street name of the address point, and summaries of the RouteID Min and Max, Measure Min, Max and Range.  Fix all duplications of an address that are due to more than one street having the same base name within the tolerance distance to the address by deleting the incorrect record.

6.       Select all records in the ADDRESS_POINTS_Matched table and apply the selected records only option to the table view for that event table.  Sort all of the events so that the Base Street Names are ascending, subsorted by the Route Full Street Names and any other values that you think will help you easily identify the real matches.  Highlight all records that are correct matches and unselect them.  When you have finished, all remaining selected records should be able to be deleted.

7.       Right click the ADDRESS_POINTS_Matched in the table of Contents and use the Joins and Relates context menu item to generate a Relate to the Located Events table output from step 3 above on the INPUTOID fields (or any other unique value field retained from your addresses).

8.       Select all records in the ADDRESS_POINTS_Matched table.

9.       Perform the relate selection to the Located Events table and sort the selection to group all base street names together, subsorted by the Route Full Street Names..

10.       Invert the transferred selection.

11.    Select from the Current Selection all events that have the same first letter in the base street name and the street full name using the expression:  SUBSTRING(ROUTE_NAME FROM 1 FOR 1) = SUBSTRING(STREET_NAME FROM 1 FOR 1)

12.    Right click the Located Events table output from step 3 in the table of Contents and use the Joins and Relates context menu item to generate a Relate to the original Address Points on the INPUTOID/OBJECTID fields (or any other unique value field retained from your addresses).

13.    Perform the relate selection from the Located Events table to the Address Points.

14.    Examine the groups of events that did not match due to spelling variants or misspellings and if necessary zoom to the corresponding address point locations to validate any incorrect spellings of base street names in either the address points or the centerlines that prevented matching.  Unselect all of the records from the Located Events table that have names that should not have matched.  When you have completed this step only real matches affected by misspellings will be selected.

15.    Use the Append tool located in the Data Management toolbox in the General toolset to append all selected records from the Located Events table output from step 3 above to the ADDRESS_POINTS_Matched table.

16.     Close and reopen the ADDRESS_POINTS_Matched table to refresh it and select all records in the ADDRESS_POINTS_Matched table.

17.     Perform the relate selection to the Located Events table.

18.     Invert the transferred selection.

19.    Perform the relate selection from the Located Events table to the Address Points.

20.    Examine the pattern of address points on the map and determine where a geometric relationship between the alignment of the address points and the roads should have been matched, but the street names are totally different.  Select small sets of actual matches that meet these criteria in the Locate Feature Event table and transfer them to the ADDRESS_POINTS_Matched table.

22.    Perform a Summary Statistics again on the ADDRESS_POINTS_Matched table to find all address events that are duplicated at two positions or that have more than one route associated with them and relate the selection as necessary to the Locate Features event output and Address Points as necessary to determine which matches should be retained and which false matches should be eliminated.

23. Add two double fields called EVENT_X and EVENT_Y to the ADDRESS_POINTS_Matched table.  These fields will be calculated later in the next section.

Symbolizing and Displaying your Linear Referenced Address Point Events

1.       Once the events have been created, add a text field with 25 characters called SIDE_PARITY (parity is the term for even or odd numbering) and calculate it using the Field Calculator  with the following formula:

a.       Parser: VB Script

b.      Show Codeblock: Checked

c.       Prelogic Script Code:

Distance = [Distance] ' Match your Side Distance field

House_Number = [HOUSE_NUMBER] ' Match your House Number field

 

If IsNull(Distance) Then

  Side = "NULL_DIST"

ElseIf Not IsNumeric(Distance) Then

  Side = "NON_DIST"

ElseIf CDbl(Distance) = 0 Then

  Side = "ON_LINE"

ElseIf CDbl(Distance) > 0 Then

  Side = "LEFT_SIDE"

ElseIf CDbl(Distance) < 0 Then

  Side = "RIGHT_SIDE"

Else

  Side = "CHECK_SIDE"

End If

 

If IsNull(House_Number) then

  Parity = "NULL_HOUSE"

ElseIf Not IsNumeric(House_Number) then

  Parity = "NON_HOUSE"

ElseIf CLng(House_Number) = 0 Then

  Parity = "ZERO_HOUSE"

ElseIf CLng(House_Number) Mod 2 = 0 Then

  Parity = "EVEN_HOUSE"

ElseIf CLng(House_Number) Mod 2 = 1 Then

  Parity = "ODD_HOUSE"

Else

  Parity = "CHECK_HOUSE"

End If

Output = Side & ", " & Parity

d.      SIDE_PARITY = Output (just enter Output in the expression for the field).

e.      If this is the first time you have done this calculation, save it to a file with the Save button for easy reuse in the future.

f.        Here is a screenshot of how it should look:

5.       Save the Calculation Expression with the Save option if this is the first time you are doing this process so you can load it in the future.  Name it something like Side_Parity.

6.   Right click on the validated Address Event table and choose the Make Route Event Layer context menu item to create a point event layer from the event table.  Use the Distance field as the Side Offset.

7.   Click the Advanced Options… button at the bottom left corner of the dialog and set the  paying particular attention to the “Events with a positive offset will be placed to the:” option.

8.   Change the symbology of the layer to the Categories using Unique Values and choose the SIDE_PARITY field as the field to symbolize.  Add all values and make the colors contrast (My preference is to pair Blue and Gold and Red and Green on opposing sides.)  Here is a screen shot of a layer set up:

9.   Make sure the addresses appear to match the expected sides of the street.  If they don’t, open the layer Properties, go to the Source Tab, press the Set Data Source… button, press the Advanced Options… button and change the “Events with a positive offset will be placed to the:” option.

10.   This should reveal the pattern of addressing that is typical for the East/West roadways and the North/South roadways.  In this area East/West roadways are predominantly Left and Odd and Right and Even, while North/South roadways are Left and Even and Right and Odd house numbers.

11. Export the Address Point Event layer from the table of contents to create an actual point feature class called Address_Points_Temp.

12. Use the Geometry Calculator to calculate the EVENT_X and EVENT_Y fields in the Address_Points_Temp feature class (Although the .Geometry Calculator can be used on the point event layer, it will take a very long time to complete the calculation if there are a lot of address point events, but only a short time to do the Geometry Calculation on points in an actual feature class).

13. Open the ADDRESS_POINTS_Matched table and join the Address_Points_Temp feature class to it using the common unique ADDRESS_ID field from your original Address Points and calculate the EVENT_X and EVENT_Y fields of the ADDRESS_POINTS_Matched table from the same fields in the Address_Points_Temp feature class.  Afterward, break the join.

14.  Open the Address Point Event layer attribute table and select records with the following expression to select address points that have been moved more than a specified distance in either coordinate direction (I specified a displacement of at least 15 in each direction):  (ABS( X_COORDINATE - EVENT_X ) > 15 OR ABS( Y_COORDINATE - EVENT_Y ) > 15) AND RID > ' '.

15.  This selection can be used to find all address points that are either located around the bulb of a cul-de-sac, curving around a knuckle, affected by odd curves or zigzags in your route, or that fall within a gap in your route or beyond the end of a route that does not fully cover an addressed road.  A relate to the original Address Points is useful to also select the original address events so you can more easily see how much displacement has occurred.  I retained points that are surrounding cul-de-sacs and knuckles, but removed points that fell in gaps or beyond route ends where the routes do not fully cover the actual road, since those addresses should be assigned to ranges on new portions of centerline that I should create later.  Zigzag roads may cause the portion of the road that the address actually fronts along to move farther away from the address point than the portion that it does not front along.  I either remove those address events or manually adjust the measure to make them fall along the correct portion of the zigzag.

Dealing with Unitized Addresses

Most addresses do not have a Unit number assigned, but those that do can create clusters of duplicated house numbers, which can distort address range assignments if they are not isolated to a single house number location.

 

 

1.       Here is my address layer after using a definition query to filter the layer to only show addresses that have Unit numbers.

2.       Each cluster that duplicates the same House Number address will have to be reduced to a single house number point location using a new field (which I chose to call REMOVE_UNITS) that will flag the unit points that should not be used for creating ranges.

3.       Here is an example of one set of duplicated house numbers due to unitization with the units to be removed shown selected in blue and the unit that will remain shown in red (next to the road below the entry driveway).

4.       In some cases the measure value of the remaining cluster point may need to be adjusted to a new pseudo-position relative to the street they are addressed on if their real position is not well placed for the overall range or where the mailing address is really located.

Converting Address Points to Line Segment Events

 

To help understand our address configuration better and get house pairs on each side of our centerline segment end points, we will convert the Address Points to Address line segments that connect each  adjacent address of the same number type (odd or even) that are on the same side (left or right) of the centerline routes.  Here is how to do that:

1.       First create a copy of the addresses from the event table (not the event layer) with a filter set to REMOVE_UNITS IS NULL to another table and name it something like Address_Lines_Temp.

2.       Use the Append tool in the Database Management Tools toolbox -> General toolset to append another copy of the addresses with the filter into the Address_Lines_Temp table you just copied from the address point events to duplicate them.

3.       Use the Summary Statistics tool in the Analysis Tools toolbox -> Statistics toolset on the Address_Lines_Temp table and name its output Address_Lines_Sort.  For the Statistics field get the Count of the OBJECTID field (the objective it to get a count of 1 for each record with no summarization, only sorting).  For the Case fields use the field below in the following order:

a.       RID field

b.      SIDE_PARITY field

c.       MEAS Field

d.      The House Number field

e.      The Full Street Name field or all of the Street Name component fields

f.        Unit Number (distinguishes House_Numbers that may repeat)

g.       City Field

h.      Zip Code Field

i.         An Assessor’s Parcel Number field if available.

j.        The Distance field for the side offset.

k.       OBJECTID field (this field will not appear twice in the output, but still acts to prevent summarization of otherwise duplicate records in the output)

l.         Any other field you like from the address points attributes.  I Included:

  i.  STREET_NAME (Base Street Name)

  ii.  STREET_TYPE

  iii.  DIR (Street Direction in Full Name)

4.       Add a long field called LINE_NUMBER to the summary table and calculate its value to be:

a.       Parser: VB Script

b.      Show Codeblock: Checked

c.       Prelogic Script Code:

If [OBJECTID] = Round( [OBJECTID] / 2 , 0) * 2 Then

  Output = [OBJECTID] + 1

Else

  Output = [OBJECTID]

End If

d.      LINE_NUMBER = Output (Only type the word Output in the expression text box)

e.      Here is a screen shot of how the field calculator set up should look:

5.       Save the Calculation Expression with the Save option if this is the first time you are doing this process so you can load it in the future.  Name it something like Line_Numbers.

6.       Use the Summary Statistics tool in the Analysis Tools toolbox -> Statistics toolset on the Address_Lines_Sort table and name its output Address_Lines.  For the Statistics fields use the following:

a.       MEAS Min

b.      MEAS Max

c.       OBJECTID Min

d.      OBJECTID Max

For the Case fields use the fields below in the following order:

a.       RID Field

b.      SIDE_PARITY Field

c.       LINE_NUMBER field

7.       The number of records generated should be slightly more than half of the duplicated records in the Address_Lines_Temp table if several routes were represented, since the first record and last record of each new route will not link to the adjacent record of another route.

8.   Add the following fields to the Address_Lines summary output:

a.       FROM_MEAS double

b.      TO_MEAS double

c.       FROM_HOUSE_NUMBER Long

d.      TO_HOUSE_NUMBER Long

9.   Optionally add additional fields.  I chose to add the fields below

a.       FROM_FULL_NAME text 60

b.      TO_FULL_NAME text 60

c.       FROM_STREET_NAME text 40

d.      TO_STREET_NAME text 40

e.      FROM_STREET_TYPE text 5

f.        TO_STREET_TYPE text 5

g.       FROM_DIR text 2

h.      TO_DIR text 2

i.         FROM_DISTANCE double

j.        TO_DISTANCE double

10.   Calculate the FROM_MEAS field to be equal to the MIN_MEAS field and the TO_MEAS field to be equal to the MAX_MEAS field.

11.   Join the MIN_OBJECTID to the OBJECTID of the Address_Lines_Sort table and calculate the matching FROM_ fields set to be equal to the HOUSE_NUMBER, FULL_NAME, STREET_NAME, STREET_TYPE, DIR, and Distance fields.

12.   Break the join.

13.   Join the MAX_OBJECTID to the OBJECTID of the Address_Lines_Sort table and calculate the matching TO_ fields set to be equal to the HOUSE_NUMBER, FULL_NAME, STREET_NAME, STREET_TYPE, DIR, and Distance fields.

14.   Break the join.

15.   Add a text field with 20 characters called ASC_DESC to the table.

16.   Calculate the ASC_DESC field to be equal to the following:

a.       Parser: VB Script

b.      Show Codeblock: Checked

c.       Prelogic Script Code:

Min_Meas = [MIN_MEAS]

Max_Meas = [MAX_MEAS]

From_House_Number = [FROM_HOUSE_NUMBER]

To_House_Number = [TO_HOUSE_NUMBER]

 

If Min_Meas = Max_Meas Then

  Output = "Same Measure"

ElseIf To_House_Number > From_House_Number Then

  Output = "Ascending"

ElseIf To_House_Number < From_House_Number  Then

  Output = "Descending"

ElseIf To_House_Number = From_House_Number Then

  Output = "Same House Number"

Else

  Output = ""

End If

d.      ASC_DESC = Output (only type the word Output in the expression text box).

e.      Here is a screenshot of how the calculation looks:

17.   Save the Calculation Expression with the Save option if this is the first time you are doing this process so you can load it in the future.  Name it something like Asc_Desc_Add_Pairs.

18.   Add a double field named HOUSE_INTERVAL to the line event table.

19.   Calculate the HOUSE_INTERVAL field to be equal to the following:

a.       Parser: VB Script

b.      Show Codeblock: Checked

c.       Prelogic Script Code:

Min_Meas = [MIN_MEAS]

Max_Meas = [MAX_MEAS]

From_House_Number = [FROM_HOUSE_NUMBER]

To_House_Number = [TO_HOUSE_NUMBER]

 

If Min_Meas = Max_Meas Then

  Output = 0

ElseIf Not IsNull(To_House_Number) and Not IsNull(From_House_Number) and (To_House_Number - From_House_Number) <> 0 Then

  Output = (Max_Meas - Min_Meas) / (To_House_Number - From_House_Number)

Else

  Output = 0

End If

d.      HOUSE_INTERVAL = Output (Just type the word Output in the expression text box).

 

e.      Here is how the calculation looks:

20.   Save the Calculation Expression with the Save option if this is the first time you are doing this process so you can load it in the future.  Name it something like House_Interval_Add_Pairs.

Evaluating the Address Line Output for Unexpected Patterns

1.       Right click the Address_Lines table in the Source tab of the Table of Contents and choose the Display Route Events… context menu item.

2.       Set the layer to be a Line Event layer with the MIN_MEAS and MAX_MEAS fields being the From and To Measure fields respectively.  Assign either the FROM_DISTANCE or TO_DISTANCE Field as the Side Offset field.

 

 

3.       Here is how the dialog looked when I configured it for my specific data:

4.       Press the OK button.

5.       If the lines do not align with the Address points, open the layer Properties, go to the Source Tab, press the Set Data Source… button, press the Advanced Options… button and change the “Events with a positive offset will be placed to the:” option.

6.       In the Layer Properties go to the Symbology tab and use a Multi-field Category symbology for the layer with the ASC_DESC field and the SIDE_PARITY field providing the Unique values.

7.       Press the Advanced… button and change the delimiter character for field value separation from a comma to a slash.

8.       Add all values and then remove all values that are not Ascending or Descending.

9.       Adjust the colors to make the primary colors match the expected sort direction for the addresses and contrasting secondary colors for the opposing unexpected directions and thicken the unexpected direction lines slightly.  You can make <all other values> grey.

 

 

10.   Here is the symbology configuration that worked for this particular project area:

11.   Small segments near the disconnected end of a line are frequently due to House Numbers not aligning perfectly around cul-de-sac bulbs, which is a known issue.

12.   Longer segments or segments along the length of the line or near a connected line end are potential errors in house numbering (such as number transpositions) or possibly indicators that the house numbering pattern intentionally changes at different portions of the line and should be handled as separate address ranges.  These segments should be examined more carefully to determine whether or not the anomaly can be ignored or needs to be addressed.

13.   You can also make a copy of the line event layer and then change the Symbology to a Quantities – Graduated Color symbology based on the HOUSE_INTERVAL field.  For my specific data the quantity ranges were divided as shown in the screen shot below (generally considering the range of values between 2 and 20 or -2 and -20 as valid).

 

 

14.   Here is a screen shot of a layer set up based on the suggestion of step 13.

15.   Values at the more extreme ranges (too small or too great on the positive or negative side) can indicate an anomalous house number or that a range expands or contracts significantly along portions of the route in a way that should be examined.  When a centerline segment end is surrounded by an extreme range segment, it is more important that it should be examined and corrected if necessary.

16.   To see some of the worst extremes that are most likely to include transposed numbers use this query and examine these segments:

"HOUSE_INTERVAL" > -0.5 AND "HOUSE_INTERVAL" < 0.5 AND "HOUSE_INTERVAL" <> 0 AND ABS( "FROM_HOUSE_NUMBER" - "TO_HOUSE_NUMBER") > 50

17.   You can try expanding the range to see if other sets caused problems that need to be fixed using selections such as:

"HOUSE_INTERVAL" > -1 AND "HOUSE_INTERVAL" < 1 AND "HOUSE_INTERVAL" <> 0 AND ABS( "FROM_HOUSE_NUMBER" - "TO_HOUSE_NUMBER") > 50

Summarizing the Overall Range Trend and House Interval Line Events of an Address Range

Obtaining the route’s overall address range trend will help us determine the significance of a given house pair segment or set of segments that do not match our expectations.

1.       On the Address_Lines event table Select by Attributes the rows where:

ASC_DESC IN (‘Ascending’, ‘Descending’)

2.       Perform a Summary Statistics on the selection with an output called ADDRESS_LINES_FULL.  For the Summary fields use the settings below:

a.       FROM_MEAS Min

b.      FROM_MEAS Max

c.       TO_MEAS Min

d.      TO_MEAS Max

e.       FROM_HOUSE_NUMBER Min

f.      FROM_HOUSE_NUMBER Max

g.      TO_HOUSE_NUMBER Min

h.        TO_HOUSE_NUMBER Max

i.       HOUSE_INTERVAL Min

j.      HOUSE_INTERVAL Max

k.         HOUSE_INTERVAL Mean

3.       For the Case fields use the fields below in the following order:

a.       RID field

b.      SIDE_PARITY field

4.       Add a text field called ASC_DESC with 20 characters.

5.       With the Field Calculator calculate the ASC_DESC field with the following settings:

a.       Parser: VB Script

b.      Show Codeblock: Checked

c.       PreLogic Script Code:

If [MAX_TO_HOUSE_NUMBER] - [MIN_FROM_HOUSE_NUMBER] > [MAX_FROM_HOUSE_NUMBER] - [MIN_TO_HOUSE_NUMBER] Then

  Output = "Ascending"

ElseIf [MAX_TO_HOUSE_NUMBER] - [MIN_FROM_HOUSE_NUMBER] < [MAX_FROM_HOUSE_NUMBER] - [MIN_TO_HOUSE_NUMBER] Then

  Output = "Descending"

ElseIf [MAX_TO_HOUSE_NUMBER] < [MIN_FROM_HOUSE_NUMBER] AND [MAX_FROM_HOUSE_NUMBER] < [MIN_TO_HOUSE_NUMBER] Then

  Output = "Ascending"

ElseIf [MAX_TO_HOUSE_NUMBER] > [MIN_FROM_HOUSE_NUMBER] AND [MAX_FROM_HOUSE_NUMBER] > [MIN_TO_HOUSE_NUMBER] Then

  Output = "Descending"

Else

  Output = ""

End If

d.      ASC_DESC = Output (Only type the word Output in the expression text box).

6.       Save the Calculation Expression with the Save option if this is the first time you are doing this process so you can load it in the future.  Name it something like Asc_Desc_Full.

7.       Add a double field called HOUSE_INTERVAL.

8.       With the Field Calculator calculate the HOUSE_INTERVAL field with the following settings:

a.       Parser: VB Script

b.      Show Codeblock: Checked

c.       PreLogic Script Code:

If [ASC_DESC] = "Ascending" Then

  Output = ( [MAX_TO_MEAS] - [MIN_FROM_MEAS]) / ([MAX_TO_HOUSE_NUMBER] - [MIN_FROM_HOUSE_NUMBER])

ElseIf [ASC_DESC] = "Descending" Then

  Output = ( [MAX_TO_MEAS] - [MIN_FROM_MEAS]) / ([MIN_TO_HOUSE_NUMBER] - [MAX_FROM_HOUSE_NUMBER])

Else

  Output = 0

End If

d.      HOUSE_INTERVAL = Output (Only type the word Output in the expression text box).

9.       Save the Calculation Expression with the Save option if this is the first time you are doing this process so you can load it in the future.  Name it something like House_Interval_Full.

10.   Add a text field called GOOD_BAD with 4 characters to the output.

11.   The different SIDE_PARITY values need to be separated into separate files and compared to determine the best range type for each road side.  Therefore do four separate Select by Attribute queries for the four valid SIDE_PARITY values (SIDE_PARITY = ‘LEFT_SIDE, EVEN_HOUSE’, SIDE_PARITY = ‘LEFT_SIDE, ODD_HOUSE’, SIDE_PARITY = ‘RIGHT_SIDE, EVEN_HOUSE’, and SIDE_PARITY = ‘RIGHT_SIDE, ODD_HOUSE’) and export each selection to files named Add_Left_Even, Add_Left_Odd, Add_Right_Even and Add_Right_Odd respectively.

12.   Join the Add_Left_Even table to the Add_Left_Odd table on the common RID fields.

13.   Select the records where the Add_Left_Even.Frequency > Add_Left_Odd.Frequency OR Add_Left_Odd.OBJECTID IS NULL.  Calculate the GOOD_BAD field of the Add_Left_Even table to “Good”.

14.   Select the records where the Add_Left_Even.Frequency = Add_Left_Odd.Frequency.  Calculate the GOOD_BAD field of the Add_Left_Even table to “Look”.

15.   Select the records where the Add_Left_Even.Frequency < Add_Left_Odd.Frequency.  Calculate the GOOD_BAD field of the Add_Left_Even table to “Bad”.

16.   Break the Join.

17.   Select all of the records that were marked ‘Bad’ in the GOOD_BAD field and in an edit session delete them.

18.   Join the Add_Left_Odd table to the Add_Left_Even table on the common RID fields.

19.   Select the records where the Add_Left_Odd.Frequency > Add_Left_Even.Frequency OR Add_Left_Even.OBJECTID IS NULL.  Calculate the GOOD_BAD field of the Add_Left_Odd table to “Good”.

20.   Select the records where the Add_Left_Odd.Frequency = Add_Left_Even.Frequency.  Calculate the GOOD_BAD field of the Add_Left_Odd table to “Look”.

21.   Select the records where the Add_Left_Odd.Frequency < Add_Left_Even.Frequency.  Calculate the GOOD_BAD field of the Add_Left_Odd table to “Bad”.

22.   Break the Join.

23.   Select all of the records that were marked ‘Bad’ in the GOOD_BAD field and in an edit session delete them.

24.   Join the Add_Right_Even table to the Add_Right_Odd table on the common RID fields.

25.   Select the records where the Add_Right_Even.Frequency > Add_Right_Odd.Frequency OR Add_Right_Odd.OBJECTID IS NULL.  Calculate the GOOD_BAD field of the Add_Right_Even table to “Good”. 

26.   Select the records where the Add_Right_Even.Frequency = Add_Right_Odd.Frequency.  Calculate the GOOD_BAD field of the Add_Right_Even table to “Look”.

27.   Select the records where the Add_Right_Even.Frequency < Add_Right_Odd.Frequency.  Calculate the GOOD_BAD field of the Add_Right_Even table to “Bad”.

28.   Break the Join.

29.   Select all of the records that were marked ‘Bad’ in the GOOD_BAD field and in an edit session delete them.

30.   Join the Add_Right_Odd table to the Add_Right_Even table on the common RID fields.

31.   Select the records where the Add_Right_Odd.Frequency > Add_Right_Even.Frequency OR Add_Right_Even.OBJECTID IS NULL.  Calculate the GOOD_BAD field of the Add_Right_Odd table to “Good”.

32.   Select the records where the Add_Right_Odd.Frequency = Add_Right_Even.Frequency.  Calculate the GOOD_BAD field of the Add_Right_Odd table to “Look”.

33.   Select the records where the Add_Right_Odd.Frequency < Add_Right_Even.Frequency.  Calculate the GOOD_BAD field of the Add_Right_Odd table to “Bad”.

34.   Break the Join.

35.   Select all of the records that were marked ‘Bad’ in the GOOD_BAD field and in an edit session delete them.

Validation of House Range End House Numbers for the Left Even Addresses

Although not all of the range patterns below will need any adjustment to the range ends house numbers, some will, particularly for cul-de-sac bulbs.  The steps below outline how to get the largest house ranges from your data where they may have been missed by prior steps.

1.       Create a Relate from the Add_Left_Even table to the Address_Lines table on the common RID field.

2.       Create a Join from the Address_Lines table to the Add_Left_Even table.

3.       Select By Attribute on the joined table and with the Create a Selection option select the records where:

Address_Lines.SIDE_PARITY = Add_Left_Even.SIDE_PARITY AND Add_Left_Even.GOOD_BAD = 'Good' AND Address_Lines.FROM_MEAS = Add_Left_Even.MIN_FROM_MEAS AND Add_Left_Even.ASC_DESC = 'Ascending' AND Address_Lines.FROM_HOUSE_NUMBER < Add_Left_Even.MIN_FROM_HOUSE_NUMBER

4.       Break the Join.

5.       From the Address_Line table perform the relate to get the matching record selection in the Add_Left_Even table.

6.       Start an edit session on the Add_Left_Even table workspace.

7.       Copy paste the lowest FROM_HOUSE_NUMBER of the Address_Lines selection to the MIN_FROM_HOUSE_NUMBER of the Add_Left_Even table on the matching RID values.

8.       Recalculate the HOUSE_INTERVAL of the Add_Left_Even table using the formula saved in

9.       Save the edits when all are finished.

10.   Create a Join from the Address_Lines table to the Add_Left_Even table.

11.   Select By Attribute on the joined table and with the Create a Selection option select the records where:

Address_Lines.SIDE_PARITY = Add_Left_Even.SIDE_PARITY AND Add_Left_Even.GOOD_BAD = 'Good' AND Address_Lines.FROM_MEAS = Add_Left_Even.MIN_FROM_MEAS AND Add_Left_Even.ASC_DESC = 'Ascending' AND Address_Lines.FROM_HOUSE_NUMBER > Add_Left_Even.MIN_FROM_HOUSE_NUMBER

12.   With the Field Calculator calculate the FROM_HOUSE_NUMBER of the Addess_Lines table to be equal to [Add_Left_Even.MIN_FROM_HOUSE_NUMBER].

13.   Break the Join.

14.   Create a Join from the Address_Lines table to the Add_Left_Even table.

15.   Select By Attribute on the joined table and with the Create a Selection option select the records where:

Address_Lines.SIDE_PARITY = Add_Left_Even.SIDE_PARITY AND Add_Left_Even.GOOD_BAD = 'Good' AND Address_Lines.TO_MEAS = Add_Left_Even.MAX_TO_MEAS AND Add_Left_Even.ASC_DESC = 'Ascending' AND Address_Lines.TO_HOUSE_NUMBER > Add_Left_Even.MAX_TO_HOUSE_NUMBER

16.   Break the Join.

17.   From the Address_Line table perform the relate to get the matching record selection in the Add_Left_Even table.

18.   Start an edit session on the Add_Left_Even table workspace.

19.   Copy paste the highest TO_HOUSE_NUMBER of the Address_Lines selection to the MAX_TO_HOUSE_NUMBER of the Add_Left_Even table on the matching RID values.

20.   Recalculate the HOUSE_INTERVAL of the Add_Left_Even table using the formula saved in

21.   Save the edits when all are finished.

22.   Create a Join from the Address_Lines table to the Add_Left_Even table.

23.   Select By Attribute on the joined table and with the Create a Selection option select the records where:

Address_Lines.SIDE_PARITY = Add_Left_Even.SIDE_PARITY AND Add_Left_Even.GOOD_BAD = 'Good' AND Address_Lines.TO_MEAS = Add_Left_Even.MAX_TO_MEAS AND Add_Left_Even.ASC_DESC = 'Ascending' AND Address_Lines.TO_HOUSE_NUMBER < Add_Left_Even.MAX_TO_HOUSE_NUMBER

24.   With the Field Calculator calculate the TO_HOUSE_NUMBER of the Addess_Lines table to be equal to [Add_Left_Even.MAX_TO_HOUSE_NUMBER].

25.   Break the Join.

26.   Another check that can be done is to find instances where the extreme house numbers are inside the ends of the address range sets.  After joining the line event table for the house pairs to one of the Add_... tables, this query can do that for Ascending routes for the Add_Left_Even table on the Min_From_House_Number:

Address_Lines_Features.SIDE_PARITY = Add_Left_Even.SIDE_PARITY AND Add_Left_Even.GOOD_BAD = 'Good' AND Address_Lines_Features.ASC_DESC IN ('Ascending', 'Descending') AND Add_Left_Even.ASC_DESC =  'Ascending' AND Address_Lines_Features.FROM_MEAS <> Add_Left_Even.MIN_FROM_MEAS AND Address_Lines_Features.FROM_HOUSE_NUMBER = Add_Left_Even.MIN_FROM_HOUSE_NUMBER

Validation of House Range End House Numbers for the Left Odd Addresses

These steps apply to the Right Odd Addresses, and are listed out to make it easier to follow.  However, basically this section is a repeat of the process listed under the Validation of House Range End House Numbers for the Left Even Addresses.

1.       Create a Relate from the Add_Odd_Even table to the Address_Lines table on the common RID field.

2.       Create a Join from the Address_Lines table to the Add_Odd_Even table.

3.       Select By Attribute on the joined table and with the Create a Selection option select the records where:

Address_Lines.SIDE_PARITY = Add_Left_Odd.SIDE_PARITY AND Add_Left_Odd.GOOD_BAD = 'Good' AND Address_Lines.FROM_MEAS = Add_Left_Odd.MIN_FROM_MEAS AND Add_Left_Odd.ASC_DESC = 'Ascending' AND Address_Lines.FROM_HOUSE_NUMBER < Add_Left_Odd.MIN_FROM_HOUSE_NUMBER

4.       Break the Join.

5.       From the Address_Line table perform the relate to get the matching record selection in the Add_Left_Odd table.

6.       Start an edit session on the Add_Left_Odd table workspace.

7.       Copy paste the lowest FROM_HOUSE_NUMBER of the Address_Lines selection to the MIN_FROM_HOUSE_NUMBER of the Add_Left_Odd table on the matching RID values.

8.       Recalculate the HOUSE_INTERVAL of the Add_Left_Odd table using the formula saved in

9.       Save the edits when all are finished.

10.   Create a Join from the Address_Lines table to the Add_Left_Odd table.

11.   Select By Attribute on the joined table and with the Create a Selection option select the records where:

Address_Lines.SIDE_PARITY = Add_Left_Odd.SIDE_PARITY AND Add_Left_Odd.GOOD_BAD = 'Good' AND Address_Lines.FROM_MEAS = Add_Left_Odd.MIN_FROM_MEAS AND Add_Left_Odd.ASC_DESC = 'Ascending' AND Address_Lines.FROM_HOUSE_NUMBER > Add_Left_Odd.MIN_FROM_HOUSE_NUMBER

12.   With the Field Calculator calculate the FROM_HOUSE_NUMBER of the Addess_Lines table to be equal to [Add_Left_Odd.MIN_FROM_HOUSE_NUMBER].

13.   Break the Join.

14.   Create a Join from the Address_Lines table to the Add_Left_Odd table.

15.   Select By Attribute on the joined table and with the Create a Selection option select the records where:

Address_Lines.SIDE_PARITY = Add_Left_Odd.SIDE_PARITY AND Add_Left_Odd.GOOD_BAD = 'Good' AND Address_Lines.TO_MEAS = Add_Left_Odd.MAX_TO_MEAS AND Add_Left_Odd.ASC_DESC = 'Ascending' AND Address_Lines.TO_HOUSE_NUMBER > Add_Left_Odd.MAX_TO_HOUSE_NUMBER

16.   Break the Join.

17.   From the Address_Line table perform the relate to get the matching record selection in the Add_Left_Odd table.

18.   Start an edit session on the Add_Left_Odd table workspace.

19.   Copy paste the highest TO_HOUSE_NUMBER of the Address_Lines selection to the MAX_TO_HOUSE_NUMBER of the Add_Left_Odd table on the matching RID values.

20.   Recalculate the HOUSE_INTERVAL of the Add_Left_Odd table using the formula saved in

21.   Save the edits when all are finished.

22.   Create a Join from the Address_Lines table to the Add_Left_Odd table.

23.   Select By Attribute on the joined table and with the Create a Selection option select the records where:

Address_Lines.SIDE_PARITY = Add_Left_Odd.SIDE_PARITY AND Add_Left_Odd.GOOD_BAD = 'Good' AND Address_Lines.TO_MEAS = Add_Left_Odd.MAX_TO_MEAS AND Add_Left_Odd.ASC_DESC = 'Ascending' AND Address_Lines.TO_HOUSE_NUMBER < Add_Left_Odd.MAX_TO_HOUSE_NUMBER

24.   With the Field Calculator calculate the TO_HOUSE_NUMBER of the Addess_Lines table to be equal to [Add_Left_Odd.MAX_TO_HOUSE_NUMBER].

25.   Break the Join.

26.   Another check that can be done is to find instances where the extreme house numbers are inside the ends of the address range sets.  After joining the line event table for the house pairs to one of the Add_... tables, this query can do that for Ascending routes for the Add_Left_Odd table on the Min_From_House_Number:

Address_Lines_Features.SIDE_PARITY = Add_Left_Odd.SIDE_PARITY AND Add_Left_Odd.GOOD_BAD = 'Good' AND Address_Lines_Features.ASC_DESC IN ('Ascending', 'Descending') AND Add_Left_Odd.ASC_DESC =  'Ascending' AND Address_Lines_Features.FROM_MEAS <> Add_Left_Odd.MIN_FROM_MEAS AND Address_Lines_Features.FROM_HOUSE_NUMBER = Add_Left_Odd.MIN_FROM_HOUSE_NUMBER

Validation of House Range End House Numbers for the Right Even Addresses

These steps apply to the Right Odd Addresses, and are listed out to make it easier to follow.  However, basically this section is a repeat of the process listed under the Validation of House Range End House Numbers for the Left Even Addresses.

1.       Create a Relate from the Add_Right_Even table to the Address_Lines table on the common RID field.

2.       Create a Join from the Address_Lines table to the Add_Right_Even table.

3.       Select By Attribute on the joined table and with the Create a Selection option select the records where:

Address_Lines.SIDE_PARITY = Add_Right_Even.SIDE_PARITY AND Add_Right_Even.GOOD_BAD = 'Good' AND Address_Lines.FROM_MEAS = Add_Right_Even.MIN_FROM_MEAS AND Add_Right_Even.ASC_DESC = 'Ascending' AND Address_Lines.FROM_HOUSE_NUMBER < Add_Right_Even.MIN_FROM_HOUSE_NUMBER

4.       Break the Join.

5.       From the Address_Line table perform the relate to get the matching record selection in the Add_Right_Even table.

6.       Start an edit session on the Add_Right_Even table workspace.

7.       Copy paste the lowest FROM_HOUSE_NUMBER of the Address_Lines selection to the MIN_FROM_HOUSE_NUMBER of the Add_Right_Even table on the matching RID values.

8.       Recalculate the HOUSE_INTERVAL of the Add_Right_Even table using the formula saved in

9.       Save the edits when all are finished.

10.   Create a Join from the Address_Lines table to the Add_Right_Even table.

11.   Select By Attribute on the joined table and with the Create a Selection option select the records where:

Address_Lines.SIDE_PARITY = Add_Right_Even.SIDE_PARITY AND Add_Right_Even.GOOD_BAD = 'Good' AND Address_Lines.FROM_MEAS = Add_Right_Even.MIN_FROM_MEAS AND Add_Right_Even.ASC_DESC = 'Ascending' AND Address_Lines.FROM_HOUSE_NUMBER > Add_Right_Even.MIN_FROM_HOUSE_NUMBER

12.   With the Field Calculator calculate the FROM_HOUSE_NUMBER of the Addess_Lines table to be equal to [Add_Right_Even.MIN_FROM_HOUSE_NUMBER].

13.   Break the Join.

14.   Create a Join from the Address_Lines table to the Add_Right_Even table.

15.   Select By Attribute on the joined table and with the Create a Selection option select the records where:

Address_Lines.SIDE_PARITY = Add_Right_Even.SIDE_PARITY AND Add_Right_Even.GOOD_BAD = 'Good' AND Address_Lines.TO_MEAS = Add_Right_Even.MAX_TO_MEAS AND Add_Right_Even.ASC_DESC = 'Ascending' AND Address_Lines.TO_HOUSE_NUMBER > Add_Right_Even.MAX_TO_HOUSE_NUMBER

16.   Break the Join.

17.   From the Address_Line table perform the relate to get the matching record selection in the Add_Right_Even table.

18.   Start an edit session on the Add_Right_Even table workspace.

19.   Copy paste the highest TO_HOUSE_NUMBER of the Address_Lines selection to the MAX_TO_HOUSE_NUMBER of the Add_Right_Even table on the matching RID values.

20.   Recalculate the HOUSE_INTERVAL of the Add_Right_Even table using the formula saved in

21.   Save the edits when all are finished.

22.   Create a Join from the Address_Lines table to the Add_Right_Even table.

23.   Select By Attribute on the joined table and with the Create a Selection option select the records where:

Address_Lines.SIDE_PARITY = Add_Right_Even.SIDE_PARITY AND Add_Right_Even.GOOD_BAD = 'Good' AND Address_Lines.TO_MEAS = Add_Right_Even.MAX_TO_MEAS AND Add_Right_Even.ASC_DESC = 'Ascending' AND Address_Lines.TO_HOUSE_NUMBER < Add_Right_Even.MAX_TO_HOUSE_NUMBER

24.   With the Field Calculator calculate the TO_HOUSE_NUMBER of the Addess_Lines table to be equal to [Add_Right_Even.MAX_TO_HOUSE_NUMBER].

25.   Break the Join.

26.   Another check that can be done is to find instances where the extreme house numbers are inside the ends of the address range sets.  After joining the line event table for the house pairs to one of the Add_... tables, this query can do that for Ascending routes for the Add_Left_Even table on the Min_From_House_Number:

Address_Lines_Features.SIDE_PARITY = Add_Right_Even.SIDE_PARITY AND Add_Right_Even.GOOD_BAD = 'Good' AND Address_Lines_Features.ASC_DESC IN ('Ascending', 'Descending') AND Add_Right_Even.ASC_DESC =  'Ascending' AND Address_Lines_Features.FROM_MEAS <> Add_Right_Even.MIN_FROM_MEAS AND Address_Lines_Features.FROM_HOUSE_NUMBER = Add_Right_Even.MIN_FROM_HOUSE_NUMBER

Validation of House Range End House Numbers for the Right Odd Addresses

These steps apply to the Right Odd Addresses, and are listed out to make it easier to follow.  However, basically this section is a repeat of the process listed under the Validation of House Range End House Numbers for the Left Even Addresses.

1.       Create a Relate from the Add_Odd_Even table to the Address_Lines table on the common RID field.

2.       Create a Join from the Address_Lines table to the Add_Odd_Even table.

3.       Select By Attribute on the joined table and with the Create a Selection option select the records where:

Address_Lines.SIDE_PARITY = Add_Right_Odd.SIDE_PARITY AND Add_Right_Odd.GOOD_BAD = 'Good' AND Address_Lines.FROM_MEAS = Add_Right_Odd.MIN_FROM_MEAS AND Add_Right_Odd.ASC_DESC = 'Ascending' AND Address_Lines.FROM_HOUSE_NUMBER < Add_Right_Odd.MIN_FROM_HOUSE_NUMBER

4.       Break the Join.

5.       From the Address_Line table perform the relate to get the matching record selection in the Add_Right_Odd table.

6.       Start an edit session on the Add_Right_Odd table workspace.

7.       Copy paste the lowest FROM_HOUSE_NUMBER of the Address_Lines selection to the MIN_FROM_HOUSE_NUMBER of the Add_Right_Odd table on the matching RID values.

8.       Recalculate the HOUSE_INTERVAL of the Add_Right_Odd table using the formula saved in

9.       Save the edits when all are finished.

10.   Create a Join from the Address_Lines table to the Add_Right_Odd table.

11.   Select By Attribute on the joined table and with the Create a Selection option select the records where:

Address_Lines.SIDE_PARITY = Add_Right_Odd.SIDE_PARITY AND Add_Right_Odd.GOOD_BAD = 'Good' AND Address_Lines.FROM_MEAS = Add_Right_Odd.MIN_FROM_MEAS AND Add_Right_Odd.ASC_DESC = 'Ascending' AND Address_Lines.FROM_HOUSE_NUMBER > Add_Right_Odd.MIN_FROM_HOUSE_NUMBER

12.   With the Field Calculator calculate the FROM_HOUSE_NUMBER of the Addess_Lines table to be equal to [Add_Right_Odd.MIN_FROM_HOUSE_NUMBER].

13.   Break the Join.

14.   Create a Join from the Address_Lines table to the Add_Right_Odd table.

15.   Select By Attribute on the joined table and with the Create a Selection option select the records where:

Address_Lines.SIDE_PARITY = Add_Right_Odd.SIDE_PARITY AND Add_Right_Odd.GOOD_BAD = 'Good' AND Address_Lines.TO_MEAS = Add_Right_Odd.MAX_TO_MEAS AND Add_Right_Odd.ASC_DESC = 'Ascending' AND Address_Lines.TO_HOUSE_NUMBER > Add_Right_Odd.MAX_TO_HOUSE_NUMBER

16.   Break the Join.

17.   From the Address_Line table perform the relate to get the matching record selection in the Add_Right_Odd table.

18.   Start an edit session on the Add_Right_Odd table workspace.

19.   Copy paste the highest TO_HOUSE_NUMBER of the Address_Lines selection to the MAX_TO_HOUSE_NUMBER of the Add_Right_Odd table on the matching RID values.

20.   Recalculate the HOUSE_INTERVAL of the Add_Right_Odd table using the formula saved in

21.   Save the edits when all are finished.

22.   Create a Join from the Address_Lines table to the Add_Right_Odd table.

23.   Select By Attribute on the joined table and with the Create a Selection option select the records where:

Address_Lines.SIDE_PARITY = Add_Right_Odd.SIDE_PARITY AND Add_Right_Odd.GOOD_BAD = 'Good' AND Address_Lines.TO_MEAS = Add_Right_Odd.MAX_TO_MEAS AND Add_Right_Odd.ASC_DESC = 'Ascending' AND Address_Lines.TO_HOUSE_NUMBER < Add_Right_Odd.MAX_TO_HOUSE_NUMBER

24.   With the Field Calculator calculate the TO_HOUSE_NUMBER of the Addess_Lines table to be equal to [Add_Right_Odd.MAX_TO_HOUSE_NUMBER].

25.   Break the Join.

26.   Another check that can be done is to find instances where the extreme house numbers are inside the ends of the address range sets.  After joining the line event table for the house pairs to one of the Add_... tables, this query can do that for Ascending routes for the Add_Right_Odd table on the Min_From_House_Number:

Address_Lines_Features.SIDE_PARITY = Add_Right_Odd.SIDE_PARITY AND Add_Right_Odd.GOOD_BAD = 'Good' AND Address_Lines_Features.ASC_DESC IN ('Ascending', 'Descending') AND Add_Right_Odd.ASC_DESC =  'Ascending' AND Address_Lines_Features.FROM_MEAS <> Add_Right_Odd.MIN_FROM_MEAS AND Address_Lines_Features.FROM_HOUSE_NUMBER = Add_Right_Odd.MIN_FROM_HOUSE_NUMBER

Extracting End Points from Centerlines

We now will begin to turn our attention to the process of transferring address range data to the Centerlines.  To do that, we need to work with the end points of each centerline.  This section explains how to get those end points.

1.       If you have an Advanced license you can just use the Feature Vertices to Points tool with the Both Ends setting.  This tool will extract the two end points of every centerline with the odd ObjectID records being the From End and even ObjectID values being the To End.  This tool will create a field called ORIG_FID that contains the original OBJECTID values of each centerline.  You must also add a field to this output called FROM_OR_TO that is text with 8 characters and calculate it to be:

a.       Parser: VB Script

b.      Show Codeblock: Checked

c.       PreLogic Script Code:

If [OBJECTID] <> Round([OBJECTID] / 2, 0) * 2 Then

  Output = “FROM_END”

Else

  Output = “TO_END”

End If

d.      FROM_OR_TO = Output (Just type the word Output in the expression text box)

2.       If you do not have an Advanced license you can still extract the End Points, but it takes more steps.

a.       To begin add a long field called ORIG_FID and calculate it to be equal to the OBJECTID field of the centerlines.

b.      Add four double fields called X_FROM, Y_FROM, X_TO, and Y_TO.

c.       Use the Geometry Calculator to calculate the X and Y of the Beginning and End of the line into the corresponding field.  Alternatively, use the Field Calculator with the expression !Shape.FirstPoint.X!, !Shape.FirstPoint.Y!, !Shape.LastPoint.X!, !Shape.LastPoint.Y! respectively.

d.      From the open table view Export the Centerline attributes to a standalone table called CL_Ends_Temp.

e.      In the Table of Contents right click the CL_Ends_Temp table and choose the Display XY Events… context menu item to create a layer based on the FROM_X and FROM_Y fields.

f.        Right click the first XY event layer (based on the X_FROM and Y_FROM fields) you just created in the Table of Contents and export it as a point feature class called CL_Ends.

g.       In the CL_Ends feature class add a text field with 8 characters called FROM_OR_TO and Calculate its value to be: “FROM_END”

h.      In the Table of Contents right click the CL_Ends_Temp table and choose the Display XY Events… context menu item to create a layer based on the TO_X and TO_Y fields.

e.      Use the Append tool in the Data Management Tools toolbox in the General toolset to append the second XY Event Table (based on the X_TO and _Y_TO fields) to the CL_Ends point feature class using the NO_TEST option.

f.        Select all records where the FROM_OR_TO field IS NULL and calculate those records to be: “TO_END”

Convert the Centerline End Points to Linear Referenced Point Events and Determine their orientation relative to the Routes.

1.       Use the Locate Features Along Routes tool in the Linear Referencing Tools toolbox to create point events from the CL_Ends point feature class along your routes.  Call the output CL_Ends_Locate.  The search radius can be left at 0 and you should use the All route Locations option and include a distance field.

2.       Select all events where the RID value does not equal the Centerline Route ID/Name and in an edit session delete those records.

3.       Create a double field called END_MEAS and calculate it to be equal to the MEAS field to preserve the Measure values during the overlay processes that follows below.

4.       Because the Centerlines may not be oriented with the direction of the Route, add an 8 characer text field called ROUTE_FROM_OR_TO.

5.       Use the Summary Statistics tool on the CL_Ends_Locate table with the following Settings:

a.       Output:  CL_Lines

b.      Summary Fields:

MEAS MIN

MEAS MAX

c.       Case Fields:

RID

ORIG_FID

Any other fields that came from the original centerlines, excluding Meas or END_MEAS.

6.       Join the CL_Ends_Locate as the primary table to the CL_Line_Events table on the common ORIG_FID fields.

a.       Select all Ends where the CL_Ends_Locate.MEAS  = CL_Line_Events.MIN_MEAS

b.      Calculate the ROUTE_FROM_OR_TO field to be “FROM_END”

c.       Select all Ends where the CL_Ends_Locate.MEAS  = CL_Line_Events.MAX_MEAS

d.      Calculate the ROUTE_FROM_OR_TO field to be “TO_END”

e.      Verify that no ROUTE_FROM_OR_TO field has a NULL value due to any possible SQL precision errors affecting selection queries or because somehow two or more locations were matched by the locate process (usually due to Routes having non-monotonic measures).  Fix any errors.

7.       Break the Join.

8.       Wherever FROM_OR_TO = ROUTE_FROM_OR_TO the centerline is oriented with the Route.  Wherever FROM_OR_TO <> ROUTE_FROM_OR_TO the centerline is not oriented with the Route.

9.       Create a Relate from the CL_Ends_Locate table to the CL_Line_Events on the ORIG_FID field.  Perform each the above selections separately on the CL_Ends_Locate and apply the relate.  Add a text field of 3 characters called ROUTE_ORIENTED to the CL_Line_Events table and calculate the matched orientation selection to “Yes” and the unmatched orientation routes to “No”.

Overlay the CL_Ends_Locate Events with the Address_Lines Events

1.       Use the Overlay Route Events tool the Linear Referencing Tools toolbox with the following settings:

a.       In_Table:  CL_Ends_Locate

b.      In_Event_Properties:  RID, POINT, END_MEAS

c.       Overlay_Table:  Address_Lines

d.      Overlay_Event_Properties: RID, LINE, MIN_MEAS, MAX_MEAS

e.      Overlay_Type: Intersect

f.        Out_Table:  CL_Ends_Add_Lines

g.       Out_Event_Properties: Default settings (rid, POINT, meas)

h.      Fields: Keep all fields

i.         Build_Index: Index

2.       The output will only include those Centerline End Point events which fell between two address point events.  Centerline end points which fell outside of the address point ranges will be processed separately later.

3.       Add a Long Field Called House_Number to the output and calculate it to be equal to the following:

a.       Parser:  VB Script

b.      Show Codeblock: Checked

c.       PreLogic Script Code:

if [TO_MEAS] <> [FROM_MEAS] Then

  If [ROUTE_FROM_OR_TO] = "FROM_END" Then

  Raw_House = ( [TO_HOUSE_NUMBER] - [FROM_HOUSE_NUMBER] ) * ( [meas] - [FROM_MEAS] ) / ( [TO_MEAS] - [FROM_MEAS] ) + [FROM_HOUSE_NUMBER]

  Int_House = Round(( [TO_HOUSE_NUMBER] - [FROM_HOUSE_NUMBER] ) * ( [meas] - [FROM_MEAS] ) / ( [TO_MEAS] - [FROM_MEAS] ) / 2, 0) * 2 + [FROM_HOUSE_NUMBER]

 

  If [FROM_HOUSE_NUMBER] < [TO_HOUSE_NUMBER] Then

    If Int_House < Raw_House Then

      Output = Int_House + 2

    Else

      Output = Int_House

    End If

  Else

    If Int_House > Raw_House Then

      Output = Int_House - 2

    Else

      Output = Int_House

    End If

  End If

  ElseIf [ROUTE_FROM_OR_TO] = "TO_END" Then

  Raw_House = ( [TO_HOUSE_NUMBER] - [FROM_HOUSE_NUMBER] ) * ( [meas] - [FROM_MEAS] ) / ( [TO_MEAS] - [FROM_MEAS] ) + [FROM_HOUSE_NUMBER]

  Int_House = Round(( [TO_HOUSE_NUMBER] - [FROM_HOUSE_NUMBER] ) * ( [meas] - [FROM_MEAS] ) / ( [TO_MEAS] - [FROM_MEAS] ) / 2, 0) * 2 + [FROM_HOUSE_NUMBER]

 

  If [FROM_HOUSE_NUMBER] < [TO_HOUSE_NUMBER] Then

    If Int_House < Raw_House Then

      Output = Int_House

    Else

      Output = Int_House - 2

    End If

  Else

    If Int_House > Raw_House Then

      Output = Int_House

    Else

      Output = Int_House + 2

    End If

  End If

  Else

  Output = -1

  End If

Else

  Output = -1

End If

d.      HOUSE_NUMBER = Output (Just type the word Output in the Expression text box)

4.       Select and delete all records where the HOUSE_NUMBER = -1.  These should be the locations where the measures did not change, typically at the ends of the Routes.  Earlier steps should have ensured that the widest range of addresses applied at these locations.

5.       Performed a Join with each of the Address Side Parity summaries (like Add_Left_Even) and selected all records that matched the given SIDE_PARITY value and that have an OBJECTID IS NULL for the joined table (meaning the addresses were not the predominant set for that side).  Delete these records.

6.       The overlay should have included the address range fields from your Centerline data.  The range fields need to be either Long or Double fields, so if your normal ranges are text fields create new fields that are type Long.

7.       Use the following calculation to calculate the L_F_ADD field (which is for the Left From Address field):

a.       Parser:  VB Script

b.      Show Codeblock:  Checked

c.       Pre-Logic Script Code:

If ([SIDE_PARITY] = "LEFT_SIDE, EVEN_HOUSE" OR [SIDE_PARITY] = "LEFT_SIDE, ODD_HOUSE") AND [ROUTE_FROM_OR_TO] = "FROM_END" then

  Output = [HOUSE_NUMBER]

Else

  Output = -1

End If

d.      L_F_ADD = Output (Just put the word Output in the expression text box)

e.      If this is the first time you are doing the calculation save it with the Save Button for each of the range fields.

8.       Use the following calculation to calculate the L_T_ADD field (which is for the Left To Address field):

a.       Parser:  VB Script

b.      Show Codeblock:  Checked

c.       Pre-Logic Script Code:

If ([SIDE_PARITY] = "LEFT_SIDE, EVEN_HOUSE" OR [SIDE_PARITY] = "LEFT_SIDE, ODD_HOUSE") AND [ROUTE_FROM_OR_TO] = "TO_END" then

  Output = [HOUSE_NUMBER]

Else

  Output = -1

End If

d.      L_T_ADD = Output (Just put the word Output in the expression text box)

e.      If this is the first time you are doing the calculation save it with the Save Button for each of the range fields.

9.       Use the following calculation to calculate the R_F_ADD field (which is for the Right From Address field):

a.       Parser:  VB Script

b.      Show Codeblock:  Checked

c.       Pre-Logic Script Code:

If ([SIDE_PARITY] = "RIGHT_SIDE, EVEN_HOUSE" OR [SIDE_PARITY] = "RIGHT_SIDE, ODD_HOUSE") AND [ROUTE_FROM_OR_TO] = "FROM_END" then

  Output = [HOUSE_NUMBER]

Else

  Output = -1

End If

d.      R_F_ADD = Output (Just put the word Output in the expression text box)

e.      If this is the first time you are doing the calculation save it with the Save Button for each of the range fields.

10.       Use the following calculation to calculate the R_T_ADD field (which is for the Right To Address field):

a.       Parser:  VB Script

b.      Show Codeblock:  Checked

c.       Pre-Logic Script Code:

If ([SIDE_PARITY] = "RIGHT_SIDE, EVEN_HOUSE" OR [SIDE_PARITY] = "RIGHT_SIDE, ODD_HOUSE") AND [ROUTE_FROM_OR_TO] = "TO_END" then

  Output = [HOUSE_NUMBER]

Else

  Output = -1

End If

d.      R_T_ADD = Output (Just put the word Output in the expression text box)

e.      If this is the first time you are doing the calculation save it with the Save Button for each of the range fields.

11.       You can use the Display Route Events context menu item on the output to create a point event layer showing the centerline segment end points.  If you calculated a FROM_DISTANCE and TO_DISTANCE field in your Address lines, use that value for the side offset.  The layer coloring can import the settings of the Address points

 

 

8.       Here is how my particular layer looked:

Creating Address Ranges on the Centerlines

1.       Create Address Range fields in the CL_Lines event table which are type Long.  The field names I used were L_F_ADD_LONG, L_T_ADD_LONG, R_F_ADD_LONG and R_T_ADD_LONG.  Calculate the initial value of all of these fields to -1.

2.       Perform a summary of the overlay table to get the transferrable house numbers at the centerline segment ends that were actually between the address points using the Summary Statistics tool with the following settings:

a.       Summary fields

L_F_ADD Max

L_T_ADD Max

R_F_ADD Max

R_T_ADD Max

b.      Case Fields

RID

ORIG_FID

3.       Join the CL_LINES table as the master table to the summary output as the join table and select the records where the join records actually exist.  Then transfer the result to the CL_Lines table with the Field Calculator.

4.       Create a line layer from the CL_Lines table using the Display Route Events and put the following definition query on it to only show the fully addressed lines:

a.       "L_F_ADD_LONG" > -1 AND "L_T_ADD_LONG" > -1 AND "R_F_ADD_LONG" > -1 AND "R_T_ADD_LONG" > -1

5.       Create another line layer from the CL_Lines table using the Display Route Events and put the following definition query on it to only show the partially addressed lines:

a.       ("L_F_ADD_LONG" > -1 OR "L_T_ADD_LONG" > -1 OR "R_F_ADD_LONG" > -1 OR "R_T_ADD_LONG" > -1) AND NOT ("L_F_ADD_LONG" > -1 AND "L_T_ADD_LONG" > -1 AND "R_F_ADD_LONG" > -1 AND "R_T_ADD_LONG" > -1)

6.       Rename the two layers to make it clear what they are showing.

7.       Make the two line event layers distinct colors from each other and adjust the symbology settings to make them stand out.

 

 

8.       Here is the result of how I displayed my two event layers with dark red lines being fully addressed centerlines and pink lines being partially addressed centerlines.  The grey lines have no address and are the original centerlines used as background layer.

9.       Join the CL_Lines table as the primary table to the Add_Left_Even table on the common RID fields.

10.   Select the records where Add_Left_Even.OBJECTID >  -1 AND CL_Lines.L_F_ADD_LONG = -1

 

 

11.   Calculate the L_F_ADD field with the following calculation:

a.       Parser:  VB Script

b.      Show Codeblock:  Checked

c.       Pre-Logic Script Code:

Min_Meas = [CL_LINES.MIN_MEAS]

Asc_Desc = [Add_Left_Even.ASC_DESC]

Min_Min_Meas = [Add_Left_Even.MIN_FROM_MEAS]

Max_Max_Meas = [Add_Left_Even.MAX_TO_MEAS]

House_Interval = [Add_Left_Even.HOUSE_INTERVAL]

Min_From_House_Number = [Add_Left_Even.MIN_FROM_HOUSE_NUMBER]

Max_To_House_Number = [Add_Left_Even.MAX_TO_HOUSE_NUMBER]

Max_From_House_Number = [Add_Left_Even.MAX_FROM_HOUSE_NUMBER]

Min_To_House_Number = [Add_Left_Even.MIN_TO_HOUSE_NUMBER]

 

If Asc_Desc = "Ascending" AND Min_Meas < Min_Min_Meas Then

  Raw = (Min_Meas - Min_Min_Meas) / House_Interval + Min_From_House_Number

  Output = Round((Min_Meas - Min_Min_Meas) / House_Interval/2, 0) * 2 + Min_From_House_Number

  If Output < Raw Then Output = Output + 2

ElseIf Asc_Desc = "Ascending" AND Min_Meas > Max_Max_Meas Then

  Raw = (Min_Meas - Max_Max_Meas) / House_Interval + Max_To_House_Number

  Output = Round((Min_Meas - Max_Max_Meas) / House_Interval/2, 0) * 2 + Max_To_House_Number

  If Output < Raw Then Output = Output + 2

ElseIf Asc_Desc = "Descending" AND Min_Meas < Min_Min_Meas Then

  Raw = (Min_Meas - Min_Min_Meas) / House_Interval + Max_From_House_Number

  Output = Round((Min_Meas - Min_Min_Meas) / House_Interval/2, 0) * 2 + Max_From_House_Number

  If Output > Raw Then Output = Output - 2

ElseIf Asc_Desc = "Descending" AND Min_Meas > Max_Max_Meas Then

  Raw = (Min_Meas - Max_Max_Meas) / House_Interval + Min_To_House_Number

  Output = Round((Min_Meas - Max_Max_Meas) / House_Interval/2, 0) * 2 + Min_To_House_Number

  If Output > Raw Then Output = Output - 2

Else

  Output = -1

End If

d.      L_F_ADD = Output (Just type the word Output in the expression text box)

12.   Select the records where Add_Left_Even.OBJECTID >  -1 AND CL_Lines.L_T_ADD_LONG = -1

13.   Calculate the L_T_ADD field with the following calculation:

a.       Parser:  VB Script

b.      Show Codeblock:  Checked

c.       Pre-Logic Script Code:

Max_Meas = [CL_Lines.MAX_MEAS]

Asc_Desc = [Add_Left_Even.ASC_DESC]

Min_Min_Meas = [Add_Left_Even.MIN_FROM_MEAS]

Max_Max_Meas = [Add_Left_Even.MAX_TO_MEAS]

House_Interval = [Add_Left_Even.HOUSE_INTERVAL]

Min_From_House_Number = [Add_Left_Even.MIN_FROM_HOUSE_NUMBER]

Max_To_House_Number = [Add_Left_Even.MAX_TO_HOUSE_NUMBER]

Max_From_House_Number = [Add_Left_Even.MAX_FROM_HOUSE_NUMBER]

Min_To_House_Number = [Add_Left_Even.MIN_TO_HOUSE_NUMBER]

 

If Asc_Desc = "Ascending" AND Max_Meas < Min_Min_Meas Then

  Raw = (Max_Meas - Min_Min_Meas) / House_Interval + Min_From_House_Number

  Output = Round((Max_Meas - Min_Min_Meas) / House_Interval/2, 0) * 2 + Min_From_House_Number

  If Output > Raw Then Output = Output - 2

ElseIf Asc_Desc = "Ascending" AND Max_Meas > Max_Max_Meas Then

  Raw = (Max_Meas - Max_Max_Meas) / House_Interval + Max_To_House_Number

  Output = Round((Max_Meas - Max_Max_Meas) / House_Interval/2, 0) * 2 + Max_To_House_Number

  If Output > Raw Then Output = Output - 2

ElseIf Asc_Desc = "Descending" AND Max_Meas < Min_Min_Meas Then

  Raw = (Max_Meas - Min_Min_Meas) / House_Interval + Max_From_House_Number

  Output = Round((Max_Meas - Min_Min_Meas) / House_Interval/2, 0) * 2 + Max_From_House_Number

  If Output < Raw Then Output = Output + 2

ElseIf Asc_Desc = "Descending" AND Max_Meas > Max_Max_Meas Then

  Raw = (Max_Meas - Max_Max_Meas) / House_Interval + Min_To_House_Number

  Output = Round((Max_Meas - Max_Max_Meas) / House_Interval/2, 0) * 2 + Min_To_House_Number

  If Output < Raw Then Output = Output + 2

Else

  Output = -1

End If

d.      L_T_ADD = Output (Just type the word Output in the expression text box)

14.   Break the Join

15.   Join the CL_Lines table as the primary table to the Add_Left_Odd table on the common RID fields.

16.   Repeat steps 9 through 13 changing all references to the Add_Left_Even table to the Add_Left_Odd table.

17.   Join the CL_Lines table as the primary table to the Add_Right_Even table on the common RID fields.

18.   Repeat steps 9 through 13 changing all references to the Add_Left_Even table to the Add_Right_Even table and the references to L_F_ADD to R_F_ADD and the references to L_T_ADD to R_T_ADD.

19.   Join the CL_Lines table as the primary table to the Add_Right_Odd table on the common RID fields.

20.   Repeat steps 9 through 13 changing all references to the Add_Left_Even table to the Add_Right_Odd table and the references to L_F_ADD to R_F_ADD and the references to L_T_ADD to R_T_ADD.

 

 

21.   This shows the result for my data in the 2 layers that were set up in step 7 of this section:

22.   The remaining roads that have been addressed on one side of the road you can simply be duplicate the address ranges of the addressed side over to the unassigned side with a one house adjustment for parity.

23.   Select all CL_Lines records where "L_F_ADD " <> -1 AND "L_T_ADD " <> -1 AND "R_F_ADD " = -1 AND "R_T_ADD " = -1

24.   Calculate the R_F_ADD to be equal to [L_F_ADD] + 1

25.   Calculate the R_T_ADD to be equal to [L_T_ADD] + 1

26.   Select all CL_Lines records where "R_F_ADD " <> -1 AND "R_T_ADD " <> -1 AND "L_F_ADD " = -1 AND "L_T_ADD " = -1

27.   Calculate the L_F_ADD to be equal to [R_F_ADD] - 1

28.   Calculate the L_T_ADD to be equal to [R_T_ADD] – 1

29.   The remaining roads that have -1 for the address ranges there was either no house numbers or no more than one house number on any side of the road.

 

 

30.   Here is the map showing the final assignment of ranges to all roads that could be addressed based on actual address points:

31.   The address ranges should be inspected for any oddities, such as negative house number (other than -1).  This could indicate that a bad range was not deleted previously from the Full event tables or that an anomalous house number was not deal with correctly during previous validation steps.

32.   Select the records where there is a wide spread between the sides.  This could indicate that one side had too few addresses near the ends of the road and the house interval was too large or too small relative to the side with more addresses or some other unusual addressing pattern for that particular road.  Select these records using a query like this:

ABS(CL_Lines.L_F_ADD - CL_Lines.R_F_ADD) > 200 OR ABS(CL_Lines.L_T_ADD - CL_Lines.R_T_ADD) > 200

33.   It may be best to select all parts of these records and return to step 10 to get only actual address values and then manually fix and fill in these ranges in an edit session.

 

34.   Once the records are validated join the the original Centerlines as the primary table and the CL_Lines table as the join table on the Orig_FID field and calculate the final ranges into the centerline’s house number fields.  If you prefer you can select only the records with house numbers greater than -1 and just calculate over those address ranges.