SQL Query Modelbuilder

981
5
09-17-2018 07:06 PM
chadhickman
Occasional Contributor

Does anyone know if you can add an SQL query that applies to multiple features in modelbuilder?

0 Kudos
5 Replies
DanPatterson_Retired
MVP Esteemed Contributor

If it uses a tool in ArcToolbox and you can do the process using only the tools in arctoolbox, then yes.  If it gets fancier, then you can incorporate scripts in the model.  It may be easier in that case to simply script what you need.

Perhaps if you can show what it is that you are trying to do, other specific suggestions might come forward.

0 Kudos
chadhickman
Occasional Contributor

Yes all the tools I am using are in ArcToobox. Basically I would like a SQL and/or drop-down to select the appropriate county across several feature classes.

0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

do you currently do that using the Select By Attributes tool?

0 Kudos
chadhickman
Occasional Contributor

No I am not using the Select by Attributes tool, should I be?

0 Kudos
TedKowal
Regular Contributor II

On a similar note and using "Select by Attributes", as Dan suggested that you can use a python script to build further complexity.  Following is an example of a script that I used in a model to build a where clause to get a one to many related records ....  hope this helps (it is crude but it gets the job done... I am a vb programmer not python) ....

import arcpy, os

# Local Variables
OriginTable = arcpy.GetParameterAsText(0) #Sign Support -->   Make Table View
DestinationTable = arcpy.GetParameterAsText(1) #Sign Panels --->  Make Table View
PrimaryKeyField = arcpy.GetParameterAsText(2) #SupportID /Sign Support
ForiegnKeyField = arcpy.GetParameterAsText(3) #SupportID /Sign Panels

def buildWhereClauseFromList(OriginTable, PrimaryKeyField, valueList):
  """Takes a list of values and constructs a SQL WHERE
       clause to select those values within a given PrimaryKeyField
       and OriginTable."""

  fieldDelimited = arcpy.AddFieldDelimiters(arcpy.Describe(OriginTable).path, PrimaryKeyField)

  # Determine field type
  fieldType = arcpy.ListFields(OriginTable, PrimaryKeyField)[0].type
  # Add DBMS-specific field delimiters

  # Add single-quotes for string field values
  if str(fieldType) == 'String':
    valueList = ["'%s'" % value for value in valueList]

  # Format WHERE clause in the form of an IN statement
  whereClause = "%s IN(%s)" % (fieldDelimited, ', '.join(map(str, valueList)))
  #arcpy.AddMessage(whereClause)
  return whereClause

def selectRelatedRecords(OriginTable, DestinationTable, PrimaryKeyField, ForiegnKeyField):
    """Defines the record selection from the record selection of the OriginTable
      and apply it to the DestinationTable using a SQL WHERE clause built
      in the previous definition"""

    # Set the SearchCursor to look through the selection of the OriginTable
    sourceIDs = set([row[0] for row in arcpy.da.SearchCursor(OriginTable, PrimaryKeyField)])

    # Establishes the where clause used to select records from DestinationTable
    whereClause = buildWhereClauseFromList(DestinationTable, ForiegnKeyField, sourceIDs)

    # Process: Select Layer By Attribute
    arcpy.SelectLayerByAttribute_management(DestinationTable, "NEW_SELECTION", whereClause)

# Process: Select related records between OriginTable and DestinationTable
selectRelatedRecords(OriginTable, DestinationTable, PrimaryKeyField, ForiegnKeyField) #output same as Destination Table
0 Kudos