Field calculations in Python

[ShaunDwyer]

Hi there,

I have a simple point feature class called 'drillholes' in an 9.3.1 ArcSDE database.  This feature class has two numeric fields; one called 'GRND_ELEV' which is fully populated, and 'REF_ELEV' which is only partially populated.  I need to create a python script which will select all of the rows where 'REF_ELEV' is not populated and then assign the respective value of 'GRND_ELEV' to those rows.  I have used model builder with the 'Calculate Field' tool as a starting point and used the expression REF_ELEV = [GRND_ELEV] where [REF_ELEV] is 'NULL'.  Running this script generates the following error: File "D:\scripts\DH_Update\TEST.py" line 22, in <module> gp.calculateFieldManagement<GIS_ADMIN_DRILLHOLES__2_, "REF_ELEV"' "[GRND_ELEV] where [REF_ELEV] is 'NULL'", "VB", ""> arcgisscripting.ExecuteError: ERROR 999999: Error executing function.  Expected end of statement.  Failed to execute <CalculateField>.

Any thoughts or directions on where I'm going wrong?

# ---------------------------------------------------------------------------
# TEST.py
# Created on: Wed Jul 11 2012 02:56:01 PM
#   (generated by ArcGIS/ModelBuilder)
# ---------------------------------------------------------------------------

# Import system modules
import sys, string, os, arcgisscripting

# Create the Geoprocessor object
gp = arcgisscripting.create()

# Load required toolboxes...
gp.AddToolbox("D:/Program Files (x86)/ArcGIS/ArcToolbox/Toolboxes/Data Management Tools.tbx")

# Local variables...
GIS_ADMIN_Drillholes = "Database Connections\\GIS_PROD_ADMIN.sde\\GIS.ADMIN.GROUNDWATER\\GIS.ADMIN.Drillholes"
GIS_ADMIN_Drillholes__2_ = "Database Connections\\GIS_PROD_ADMIN.sde\\GIS.ADMIN.GROUNDWATER\\GIS.ADMIN.Drillholes"

# Process: Calculate Field...
gp.CalculateField_management(GIS_ADMIN_Drillholes__2_, "REF_ELEV", "[GRND_ELEV] where [REF_ELEV] is 'NULL'", "VB", "")

[MarcinGasior · Accepted solution]

You don't really need to create model or script to calculate this.
Just use Field Calculator on REF_ELEV field:
Pre-Logic VBA Script Code:

Dim outRef as double If Not IsNull( [REF_ELEV] ) Then   outRef = [REF_ELEV]  Else   outRef = [GRND_ELEV]  End If

REF_CODE =
outRef

Here're some results:
[ATTACH=CONFIG]15945[/ATTACH]


If you prefer Python, it can be done in Calculate Field tool.
Code Block:

def fillGaps(grnd, ref):   if ref:     return ref   else:     return grnd

Expression:
fillGaps( !GRND_ELEV!, str(!REF_ELEV!))

I had a problem with passing NULL values from REF_ELEV field to function, but converting it to string str() works well.
[ATTACH=CONFIG]15947[/ATTACH]

[ShaunDwyer]

Thanks for the help Marcin.  The process needs to be run every night hence the need for it to be a script rather than using the field calculator in ArcMap. Your code for the calculate field tool worked perfectly.  Here's the resulting Python script:

# ---------------------------------------------------------------------------
# TEST.py
# Created on: Thu Jul 12 2012 08:48:42 AM
#   (generated by ArcGIS/ModelBuilder)
# ---------------------------------------------------------------------------

# Import system modules
import sys, string, os, arcgisscripting

# Create the Geoprocessor object
gp = arcgisscripting.create()

# Load required toolboxes...
gp.AddToolbox("D:/Program Files (x86)/ArcGIS/ArcToolbox/Toolboxes/Data Management Tools.tbx")


# Local variables...
GIS_ADMIN_Drillholes = "Database Connections\\GIS_TEST_ADMIN.sde\\GIS.ADMIN.GROUNDWATER\\GIS.ADMIN.Drillholes"
GIS_ADMIN_Drillholes__2_ = "Database Connections\\GIS_TEST_ADMIN.sde\\GIS.ADMIN.GROUNDWATER\\GIS.ADMIN.Drillholes"

# Process: Calculate Field...
gp.CalculateField_management(GIS_ADMIN_Drillholes__2_, "REF_ELEV", "fillGaps(!GRND_ELEV!,str(!REF_ELEV!))", "PYTHON_9.3", "def fillGaps(grnd, ref):\\n  if ref:\\n   return ref\\n  else:\\n   return grnd")