topic Re: arcpy.SelectLayerByAttributes Where Clause in Python Questions

arcpy.SelectLayerByAttributes Where Clause

JoeBorgione — Sat, 11 Dec 2021 15:54:33 GMT

In post earlier today, (Another da.SearchCursor Issue ) through an oversight on my part I couldn't execute a select layer by attributes function. Silly me.... At rate, I've moved past that issue but now faced with constructing a where clause that involves membership in a dictionary.

Essentially, I've constructed a dictionary of street types that looks something like this:

typeDict = {"AVE":1,"BLVD":2,"CIR":3}  #etc, etc‍‍‍‍

As best as I can tell, the way to see if a given string is a member of a dictionary, one can :

>>> typeDict
{'BLVD': 2, 'AVE': 1, 'CIR': 3}
>>> "AVE" in typeDict
True
>>> "ST" in typeDict
False
>>> "ST" not in typeDict
True
>>> if "somevalue" not in typeDict:
...     print "nope"
...     
nope

‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Okay, easy enough. The problem is constructing a valid "where clause' for the SelectLayerByAttribute() when considering membership in a dictionary. I've tried dozens of {}.format() approaches but the red font on my screen looks something like the U.S. spend deficit. I need some advice to successfully select a record or records based on the dictionary:

intable = "C:\Path\To\File.gdb\Table
arcpy.MakeTableView_management(intable,"view")
typeDict = {"AVE":1,"BLVD":2,"CIR":3}  #etc, etc‍‍‍

fields = ["Type","StreetName"]
where = "StreetName >= 'A'    #just concerned with named streets, no numbers

with arcpy.da.SearchCursor("view",fields,where) as cursor:
    for row in cursor:
       Select = #need to format a where clause that
                # evalutes row[0] not in typeDict
       arcpy.SelectLayer,ByAttribute(view,"ADD_TO_SELECTION",Selection)‍‍‍‍‍‍‍‍‍‍‍‍

jamesfreddyc‌

bixb0012‌

Dan_Patterson‌

(I should just create a signature block with you guys in it; maybe I can convince my boss to put you on retainer....)

Re: arcpy.SelectLayerByAttributes Where Clause

JoshuaBixby — Wed, 15 Nov 2017 23:03:51 GMT

Instead of making specific code suggestions, let's take a step back to understand what you are trying to accomplish. It seems to me you are trying to select all streets that are not avenues, boulevards, circles, etc...., i.e., you have a set of criteria that you want to invert. Is this correct? If so, what do you plan on doing with the selected records?

Generally, Select By Layer or Select By Attribute within a cursor can be refactored, especially when the cursor and selections are all based on the same layer or view.

Re: arcpy.SelectLayerByAttributes Where Clause

JoeBorgione — Wed, 15 Nov 2017 23:27:26 GMT

Joshua- this is part of an overall QA/QC procedure I'm trying to implement. In this particular case, I will be examining streets and the value of the "Type" field and if an editor has added a named street, the Type field must be populated by at least on value in the dictionary: a lot like validating a domain, but without the domain.

Members of the selected set are written to a table in memory called "Errors". Errors_in_memory has a field called "Error_Description" which I update with a arcpy.CalculateField_management(); once that is done, those records are appended to a table on disc that is used as a check list to 'fix' any errors. Errors_in_memory is then cleaned out with a DeleteRows_management() and I go on to the next QA/QC loop which could be a look at the numbered streets (where = "StreetName < 'A'") and select those where "Type is not None". They get written to Errors_In_Memory, flagged with a description and then appended to disc, and so on....

Thanks.

Re: arcpy.SelectLayerByAttributes Where Clause

DarrenWiens2 — Sat, 11 Dec 2021 15:54:36 GMT

Working backwards, you need to use SQL in your where clause, something like:

StreetName not in ('BLVD', 'AVE', 'CIR')‍

So, you need to somehow format your dictionary keys into a list of quoted strings, like in the final line below:

typeDict = {"AVE":1,"BLVD":2,"CIR":3}
print typeDict
print typeDict.keys()
print ','.join(typeDict.keys())
print ','.join("'{}'".format(w) for w in typeDict.keys())

...
{'BLVD': 2, 'AVE': 1, 'CIR': 3}
['BLVD', 'AVE', 'CIR']
BLVD,AVE,CIR
'BLVD','AVE','CIR'‍‍‍‍‍‍‍‍‍‍‍

Re: arcpy.SelectLayerByAttributes Where Clause

JoeBorgione — Sat, 11 Dec 2021 15:54:38 GMT

I kind of went down that route with

>>> print list
['BLVD', 'AVE', 'CIR']
>>> where = "Type not in list"
>>> where
'Type not in list'
‍‍‍‍‍

but when I try to get where into something SQL likes, that's where things go south...

Re: arcpy.SelectLayerByAttributes Where Clause

deleted-user-qzyyiYme1rz9 — Thu, 16 Nov 2017 01:02:23 GMT

Joe, first of all, you have the list in quotes, so it isn't reading your variable. Second, you need to change the list to string format with parentheses instead of brackets. See if this works.

>>> list = ['BLVD', 'AVE', 'CIR']
>>> list = str(list)
>>> list = list.replace("[", "(")
>>> list = list.replace("]", ")")
>>> print list
('BLVD', 'AVE', 'CIR')
>>> where = "Type NOT IN "+list
>>> print where
Type NOT IN ('BLVD', 'AVE', 'CIR')

Re: arcpy.SelectLayerByAttributes Where Clause

DanPatterson_Retired — Sat, 11 Dec 2021 15:54:41 GMT

Joe... for the back burner... but using Feature or Table to NumpyArray... even for one column, conversions can be quasi-vectorized easily, once you have a couple of steps. Shown verbosely, but in essence, if you already have a dictionary of key/value pairs, they can be converted to an array, then the whole field can be queried by case rather than by row... a small demo to give you ideas for the future.

# ---- Start with the beginning fluff of taking the data, making a temporary
#      storage array for output
#      Convert your dictionary... then run the 'for' loop which queries by case
#      rather than by row (this of course can be done all at once, but loops are 
#      good for now
#
a = ['AVE', 'Not', 'BLVD', 'Nope', 'CIR', 'AVE', 'AVE', 'BLVD', 'CIR', 'nada']
a = np.array(a)
result = np.full(a.shape, -9, dtype='int32')
typeDict = {"AVE": 1, "BLVD": 2, "CIR": 3}  # ad nauseum
dt = [('Lookfor', '<U5'), ('ReplaceWith', '<i4')]
arr = np.array(list(typeDict.items()), dtype=dt)
ins = arr['Lookfor']
outs = arr['ReplaceWith']
frmt = """
Input data array
{}
Temporary holding array
{}\n
Dictionary to array conversion
{}
lookfor ..... {}
replace with. {}
-----
"""
print(frmt.format(a, result, arr, ins, outs))

for i in range(len(arr)):
    #result[np.where(a == arr['Lookfor'])[0]] = arr['ReplaceWith']
    result[np.where(a == ins[i])[0]] = outs[i]

frmt = """
magic done
In again, then out
{}
{}
"""
print(frmt.format(a, result))‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Then the results of the demo are

input data
['AVE' 'Not' 'BLVD' 'Nope' 'CIR' 'AVE' 'AVE' 'BLVD' 'CIR' 'nada']
output result
[-9 -9 -9 -9 -9 -9 -9 -9 -9 -9]
dictionary to array
[('AVE', 1) ('BLVD', 2) ('CIR', 3)]
lookfor ..... ['AVE' 'BLVD' 'CIR']
replace with. [1 2 3]
-----


magic done
In again, then out
['AVE' 'Not' 'BLVD' 'Nope' 'CIR' 'AVE' 'AVE' 'BLVD' 'CIR' 'nada']
[ 1 -9  2 -9  3  1  1  2  3 -9]


# ---- you use arcpy.da.ExtendTable to attach the output result to the original
#      table...  Works best when your cases are obviously less than your records‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: arcpy.SelectLayerByAttributes Where Clause

curtvprice — Sat, 11 Dec 2021 15:54:44 GMT

Here's how I do it:

list = ['BLVD', 'AVE', 'CIR']
list1 = ",".join(["'{}'".format(k) for k in list])
where = "FIELD IN ({})".format(list1)
print(where) 
# prints: FIELD IN ('BLVD','AVE','CIR')‍‍‍‍‍

Re: arcpy.SelectLayerByAttributes Where Clause

DanPatterson_Retired — Sat, 11 Dec 2021 15:54:47 GMT

I might have it now (I think)

Basically you want to check a street address to see if a street type occurs and if it does, you want to produce a new column which reclasses the street type.

Lets consider the following

from_s = ['Road', 'Street','Lane', 'Court']
to_s = [10, 20, 30, 40]

So in the above, if the address name contains any of the values in the 'from_s' you want to reclass that address to a new type for whatever purpose.

It isn't fully vectorized, but here is what I did in verbose form

# ---- Read in your street index and query fields shortcutting the searchcursor stuff
#
in_fc = r'C:\Your_spaceless\path_to_your\address.gdb\streets'
flds = ['OID@', 'Address_']
args = [in_fc, flds, None, None, False, (None, None)]
cur = arcpy.da.SearchCursor(*args)
a = cur._as_narray()
# ----
# ---- Establish your 'from' and 'to' classes
#      Make a temporary array for filling and ultimately joining
# ----
from_s = ['Road', 'Street','Lane', 'Court']
to_s = [10, 20, 30, 40]
dt = [('IDs', '<i4'), ('Str_class', '<i4')]
out = np.zeros((len(a),), dtype=dt)
out['IDs'] = np.arange(1, 1001, dtype='int32')
#
# ---- Let's rock and roll
cnt = 0
for f in from_s:
    idx = np.array([i for i, item in enumerate(a['Address_'])
                    if f in item])
    out['Str_class'][idx] = to_s[cnt]
    cnt += 1
arcpy.da.ExtendTable(in_fc, 'OBJECTID', out, 'IDs')‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

The reveal... Is this what you want to do?

I should point out that would be prudent to use a null value for an integer field this could be 'None' for text fields or another value for integer fields, for example on line 15.5 you could add

out['Str_class'].fill(-9)‍

Re: arcpy.SelectLayerByAttributes Where Clause

JoeBorgione — Thu, 16 Nov 2017 13:02:17 GMT

Thanks Dan, but way more than I need. Boiled down to extreme basics: I want to examine a table of records and determine if each record has a valid value in a given field. Validity is confirmed by virtue of comparing the field value against a list. If the record's field value is confirmed as valid, move on to the next record. If the record's field value is confirmed as not valid, write that record to another table: then move on to the next record in the original table for examination....

Re: arcpy.SelectLayerByAttributes Where Clause

DanPatterson_Retired — Thu, 16 Nov 2017 14:00:24 GMT

darn... so I guess you don't want to see my next incarnation... I will leave that to a blog

Re: arcpy.SelectLayerByAttributes Where Clause

JoeBorgione — Thu, 16 Nov 2017 14:12:04 GMT

I'll read it there!

Thanks!

Re: arcpy.SelectLayerByAttributes Where Clause

JoeBorgione — Sat, 11 Dec 2021 15:54:49 GMT

Thanks Alex- I simply created list directly as:

>>> list = "('Blah','BlahBlay','BlahBlahBlah')"
>>> print list
('Blah','BlahBlay','BlahBlahBlah')
>>> where = "NOT IN"+list
>>> print where
NOT IN('Blah','BlahBlay','BlahBlahBlah')
>>>‍‍‍‍‍‍‍

Re: arcpy.SelectLayerByAttributes Where Clause

JoshuaBixby — Thu, 16 Nov 2017 15:23:16 GMT

You have a field named "Type" and you mention domain-like functionality for that field. Is there only a set list of choices, or at least structured choices, for that field? That is, you would only find "AVE" but not "avenue?" Or, you would have "BLVD" but not "West BLVD?"

How structured the data is strongly influences how much work is involved in building an SQL statement. If the data is loosely structured, i.e., it could have "AVE" and "avenue" or "ST" and "street", then using a cursor with Python to generate a selection set is probably better than building an complicated SQL WHERE statement.

Re: arcpy.SelectLayerByAttributes Where Clause

JoeBorgione — Thu, 16 Nov 2017 16:10:06 GMT

Thankfully the data is tight" AVE, ST, BLVD etc. No Ave, no Str etc...