topic Re: Evaluate Where Clause in Python Questions

Evaluate Where Clause

JoeBorgione — Sun, 12 Dec 2021 00:12:59 GMT

A new day, a new question...

Working with arcpy.da.SearchCursor, I can select features based on a given where statement. However, rather than selecting records, I just want to see if the where statement evaluates to 'true', and if so, update a field in the row of the cursor. Currently, for a selection I use:

where = "FullStreet = ('{}') AND inthousenum BETWEEN MinRange and MaxRange)".format(street)
 arcpy.SelectLayerByAttribute_management ("AddressPoints","ADD_TO_SELECTION",where)‍‍‍‍‍‍

So rather than select by attribute, I want to set a variable to 1 or 0 (or what ever) to indicate the success of the where statement, and take the appropriate action based on that value.

curtvprice‌ Dan_Patterson‌ dkwiens‌

Re: Evaluate Where Clause

JakeSkinner — Sun, 12 Dec 2021 00:13:01 GMT

Hi Joe,

You could use the MakeFeatureLayer function and pass your where clause to that. Then, execute the GetCount function to see if any records exist.

arcpy.MakeFeatureLayer_management("AddressPoints", "AddressPointsView", where)
count = int(arcpy.GetCount_management("AddressPointsView").getOutput(0))

if count > 0:
    # Perform action
else:
    # Where clause was invalid‍‍‍‍‍‍‍

Re: Evaluate Where Clause

DarrenWiens2 — Wed, 20 Sep 2017 15:53:41 GMT

I just want to see if the where statement evaluates to 'true', and if so, update a field in the row of the cursor.

Can't you just use an UpdateCursor, rather than SearchCursor?

UpdateCursor—Help | ArcGIS for Desktop

Re: Evaluate Where Clause

JoeBorgione — Sun, 12 Dec 2021 00:13:04 GMT

After fiddling a bit with both approaches suggested, I'm still fumbling as usual. None the less, perhaps a little more detail on my process may clarify things.

My intent is to cursor through a table and based on a couple field values, (I currently) select some records in a separate feature class. The table is 3300 records and the feature class has 400,000+ records. Here is how I cursor through the table (called NoHitStreets):

fields = ["FullStreet","MinRange","MaxRange"]
with arcpy.da.SearchCursor("NoHitStreets", fields) as cursor:
    for row in cursor:
      streetlist = [row[0]]
      street = " ".join(streetlist)
      minlist = [row[1]]
      maxlist = [row[2]]
      intmin = int(minlist[0])
      intmax = int(maxlist[0])
      where = "FullStreet = ('{}') AND NumericHouseNum <=({}) AND NumericHouseNum >= ({})".format(street,intmax,intmin)
      arcpy.SelectLayerByAttribute_management ("AddressPoints","ADD_TO_SELECTION",where)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

The NohitStreets table has a FullStreet field that is typically populated with something like "S MAIN ST" and it has two numeric fields MinRange and MaxRange which indicate the minimum and maximum bounding address ranges respectively, so values might 100 and 500. I want to see if there are any points in the AddressPoints feature class that are on S MAIN ST with a house number between 100 and 500.

This works, but with 3300 records to cursor through, and 400K+ points to select from, you can imagine it's pretty slow; and maybe that's just the nature of the beast.

The antithesis of this would be to cursor through the AddressPoints feature class, and see if there are any NoHitStreets records that meet that same basic criteria:

fields = ["FullStreet","NumericHouseNum"]
with arcpy.da.SearchCursor("AddressPoints", fields) as cursor:
         for row in cursor:
              streetlist = [row[0]]
              street = " ".join(streetlist)
               housenumlist = [row[1]]
               inthousenum = int(housenumlist[0])
               where = "FullStreet = ('{}') AND {} BETWEEN MaxRange AND MinRange".format(street,inthousenum)
               arcpy.SelectLayerByAttribute_management ("NoHitStreets","ADD_TO_SELECTION",where)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I tried adding an UpdateCursor at the location of the SelectLayerByAttribute_management line in this second scenario and it failed miserably:

###........where = "FullStreet = ('{}') AND {} BETWEEN MaxRange AND MinRange".format(street,inthousenum,inthousenum)

update = ["UpdateField"]
with arcpy.da.UpdateCursor(myintable,update) as cursor:
            for row in cursor: 
               row[0] =row[0] + 1
               cursor.updateRow(ro‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍w)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

### My concept, how ever flawed the execution may be, 
### is to update the UpDateField value in the NoHitsTable from
### 0 (zero) to 1 (one)
###
### This tells me "hey, you have at least one address point
### on this street within this range.  which the begs the question:
### Why don't you have corresponding centerline geometry?"‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Either way, I'd like to some how flag a record in the NoHitsTable when a corresponding AddressPoint feature meets the "Where" criteria. If anything I hope I'm providing you guys with a few laughs as I fumble and bumble my through this.....

Darren Wiens‌ Jake Skinner‌

Re: Evaluate Where Clause

DarrenWiens2 — Thu, 21 Sep 2017 00:05:55 GMT

Ah, okay. Here are my thoughts on speed, but someone correct me if I'm wrong.

You're in a situation that would naturally result in nested cursors (for each street, look through each address point).
Nested cursors are bad due to the exponential operations, but I think they might actually be faster in some cases than selections because selections are just so dreadfully slow individually. Either way, check here for Richard Fairhurst's cursor/dictionary pattern to speed things up: /blogs/richard_fairhurst/2014/11/08/turbo-charging-data-manipulation-with-python-cursors-and-dictionaries

So, I think you should load the points into a dictionary and read through streets with UpdateCursor. For each street, fetch the matching points and test whether they meet the street min/max criteria. If so, update the street row. Not sure if 400,000 records is reasonable for a dictionary, but I'd think so.

Re: Evaluate Where Clause

JoeBorgione — Thu, 21 Sep 2017 03:22:25 GMT

Thanks Darren- checking in here a little late at night, but I'll take at look at what you suggest in the morning. I actually let the selection scenario run as Ieft the office; just wanted to have something to show the client. I ran it a small set of points, and figured out a post process of the selected records of points and how they relate back to the no hits data. Sometimes a little strong arm data processing is warrented. I'll have soemthing to show, but I would very much like to improve my skills.

Thanks again-

dkwiens‌