Pass sql expression with arcpy

899
8
04-09-2020 10:11 AM
CCWeedcontrol
Occasional Contributor III

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)‍‍‍‍‍‍‍‍‍‍‍‍
0 Kudos
8 Replies
DanPatterson_Retired
MVP Emeritus

are you using arcgis pro or arc map?

Pro's help

Select Layer By Location—Data Management toolbox | Documentation 

0 Kudos
CCWeedcontrol
Occasional Contributor III

My bad, Arcmap and would be using arcpy.SelectLayerByAttribute_management not ByLocation.

I have tried the following I don't get an error but nothing is selected

sql = "select MAX(Shape_Area) from fc group by PIN"
with arcpy.da.SearchCursor(fc2, ['SHAPE@']) as cursor:
     for row in cursor:
          arcpy.SelectLayerByAttribute_management(fc2,"NEW_SELECTION", sql)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
0 Kudos
DanPatterson_Retired
MVP Emeritus

not sure why you would be running it through a searchcursor in the first place when the selectbyattribute would work on the whole of fc2

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

I agree with Dan, not sure what the search cursor is doing here.  You cursor is getting a shape but you aren't doing anything with the shape.

How about you step back from the code and explain what you are trying to achieve.  There may be a different way of going about it.  If you can explain your data structures a bit, even better.

JoeBorgione
MVP Emeritus

I have tried the following I don't get an error but nothing is selected

Are you running this against a feature class or have you applied MakeFeatureLayer() such that fc2 = yourFeatureLayer

Take a look at this:  arcpy - Selecting maximum value in attribute table using Python code? - Geographic Information Syste... 

That should just about do it....
0 Kudos
CCWeedcontrol
Occasional Contributor III

I was able to get to select features with the following but i was trying to use the arcpy.da.SearchCursor .

sql = "[Shape_Area] in (SELECT max([Shape_Area]) FROM fc2 GROUP BY [PIN])"
arcpy.SelectLayerByAttribute_management(fc2,"NEW_SELECTION", sql)‍‍‍‍‍‍
0 Kudos
DanPatterson_Retired
MVP Emeritus

the searchcursor isn't necessary

VinceAngelo
Esri Esteemed Contributor

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:

SELECT	objectid,
      	continent as continent_name,
      	name as country_name
FROM	country_gen
WHERE	objectid in (
		SELECT 	objectid
		FROM (
			SELECT	objectid, 
			      	continent, 
			      	(row_number() over (
				        	PARTITION BY continent
				        	ORDER 	  BY ST_Area(shape::geography) DESC)
			      	)::int as group_rank
			FROM    country_gen
		) vt
		WHERE 	group_rank = 1
	)
ORDER 	BY ST_Area(shape::geography) DESC‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

the output of which is:

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