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")

Yes the line buffer_distance = (SELECT MAX("Distance") FROM "distanceTable") is not valid syntax — jbchurchill
– jbchurchill, Commented Aug 8, 2017 at 19:34
It's not valid Python syntax, and a SQL subquery is not applicable to a dBase table. — Vince
– Vince, Commented Aug 8, 2017 at 20:00

Emil Brundage · Accepted Answer · 2017-08-08 20:58:37Z

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)

Marc Pfister · Accepted Answer · 2017-08-08 20:09:22Z

0

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()

answered Aug 8, 2017 at 20:09

Marc Pfister

4,14718 silver badges11 bronze badges

Add a comment |

JasonInVegas · Accepted Answer · 2017-08-08 20:23:48Z

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(.....)

Stack Exchange Network

Using an SQL Query in arcpy

3 Answers 3

Your Answer

Hot Network Questions

Using an SQL Query in arcpy

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions