2

While working with SearchCursor, I'm trying to specify the SQL where_clause from a table called "Parcel" within the featureClass, and I want the MAX and MIN value from the column LAND_VALUE. Below is what I try just for MAX.

import arcpy

feature_class = "C:\foo.ext"
where_clause = "SELECT MAX(LAND_VALUE) AS MaxLandVal from Parcel"

rows = arcpy.SearchCursor(feature_class, where_clause, "", "", "LAND_VALUE D")

I'm told an invalid SQL statement was used. However, I'm not sure if the SQL statement is invalid, or if perhaps I'm stating the SQL query within SearchCursor incorrectly. Despite using the reference material for SearchCursor, SQL MAX() and SQL MIN(), and despite referencing a few different examples, I can't get this to work!

Finally, how would I display MaxLandVal if the query were to be correct?

Perhaps something like...?

for blah in rows:
    print blah.MaxLandVal
0

3 Answers 3

3

When using SQL statements to restrict your cursors, you don't need to (and presumably cannot) use the full syntax. From the help it mentions

SELECT * FROM forms the first part of the SQL expression and is automatically supplied for you.

For instance, if you wanted to select features from a shapefile with a FID > 1000, your SQL statement would be:"\"FID\">1000" (As when using it in ArcMap, the quotations around the field names are necessary, and therefore are escaped with the backslash character. You could also write it as """"FID">1000""", but it's really user preference. I prefer the former as it's easier for me to remember to escape each quotation mark rather than remember how many I need.)

In you case, I wouldn't bother with using SQL clause at all. Write all your values to a list and then use Python's built-in min() and max() functions to return the values.

If you are using ArcMap 10.1, I would recommend you switch to the Data Access module for your cursors as they are faster and handle releasing locks better than the original cursors. Syntactically, they are fairly similar.

1
  • 1
    You can also do '"FID" > 1000' Using ' around the string to avoid escaping.
    – Nathan W
    Jun 25, 2013 at 4:28
1

A solution is to take advantage of using the numpy array. You can convert your Featureclass field into an array and query that for minimum and maximum values, an example is shown below querying a distance field.

import arcpy
try:
    array = arcpy.da.FeatureClassToNumPyArray("fcCommunity_to_nearest_District",["Distance"])
    minVal = array["Distance"].min()
    maxVal = array["Distance"].max()
    print("Minimum = " + str(minVal))
    print("Maximum = " + str(maxVal))
except arcpy.ExecuteError:
    # Geoprocessor threw an error
    print(arcpy.GetMessages(2))
finally:
    # clean up by deleting array
    del array
0

Another solution is to use the Summary Statistics tool to retrieve the minimum and maximum value. This would create a table of just 1 row that you would query. This could be done easily in model builder but also as a few lines of python code as shown below:

import arcpy
try:
    # Extract Min and Max into a temporary in_memory table, in this example we are querying a field called Distance
    arcpy.Statistics_analysis(in_table="fcCommunity_to_nearest_District", out_table="in_memory/tblMinMax", statistics_fields="Distance MIN;Distance MAX", case_field="")
    with arcpy.da.SearchCursor("in_memory/tblMinMax",["MIN_Distance","MAX_Distance"]) as cursor:
        for row in cursor:
            minVal = row[0]
            maxVal = row[1]
    print("Minimum = " + str(minVal))
    print("Maximum = " + str(maxVal))
except arcpy.ExecuteError:
    # Geoprocessor threw an error
    print(arcpy.GetMessages(2))
finally:
    # clean up by deleting in_memory table
    arcpy.Delete_management("in_memory/tblMinMax")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.