select max value per feature location for overlapping point features

[DavidMedeiros]

I have a "grid" (vector grid) of evenly spaced points for ocean alkalinity. At every location in the grid there is a stack of up to 15 points with identical coordinates but different values for their depth and alkalinity attributes. I need to join the alkalinity values for the deepest points at each location to another point layer.

How can I create a selection that will grab just the deepest points at each location?

This is not a simple get max value from the entire layer problem, I need the points with the max depth value at each of 6000 point stacks.

I've tried various versions of this: "depth" = (SELECT MAX("depth") FROM INPUT) in the select by attribute window but get a bunch of syntax errors. I'm pretty sure I also need some other selection attribute like a unique coordinate ID so I'm picking the max from each stack and not just the whole data set. I have that ID created.

Any help is greatly appreciated!

David

[DaleHoneycutt]

If all the points in your 'point stack' have a common ID (i.e., "STACK_ID"), try using the Summary Statistics tool.  You'll find the MAX of the DEPTH field and your Case Field is your STACK_ID.  The result will be a table that will have a record for each unique STACK_ID with the maximum depth.  What it won't have is the alkalinity field value for that point.  But you do have enough information (the STACK_ID and the DEPTH) to find that unique point using an attribute query.  The way I'd do this is to create a new field on the point feature class that concatenates STACK_ID and DEPTH.  This would be a text field.  See this blog post on how to concatenate field values.  If you can create this field on your point feature class, you can also create it on the output of Summary Statistics.  Then you have a common key field to use in joins and relates.  You could use Join Field to join alkalinity from the point features to the statistics table for example. 

If you have an advanced license, you could use the Sort tool to sort your points on STACK_ID and DEPTH (descending) , then use Summary Statistics with the FIRST statistic to find the alkalinity value.  STACK_ID would be your Case field.  This'll return the first value of alkalinity found for each unique STACK_ID.  Since the table is sorted, the first occurrence is the deepest point.

There's probably a better way to do this using an SQL query, but I can't think of it right now.

[DavidMedeiros · Accepted solution]

If all the points in your 'point stack' have a common ID (i.e., "STACK_ID"), try using the Summary Statistics tool.  You'll find the MAX of the DEPTH field and your Case Field is your STACK_ID.  The result will be a table that will have a record for each unique STACK_ID with the maximum depth.  What it won't have is the alkalinity field value for that point.  But you do have enough information (the STACK_ID and the DEPTH) to find that unique point using an attribute query.  The way I'd do this is to create a new field on the point feature class that concatenates STACK_ID and DEPTH.  This would be a text field.  See this blog post on how to concatenate field values.  If you can create this field on your point feature class, you can also create it on the output of Summary Statistics.  Then you have a common key field to use in joins and relates.  You could use Join Field to join alkalinity from the point features to the statistics table for example. 

If you have an advanced license, you could use the Sort tool to sort your points on STACK_ID and DEPTH (descending) , then use Summary Statistics with the FIRST statistic to find the alkalinity value.  STACK_ID would be your Case field.  This'll return the first value of alkalinity found for each unique STACK_ID.  Since the table is sorted, the first occurrence is the deepest point.

There's probably a better way to do this using an SQL query, but I can't think of it right now.

I think that will work. I already have the unique ID for each stack (concatenated X and Y coordinates) so that as the Case Field in the summary statistics tool should give me what I need to join back to the data to get the max points. I'll try it with the student I'm helping when we meet next week and will report back if this was the solution.

Thanks!

[DaleHoneycutt]

I hunted around a bit for the SQL... this could be done by using GROUP BY in SQL (something like "Select MAX(DEPTH) GROUP BY STACK_ID").  Unfortunately, GROUP BY queries aren't supported on file geodatabases, only enterprise databases (i.e., SQL Server, Oracle, etc.).