[GIS] Performing multiple calculations in ArcGIS Attribute Table

Question

I have an existing Excel spreadsheet developed to track specific fire hydrant data, this is periodically updated and joined to the spatial hydrant data in ArcGIS. In the spreadsheet there is a number of columns to enter data related to Static Pressure, Residual Pressure, and Flow Rate.

Utilizing the Rated Capacity at 20 PSI in a Fire Flow Test formula to calculate fire flow, the spreadsheet automatically calculates. The calculation is as follows.

Fire Flow = Flow * ((Static - 20)/(Static - Residual))^0.54

I am not sure the best way to attack this and am looking for suggestions. Based upon the results of the calculation, I have an additional column with an IF statement that indicates the appropriate fire flow color the hydrant should be painted. For those that do not know, the hydrant color is an indicator to fire personnel in an emergency the fire flow conditions. I hav e done some research and developed the following python script:

def Reclass !Bon_Color!:
     if ( !Fire_Flow! <= 0):
       return Black
     elif ( !Fire_Flow! >= 1 and !Fire_Flow! <= 499):
       return  Red
     elif ([Fire_Flow] > 499 and [Fire_Flow] <= 999):
       return Orange
     elif ( !Fire_Flow! > 999 and !Fire_Flow! <= 1499):
         return Green
     elif ( !Fire_Flow! > 1499):
         return Blue
  end if

I would like to know if the calculations mentioned above, used in the Excel spreadsheet could be replicated in the database attribute table utilizing the field calculator and the python-parser? Rather than relying on the Excel spreadsheet and the need to rejoin the data.

Answer 1

Your python code makes sense but has a few errors. Paste this into the "Pre-logic script code" box in field calculator:

def Reclass (fire_flow):
     if (fire_flow <= 0):
       return "Black"
     elif (fire_flow >= 1 and fire_flow <= 499):
       return "Red"
     elif (fire_flow > 499 and fire_flow <= 999):
       return "Orange"
     elif (fire_flow > 999 and fire_flow <= 1499):
       return "Green"
     elif (fire_flow > 1499):
       return "Blue"

Then in the box below that, paste:

Reclass (!Fire_Flow!)

The indentation in that top block is a bit unusual, but the exact amount of indentation doesn't matter as long as the lines are indented correctly relative to each other.

The errors:

def Reclass !Bon_Color!:

When you define a function, you need to follow it with a list of parameters the function uses to do its work. The list should be in parentheses. In your case you are only using one input parameter, your Fire_Flow number.

if ( !Fire_Flow! <= 0):
   return Black

You are going to pass !Fire_Flow! into the function, once you're in the function that value is assigned to the variable fire_flow, so refer to that variable instead. Also, you need to put Black in quotes, so a string is returned. The way you have it here, your script is looking for a variable named Black to return, and it doesn't exist.

end if

You don't need end if in Python.

For your first field, the fire flow number, you need to name your fields appropriately and the operator for exponents in Python is **, not ^.paste this into the bottom box in the field calculator:

!Fire_Flow! = !Flow! * ((!Static! - 20)/(!Static! - !Residual!))**0.54

If you need to update more than one field simultaneously, I agree with MacroZED that an update cursor is the better way, but those can be a little confusing if you're new to Python.