Setting query loop in ModelBuilder/ArcPy?

Question

I am looking to apply a Select By Location protocol to my data, but need to first query individual IDs in my target and source layer (the same ID in each table, one at a time). How do I loop through a query? I have far too many IDs to manually set a query in each table then perform the selection.

Is there a way to create a looping model (with parameters) or script that will systematically select each (matching) ID in my target and source layers then perform the tools I specify?

I have used the Iterate Feature Selection tool in ModelBuilder to select by ID field in my target layer, but ModelBuilder will not let me have two iterators in the model (even if I could, there is no way to guarantee the iterators are selecting the same IDs at the same time). I have tried linking the Value output of the iterator to a Select by Attribute tool for my source layer but the value can only be set as the whole expression, not part of it.
So I am nearly there.

enter image description here

---

After extensive discussion with Roy this is the code I've come up with. Unfortunately, while no errors pop up when I run it, nothing actually happens. The process is marked as completely immediately after I run it and nothing has changed. Any thoughts?

rows = arcpy.SearchCursor("hmb_logbook_2009")
for row in rows:
 whereClause = '"trip_id" = ' + str(row.trip_id)
 arcpy.MakeFeatureLayer_management("hmb_logbook_2009", "currentLines", whereClause)
 arcpy.SelectLayerByLocation_management("hmb2009", "WITHIN_A_DISTANCE", "currentLines", "1 Miles", "NEW_SELECTION")
 arcpy.SelectLayerByAttribute_managemet("hmb2009", "SUBSET_SELECTION", ' "trip_id" = ' + str(row.trip_id))
 arcpy.CalculateField_management("hmb2009", "match_activity", 1)
 arcpy.SelectLayerByAttribute_managemet("hmb2009", "CLEAR_SELECTION")
 arcpy.Delete_management("currentLines")
del row, rows 

• AlmaThom: Using in line variable substitution in the Expression field of my Select tool to reference the current ID in the iterator does not work. What am I doing wrong?
• nagytech: Export your script to python, understand how it does what it does, then develop it further in python, not model builder. Specifically you're going to need to learn the foreach and for processes.
• AlmaThom: Yeah I gave this a stab in Python but putting a Select tool inside a Cursor gave me a 'cannot acquire lock' error. Obviously the Iterator tools won't export to Python so I've been trying to solve the problem there but also looked for alternatives.

Answer 1

@Geoist is right. You'll have much more control over this using Python -- you can nest as many loops as you damn well please.

Pseudo Code:

1. Create a search cursor
2. Loop through each record with a for loop
   • Compose a whereClause using the ObjectID (unique identifier)
   • Make feature layer
   • Select by location
   • etc. (delete feature layer to end the loop)

Code Example:

try:
 polys = arcpy.SearchCursor(polygonFeatures)
 for poly in polys:
 # Create a clause to select only the current record
 whereClause = '"OBJECTID" = ' + str(poly.OBJECTID)
 # Create a feature layer from the current row only
 arcpy.MakeFeatureLayer_management(polygonFeatures, "currentPolygon", whereclause)
 # Select all features from thisFeature that intersect the feature layer
 arcpy.SelectLayerByLocation_management(thisFeature, "INTERSECT", "currentPolygon")
 # Do your thing with your spatial selection (this example: create a new featureclass)
 arcpy.Copy_management(thisFeature, outFeatureClass)
 arcpy.Delete_management("currentPolygon")
except:
 print arcpy.GetMessages()
finally:
 # This will run if the script is successful or not.
 if row:
 del row, rows

• Brent Edwards: The Model Builder is good for testing out and visualizing workflows. It is also great starting point for developing scripts, using the export option to export to your scripting language of choice. That being said, it can be very difficult to build a model that runs consistently across different sessions, connections and machines. In the long run, the extra effort necessary to develop a script is worthwhile for the added flexibility, control and stability. Have fun.
• Roy: Agreed - I use model builder for documenting my analysis (it's much easier for most people to understand than looking at code). That being said I don't like to export models to scripts. It's sort of like using Dreamweaver to make a website WYSIWYG style -- I'm not a fan of the code it churns out. Might just be a personal style preference tho.
• AlmaThom: I understand where and for clauses, my problem is that if I use Python I am nesting an arcpy tool (Select By Location) in a Cursor and for whatever reason that doesn't work for me. I get an error that there is a lock on the layer. I will try it again though.
• Roy: If that's the case we need the error messages -- I use this technique regularly, and can confirm that it works.
• Roy: Then you might as well continue to use an UpdateCursor now -- use a try:, except: and finally: block to make sure you delete your cursor at the end of your script as illustrated in my sample code.