topic SQL query and Export Metadata in Transportation Questions

SQL query and Export Metadata

AlexGole1 — Fri, 15 Mar 2013 21:17:50 GMT

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

Re: SQL query and Export Metadata

LucasDanzinger — Fri, 15 Mar 2013 21:37:55 GMT

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.

Re: SQL query and Export Metadata

Anonymous User — Fri, 15 Mar 2013 21:46:14 GMT

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...

Re: SQL query and Export Metadata

LucasDanzinger — Fri, 15 Mar 2013 21:59:57 GMT

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).

Re: SQL query and Export Metadata

Anonymous User — Sat, 11 Dec 2021 17:30:12 GMT

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)

Re: SQL query and Export Metadata

LucasDanzinger — Fri, 15 Mar 2013 22:41:03 GMT

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.

Re: SQL query and Export Metadata

Anonymous User — Sat, 11 Dec 2021 17:30:15 GMT

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-input

# 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))

Re: SQL query and Export Metadata

AlexGole1 — Mon, 18 Mar 2013 17:58:57 GMT

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.

Re: SQL query and Export Metadata

Anonymous User — Mon, 18 Mar 2013 20:50:03 GMT

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.

Re: SQL query and Export Metadata

AlexGole1 — Mon, 18 Mar 2013 21:50:13 GMT

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?

Re: SQL query and Export Metadata

Anonymous User — Mon, 18 Mar 2013 22:26:58 GMT

Original User: ldanzinger

Yes, have that as an input parameter and use Obtained From for the SQL Query.

Re: SQL query and Export Metadata

AlexGole1 — Mon, 18 Mar 2013 22:46:23 GMT

Yes, have that as an input parameter and use Obtained From for the SQL Query.

Last question and I will stop there then. Is it possible to make a parameter that gives the user no control (i know it is weird), but i dont want the user to change the link to the shapefile.

Re: SQL query and Export Metadata

Anonymous User — Tue, 19 Mar 2013 16:56:37 GMT

Original User: ldanzinger

You could probably set an error message if the user changes it using a ToolValidator class.

Re: SQL query and Export Metadata

AlexGole1 — Thu, 21 Mar 2013 16:46:53 GMT

You could probably set an error message if the user changes it using a ToolValidator class.

Thank you my script works great. I am now trying to write metadata to a feature class.