Pass sql expression with arcpy

CCWeedcontrol

I am try to select the polygons with the max area within parcels but i am having trouble passing the sql through arcpy.da.SearchCursor. How can i pass this sql through SearchCursor?

sql = Shape_Area in (select max(Shape_Area) from Bldg_FP1 group by PIN)

with arcpy.da.SearchCursor(fc2, ['SHAPE@']) as cursor:
     for row in cursor:
          arcpy.SelectLayerByAttribute_management(fc,"NEW_SELECTION", sql)‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

VinceAngelo

That SQL clause is fraught for failure. An IN comparison on a floating-point value is a recipe for random results -- You could cross-select features with areas that just happen to share a parcel size but don't meet the intended attribute constraint and you might miss intended features due to representation issues.

In general, placing the SelectLayerByAttribute inside the cursor iteration is an anti-pattern (because you alter the layer on which the cursor is based), but in this case, it's also unnecessary (because you wanted to restrict the layer before cursor execution would begin).

If your dataset supports the full SQL implementation (not file geodatabase or Access), you can use the ROW_NUMBER window function to assign rank in a virtual table, then select parcel identifiers associated with the first-ranked features.  In PostgreSQL the full query could look like:

<SPAN class="keyword token">SELECT</SPAN>	objectid<SPAN class="punctuation token">,</SPAN>
      	continent <SPAN class="keyword token">as</SPAN> continent_name<SPAN class="punctuation token">,</SPAN>
      	name <SPAN class="keyword token">as</SPAN> country_name
<SPAN class="keyword token">FROM</SPAN>	country_gen
<SPAN class="keyword token">WHERE</SPAN>	objectid <SPAN class="operator token">in</SPAN> <SPAN class="punctuation token">(</SPAN>
		<SPAN class="keyword token">SELECT</SPAN> 	objectid
		<SPAN class="keyword token">FROM</SPAN> <SPAN class="punctuation token">(</SPAN>
			<SPAN class="keyword token">SELECT</SPAN>	objectid<SPAN class="punctuation token">,</SPAN> 
			      	continent<SPAN class="punctuation token">,</SPAN> 
			      	<SPAN class="punctuation token">(</SPAN>row_number<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">over</SPAN> <SPAN class="punctuation token">(</SPAN>
				        	<SPAN class="keyword token">PARTITION</SPAN> <SPAN class="keyword token">BY</SPAN> continent
				        	<SPAN class="keyword token">ORDER</SPAN> 	  <SPAN class="keyword token">BY</SPAN> ST_Area<SPAN class="punctuation token">(</SPAN>shape::geography<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">DESC</SPAN><SPAN class="punctuation token">)</SPAN>
			      	<SPAN class="punctuation token">)</SPAN>::<SPAN class="keyword token">int</SPAN> <SPAN class="keyword token">as</SPAN> group_rank
			<SPAN class="keyword token">FROM</SPAN>    country_gen
		<SPAN class="punctuation token">)</SPAN> vt
		<SPAN class="keyword token">WHERE</SPAN> 	group_rank <SPAN class="operator token">=</SPAN> <SPAN class="number token">1</SPAN>
	<SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">ORDER</SPAN> 	<SPAN class="keyword token">BY</SPAN> ST_Area<SPAN class="punctuation token">(</SPAN>shape::geography<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">DESC</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>

the output of which is:

objectid	continent_name	country_name
253	'Asia'	'Russian Federation'
29	'Antarctica'	'Antarctica'
18	'North America'	'Canada'
25	'South America'	'Brazil'
223	'Australia'	'Australia'
105	'Africa'	'Congo DRC'
196	'Europe'	'Ukraine'
99	'Oceania'	'New Zealand'

You'd only want lines 5-17 in your WHERE clause, suitably altered for your data.