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.

enter image description here

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.

• Dan C: They can, but this is more of an SQL question than a GIS question.
• LandArch: They second half can be done through Field Calculator, I just have to get the Python Coding right.
• PolyGeo: If this is a question about the ArcGIS Field Calculator and its Python Parser then I think you should edit it to make that clear and avoid it getting closed for being too broad (by asking two questions).
• Dan C: I think you'll have an easier time with this if you do it on the back end, in the SQL db that stores the features. You could set up an update query to run daily or however often to calculate these values and you don't have to think about it again. You may also be able to create it as a 'computed column', which would automatically be populated with the correct values as you edit the layer. Talk to your db admin.
• LandArch: @Dan C I don't disagree with you. Unfortunately we don't have a database administrator. I guess that I am it. I figured out enough to set up the PostgreSQL so that we could use the data in the field in the ARCGIS Collector App on a tablet. I am thinking that Python and field calculator may be my better option only so that I am learning something that i can use later on.

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.

• LandArch: Thanks, this worked to calculate the bonnet color, any thoughts on calculating the other part of the if?
• Dan C: @LandArch Sorry, I thought the question was saying that that part was taken care of. I'll add that.

Answer 2

This can definately be done in ArcGIS without the need to in excel. If these fields (Static Pressure, Residual Pressure, and Flow Rate) are already in a dataset, then we can use the following cursors to add the new fields and update them:

import arcpy
ds = r"path/to/dataset" 
with arcpy.da.UpdateCursor(ds, ["Fire_Flow", "Colour"]) as ucursor:
 with arcpy.da.SearchCursor(ds, ["Static", "Residual", "Flow", "Fire_Flow"]) as scursor:
 for urow in ucursor:
 for srow in scursor:
 urow[0] = "{}" * (("{}"-20)/("{}"-"{}"))**0.54.format(srow[2], srow[0], srow[0], srow[1])
 ucursor.updateRow(urow)
 if srow[3] <= 0:
 urow[1] = "Black"
 if srow[3] >= 1 and srow[3] <= 499:
 urow[1] = "Red"
 if srow[3] > 499 and srow[3] <= 999:
 urow[1] = "Orange"
 if srow[3] > 999 and srow[3] <= 1499:
 urow[1] = "Green"
 if srow[3] > 1499:
 urow[1] = "Blue"
 ucursor.updateRow(urow)

• LandArch: I dont want to add the columns, I just want to update then when the testing of Static, Residual, and Flow is conducted and number added.
• MacroZED: no problem, you can just remove that. ill edit the script to reflect this. The script will need to be rerun every time the Static, Residual or Flow values change. But this method will be quicker then Calculate field.
• LandArch: I get the following error, does the [#] have to be changed since they are not row 1 through 3 in the attribute table? Runtime error Traceback (most recent call last): File "<string>", line 5, in <module> RuntimeError: cannot open 'path/to/dataset'
• MacroZED: No. the error is because you haven't specified the dataset. The variable "ds" needs to be changed to your actual dataset.
• LandArch: Sorry for the hand holding, I need to include the physical path, e.g C:/....