4

I am trying to use a SQL expression in order to select values that are >= 25% of its max value in that column. For instance, VALUE >= MAX(VALUE)*0.25, and those will ultimately be the selected points it pulls out. Is there syntax to make that happen, or am I going to need to create a new column and use field calculator on it. Biggest issue with that is I will have to do this for about 250 files. I'm also trying to create this in modelbuilder.

When I try it as above it just returns there is an error with the expression.

  • I don't believe you can calculate an expression this way but perhaps someone will have a good solution for a way to do this in the modelbuilder environment. If you can put some of this into a python script, I can help you with a function that accepts a FeatureClass and a Field (from the fc) as arguments and returns an array of all the values. That returned array can then be used to get the maximum value of the array. Let me know if that would be something you could use. – jbchurchill Aug 7 '14 at 14:55
3

The technique I use to do this in ModelBuilder is to:

  1. Run Summary Statistics (Analysis) to write the MAX value into its output table
  2. Use Get Field Value (ModelBuilder) to read the first row of the table just created which holds that MAX value
4

I think the problem you are having is, with your expression as is, this condition you wrote is being evaluated for each row/record in your data. So, if you had a table like:

OID | Value
 1  | 123
 2  | 456
 3  | 789

my understanding is that your expression, with real values substituted in for variables, would look like:

123 >= MAX(123)*0.25
456 >= MAX(456)*0.25
789 >= MAX(789)*0.25

First off, you need to make sure your value field is of a numeric field type (long/short integer, double, float). Then, if that is true, you need to look at the help concept at http://resources.arcgis.com/en/help/main/10.2/index.html#//00s500000033000000 and scroll down to the section on subqueries and read through that as that I think is what you are really looking for. I don't know your table names and such, so I don't know the exact query that would be needed, but it might look something like

VALUE >= (SELECT MAX(VALUE)*0.25 FROM TABLE)

That should hopefully get you at least in the ball park. If you do a subquery, it is going to evaluate each record (the first VALUE variable), against the results of the subquery( which can look at the entire table and extract a MAX value). To my knowledge, with the way ArcGIS Desktop SQL input options are set up, you can generally only use the functions like AVG, COUNT, MIN, MAX, and SUM in subqueries.

  • Might be part of my problem, I'm not real sure of the table name either :) It is coming from an output in model builder, would it just be the name of that file? – twistr25 Aug 7 '14 at 15:14
  • It should be, but be warned, I don't know that sub-queries work on shapefiles, I think it has to be in a geodatabase for subqueries to work. The only other option I can see as a work around would be a long, cumbersome work around involving performing summary statistics (help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//…) on each table/feature class and then opening and analyzing that output table to get the value you need to hard-code the correct max value into your SQL query (but I would advise against this if you can avoid it). – John Aug 7 '14 at 15:25
  • Otherwise, as @jbchurchill notes in his comment above, you could always export the model-builder model out to a python script and then modify it by adding a search cursor (resources.arcgis.com/en/help/main/10.1/index.html#//…) that would loop through the features/records in a dataset and give you a list of values you could pull the max from, or you could even just have it return only the max value and then again use that actual number in place of MAX(VALUE) in your SQL query, but again, this shouldn't be needed if you can use a subquery. – John Aug 7 '14 at 15:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.