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)
are you using arcgis pro or arc map?
Pro's help
Select Layer By Location—Data Management toolbox | Documentation
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)
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
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.
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...
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)
the searchcursor isn't necessary
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.