topic Re: Why sql_clause is not respected in da.SearchCursor? in Python Questions

Why sql_clause is not respected in da.SearchCursor?

AdamBorczyk — Sat, 11 Dec 2021 15:13:56 GMT

I have a shapefile where I have polylines and their fields are:

- length

- area into which they fall in

Now I need 3 longest lines from each area. I have 3 areas and 50 polylines, so I should get 3*3=9 records as a result. What I try to do is:

import arcpy

myFile = somePath
sql = ('TOP 3', 'ORDER BY "length" DESC GROUP BY "area_id" ')

cursor = arcpy.da.SearchCursor(myFile , "*", None, None, False, sql)

with cursor:
    for r in cursor:
        print r‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I get all of 50 records here. The same happens when I set `sql` to just `('TOP 1', None) or anything else.

Is the syntax wrong here?

Re: Why sql_clause is not respected in da.SearchCursor?

RandyBurton — Sun, 19 Nov 2017 20:03:08 GMT

Are you using a file geodatabase? Or an SQL server? Some commands do not work with a file geodatabases.

TOP is only supported by SQL Server and MS Access databases.

See: SearchCursor

Re: Why sql_clause is not respected in da.SearchCursor?

AdamBorczyk — Sun, 19 Nov 2017 20:21:51 GMT

Thanks. I am iterating over a simple shapefile, and if I stop using TOP and change clause to eg. `sql = (None, 'ORDER BY "length" ')`, I still get every record.

Re: Why sql_clause is not respected in da.SearchCursor?

RandyBurton — Sat, 11 Dec 2021 15:13:58 GMT

Perhaps something like (to get only first row):

OID = arcpy.da.SearchCursor(myFile, ["OBJECTID","length"],
                              where_clause = "area_id = 'some_code'",
                              sql_clause = (None, 'ORDER BY length DESC')
                              ).next()[0] # use [0] to return just OBJECTID, remove for tuple‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Why sql_clause is not respected in da.SearchCursor?

JoshuaBixby — Mon, 20 Nov 2017 15:52:53 GMT

As rvburton‌ points out, TOP is not supported with shape files. The expression, ORDER BY "length", won't limit the number of records returned, it just sorts them. If TOP isn't supported and ORDER BY only sorts records, it is expected the cursor will return all of the records. That said, you can use Python to grab the first three records, representing the longest three lengths by area, or are you wanting/hoping for an SQL only solution?

Re: Why sql_clause is not respected in da.SearchCursor?

AdamBorczyk — Sat, 11 Dec 2021 15:14:01 GMT

Thanks Joshua Bixby and Randy Burton, I know how TOP and ORDER BY should work, but the point here is that none of example queries in `sql_clause` work, I couldn't get any ORDER BY or GROUP BY to work, or anything else. This is the code I tried right now:

import arcpy
arcpy.env.overwriteOutput=True

testVector = r"C:\Users\adam\Desktop\testarcpy\cont_int.shp"
fields = ["glac_id", "length"]
sql = (None, 'ORDER BY length DESC')

# One method (get ordered list)
with arcpy.da.SearchCursor(testVector, fields, None, None, False, sql) as cursor:
     for r in cursor:
        print r

# Other method (just give me longest one)
OID = arcpy.da.SearchCursor(testVector, fields, sql_clause = sql).next()
print OID‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

With above I always get unsorted values from the table. Even if 'length' was a string (which is not, it's a float), it is not ordered in any way, I get rows in the same order as they are in attribute table in ArcMap. There are no duplicate columns in the file obviously, no Nulls, nothing special really. I also tried ordering by SHAPE@LENGTH with no luck (same result again). Path to file is correct (I've just triple-checked it), column names are fine.

I also tried with a test WHERE clause: 'length > 100.0' with no luck again. Very same results.

I hoped this was a simple syntax error I just couldn't spot, but it looks like a bigger issue now.

Re: Why sql_clause is not respected in da.SearchCursor?

RandyBurton — Mon, 20 Nov 2017 20:09:32 GMT

In your original question, you were wanting to "group by" area_id. My thoughts were to use the 3 area_id values to feed the where_clause in 3 SearchCursors. You would use only the first row of each search.

Re: Why sql_clause is not respected in da.SearchCursor?

AdamBorczyk — Mon, 20 Nov 2017 20:20:27 GMT

This is correct and it would be the next step I did, but I cannot even get to this point, because ArcPy doesn't honour any ORDER BY clause. What more, trying to put `'glac_id = "G012039E78878N"'` as `WHERE` clause doesn't modify results at all either.

Re: Why sql_clause is not respected in da.SearchCursor?

RandyBurton — Mon, 20 Nov 2017 20:26:14 GMT

Try this for the where clause (double quotes around entire where clause, single quotes only around the id code):

where_clause = "glac_id = 'G012039E78878N'",‍‍‍

Re: Why sql_clause is not respected in da.SearchCursor?

AdamBorczyk — Mon, 20 Nov 2017 20:32:15 GMT

This one works! Partly though. I can get correct result if I specify where_clause for a given area, but not a general highest result. When I don't know exact area IDs, this method will be hard to use. Without specifying WHERE, the sql_clause does not give any effect.

Re: Why sql_clause is not respected in da.SearchCursor?

RandyBurton — Sat, 11 Dec 2021 15:14:04 GMT

Try this one:

import arcpy
arcpy.env.overwriteOutput=True

testVector = r"C:\Users\adam\Desktop\testarcpy\cont_int.shp"

result = arcpy.da.SearchCursor(testVector, ["glac_id","length"],
                               where_clause = "glac_id = 'G012039E78878N'",
                               sql_clause = (None, 'ORDER BY length DESC')
                               ).next()

print result[0] # glac_id
print result[1] # length ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Why sql_clause is not respected in da.SearchCursor?

AdamBorczyk — Mon, 20 Nov 2017 20:41:20 GMT

Thank you for your code. I was too fast though. It appears that your and my code correctly use"where" clause, but totally omit "sql_clause". With the code you supplied, when I iterate over "result" (removing .next() beforehand) I get all records belonging to my area ID, but unsorted. Maybe sql_clause needs some more parameters which aren't documented that well?

Re: Why sql_clause is not respected in da.SearchCursor?

JamesMacKay3 — Mon, 20 Nov 2017 20:48:33 GMT

From the doc:

"DISTINCT, ORDER BY, and ALL are only supported when working with databases. They are not supported by other data sources (such as dBASE or INFO tables)."

Since shapefiles use dBASE tables to store attributes, ORDER BY won't work on shapefiles.

Re: Why sql_clause is not respected in da.SearchCursor?

RandyBurton — Sat, 11 Dec 2021 15:14:06 GMT

Since you don't know the specific glac_id, it might work to read the data from a SearchCursor into an array or dictionary and process the results from there. Untested code:

sourceFC = r"C:\Users\adam\Desktop\testarcpy\cont_int.shp"  
  
sourceFieldsList = ["glac_id", "length"]

# Use list comprehension to build a dictionary from a da SearchCursor  
valueDict = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(sourceFC, sourceFieldsList)}  

for k, v in valueDict.items():
    print k, max(v)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Why sql_clause is not respected in da.SearchCursor?

JamesCrandall — Sat, 11 Dec 2021 15:14:09 GMT

Not sure what you want to do with the results, but I think this gets close to the desired output.

import pandas
import numpy as np

fc = r'C:\somefeatureclass'
fields = ['area_id','length']
nparr = arcpy.da.FeatureClassToNumPyArray(fc, fields)
df = pandas.DataFrame(nparr,columns=fields)
dfgrouped = df.groupby('area_id').head(3).reset_index(drop=True)

print dfgrouped.values‍‍‍‍‍‍‍‍‍‍

Re: Why sql_clause is not respected in da.SearchCursor?

AdamBorczyk — Mon, 20 Nov 2017 21:25:07 GMT

Thank you everyone for your help. I missed the part of docs saying that ORDER BY won't work with shapefiles. Fortunately a where clause works, so I can get my output in a little bit longer way. I just have to iterate over entire cursor and find max (or top 3) from the results.

Re: Why sql_clause is not respected in da.SearchCursor?

JamesCrandall — Mon, 20 Nov 2017 21:30:20 GMT

I still don't see how you are getting the top 3. I'd like to know too so that I can choose between my solution above and a strictly arcpy approach.

Re: Why sql_clause is not respected in da.SearchCursor?

AdamBorczyk — Sat, 11 Dec 2021 15:14:12 GMT

This is my code, I hope it helps you James Crandall‌

    

    import arcpy
    from collections import defaultdict

    vector = 'yourShapefile.shp'

    # glac_id is my area ID, FID is the very first column in ArcMap attribute table and I calculate length of the shape on the fly
    fields = ["glac_id", "FID", "SHAPE@LENGTH"]
    selections = defaultdict(dict)
    top3 = defaultdict(list)

    with arcpy.da.SearchCursor(vector, fields) as cursor:
        for row in cursor:
            selections[row[0]][row[1]] = row[2]

    for i in selections.keys():
        # I take 3 last items (3 biggest) or sorted list of values. I could also reverse it and get 3 first ones.
        top3[i] = sorted(selections[i], key=selections[i].get)[-3:]‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

The if I print top3, it will give me:

defaultdict(<type 'list'>, {u'G012161E78870N': [41, 46, 47], u'G011895E78886N': [5, 3, 18], u'G012039E78878N': [44, 42, 17]})‍

which are FIDs of 3 longest lines per area. Now all you have to do is extract lines by ID from original file (or do whatever you want).

Re: Why sql_clause is not respected in da.SearchCursor?

RandyBurton — Sat, 11 Dec 2021 15:14:14 GMT

I think a sort is required between line 7 and 8. Interesting code, James. Thanks for sharing.

# line 7
df = df.sort(['area_id','length'],ascending=[True,False])
# line 8‍‍‍‍‍‍‍‍‍‍‍‍

Re: Why sql_clause is not respected in da.SearchCursor?

JamesCrandall — Sat, 11 Dec 2021 15:14:17 GMT

Depending upon what the use of the result is, it can be converted to a dictionary with one more line:

dfgrouped = dfgrouped.sort(['area_id,'length'],ascending=[True,False])
dict = dfgrouped.set_index(area_id').T.to_dict()‍‍