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