4

I am trying to automate a field calculation using ModelBuilder, using a GDB table to populate a feature class field given two conditions:

  1. Traffic Count Value and
  2. Posted Speed limit

I have created an IF statement via the field calculator but my manager does not want to have to rely on my VBA code if the table changes. The goal being changes to the table must be made in the table and updated via the table.

I am using ArcGIS Desktop 10.3 and ModelBuilder and Excel.

Example: If PSL is between 91-100 and Traffic Count is 500-999 the value is 1

Can some sort of vlookup function work?

  • 2
    So you want the calculation to be done on the GDB table? Is all code off limits i.e. python? Also, even if you have a tool instead of code, if the structure of the table changes it will still break. – Rex Jul 5 '18 at 19:20
  • I am open to python, I am hoping the table stays the same # of c and # of rows but the values within can change. Currently trying to work with an update cursor – Pat the forest Jul 6 '18 at 14:32
  • 1
    @Pattheforest if your reference data are in Excel and you need to maintain/modify them in Excel, I would look at joining the Excel file to your feature class. How To: Join an Excel spreadsheet (.xls) to a feature class in ArcMap. Or do you mean that you are moving data from GDB table to Excel and then back? Might be good if you post samples of your GDB table and feature class tables. – Andy Nov 28 '18 at 14:02
3

You can use python.

Lookup table: Note that all intervals need to have a start and stop, with a '-' sign between them. So "53000-" need to be "53000-100000": enter image description here

import arcpy

lookuptable = r"D:\lookuptable.csv"

lookupview = 'lookupview'
arcpy.MakeTableView_management(in_table=lookuptable, out_view=lookupview)

columnlist = [f.name for f in arcpy.ListFields(lookupview)]
rowlist = [r[0] for r in arcpy.da.SearchCursor(lookupview,columnlist)]
del columnlist[0]

lookupvalues = [row for row in arcpy.da.SearchCursor(lookupview,columnlist)]

def givemearange(r):
    #'1-5' -> range(1,6)
    try:
        start,stop = r.split('-')
        return range(int(start),int(stop)+1)
    except:
        pass

def givemecolumnindex(columnvalue):
    columnindex = [i for i in range(len(columnlist)) if columnvalue in givemearange(columnlist[i])][0]
    return columnindex
def givemerowindex(rowvalue):
    rowindex = [i for i in range(len(rowlist)) if rowvalue in givemearange(rowlist[i])][0]
    return rowindex

You now can do:

lookupvalues[givemerowindex(4000)][givemecolumnindex(85)]
>>>9

enter image description here

Then use the da.UpdateCursor to read and write the values.

with arcpy.da.UpdateCursor(somefc,['Trafficcountfield','Speedlimitfield','field_to_calculate']) as cursor:
    for row in cursor:
        row[2] = lookupvalues[givemerowindex(row[0])][givemecolumnindex([row[1]])]
        cursor.updateRow(row)
-1

The very basic solution is creating polygons from your break points. After that you can make XY layer from 2 relevant inputs and query polygons at points by using spatial join or similar tool. ArcGIS will complain about missing projection, who cares.

Picture below shows flood risk polygons I am using, where lookup values are flood depth and velocity:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.