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?

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 )

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Why sql_clause is not respected in da.SearchCursor?

AdamBorczyk

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:

<SPAN class="keyword token">import</SPAN> arcpy

myFile <SPAN class="operator token">=</SPAN> somePath
sql <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="string token">'TOP 3'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'ORDER BY "length" DESC GROUP BY "area_id" '</SPAN><SPAN class="punctuation token">)</SPAN>

cursor <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN>myFile <SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"*"</SPAN><SPAN class="punctuation token">,</SPAN> None<SPAN class="punctuation token">,</SPAN> None<SPAN class="punctuation token">,</SPAN> <SPAN class="token boolean">False</SPAN><SPAN class="punctuation token">,</SPAN> sql<SPAN class="punctuation token">)</SPAN>

<SPAN class="keyword token">with</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> r <SPAN class="keyword token">in</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> r‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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?

Najděte více příspěvků označených jako

Accepted answers

RandyBurton

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 <SPAN class="operator token">=</SPAN> r<SPAN class="string token">"C:\Users\adam\Desktop\testarcpy\cont_int.shp"</SPAN>&nbsp; 
&nbsp; 
sourceFieldsList <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">"glac_id"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"length"</SPAN><SPAN class="punctuation token">]</SPAN>

<SPAN class="comment token"># Use list comprehension to build a dictionary from a da SearchCursor&nbsp; </SPAN>
valueDict <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">{</SPAN>r<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">:</SPAN><SPAN class="punctuation token">(</SPAN>r<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">:</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">for</SPAN> r <SPAN class="keyword token">in</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN>sourceFC<SPAN class="punctuation token">,</SPAN> sourceFieldsList<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">}</SPAN>&nbsp; 

<SPAN class="keyword token">for</SPAN> k<SPAN class="punctuation token">,</SPAN> v <SPAN class="keyword token">in</SPAN> valueDict<SPAN class="punctuation token">.</SPAN>items<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> k<SPAN class="punctuation token">,</SPAN> max<SPAN class="punctuation token">(</SPAN>v<SPAN class="punctuation token">)</SPAN>‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

All comments

JamesCrandall

I get 'IndexError: list assignment index out of range' on this line:

selections[row[0]][row[1]] = row[2]<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

JamesCrandall

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()<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

RandyBurton

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

<SPAN class="comment token"># line 7</SPAN>
df <SPAN class="operator token">=</SPAN> df<SPAN class="punctuation token">.</SPAN>sort<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="string token">'area_id'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'length'</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN>ascending<SPAN class="operator token">=</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="token boolean">True</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="token boolean">False</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="comment token"># line 8‍‍‍‍‍‍‍‍‍</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

AdamBorczyk

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

&nbsp;&nbsp;&nbsp; 

&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">import</SPAN> arcpy
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">from</SPAN> collections <SPAN class="keyword token">import</SPAN> defaultdict

&nbsp;&nbsp;&nbsp; vector <SPAN class="operator token">=</SPAN> <SPAN class="string token">'yourShapefile.shp'</SPAN>

&nbsp;&nbsp;&nbsp; <SPAN class="comment token"># 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</SPAN>
&nbsp;&nbsp;&nbsp; fields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">"glac_id"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"FID"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"SHAPE@LENGTH"</SPAN><SPAN class="punctuation token">]</SPAN>
&nbsp;&nbsp;&nbsp; selections <SPAN class="operator token">=</SPAN> defaultdict<SPAN class="punctuation token">(</SPAN>dict<SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; top3 <SPAN class="operator token">=</SPAN> defaultdict<SPAN class="punctuation token">(</SPAN>list<SPAN class="punctuation token">)</SPAN>

&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN>vector<SPAN class="punctuation token">,</SPAN> fields<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; selections<SPAN class="punctuation token">[</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">[</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">]</SPAN>

&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> i <SPAN class="keyword token">in</SPAN> selections<SPAN class="punctuation token">.</SPAN>keys<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="comment token"># I take 3 last items (3 biggest) or sorted list of values. I could also reverse it and get 3 first ones.</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; top3<SPAN class="punctuation token">[</SPAN>i<SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> sorted<SPAN class="punctuation token">(</SPAN>selections<SPAN class="punctuation token">[</SPAN>i<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN> key<SPAN class="operator token">=</SPAN>selections<SPAN class="punctuation token">[</SPAN>i<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN>get<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="operator token">-</SPAN><SPAN class="number token">3</SPAN><SPAN class="punctuation token">:</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

The if I print top3, it will give me:

defaultdict(<type&nbsp;'list'>,&nbsp;{u'G012161E78870N':&nbsp;[41,&nbsp;46,&nbsp;47],&nbsp;u'G011895E78886N':&nbsp;[5,&nbsp;3,&nbsp;18],&nbsp;u'G012039E78878N':&nbsp;[44,&nbsp;42,&nbsp;17]})<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

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

JamesCrandall

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.

AdamBorczyk

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.

JamesCrandall

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<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

AdamBorczyk

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:

<SPAN class="keyword token">import</SPAN> arcpy
arcpy<SPAN class="punctuation token">.</SPAN>env<SPAN class="punctuation token">.</SPAN>overwriteOutput<SPAN class="operator token">=</SPAN><SPAN class="token boolean">True</SPAN>

testVector <SPAN class="operator token">=</SPAN> r<SPAN class="string token">"C:\Users\adam\Desktop\testarcpy\cont_int.shp"</SPAN>
fields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">"glac_id"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"length"</SPAN><SPAN class="punctuation token">]</SPAN>
sql <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">(</SPAN>None<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'ORDER BY length DESC'</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="comment token"># One method (get ordered list)</SPAN>
<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN>testVector<SPAN class="punctuation token">,</SPAN> fields<SPAN class="punctuation token">,</SPAN> None<SPAN class="punctuation token">,</SPAN> None<SPAN class="punctuation token">,</SPAN> <SPAN class="token boolean">False</SPAN><SPAN class="punctuation token">,</SPAN> sql<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> r <SPAN class="keyword token">in</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> r

<SPAN class="comment token"># Other method (just give me longest one)</SPAN>
OID <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN>testVector<SPAN class="punctuation token">,</SPAN> fields<SPAN class="punctuation token">,</SPAN> sql_clause <SPAN class="operator token">=</SPAN> sql<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>next<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">print</SPAN> OID‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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.

JoshuaBixby

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?

RandyBurton

Perhaps something like (to get only first row):

OID <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN>myFile<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">"OBJECTID"</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">"length"</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where_clause <SPAN class="operator token">=</SPAN> <SPAN class="string token">"area_id = 'some_code'"</SPAN><SPAN class="punctuation token">,</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; sql_clause <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">(</SPAN>None<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'ORDER BY length DESC'</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>next<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="comment token"># use [0] to return just OBJECTID, remove for tuple‍‍‍‍‍‍‍‍‍‍‍‍‍</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

AdamBorczyk

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.

RandyBurton

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

Připojte se k diskusi

Přihlášení členové mohou přispívat, sledovat aktualizace a další. Jste tu noví? Zaregistrujte si bezplatný účet.

Rychlé odkazy

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.