0

I have a list of street_names

['SH-701', 'SH-67', ' ABC', 'Dharampur Road', 'Kailash Road', 'Tithal Road', 'SH-6', 'Halar Road', 'SH-183', 'Ramji Tekra Road', 'Mahatma Gandhi Road', 'Bandar Road', 'M G Road', 'M Road', 'Station Road', 'Azad Chowk', 'Golden Quadrilateral', 'Service Road', 'NH-48', 'NH-8', 'Baldev Street', 'Stadium Road', 'Civil Road', 'Rotatry Circle', 'Auranga River Major Bridge', 'Police Headquarters Road', 'Ramroti Chowk', ' dalaram st', 'Maa Sharda Path', 'Tower Road', 'Pranami Street', 'Abrama Road', 'Atul Road', 'Gali No 7', 'Javahar Dhakka Road', 'Railway Yard Road', 'Azad Road', 'Shivraj Park Road', 'Vajifdar Street', 'Tithal Crass Lane', 'Desai Jagdishbhai Marg', 'Approach Road', 'Nana Parsiwad Road', 'Kosamba Road', 'Desaivad Road', 'NCB Road', 'Juna Kosamba Road', 'Mota Taiwad Road', 'Gachiwad Road', 'Dadiya Faliya Road', 'Valsad Callege Road', 'B Road', 'D Road', 'E Road', 'C Road', 'I Road', 'H Road', 'Kharia Road', 'Nanakwada Kharia Faliya Road', 'Nanakwada Road', 'Vinayak Road', 'Lal School Road', 'Nana Talwad Road', 'Sipaiwad Road', 'Malaviyaji Road', 'Agyari Street', 'Parsiwad Road', 'Disepensary Road', 'Moti Mehtwad Road', 'Ghandhi Chowk', 'Desai Street', 'Tandel Street', 'Divadandi Road', 'Mullawadi Road', 'Ramwadi Road', 'Geeta Sadan Road', 'Shahid Road', 'Satyam Road', 'Street Number 1', 'Ambelal Patel Marg', 'Mogravadi Road', 'Valsad Station Road', 'Thakorbhai Park Society Road', 'Jujwa Sugar Road', 'Tapawad Road', 'Jujwa Marala Road', 'IP Gandhi School Road', 'Tadkeshwar Road', 'Kapadia Chawl Bechar Road', 'Valsad Godi Road', 'Bechar Road', 'Railway Colony Road', 'Control Office Road', 'A Road', 'F Road', 'Devang Gandhi', 'Desai Faliya Halar Road', 'Suraj Srushti Halar Road', 'Patel Street', 'Krishna Park Road', 'White House Lane', 'Abrama Bridge', 'Leela Park Society Road', 'Wanki River Bridge Road']

I want to use SelectLayerByAttribute for multiple fields where it will iterate select layer by attribute function by looping through different street_names values for multiple fields each time and will provide me the required features.

I am using this code:

    for item in street_names:
        query = '"' + "ST_NAME1 = '" + item +  "' Or Remark1 = '" + item + "' Or ST_NAME2 = '" + item + "' Or Remark2 = '" + item + "' Or ST_NAME3 = '" + item + "' Or Remark3 = '" + item + "' Or ST_NAME4 = '" + item + "' Or Remark4 = '" + item + "' Or ST_NAME5 = '" + item + "' Or Remark5 = '" + item + "' Or ST_NAME6 = '" + item + "' Or Remark6 = '" + item + "' Or ST_NAME7 = '" + item + "' Or Remark7 = '" + item + "' Or ST_NAME8 = '" + item + "' Or Remark8 = '" + item + "' Or ST_NAME9 = '" + item + "' Or Remark9 = '" + item + "'" + '"'
        in_data = arcpy.management.SelectLayerByAttribute(self.street, "ADD_TO_SELECTION", query , "NON_INVERT")

But it is throwing this error:

return lambda *args: val(*gp_fixargs(args, True)) arcgisscripting.ExecuteError: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute).

I am using ArcGIS Pro 2.7 and I need to perform this using ArcPy package in a Python script.

3
  • 2
    String math, especially string math with more than a score of terms, is pretty much worst practice, and very difficult to debug (yes, these are tightly coupled). Instead, learn to use str.format()' with a triple-quote string. If you use '{item}'` as your placeholder, you can .format(item=item). You might even want to explore using str.join() to assemble your terms.
    – Vince
    Aug 21, 2021 at 12:19
  • @Thats great I was already thinking of that, Thanks Aug 21, 2021 at 13:01
  • Can you add a screenshot showing your attibute table. And an example of what you want to select
    – BERA
    Aug 22, 2021 at 8:13

2 Answers 2

1

Debugging your query assembly math with a fixed item of 'Named Road' using:

parts = query.split('Or')
print("Expression members:\n\t{:s}".format('OR\n\t'.join(parts)))

I got output:

    "ST_NAME1 = 'Named Road' OR
     Remark1 = 'Named Road' OR
     ST_NAME2 = 'Named Road' OR
     Remark2 = 'Named Road' OR
     ST_NAME3 = 'Named Road' OR
     Remark3 = 'Named Road' OR
     ST_NAME4 = 'Named Road' OR
     Remark4 = 'Named Road' OR
     ST_NAME5 = 'Named Road' OR
     Remark5 = 'Named Road' OR
     ST_NAME6 = 'Named Road' OR
     Remark6 = 'Named Road' OR
     ST_NAME7 = 'Named Road' OR
     Remark7 = 'Named Road' OR
     ST_NAME8 = 'Named Road' OR
     Remark8 = 'Named Road' OR
     ST_NAME9 = 'Named Road' OR
     Remark9 = 'Named Road'"

So it appears that you put double-quotes around the whole expression, making it one huge Boolean field name (hence the syntax error).

But there's lots of simplification opportunities here, exploiting the use of both str.format() and str.join():


    fields = []
    for i in range(1,10):
        fields.append("ST_NAME{:d}".format(i))
        fields.append("Remark{:d}".format(i))
    
    for item in street_names:
        terms = []
        for field in fields:
            terms.append("{:s} = '{:s}'".format(field,item))
        query = ' OR '.join(terms)
            
        in_data = arcpy.management.SelectLayerByAttribute(
                self.street, "ADD_TO_SELECTION", query , "NON_INVERT")

Not only is this cleaner, you also don't need to debug string math.

It will run slower (albeit not measurably slower, in the context of layer selection). I benchmarked the two different query assembly algorithms, and the .join added one millisecond to overall execution across all 104 street names). So then I tried a list comprehension in terms assembly:

    for item in street_names:
        terms = ["{:s} = '{:s}'".format(field,item) for field in fields]
        query = ' OR '.join(terms)

and was able to cut the cost to 0.5 milliseconds (measurement averaged across 500 iterations).

If you want double quotes around each field name (permitted, but not required for file geodatabase), then the list comprehension gets six extra characters:

    for item in street_names:
        terms = [""""{:s}" = '{:s}'""".format(field,item) for field in fields]
        query = ' OR '.join(terms)
1
  • Thanks I was able to get the answer following similar approach. Aug 24, 2021 at 14:02
-1

This approach helped.

for item in street_names: in_data = arcpy.management.SelectLayerByAttribute(self.street, "ADD_TO_SELECTION", "ST_NAME1 ='{}'".format(item) or "Remark1 ='{}'".format(item) or "ST_NAME2 ='{}'".format(item) or "Remark2 ='{}'".format(item) or "ST_NAME3 ='{}'".format(item) or "Remark3 ='{}'".format(item) or "ST_NAME3 ='{}'".format(item) or "Remark3 ='{}'".format(item) or "ST_NAME4 ='{}'".format(item) or "Remark4 ='{}'".format(item) or "ST_NAME5 ='{}'".format(item) or "Remark5 ='{}'".format(item) or "ST_NAME6 ='{}'".format(item) or "Remark6 ='{}'".format(item) or "ST_NAME7 ='{}'".format(item) or "Remark7 ='{}'".format(item) or "ST_NAME8 ='{}'".format(item) or "Remark8 ='{}'".format(item) or "ST_NAME9 ='{}'".format(item) or "Remark9 ='{}'".format(item), "NON_INVERT")

1
  • This code example evaluates the logical expression in Python and treats all the strings as Booleans (which equate to True if not empty).
    – Vince
    Aug 24, 2021 at 14:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.