Points in Polygon Count: Error with arcpy.selectLayerByLocation_management

Question

I'm working on generating some multivariate hexbins and am running into a problem with the initial step of counting the number of points (for the first variable) that are within each hex polygon. Here is the code that I have generated.

import arcpy

arcpy.env.overwriteOutput = True
arcpy.env.workspace =  "d:/projects/scf/shapefiles"

hexBins = "d:/projects/scf/shapefiles/hexbins.shp"
points = "d:/projects/scf/shapefiles/points.shp"

bid = "BIN_ID"
updateField1 = "UCR"

with arcpy.da.UpdateCursor(hexBins, (bid, updateField1)) as binRows:

        arcpy.MakeFeatureLayer_management(points, "pointLayer")

        for bin in binRows:

            binIDstring = bin[0]
            queryString = '"' + bid + '" = "' + str(binIDstring) + '"'

            arcpy.MakeFeatureLayer_management(hexBins,"CurrentBinLayer",queryString)

            selPts = arcpy.SelectLayerByLocation_management("pointLayer",'WITHIN',"CurrentBinLayer")

            getPointCount = arcpy.GetCount_management(selPts)
            binPointCount = int(getPoimtCount.getOutput(0))

            bin[1] = binPointCount
            binRows.updateRow(bin)

            arcpy.Delete_management("CurrentBinLayer")

        arcpy.Delete_management("pointLayer")

When I run this code, I get an error at the SelectLayerByLocation line.

ExecuteError: ERROR 999999: Error executing function. A column was specified that does not exist. A column was specified that does not exist. Failed to execute (SelectLayerByLocation).

However, after running this code, I've double checked that the MakeFeatureLayers were valid (they are) and then I've successfully run the line

selPts = arcpy.SelectLayerByLocation_management("pointLayer",'WITHIN',"CurrentBinLayer")

I've tried moving the folder to different locations (testing for file permissions issues) but to no avail.

Amy thoughts?

Thanks.

Answer 1

The syntax of your WHERE clause (querystring) is not valid. String literals in SQL must be wrapped in single quotes, not double quotes. If you just want to fix it, try this:

queryString = '"' + bid + '" = \'' + str(binIDstring) + "'"

It is mostly a stylistic thing, but this type of string concatenation is fragile and difficult to read. The recommended practice is to use string formatting instead.

If you would like to use a more robust function that uses string formatting (albeit the older modulo style) to automatically add the correct field delimiters and wrap string literals in single quotes, see this answer.