2

I am trying to build a tool where the end user can run a model and make a selection from a feature class based on 3 different fields, calculate the total shape length, & export the results to a csv file. I have everything else figured out except for one part. I want the user be able to make a selection based on all three fields or based on one , two, or all three fields without them having to edit the selection query.

As you probably guess when I don't populate one of the user defined parameters it pops up with an error. I think the way to do this would be to use the "calculate value"in model builder and use Python to build a sql expression and use that expression for the selection. I was thinking of having 3 user defined parameters with drop down list of field values and using inline variable substitution in the calculate value tool to create a sql expression based on what the user defined in the parameters.

This is the expression I have right now for them to select using all three field values:

"DISTRICT" = '%District%' AND "SURFACE" = '%Surface%' AND "JURISD" = '%Jurisdiction%'

I have a basic understanding of Python. Any help or suggestions would be greatly appreciated. Thanks!

Improve this question
2
  • Why not just expose the SQL expression as a model parameter and let them go to town?
    – blah238
    Sep 9, 2013 at 22:35
  • I did do that at first but they are wanting something as simple as possible with no thinking involved. There is only one person in their office that is somewhat knowledgeable with GIS and SQL expressions and they want everyone to be able to use this tool at a moments notice.
    – PMK
    Sep 10, 2013 at 1:32

2 Answers 2

Reset to default
3

You will have to do something like the the following. I have done it for 2 variables, you will have to update it for 3.

First, as you probably already have done, have a model with the variables.

enter image description here

Next in the calculate field, you will have to write some python code. As this example:

enter image description here

In the expression, you have to call the function ("a" in this case). In the code block, you have to put the conditions and the return values, python code. The .strip() function ensure that white space are considered as empty string.

Hope this helps!

Improve this answer
1

I finally figured it out! After reviewing Jeb's response it got me thinking. I ended up using the "Calculate Value" model only tool.

Expression:

a('%District%','%Surface%','%Jurisdiction%')

Code Block:

 def a(a1,a2,a3):

  if a1 and a2 and a3 != "":
    return '"DISTRICT" = '+ "'" + a1 + "'" + "AND" +' "SURFACE" = ' + "'" + a2 + "'" + "AND" + ' "JURISD" = ' + "'" + a3 + "'"
  elif a1 and a2 != "" and a3 == "":
    return '"DISTRICT" = ' + "'" + a1 + "'" + "AND" + ' "SURFACE" =' + "'" + a2 + "'"
  elif a1 and a3 != "" and a2 == "":
    return '"DISTRICT" = ' + "'" + a1 + "'" + "AND" + ' "JURISD" =' + "'" + a3 +"'"
  elif a2 and a3 != "" and a1 == "":
    return '"SURFACE" = ' + "'" + a2 + "'" + "AND" + ' "JURISD" = ' + "'" + a3 + "'"
  elif a1 != "" and a2 == "" and a3 == "":
    return '"DISTRICT" = ' + "'" + a1 + "'"
  elif a2 != "" and a1 == "" and a3 == "":
    return '"SURFACE" =' + "'" + a2 + "'"
  elif a3 != "" and a1 == "" and a2 == "":
    return '"JURISD" = ' + "'" + a3 + "'"    
  else:
    return "error"

I know its a little messy but it works great. The results look something like this for the SQL expression:

"DISTRICT" = '1' AND "SURFACE" = 'GRAVEL' AND "JURISD" = 'FULL'

As it sits, I can populate all the parameters or choose to populate one or two and it will successfully create an expression so complete my selection.

Improve this answer
3
  • 1
    You should upvote @Jeb and accept his answer since he helped you. You also should use .format() to build your strings. For instance, '"DISTRICT" = {0}{1} "SURFACE" = {2}{1} "JURISD" = {3}'.format(a1,a,a2,a3). But in this instance, you should just hardcode a, since it should always be "AND".
    – Paul
    Sep 10, 2013 at 20:38
  • Thank you for the tips Paul. I am new to this site and relatively new to python scripting. I would vote Jeb up but I am 2 reputation points shy of being able to do so. I have hard coded the "AND" into my code and I am going to look into formatting my strings. I will update this post as soon as I complete that. Thanks again!
    – PMK
    Sep 10, 2013 at 21:00
  • It has a bit of a learning curve, but once you learn it, it makes concatenating strings with variables simpler to write and debug.
    – Paul
    Sep 10, 2013 at 21:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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