Using an SQL Query in arcpy

Question

I am trying to use arcpy to buffer a file geodatabase feature class and want to set the buffer_distance to the maximum value of a field in a separate dBase table.

My code is:

in_features = "projectArea"
out_feature_class = "bufferedProjectArea"
buffer_distance = (SELECT MAX("Distance") FROM "distanceTable")
line_side = "FULL"
line_end_type = "ROUND"
dissolve_option = "ALL"
arcpy.Buffer_analysis(in_features, out_feature_class, buffer_distance, line_side, line_end_type, dissolve_option)

My distanceTable is a dbase table (.dbf) looks like this:

OBJECTID Type Distance
1 Raccoons 2100
2 Squirrels 0
12 Cities 4200

I am trying to buffer by the maximum value in the distance field of my distanceTable. In this case it is cities, but I want to make the code robust so that when I change the values, the buffer distance is still pointing to the max value. I am receiving generic "syntax error" and am looking for assistance for the proper SQL code to replace for:

buffer_distance = (SELECT MAX("Distance") FROM "distanceTable")

• jbchurchill: Yes the line buffer_distance = (SELECT MAX("Distance") FROM "distanceTable") is not valid syntax
• Vince: It's not valid Python syntax, and a SQL subquery is not applicable to a dBase table.

Answer 1

You can use a cursor to find the max distance. Then apply the distance to your buffer.

in_features = "projectArea"
out_feature_class = "bufferedProjectArea"
line_side = "FULL"
line_end_type = "ROUND"
dissolve_option = "ALL"
#set buffer distance to None
buffer_distance = None
#create cursor
with arcpy.da.SearchCursor ("distanceTable", "Distance") as curs:
 #iterate cursor
 for dist, in curs:
 #check if value is greater than current greatest value
 #update if greater
 if dist > buffer_distance: buffer_distance = dist
arcpy.Buffer_analysis(in_features, out_feature_class, buffer_distance, line_side, line_end_type, dissolve_option)

You can also make use of a numpy array:

in_features = "projectArea"
out_feature_class = "bufferedProjectArea"
line_side = "FULL"
line_end_type = "ROUND"
dissolve_option = "ALL"
#get max value
buffer_distance = arcpy.da.TableToNumPyArray ("distanceTable", "Distance")["Distance"].max ()
arcpy.Buffer_analysis(in_features, out_feature_class, buffer_distance, line_side, line_end_type, dissolve_option)

Answer 2

You might be able to convert the table to a numpy array:

import numpy
...
np = arcpy.da.TableToNumPyArray("distanceTable", ["Distance"])
buffer_distance= np["Distance"].max()

Answer 3

I believe you would benefit from a function return to accomplish it....but this is for clarity more than for efficiency

import arcpy, dbf
def get_max_value(afield_name, my_dbf_table):
 ## open the table and loop through values
 db = dbf.Dbf(my_dbf_table)
 biggest_value = 0
 for rec in db:
 if rec[afield_name] > biggest_value:
 biggest_value = rec[afield_name]
 return biggest_value
## Now I can buffer....
in_features = "projectArea"
out_feature_class = "bufferedProjectArea"
buffer_distance = get_max_value("Distance","distanceTable")
line_side = "FULL"
line_end_type = "ROUND"
dissolve_option = "ALL"
arcpy.Buffer_analysis(.....)