SQL Query Modelbuilder

Lebowski — Tue, 18 Sep 2018 02:06:23 GMT

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

Re: SQL Query Modelbuilder

DanPatterson_Retired — Tue, 18 Sep 2018 02:16:49 GMT

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.

Re: SQL Query Modelbuilder

Lebowski — Tue, 18 Sep 2018 13:43:30 GMT

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.

Re: SQL Query Modelbuilder

DanPatterson_Retired — Tue, 18 Sep 2018 14:16:34 GMT

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

Re: SQL Query Modelbuilder

TedKowal — Sat, 11 Dec 2021 16:26:07 GMT

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

Re: SQL Query Modelbuilder

Lebowski — Tue, 18 Sep 2018 14:47:17 GMT

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

topic Re: SQL Query Modelbuilder in Geoprocessing Questions

SQL Query Modelbuilder

Re: SQL Query Modelbuilder

Re: SQL Query Modelbuilder

Re: SQL Query Modelbuilder

Re: SQL Query Modelbuilder

Re: SQL Query Modelbuilder