I have tried multiple ways to create an expression for use in the Select by Attribute tool, however it does not work past OID 3. For some reason, all the expressions work for OID 1 and OID 2, however not for OID 3. So strange.

The feature class is located in a file gdb. Ive tried all the below expressions, but does not go past OID 3.

#create a searchcusor
with arcpy.da.SearchCursor(dg_fl, ["OID@"]) as cursor:
    for row in cursor:
        expression = '"OID" = {}'.format(row[0])
        #expression = ' "OID" = {} '.format(row[0])
        #expression = ' "OID" = \'{}\' '.format(row[0])
        #expression = '\"OID\" = \'{}\''.format(row[0])
        print "OID {}".format(row[0])

        arcpy.SelectLayerByAttribute_management(dg_fl, "NEW_SELECTION", expression)
        arcpy.SelectLayerByLocation_management(tl_fl, "INTERSECT", dg_fl)

        result = arcpy.GetCount_management(tl_fl)
        print "transit line count {}".format(int(result.getOutput(0)))
        if int(result.getOutput(0)) > 0:

        #joins the transit line data to the grid cells
            dg_sj = arcpy.SpatialJoin_analysis(dg_fl, tl_fl, "in_memory/DG_SJ_{}".format(row[0]), "JOIN_ONE_TO_MANY")
            print "spatial join created with subset of fishnet grid"
            #log.write("{}: spatial join created with subset of fishnet grid".format(DT.now()))

        #statistics table is created using shiptypegroup and counts
            #identify the case fields ie. the new fields in the table
            case_fields = ["TARGET_FID", "ShipTypeGr"]
            #set the count of the ship type as the stats we want
            statsfield = [["ShipTypeGr", "Count"]]
            #create the statistics
            ss = arcpy.Statistics_analysis(dg_sj, "in_memory/Statistics", statsfield, case_fields)
            print "statistics table created"
            #log.write("{}: statistics table created".format(DT.now()))

        #pivot table of the statistics
            pt = arcpy.PivotTable_management(ss, "TARGET_FID", "ShipTypeGr", "COUNT_ShipTypeGr", "in_memory/PivotTable")
            print "pivot table from statistics created"
            #log.write("{}: pivot table from statistics created".format(DT.now()))

        #joins the pivot table to the density grid
            #this environment setting means that you get the field names without the table name preceeding it
            arcpy.env.qualifiedFieldNames = False
            #join the pivot table data back to the density grid feature layer
            arcpy.AddJoin_management(dg_fl, "OID", pt, "TARGET_FID")
            #log.write("{}: pivot table joined to density grid".format(DT.now()))

        #finalises the density grid into the GDB
            dg = arcpy.CopyRows_management(dg_fl, "AIS_PT_Join_{}".format(row[0]))
            print "final ship counts for each cell joined to fishnet grid"
            #log.write("{}: final ship counts for each cell joined to fishnet grid".format(DT.now()))

        #remove all selections
            arcpy.SelectLayerByAttribute_management(tl_fl, "CLEAR_SELECTION")
            arcpy.SelectLayerByAttribute_management(dg_fl, "CLEAR_SELECTION")
            del dg_sj
            del ss
            del pt
            del dg

            print "no transit lines through grid cell {}".format(row[0])

I get the following prints and errors:

transit line count 0
no transit lines through grid cell 1
transit line count 3
spatial join created with subset of fishnet grid
statistics table created
pivot table from statistics created
final ship counts for each cell joined to fishnet grid
Traceback (most recent call last):
  File "<string>", line 254, in run_nodebug
  File "C:\Users\zahir.ibrahim\Documents\Private\Private\Python\MMO_tools\AIS_tools\Python_scripts\Density_Grid_1km_0.1_test_shp.py", line 42, in <module>
    arcpy.SelectLayerByAttribute_management(dg_fl, "NEW_SELECTION", expression)
  File "C:\Program Files (x86)\ArcGIS\Desktop10.4\ArcPy\arcpy\management.py", line 7182, in SelectLayerByAttribute
    raise e
arcgisscripting.ExecuteError: ERROR 000358: Invalid expression
Failed to execute (SelectLayerByAttribute).
  • 1
    In a file geodatabase the OID field is normally named Objectid. You can retrieve the name by arcpy.Describe(dg_fl).OIDFieldName to use in your expression. – klewis Sep 6 '17 at 15:44
  • 1
    Is an error produced? – Emil Brundage Sep 6 '17 at 17:02
  • Yes. After it iterates over the first two OID, it errors saying that the expression is incorrect. Which I don't understand why as it successfully runs the first two times. – MacroZED Sep 6 '17 at 17:03

I suspect it doesn't run on OID #3 because when it joins the statistics table on OID #2 the field names get modified internally. Try removing the join in your cleanup step.

If you don't need the selection in dg_fl for other things, one possibility is to use the geometry of the SearchCursor rows directly:

with arcpy.da.SearchCursor(dg_fl, ["SHAPE@"]) as cursor:
    for row in cursor:
        geom = row[0]            
        arcpy.SelectLayerByLocation_management(tl_fl, 'INTERSECT', geom)

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.