SQL query and Export Metadata

[AlexGole1]

Hi all,
I would like to have your input for this part of my script.
I am trying to set up an SQL query box in my tool that will allow users to to select and query from the fields obtained in the input feature layer (Input_Poly_Feature_Layer). The only problem: it seems like there is no way to display the fields in the Query builder box. Do you guys know if there is anyway to make the fields show up?

Last thing that I will intend to do is:
Each time the script is ran, I would like to save a copy of all the Metadata (All steps used for this) as a CVS file or text file? Do you have any hints or ideas on how to do this?

Thanks,
Alex

[LucasDanzinger]

Regarding the first part of your question, when you create the script tool parameters, make sure that you select the Obtained From box for the SQL Expression, and select the input feature that you want the fields to come from.

[Anonymous User]

Original User: alex92190

Regarding the first part of your question, when you create the script tool parameters, make sure that you select the Obtained From box for the SQL Expression, and select the input feature that you want the fields to come from.

I tried this but my script input is not a FeatureLayer, it is a shapefile from which i create a feature layer in my script. The Obtain from is therefore blank...

[LucasDanzinger]

You shouldn't need a feature layer. It works as shapefile or feature class for me. Are the fields you want coming from the shapefile parameter below the SQL expression parameter, or are you trying to obtain the fields from a feature class that is defined within the script (not a parameter).

[Anonymous User]

Original User: alex92190

You shouldn't need a feature layer. It works as shapefile or feature class for me. Are the fields you want coming from the shapefile parameter below the SQL expression parameter, or are you trying to obtain the fields from a feature class that is defined within the script (not a parameter).

So here is what I have:
1) make this shape into FLayer for a select by location
input_poly = r"K:\Library\State\CA\State_Wide\Bio\CNDDB\cnddb.shp"
Input_Poly_Feature_Layer = "cnddb_Feature_Lyr"

2) From select by location  i want to make a subset selection
#Select Layer by attribute
SelectLayerByAttribute_management(Input_Poly_Feature_Layer, "SUBSET_SELECTION", SQL_Query)

Also, attached is the error popping up when drag/drop feature class to the tool "Select Layer by attribute". Should I use another tool maybe?

Thanks,
Alex

#Set Workspace
arcpy.env.workspace = arcpy.GetParameter(0)
arcpy.AddMessage("The new current workspace is: %s" % env.workspace) 

#Overwrite features
arcpy.env.overwriteOutput = True

# Get cnddb data and boundary input/output
input_poly = r"K:\Library\State\CA\State_Wide\Bio\CNDDB\cnddb.shp"
Input_Poly_Feature_Layer = "cnddb_Feature_Lyr"
Buffer_output = "Buffer_Output"
Buffer_Distance = arcpy.GetParameter(1)
Selected_cnddb_poly = "cnddb_poly_selected".format(arcpy.GetParameter(1))
Selected_cnddb_point = "cnddb_point_selected".format(arcpy.GetParameter(1))
Selectingpoly = arcpy.GetParameter(2)
SQL_Query = arcpy.GetParameterAsText(3)
UpdatedField = "Cnddb_update"

# Make Feature layer from cnddb poly
arcpy.AddMessage("Creating CNDDB Polygon Feature Layer...")
arcpy.MakeFeatureLayer_management(input_poly, Input_Poly_Feature_Layer)

#Select by location cnddb polygon
arcpy.AddMessage("Selecting features by location...") 
arcpy.SelectLayerByLocation_management(Input_Poly_Feature_Layer, 'WITHIN_A_DISTANCE', Selectingpoly, Buffer_Distance, "NEW_SELECTION")
int(arcpy.GetCount_management(Input_Poly_Feature_Layer).getOutput(0))

#Select Layer by attribute
SelectLayerByAttribute_management(Input_Poly_Feature_Layer, "SUBSET_SELECTION", SQL_Query)

[LucasDanzinger]

Well, you do need a Feature Layer to use the Select Layer by Location tool, so if you drag and drop a shapefile, this will likely give you an error. However, if you completely take your script out of the equation, you should be able to do the following:

1. Right click your toolbox and select Add > Script
2. Click Next through the first 2 pages
3. On the parameter page, make your first parameter a feature class, and call it "input feature"
4. Make a second parameter called "SQL Expression" and select data type of SQL Expression
5. For the second parameter, in the properties, select obtained from "input feature"
6. Finish the tool, then double click it in the toolbox
7. Fill in the tool with a shp/fc
8. Click the SQL button and you should see the fields from the first parameter

See if this works, and if it does, implement the same type of logic into your script.

[Anonymous User]

Original User: alex92190

Well, you do need a Feature Layer to use the Select Layer by Location tool, so if you drag and drop a shapefile, this will likely give you an error. However, if you completely take your script out of the equation, you should be able to do the following:

1. Right click your toolbox and select Add > Script
2. Click Next through the first 2 pages
3. On the parameter page, make your first parameter a feature class, and call it "input feature"
4. Make a second parameter called "SQL Expression" and select data type of SQL Expression
5. For the second parameter, in the properties, select obtained from "input feature"
6. Finish the tool, then double click it in the toolbox
7. Fill in the tool with a shp/fc
8. Click the SQL button and you should see the fields from the first parameter

See if this works, and if it does, implement the same type of logic into your script.

I understand the logic, but then the "input feature" which in my script is "Input_Poly_Feature_Layer" will need to become a parameter when it is only supposed to be used as a temporary file... not a tool input that people can see. what I am trying to stay is that i would not really want this input layer to be a parameter for this tool. The logic of this tool is the following:
- People must be able to select by location
- Based on this selection by location, people would be able to query the fields/rows that were selected.
Does that make sense? A lot like this found here: http://gis.stackexchange.com/questions/35117/python-script-for-select-by-attributes-taking-user-inpu...

# Import system modules
import arcpy
from arcpy import env

# Set the workspace
env.workspace = arcpy.GetParameterAsText(0)
Dir = env.workspace

# Local variables
input = arcpy.GetParameterAsText(1)
polygon = arcpy.GetParameterAsText(2)
expression = arcpy.GetParameterAsText(3)
name = arcpy.GetParameterAsText(4)

# Make a layer from the input feature class
arcpy.MakeFeatureLayer_management(input, "lyr") 

# Select all points or polygons which overlap the polygon/s of interest
arcpy.SelectLayerByLocation_management("lyr", "intersect", polygon, 0, "new_selection")

# Within selected features, further select based on a SQL query within the script tool    
arcpy.SelectLayerByAttribute_management("lyr", "SUBSET_SELECTION", expression)

# Write the selected features to a new featureclass
arcpy.CopyFeatures_management("lyr", Dir + "\\" + str(name))

[AlexGole1]

I tried the method you indicated "obtained from". The only option available to me was an already created table: the selecting features poly used to select from the input table (layer from which I get all my info). I have no idea how to view the fields from a table which already has a selection in the query builder.

[Anonymous User]

Original User: ldanzinger

I think I understand what you want. Basically, you want the SQL query to reflect a table that is not a parameter but rather, a selection from a table. Based on what you describe, I don't believe it would be possible. The reason being is that you would want the SQL Query to reflect the selection, however, the selection wouldn't really be made until the script has started. However, at the time of clicking the SQL button, the script wouldn't have any way of knowing what the selection would be, as it was not executed yet.

[AlexGole1]

I think I understand what you want. Basically, you want the SQL query to reflect a table that is not a parameter but rather, a selection from a table. Based on what you describe, I don't believe it would be possible. The reason being is that you would want the SQL Query to reflect the selection, however, the selection wouldn't really be made until the script has started. However, at the time of clicking the SQL button, the script wouldn't have any way of knowing what the selection would be, as it was not executed yet.

That is right, that is exactly what I was wondering...I might switch the two selections then. 1) Select by location 2nd and 2) Select by attribute 1st so it selects from the input table (the features I want to select). Do I need to make it a parameter to view the fields then?