Select Largest Polygon in each parcel

[SeanGambrel]

Hi all,

I am trying to identify the largest building in each of my parcels, so that I can join the parcel's address with the building footprint.  I am going on the assumption that the largest building in an urban setting is usually the primary structure, though I'm sure there are instances where this is not true.

So, I am attempting to do this as in an older forum post I found, But I can't get the reccommendations to work:
http://forums.esri.com/thread.asp?c=93&f=982&t=302006

- I am using ArcInfo 9.3.1.
- I have a parcels Feature Class in which each feature has a single ID and each ID has only one feature.
- I have a buildings Feature Class which I intersected with the parcels to accommodate cases where buildings overlapped parcel lines.
- I did a "One-to-Many" spatial join so that now each building on a parcel includes the host parcel's attributes.

So it seems that now I just need to write a SQL query which will look at all buildings with identical PARCEL id's and then select the one with the largest BUILDING area.  Here's what I have input in the 'Select by Attributes' dialog:

SELECT * FROM BldgsToCompare WHERE:
-----------------------------------------------------------------------------------
"BLDG_AREA" in (select max("BLDG_AREA") from BldgsToCompare group by "TAX_ACCT")
-----------------------------------------------------------------------------------

"BLDG_AREA" is the square footage of each building
"TAX_ACCT" is the unique identifier for each parcel
"BldgsToCompare" is the Feature Class resulting from the Spatial Join. 

Any help would be much appreciated!

[DonovanCameron]

Below is the result I got.

I am not sure if the Max function will work on a shapefile. So I created a Personal GDB and imported your shapefiles into it.

Renaming them (b/c I hate long file names):
Bldg_FP
Parcels_Join (don't think you need this one in there, as it will not have a selection query applied)

Query Used:

Shape_Area in (select max(Shape_Area) from BldgFP group by PIN)

[SeanGambrel]

Hi Don,

Thanks for the help, but I think I need you to try again - those weren't my shapefiles and so I still can't see where my SQL query is wrong.  I'm basically replacing the name of what I think the other user's FC and field names are with my own.  But still no luck.  I'm assuming its just something like a missing space or not formatting the name of the feature class correctly or something...

Also - I have been worknig in a File Geodatabase, so I know its not the shapefile issue.

I've attached a zip with a subset of the geodatabase (compressed) so you can take a swing at it if you are able.  I have been working on this for literally days and can't figure out what is going on!!!

If you could post the exact syntax I need to be using, I would be enormously grateful!

[SeanGambrel]

Data Attached... .Sorry!

[SeanGambrel]

It appears I have this somewhat figured this out...  I needed to be working in a Personal Geodatabase rather than a File Geodatabase.

But now, in many of the parcels, more than one building is being chosen.  Any reason why?


Many thanks!
Sean

[DonovanCameron]

It appears I have this somewhat figured this out...  I needed to be working in a Personal Geodatabase rather than a File Geodatabase.

But now, in many of the parcels, more than one building is being chosen.  Any reason why?

Right on Sean, if you read my earlier post, that's exactly what is suggested.

You are getting multiple max area buildings for a single parcel because the Area fields you have, several of them are exactly the same in terms of area calculated (i've noticed that several features are duplicates, and overlap each other)

Dissolve these out and then use the following expression for your selection:

Shape_Area in (select max(Shape_Area) from BldgsToCompare_D group by TAX_ACCT)

I've attached the PGDB that I used, with the dissolved feature class, so all you have to do is apply the def. que. to inspect the results.



Attachments:
[ATTACH]3345[/ATTACH] Screenshot of Overlapping Records
[ATTACH]3347[/ATTACH] Zipped Expression File (can be loaded into expression window)
[ATTACH]3348[/ATTACH] Zipped PGDB that I Used


**EDIT: In the attached Expression file, when loaded, there is one change that needs to be made. Change BldgsToCompare to BldgsToCompare_D (as seen in the embedded code above)

[ChrisSnyder]

If you want to use FGDB (apparently no supprt for MAX or MIN SQL statments = lame), here's a way to do it via Python using a reverse sorted dictionary:

import arcgisscripting
gp = arcgisscripting.create(9.3)
fc = r"\\snarf\am\workspace\csny490\Buildings.gdb\BldgsToCompare"
parcelDict = {}
oidFieldName = gp.describe(fc).oidfieldname
searchRows = gp.searchcursor(fc)
searchRow = searchRows.next()
while searchRow:
    taxLotValue = searchRow.MAPLOTUNIT
    buildingAreaValue = searchRow.BLDG_AREA
    oidValue = searchRow.getvalue(oidFieldName)
    if taxLotValue in parcelDict:
        parcelDict[taxLotValue].append([buildingAreaValue,oidValue])
    else:
        parcelDict[taxLotValue] = [[buildingAreaValue,oidValue]]
    searchRow = searchRows.next()
del searchRow, searchRows
sqlString = ""
for parcel in parcelDict:
    parcelDict[parcel].sort(reverse=True)
    sqlString = sqlString + str(parcelDict[parcel][0][1]) + ","
gp.MakeFeatureLayer_management(fc, "fl", oidFieldName + " in (" + sqlString[:-1] + ")", "", "") 

[SeanGambrel]

Hi Don,

Thank you again for taking the time out to help me.

I suppose I should've mentioned this before - the way we handle condominiums here is that the parcel is geometrically duplicated but has different attributes based on the individual owners.  (There is a long and arduous reason for this, which I'll be happy to share if you care.)  If you look back at the data, you'll see that each has an independent TAX_ACCT value, but is geometrically identical.  This is not actually the trouble I'm having.

The problem I'm facing (see screenshot below), is where there is a single-family residential property, with perhaps a detached garage and garden shed also on the parcel.  In some cases, the single largest structure is selected, in others, all three are selected, and in still others, the largest building and one of the others are selected.  I would say the "success rate" of just the largest building being selected is less than 50%. 

In the case shown below, (and several hundred of others I've found) none of the parcels are condominiums or otherwise have any duplication.  I've double checked that none of the building polygons are multi-part or somehow otherwised grouped with other polygons, and that there is only one feature represented for each building.

Can you identify any other error in my methodology that might be causing theis problem?  

Right on Sean, if you read my earlier post, that's exactly what is suggested.

You are getting multiple max area buildings for a single parcel because the Area fields you have, several of them are exactly the same in terms of area calculated (i've noticed that several features are duplicates, and overlap each other)

[SeanGambrel]

If you want to use FGDB (apparently no supprt for MAX or MIN SQL statments = lame), here's a way to do it via Python using a reverse sorted dictionary:

Lame:  Agreed.