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

TedKowal · ‎02-03-2015

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

TedKowal · ‎02-09-2015

My python debugging is not strong in comparison to .net.... but the code bombs out around line 22

RuntimeError: Too few parameters. Expected 1.

No clue .. everything appears to be properly coded. I noticed that this error code shows up in regards to an access geodatabase allot of times and the recommendation is to try file geodatase which is not an option for me...

I print out the field and the where_clause --- they looked fine?

BlakeTerhune · ‎02-09-2015

Can you post your modified code since you're not using that template table to get the fields?

TedKowal · ‎02-09-2015

I am using the template.

Ran your original code directly from Arcmap same error.

Modified the code slightly to run in a model -- same error

running arc map 10.2.2 python 2.7.5

The modified model version is:

import arcpy  
import os  


# Local variables  modified to work in the model as a script
#fielddb = arcpy.GetParameterAsText(3)  #in memory
panels = arcpy.GetParameterAsText (0)
supports = arcpy.GetParameterAsText (1)
template = arcpy.GetParameterAsText (2)


# 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)  
#arcpy.AddMessage(where_clause)


with arcpy.da.SearchCursor(panels, fields, where_clause) as s_cursor:  #too few paramenters. Expected 1
  with arcpy.da.InsertCursor(template, fields) as i_cursor:  
       for row in s_cursor:  
            i_cursor.insertRow(row)

BlakeTerhune · ‎02-09-2015

Forgive me, I haven't used model builder much so I may have the AddMessage() part wrong. Please feel free to fix as needed. I have only been running this code in PyScripter; not ArcMap or Model Builder.

My suggestion here is to put the code into a try statement and attempt to display more helpful debug information. Primarily, I'd like to know which operation it failed on (since you don't sound too sure about the line 22 bit).

import arcpy
import os

# Local variables  modified to work in the model as a script
#fielddb = arcpy.GetParameterAsText(3)  #in memory
panels = arcpy.GetParameterAsText (0)
supports = arcpy.GetParameterAsText (1)
template = arcpy.GetParameterAsText (2)

try:
    # 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)
    #arcpy.AddMessage(where_clause)

    with arcpy.da.SearchCursor(panels, fields, where_clause) as s_cursor:  #too few paramenters. Expected 1
      with arcpy.da.InsertCursor(template, fields) as i_cursor:
          for row in s_cursor:
                i_cursor.insertRow(row)


except Exception as err:
    if arcpy.GetMessages(2):
        arcpy.AddMessage(arcpy.GetMessages())
    else:
        arcpy.AddMessage(err)

TedKowal · ‎02-10-2015

I tried a few other things today... though it may be a file locking issue so I copied the template to the local database ... The error message is still the same even in your Try/Exception code

This is what the output shows when run

Executing (Create Table): CreateTable D:\Workspace\MaintUpdate\ProjGIS\83629_Baseline.mdb pTemplate \\mdxinfo\GIS\BaseData\Signs\MDX_Signs.mdb\SignPanelFieldTemplate #

Start Time: Tue Feb 10 10:56:53 2015

Succeeded at Tue Feb 10 10:57:00 2015 (Elapsed Time: 7.48 seconds)

Executing (Get Sign Panels for the Selected Supports): GetPanels \\mdxinfo\GIS\BaseData\Signs\MDX_Signs.mdb\SignPanels D:\Workspace\MaintUpdate\ProjGIS\83629_Baseline.mdb\SignSupports D:\Workspace\MaintUpdate\ProjGIS\83629_Baseline.mdb\pTemplate

Start Time: Tue Feb 10 10:57:01 2015

Running script GetPanels...

Too few parameters. Expected 1.

Completed script GetPanels...

Succeeded at Tue Feb 10 10:57:06 2015 (Elapsed Time: 5.86 seconds)

BlakeTerhune · ‎02-10-2015

Download this Python script file then right click, edit with IDLE.
Change the local variable paths as needed.
Then go to Run, Run Module.

TedKowal · ‎02-10-2015

Selecting WHERE SupportID IN(1001568, 1001498, 1001572, 1001584, 1001573, 1001575, 1001576, 1001574, 1001577, 1001579, 1001578, 1001580, 2001615, 1001585, 1001586, 2002193, 2002278, 2002215, 2002214, 2002194, 1001850, 1001861, 1001855, 1001854, 2001616, 1001860, 1001859, 1001824, 1001853, 1001848, 1001847, 1001849, 1001845, 1001846, 1001843, 1001844, 1001821, 2001715, 2001780, 2001781, 2001804, 2001979, 2001980, 2001981, 2002082, 2001923, 2001924, 2001925, 2001927, 2001948, 2001949, 2001950, 2002225, 2002280, 2002224, 2002282, 2002223, 2002222, 2002221, 2002290, 2002191, 2002293, 2002289, 2002220, 2002219, 2002192, 2002286, 2002218, 2002285, 1001603, 2001867, 1001613, 2002345, 1001618, 2001872, 1001622, 2002346, 1001630, 2002347, 2002348, 2002349, 1001636, 2002350, 2002511, 2002510, 2002549, 2002548, 2002577, 2002523, 2002517, 2002486, 2002547, 2002885, 2002906, 2002907, 2002913, 2002914, 2002915, 2002916, 2002917, 2002964, 2002918, 2002953, 2002954, 2002955, 2002975, 2003054, 2003053, 2003290, 2003406, 2003414, 2003422, 2003427)

Inserting rows into template table...

Too few parameters. Expected 1.

Done

BlakeTerhune · ‎02-10-2015

I just tried it again and it works fine for me. All tables and feature classes in the MDB you sent do have an ObjectID field. What version of ArcGIS are you on?

TedKowal · ‎02-10-2015

I have 10.2 .... The panels table has OID's in the version I sent -- used the create table to populate it In the "Real" table that oid field does not exists

BlakeTerhune · ‎02-10-2015

Aww, what are you doin' to me giving me data that isn't the same as what you're using! haha

I just created a new table (using MS Access) with no ObjectID field and the script still completed successfully and copied the rows. I'm on 10.2.2. Not sure why it's not working for you...

Could you try making new feature classes and tables that all have ObjectID fields, then edit the script to point to the new stuff and see if it runs.