Showing results for 
Search instead for 
Do you mean 

Finding Geometry With Min or Max Values (How to Lookup a related value)

by Technical Evangelist on ‎03-10-2016 12:15 PM - edited on ‎04-06-2016 01:51 PM by Moderator (659 Views)

Symptoms

Finding Geometry With Min or Max Values (How to Lookup a related value)

Diagnosis

User would like to create a query/functional attribute (something) that will return a query with cities that have the maximum annual rain.  Can you provide me any tips or workflows to accomplish this query?  A working example from US Sample Data with the AnnualRain attribute on Cities would be outstanding.

Solution

The most direct means might be to use the Analytical Merge commands to merge the cities and use the LOOKUP function.

Analytical Merge command is an aggregating style query.  It takes many records as input, forms groupings of the records and for each grouping, the user can build summarizing (aggregating) style expressions that return a single value (SUM, MAX, FIRST, LAST, etc.. )… LOOKUP is such an aggregating style function.

For example you might merge the Cities by State Name attribute,  then use the LOOKUP function to look at the Rainfall column, to find the MAX rainfall value, then return another attribute value.. in this case we can lookup the geometry of the city with max rainfall for each state.

In your question, if you want to get the single record/geometry from all records, then we’d use the Merge All option in Analytical Merge.

LOOKUP Syntax:
LOOKUP(what, where, result)

Example format to return the geometry that has the max rainfall:
LOOKUP(MAX(Input.ANNULRAIN),  Input.ANNULRAIN, Input.Geometry)

Find attached example .gws file created in 2014 SP2.  Use the Analysis > Queries command to review the query.

Contributors