1

I saw a lot of posts about how you select maximum value in the particular field and I've been trying to use that query in a python code, but none of the posts really helped me solve my problem. There is a problem that I always get an error saying that I have an invalid expression.

I've been using:

arcpy.Select_analysis (input_layer, output_layer,"Shape_Area = (SELECT MAX( Shape_Area)" FROM layer_name)

I'm so confused as this query sometimes works and sometimes not, even in the attribute table or if I'm trying to use a tool Select, Table Select etc. Can anyone help me? Or is it there some other way how to identify the maximum value in the field.

  • 2
    That Python string is not valid. It should not ever work. Please Edit the question to contain the actual code. You should also specify the data storage format. Testing for equivalence on floating-point values can also be fraught with danger. – Vince Dec 4 '17 at 11:38
1

Have you seen SQL reference for query expressions used in ArcGIS, subqueries section?. Depending on your data not all queries are supported:

Coverages, shapefiles, and other nongeodatabase file-based data sources do not support subqueries. Subqueries that are performed on versioned enterprise feature classes and tables will not return features that are stored in the delta tables. File geodatabases provide the limited support for subqueries explained in this section, while enterprise geodatabases provide full support. For information on the full set of subquery capabilities of enterprise geodatabases, refer to your DBMS documentation.

It could explain why it sometimes works for you if you have different inputs.

You can do it without using ArcGIS selection tools if that is an option for you. This will find max value in a specified column and print out ObjectID:

import arcpy, operator
fc = r'C:\Default.gdb\featureclass123' #Change to match your data

#Build a dictionary of objectids and values:
all_entries = {key:value for (key, value) in arcpy.da.SearchCursor(fc,['OID@','VALUECOLUMN'])} #Change VALUECOLUMN to the name of your column

#Find max value and print out objectid
print max(all_entries.iteritems(), key=operator.itemgetter(1))[0]

The objectid can of course be passed on to Select By Attributes if you want to select the row:

arcpy.MakeFeatureLayer_management(in_features=fc, out_layer='fc_lyr')
sql = '{0} = {1}'.format(arcpy.AddFieldDelimiters(datasource=fc, field=arcpy.Describe(fc).OIDFieldName), max(all_entries.iteritems(), key=operator.itemgetter(1))[0]) 
arcpy.SelectLayerByAttribute_management(in_layer_or_view='fc_lyr', where_clause=sql)
0

First of all, your query is invalid and sould be:

arcpy.Select_analysis (input_layer, output_layer,"Shape_Area = (SELECT MAX( Shape_Area) FROM layer_name)")

Second, as @bera mentioned, this only works in geodatabse sources.

Another option is to use sql_clause in da.SearchCursor and order your data by Shape_Area and get only the first record. But this is also only available in databases.

I advice you to convert your data to file geodatabase and perform your query. Or try @bera 's solution if you forced to use shapefiles.

TESTS

import arcpy, random, time, os, operator

arcpy.env.overwriteOutput = True
limit = 20000000
sr = arcpy.SpatialReference(3857)
gdb = r'D:\Python\_test\test.gdb'
fc_name = 'test_fc'
fc = os.path.join(gdb, fc_name)

def create_fc():
    arcpy.CreateFeatureclass_management(out_path = gdb, out_name = fc_name,
            geometry_type = 'POINT', spatial_reference = sr)
    arcpy.AddField_management(in_table = fc, field_name = 'TEST_FIELD',
            field_type = 'DOUBLE')

def add_points(number):
    with arcpy.da.InsertCursor(fc, ['SHAPE@','TEST_FIELD']) as ic:
        for x in xrange(number):
        geom = arcpy.PointGeometry(arcpy.Point(random.randint(-limit, limit),random.randint(-limit, limit)), sr)
        attr = random.random()
        ic.insertRow([geom, attr])

def max_cursor_dict():
    all_entries = {key:value for (key, value) in arcpy.da.SearchCursor(fc, ['OID@', 'TEST_FIELD'])}
    return max(all_entries.iteritems(), key=operator.itemgetter(1))[1]

def max_cursor_list():
    all_entries = [row for row in arcpy.da.SearchCursor(fc, ['TEST_FIELD'])]
    return max(all_entries)[0]

def max_cursor_sql():
    with arcpy.da.SearchCursor(fc, ['TEST_FIELD'], sql_clause = (None, 'ORDER BY TEST_FIELD DESC')) as sc:
        row = sc.next()
        return row[0]

def max_select():
    arcpy.Select_analysis(in_features = fc, out_feature_class = r'in_memory\test',
            where_clause = 'TEST_FIELD = (SELECT MAX(TEST_FIELD) FROM {})'.format(fc_name))
    return [row for row in arcpy.da.SearchCursor(r'in_memory\test', ['TEST_FIELD'])][0][0]

def max_statistics():
    arcpy.Statistics_analysis(in_table = fc, out_table = r'in_memory\test',
            statistics_fields = [['TEST_FIELD', 'MAX']])
    return [row for row in arcpy.da.SearchCursor(r'in_memory\test', ['MAX_TEST_FIELD'])][0][0]

Test code:

arcpy.Delete_management(fc)
for quant in [10, 100, 1000, 10000, 100000, 1000000]:
    print 'points quantity: {0}'.format(quant)
    create_fc()
    add_points(quant)
    run = 10

    t_start = time.time()
    for x in xrange(run): maxval = max_cursor_dict()
    print '\tCursor, dictionary                       avg time: {0:.4f}   result: {1:.5f}'.format((time.time() - t_start) / run, maxval)

    t_start = time.time()
    for x in xrange(run): maxval = max_cursor_list()
    print '\tCursor, list                             avg time: {0:.4f}   result: {1:.5f}'.format((time.time() - t_start) / run, maxval)

    t_start = time.time()
    for x in xrange(run): maxval = max_cursor_sql()
    print '\tCursor, SQL               no index       avg time: {0:.4f}   result: {1:.5f}'.format((time.time() - t_start) / run, maxval)

    t_start = time.time()
    for x in xrange(run): maxval = max_select()
    print '\tSelect                    no index       avg time: {0:.4f}   result: {1:.5f}'.format((time.time() - t_start) / run, maxval)
    arcpy.Delete_management(r'in_memory\test')

    t_start = time.time()
    for x in xrange(run): maxval = max_statistics()
    print '\tStatistics                no index       avg time: {0:.4f}   result: {1:.5f}'.format((time.time() - t_start) / run, maxval)
    arcpy.Delete_management(r'in_memory\test')

    arcpy.AddIndex_management(in_table = fc, fields = 'TEST_FIELD',
        index_name = 'idx_test_field', unique = False, ascending = False)

    t_start = time.time()
    for x in xrange(run): maxval = max_cursor_sql()
    print '\tCursor, SQL               with index     avg time: {0:.4f}   result: {1:.5f}'.format((time.time() - t_start) / run, maxval)

    t_start = time.time()
    for x in xrange(run): maxval = max_select()
    print '\tSelect                    with index     avg time: {0:.4f}   result: {1:.5f}'.format((time.time() - t_start) / run, maxval)
    arcpy.Delete_management(r'in_memory\test')

    t_start = time.time()
    for x in xrange(run): maxval = max_statistics()
    print '\tStatistics                with index     avg time: {0:.4f}   result: {1:.5f}'.format((time.time() - t_start) / run, maxval)
    arcpy.Delete_management(r'in_memory\test')

    arcpy.Delete_management(fc)

Results:

points quantity: 10
    Cursor, dictionary                       avg time: 0.0309   result: 0.83274
--->Cursor, list                             avg time: 0.0301   result: 0.83274
    Cursor, SQL               no index       avg time: 0.0330   result: 0.83274
    Select                    no index       avg time: 0.1076   result: 0.83274
    Statistics                no index       avg time: 0.0953   result: 0.83274
    Cursor, SQL               with index     avg time: 0.0312   result: 0.83274
    Select                    with index     avg time: 0.0965   result: 0.83274
    Statistics                with index     avg time: 0.1072   result: 0.83274
points quantity: 100
--->Cursor, dictionary                       avg time: 0.0303   result: 0.98088
    Cursor, list                             avg time: 0.0305   result: 0.98088
    Cursor, SQL               no index       avg time: 0.0333   result: 0.98088
    Select                    no index       avg time: 0.1084   result: 0.98088
    Statistics                no index       avg time: 0.0958   result: 0.98088
    Cursor, SQL               with index     avg time: 0.0324   result: 0.98088
    Select                    with index     avg time: 0.0995   result: 0.98088
    Statistics                with index     avg time: 0.1013   result: 0.98088
points quantity: 1000
--->Cursor, dictionary                       avg time: 0.0335   result: 0.99778
    Cursor, list                             avg time: 0.0336   result: 0.99778
    Cursor, SQL               no index       avg time: 0.0408   result: 0.99778
    Select                    no index       avg time: 0.1128   result: 0.99778
    Statistics                no index       avg time: 0.1090   result: 0.99778
    Cursor, SQL               with index     avg time: 0.0404   result: 0.99778
    Select                    with index     avg time: 0.0988   result: 0.99778
    Statistics                with index     avg time: 0.0994   result: 0.99778
points quantity: 10000
    Cursor, dictionary                       avg time: 0.0656   result: 0.99999
--->Cursor, list                             avg time: 0.0653   result: 0.99999
    Cursor, SQL               no index       avg time: 0.1113   result: 0.99999
    Select                    no index       avg time: 0.2493   result: 0.99999
    Statistics                no index       avg time: 0.1326   result: 0.99999
    Cursor, SQL               with index     avg time: 0.0958   result: 0.99999
    Select                    with index     avg time: 0.0981   result: 0.99999
    Statistics                with index     avg time: 0.1341   result: 0.99999
points quantity: 100000
    Cursor, dictionary                       avg time: 0.3834   result: 0.99999
    Cursor, list                             avg time: 0.3903   result: 0.99999
    Cursor, SQL               no index       avg time: 0.6812   result: 0.99999
    Select                    no index       avg time: 1.6016   result: 0.99999
    Statistics                no index       avg time: 0.4728   result: 0.99999
    Cursor, SQL               with index     avg time: 0.6815   result: 0.99999
--->Select                    with index     avg time: 0.0984   result: 0.99999
    Statistics                with index     avg time: 0.4653   result: 0.99999
points quantity: 1000000
    Cursor, dictionary                       avg time: 3.6359   result: 1.00000
    Cursor, list                             avg time: 3.5690   result: 1.00000
    Cursor, SQL               no index       avg time: 7.0856   result: 1.00000
    Select                    no index       avg time: 15.293   result: 1.00000
    Statistics                no index       avg time: 3.7506   result: 1.00000
    Cursor, SQL               with index     avg time: 6.9865   result: 1.00000
--->Select                    with index     avg time: 0.0947   result: 1.00000
    Statistics                with index     avg time: 3.8642   result: 1.00000

Conclusion.
Small dataset (less than 100k rows): use cursor and python sort functions
Large dataset: indexes + SQL.
Statistics_analysis and da.Cursors doesn't use attribute indexes.

  • "... shapefiles... don't support subqueries". And yes, as I said, your solution will work with shapefiles, but probably slower than SQL. Should be tested:) Cursors run faster on small sources as I remember. – Serge Norin Dec 4 '17 at 16:32
0

what Serge wrote is very helpful. Although it wasn't tested against Python state of the art for numerical computing aka numpy so here:

points quantity: 100000
    Cursor, dictionary                       avg time: 0.1184   result: 1.00000
    Cursor, list                             avg time: 0.1073   result: 1.00000
 -->Numpy Array                              avg time: 0.1019   result: 1.00000
    Numpy Array ravel                        avg time: 0.1027   result: 1.00000
    Cursor, SQL                              avg time: 0.9691   result: 1.00000
    Statistics                               avg time: 0.2877   result: 1.00000
    Select                    no indexing    avg time: 0.5010   result: 1.00000
    Select                    with indexing  avg time: 0.2274   result: 1.00000
points quantity: 1000000
    Cursor, dictionary                       avg time: 0.4813   result: 1.00000
    Cursor, list                             avg time: 0.4556   result: 1.00000
    Numpy Array                              avg time: 0.3581   result: 1.00000
    Numpy Array ravel                        avg time: 0.3584   result: 1.00000
    Cursor, SQL                              avg time: 8.8776   result: 1.00000
    Statistics                               avg time: 0.6675   result: 1.00000
    Select                    no indexing    avg time: 2.9018   result: 1.00000
 -->Select                    with indexing  avg time: 0.2194   result: 1.00000

Also note that the ultrafast numpy arrays can be used instead of lists for improving 100.000 records above that can use select likewise:

max(arcpy.arcpy.da.FeatureClassToNumPyArray(fc,"TEST_FIELD"],spatial_reference=sr).astype(np.float))

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.