Modelbuilder: How to select items from a tabular table base upon a field from another layer

[TedKowal]

I am an old VB/VB.Net guy and this one has me stumped in terms of building a tool using model builder so any assistance in leading me to a solution will be greatly appreciated.  (I can build a vbs script to this but I am attempting to do this within modelbuilder/python script [if necessary])

Background:

I work for a transportation agency.  Our GIS field collection is based upon new construction projects.  For any project, all of our GIS layers are extracted that fall within a buffered distance of the project along with all the attributes that are associated with the objects.  I felt that this would be a great place to use modelbuilder to make a tool that would do this so my tech's that have no SQL experience can quickly create a MS Access Field Geodatabase.  Old method was to manually export each layer (a process that could take upwards to a week to complete.

I completed 99.9% of the problem within the model and it can create the field database in a matter minutes.  However.....  I have one small problem.

Our traffic signs are stored in the database as two objects: 1.  Sign support (GIS point feature class)  2.  SignPanels which is an independent tabular table.   The linkage between both objects is the  SignSupportID  which is the primary key in the Sign Support layer and foreign key in the tabular table.

Problem:

I would like to create/copy  tabular table of specified fields from the SignPanels table that have a matching Sign Support ID from the Sign support GIS layer (This is a filtered set containing the sign support points within the project area)... This seems to be a simple common problem but I have hit wall.... 

Manually, I would simply create an sql statement selecting those SignPanels that were "in" the filtered sign support gis layer table. Select * from SignPanels SP where SP.SupportID in (Select SupportID from SignSupports where Project = "XXXXX")

Solution or Methodology needed:

.. using Model builder I need a Feature Class and Tabular Table along with  a query that can iterate through the FC for all the ID's ...)  I already have the Filtered GIS layer as an output in the model.  What I now need is to create a table containing my desired attributes of only those panels in the SignPanels table that have a support id that matches the Filtered GIS layer.

Confused?  Any Ideas?

Thanks ahead of time!

Ted

[BlakeTerhune · Accepted solution]

Either you've been in the trenches of this project too long or I'm not fully understanding the problem, but it seems like this is just a simple inner join. You should be able to use Add Join with the KEEP_COMMON option. You'll get all of the fields from both data sources (SignSupport and SignPanel) but you can pare it down to just what you need when you export it (or whatever else you want to do).

[TedKowal]

Yes I have been in the trenches of this project too long.... I have had too many interruptions.  I did have an Add Join at one time (probably when I was fresher earlier in the undertaking).  For some reason I thought the iterator was a little more full fledged and got bogged in that direction... knowing that I could perform a simple query to extract the filtered table at last resort leaving one manual step!

Mainly this project was an exercise for me to finally start to learn Python and the Modeling Tools which I have totally ignored up to this point!  I was and am determined to at least get one under my belt!

Thanks alot for pulling me out of my fog!

Ted

[BlakeTerhune]

After further investigation, JoinFields might be a better option since you can specify fields you want. And to clarify, if using a feature classes, you have to make a feature layer first before doing the join. After the join is done, field names will be like "feactureclassname.fieldname".

[TedKowal]

Unfortunately neither works in my case...the Join Fields in order for me to get something close I have to join the many to the one and takes too long and the script adds fields and changes fieldnames with the dataset qualifier to the output in both joins (Fields and Add)...  ESRI just does not have any good tabular data tools; unless I wanted a feature class or feature, which I don't.  I also ran across in making a feature layer/class in order to get the join to work when converting back to a table view it did not honor the alias name.... the model got too cludgy and messy for such a simple operation.

Although the join tool is the solution and does get the proper data it is of a format that is not usable .,.,. it will not work for my Automation project.  I am now looking at making (hopefully) a simple python script tool to export the records from the (many) from a unique list of keys. 

--- I wish they could bring back VBA --- I could have done this in my sleep -- create a recordset -- loop through it and append to a new table.  

The Dinosaur

[BlakeTerhune]

If you feel like posting some sample data we can look at how to get this working the way you want.

[TedKowal]

The sample dataset contains a table called AllSignPanels small representation my base data describing signs on supports on our system.  SignSupports is a FC containing all the Sign Supports in the Project Area.  What I am trying to put into the model --- create a table called SignPanels that contains all the sign panels that are on the supports within the Project Area.

AllSignPanels.SupportID = SignSupport.SupportID based the SignPanelTemplate.   A working query in the database  "SelectQueryIwishToModel"  creates a view of the table I wish to create.

-----------------------

I am also thinking of making a python script tool to construct an "in" statement for the where clause ....  thinking about adapting this script:

# Local Variables
OriginTable = "This must be a Table View or Feature Layer"
DestinationTable = "This must be a Table View or Feature Layer"
PrimaryKeyField = "Matching Origin Table Field"
ForiegnKeyField = "Matching Destination Table Field"


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


    # Add DBMS-specific field delimiters
    fieldDelimited = arcpy.AddFieldDelimiters(arcpy.Describe(OriginTable).path, PrimaryKeyField)


    # Determine field type
    fieldType = arcpy.ListFields(OriginTable, PrimaryKeyField)[0].type


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


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


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

[BlakeTerhune]

Well, I didn't end up using any joins after all! Like you mentioned, it was always writing the fields from both feature classes and I got tired of fighting with it. Here's my solution:

def main():
    import arcpy
    import os

    # Local variables
    sourcegdb = r"C:\temp\83629_Baseline.mdb"
    panels = os.path.join(sourcegdb, "AllSignPanels")
    supports = os.path.join(sourcegdb, "SignSupports")
    template = os.path.join(sourcegdb, "SignPanelTemplate")

    # Get all SupportIDs for where clause IN() statement
    supportid = tuple(i[0] for i in arcpy.da.SearchCursor(supports, "SupportID"))

    # Optional truncate
    arcpy.TruncateTable_management(template)

    # Retreive only panel records that have a support and write to output table
    fields = [f.name for f in arcpy.ListFields(template)]
    where_clause = "SupportID IN{}".format(supportid)
    with arcpy.da.SearchCursor(panels, fields, where_clause) as s_cursor:
        with arcpy.da.InsertCursor(template, fields) as i_cursor:
            for row in s_cursor:
                i_cursor.insertRow(row)


if __name__ == '__main__':
    main()

Since you had that nice SignPanelTemplate table, I just used that for the field names. If you want to overwrite the table each time, just truncate the table before you start writing the rows again.

[TedKowal]

That is a lot cleaner code than what I was going to try and do....  I see you were frustrated with the output of the join tools as well (That's what I was fighting!)

I will give this a shot and let you know.... I will however have to think about where to develop a template table  That was only included for clarification and does not really exist (Yet!)

Thanks for your help in this matter --- really appreciated!  I will be paying this back forward!

[BlakeTerhune]

I really only used the template table for the field names. You could easily just put the field names in a list variable instead of calling list fields on the template table.