topic Re: I WANT TO CONSTRUCT AN SQL STATEMENT TO SELECT EVEN OR ODD FIDs USING ARCPY in Python Questions

I WANT TO CONSTRUCT AN SQL STATEMENT TO SELECT EVEN OR ODD FIDs USING ARCPY

OLANIYANOLAKUNLE — Wed, 19 Jun 2013 11:43:51 GMT

I want to construct an SQL Statement to select even or odd FIDs/OBJECTIDs using Arcpy, I tried MOD("FID", 2) = 1, this is not working from my python window but it is from my table view. Any suggestions please?

Re: I WANT TO CONSTRUCT AN SQL STATEMENT TO SELECT EVEN OR ODD FIDs USING ARCPY

JamesCrandall — Sat, 11 Dec 2021 09:30:31 GMT

Not sure about a full SQL statement (maybe post up what you have so far), but this python function will check if the value you pass in is even or odd...

def EvenOrOdd(val):
  x = int(val)
  if x % 2 == 0:
    return "EVEN"
  else:
    return "ODD"

msg = EvenOrOdd(6)
print msg

Re: I WANT TO CONSTRUCT AN SQL STATEMENT TO SELECT EVEN OR ODD FIDs USING ARCPY

OLANIYANOLAKUNLE — Wed, 19 Jun 2013 12:22:54 GMT

I want to use Arcpy and not ordinary PYTHON

Re: I WANT TO CONSTRUCT AN SQL STATEMENT TO SELECT EVEN OR ODD FIDs USING ARCPY

Anonymous User — Sat, 11 Dec 2021 09:30:34 GMT

I do not know of any arcpy functions to do this...You will need to use the method that James provided using the modulus. You could do something like this:

import arcpy
fc = r'G:\Data\Geodatabase\Cedar_County.gdb\JURISDICTION\CORP_LIM'
oid = arcpy.Describe(fc).OIDFieldName
even = []
odd = []
rows = arcpy.SearchCursor(fc)
for row in rows:
    fid = row.getValue(oid)
    if fid %2 == 0:
        even.append(fid)
    else:
        odd.append(fid)
del row, rows

field = arcpy.AddFieldDelimiters(fc, oid)
even_sql = ' OR '.join('%s = %s' %(field,i) for i in even)
odd_sql = ' OR '.join('%s = %s' %(field,i) for i in odd)

# Make Feature layers
lyr = arcpy.MakeFeatureLayer_management(fc, 'Temp_layer')

# Select Even
arcpy.SelectLayerByAttribute_management(lyr, 'NEW_SELECTION', even_sql)

# Select odd
arcpy.SelectLayerByAttribute_management(lyr, 'NEW_SELECTION', odd_sql)

OR, if you have a lot of features it may not be practical to build a very long query. You may want to temporarily add a field to tell whether the fid is even or odd (these fid's are subject to change with shapefiles so be careful) and select using that new field:

import arcpy
fc = r'G:\Data\Geodatabase\Cedar_County.gdb\JURISDICTION\CORP_LIM'
oid = arcpy.Describe(fc).OIDFieldName

#Add Field to tell if even/odd
arcpy.AddField_management(fc, 'Type', 'TEXT',4)
rows = arcpy.UpdateCursor(fc)
for row in rows:
    fid = row.getValue(oid)
    if fid %2 == 0:
        row.Type = 'Even'
    else:
        row.Type = 'Odd'
    rows.updateRow(row)
del row, rows

typeField = arcpy.AddFieldDelimiters(fc, 'Type')
even_sql = "%s = 'Even'" %typeField
odd_sql = "%s = 'Odd'" %typeField

# Make Feature layers
lyr = arcpy.MakeFeatureLayer_management(fc, 'Temp_layer')

# Select Even
arcpy.SelectLayerByAttribute_management(lyr, 'NEW_SELECTION', even_sql)

# Select odd
arcpy.SelectLayerByAttribute_management(lyr, 'NEW_SELECTION', odd_sql)

Re: I WANT TO CONSTRUCT AN SQL STATEMENT TO SELECT EVEN OR ODD FIDs USING ARCPY

JamesCrandall — Sat, 11 Dec 2021 09:30:36 GMT

I was working out the SQL portion and noticed that Caleb has an excellent solution!

Thanks -- this is useful.

Edit: here's what I was working on. I just incorporated the sql from Caleb's example...

def select_Even():
  mxd = arcpy.mapping.MapDocument("CURRENT")
  df = arcpy.mapping.ListDataFrames(mxd, "Layers")[0]
  for lyr in arcpy.mapping.ListLayers(mxd):
    if lyr.name=='thelayernameintheTOC':
      
      even = []
      cursor = arcpy.SearchCursor(lyr)      
      for row in cursor:
        if int(row.OBJECTID) % 2 == 0:
          even.append(int(row.OBJECTID))
        
      field = "OBJECTID"    
      even_sql = ' OR '.join('%s = %s' %(field,i) for i in even)
      arcpy.SelectLayerByAttribute_management(lyr, 'NEW_SELECTION', even_sql)

    else:
      arcpy.AddMessage("layer not found")

select_Even()

Re: I WANT TO CONSTRUCT AN SQL STATEMENT TO SELECT EVEN OR ODD FIDs USING ARCPY

RichardFairhurst — Wed, 19 Jun 2013 13:25:43 GMT

I was working out the SQL portion and noticed that Caleb has an excellent solution!

Thanks -- this is useful.

Edit: here's what I was working on. I just incorporated the sql from Caleb's example...

def select_Even():   mxd = arcpy.mapping.MapDocument("CURRENT")   df = arcpy.mapping.ListDataFrames(mxd, "Layers")[0]   for lyr in arcpy.mapping.ListLayers(mxd):     if lyr.name=='thelayernameintheTOC':              even = []       cursor = arcpy.SearchCursor(lyr)             for row in cursor:         if int(row.OBJECTID) % 2 == 0:           even.append(int(row.OBJECTID))                field = "OBJECTID"           even_sql = ' OR '.join('%s = %s' %(field,i) for i in even)       arcpy.SelectLayerByAttribute_management(lyr, 'NEW_SELECTION', even_sql)      else:       arcpy.AddMessage("layer not found")  select_Even()

The SQL Modulus statement in the original post would work for some databases if it had been used with the SelectLayerByAttributes tool, but it would not work for all databases. Two SQL statements that work for virtually every database that do not require use of the modulus operator (which varies widely in its format from database to database) would be the following (the first statement selects even numbers and second selects odd):

Round(NumberField / 2, 0) = NumberField / 2
Round(NumberField / 2, 0) <> NumberField / 2

Just make that the SQL statement (obviously substituting your real field name with correct field delimiters for NumberField) and make it the query expression for the SelectLayerByAttribute tool. The statements above assume the numbers you are evaluating are integers. If the numbers are doubles with fractional portions, the expression should be modified to the following:

Round(Round(NumberField, 0) / 2, 0) = Round(NumberField, 0) / 2
Round(Round(NumberField, 0) / 2, 0) <> Round(NumberField, 0) / 2

Appending ObjectIDs into an SQL list is not a good approach if your database is very large (100K+ records), because the list will become huge and will take a long time to process (assuming it does not just fail). Plus by reading the database with a cursor you are effectively doubling the database read time, since the SelectLayerByAttribute tool will have to read the entire database again to select everything, making that approach very inefficient.

Re: I WANT TO CONSTRUCT AN SQL STATEMENT TO SELECT EVEN OR ODD FIDs USING ARCPY

MathewCoyle — Wed, 19 Jun 2013 13:54:12 GMT

...
Round(NumberField / 2, 0) = NumberField / 2
Round(NumberField / 2, 0) <> NumberField / 2
...
Appending ObjectIDs into an SQL list is not a good approach if your database is very large (100K+ records), because the list will become huge and will take a long time to process (assuming it does not just fail). Plus by reading the database with a cursor you are effectively doubling the database read time, since the SelectLayerByAttribute tool will have to read the entire database again to select everything, making that approach very inefficient.

For OID filtering this is the best answer here.

Re: I WANT TO CONSTRUCT AN SQL STATEMENT TO SELECT EVEN OR ODD FIDs USING ARCPY

Anonymous User — Wed, 19 Jun 2013 14:03:35 GMT

the expression should be modified to the following:

Round(Round(NumberField, 0) / 2, 0) = Round(NumberField, 0) / 2
Round(Round(NumberField, 0) / 2, 0) <> Round(NumberField, 0) / 2

Wow...this is pretty cool. I also vote for Richards answer.

Re: I WANT TO CONSTRUCT AN SQL STATEMENT TO SELECT EVEN OR ODD FIDs USING ARCPY

JamesCrandall — Wed, 19 Jun 2013 14:24:12 GMT

Wow...this is pretty cool. I also vote for Richards answer.

^this x2! Voted -- thanks Richard!