topic Re: Pass sql expression with arcpy in Python Questions

Pass sql expression with arcpy

CCWeedcontrol — Sat, 11 Dec 2021 23:27:54 GMT

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

Re: Pass sql expression with arcpy

DanPatterson_Retired — Thu, 09 Apr 2020 17:36:50 GMT

are you using arcgis pro or arc map?

Pro's help

Select Layer By Location—Data Management toolbox | Documentation

Re: Pass sql expression with arcpy

CCWeedcontrol — Sat, 11 Dec 2021 23:27:56 GMT

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

Re: Pass sql expression with arcpy

DanPatterson_Retired — Thu, 09 Apr 2020 18:07:04 GMT

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

Re: Pass sql expression with arcpy

JoeBorgione — Thu, 09 Apr 2020 18:19:04 GMT

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 Systems Stack Exchange

Re: Pass sql expression with arcpy

JoshuaBixby — Thu, 09 Apr 2020 18:21:43 GMT

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.

Re: Pass sql expression with arcpy

CCWeedcontrol — Sat, 11 Dec 2021 23:27:59 GMT

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

Re: Pass sql expression with arcpy

DanPatterson_Retired — Thu, 09 Apr 2020 19:05:23 GMT

the searchcursor isn't necessary

Re: Pass sql expression with arcpy

VinceAngelo — Sat, 11 Dec 2021 23:28:02 GMT

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:

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.