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

[OLANIYANOLAKUNLE]

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?

[JamesCrandall]

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

[OLANIYANOLAKUNLE]

I want to use Arcpy and not ordinary PYTHON

[Anonymous User]

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)

[JamesCrandall]

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()

[RichardFairhurst · Accepted solution]

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.

[MathewCoyle]

...
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.

[Anonymous User]

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.

[JamesCrandall]

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

^this x2!  Voted -- thanks Richard!