Summarize Tool

[Joey_Williams]

I am trying to find the most populated cities within various polygons (which have I.D.'s).  My cities have a POP value associated.  When I use the Summarize tool I group by the polygon ID, then select the MAX for Population.  The issue is I also need the city name.  There are multiple Cities within a given polygon.  I tried using "first" but that returns the first city entry with the max pop (which is not the same city).  I want to simply carry over the city associated with the max POP value within each polygon but I don't see a way to accomplish that.

Any help is appreciated! 

[MarqueeCrew]

If you can't group by POLYGON ID, CITY

then you might try joining back to the original data on the POP size and getting the cityname that way.

OR....

SORT the data by POLYGON, POP (Descending) and Sample the first record (grouping by POLYGON) and finding the data (city/pop) that way.

Just a thought,

Mark

[Joey_Williams]

I tried these options.  When I do this it returns each city name, the polygon ID and each associated POP. 

For example this is what the above steps are giving me:

ID   City                   POP

1     Atlanta              500

1     Marietta             300

1     Kennesaw         200

1     Alpharetta         100

2     Rome                 200

2     White                 50

The desired output I want would be:

ID   City                   POP

1     Atlanta              500

2     Rome                200

Thank you for your response.

[s_pichaipillai]

Hi Jo,

2 steps

1. sum the data by POP and Group by ID and city

2. one more Summarize now group by ID and grab Max of POP fianlly join them 

there are many ways but this also works

[GarthM]

i have an idea that may solve your problem; however, it's not bulletproof, and performance will decline depending on the amount of data being processed. anyway, the idea is to use the Generate Rows tool to generate a number of rows equivalent to size of the population:

Condition Expression: RowCount <= [POP]

Result:

Take the output from the Generate Rows tool and use the Summarize tool to group by the polygon id, use the mode function to extract the most frequently occurring city, then append the POP by taking the max value from the data&colon;

GroupBy: ID

Mode: City

Max: POP

i've attached a sample workflow to give you a better idea of what i've described.

[s_pichaipillai]

Garth,

what about , if same ID but diffrent city with same MAX value?

ex.

1 Bangalore 5

will it grab this as well ?

i concur you that it may hit the performance if we have high range of values :)

[Joey_Williams · Accepted solution]

I found the solution that worked for me. It required first decending the POP value, then using the MultiTool Formula, creating a new field called RANK grouping my polygon ID and adding in the formula [Row-1:RANK]=1, then filtering out rank 1 and it gave me the result I needed.

Thank you everyone for your help with this.

[tom_montpool · Accepted solution]

Forgive me if I'm stating the obvious, but isn't this precisely what the Unique tool is meant for??

Sort on your polygon ID, and population fields, then take the Unique polygon.