Invalid SQL Statement SelectLayerByLocation

LornaMurison — Thu, 20 Oct 2011 18:32:32 GMT

Hey everyone,
The following is the first thing that my script does after setting up variables etc...

# Create Fishnet
desc = gp.describe (streams)
xmin = desc.extent.xmin
ymin = desc.extent.ymin
xmax = desc.extent.xmax
ymax = desc.extent.ymax
origin_coord = str (xmin) + " " + str(ymin)
y_axis_coord = str (xmin) + " " + str(ymin + 10)
corner_coord = str (xmax) + " " + str(ymax)
gp.CreateFishnet_management ("Fishnet.shp", origin_coord, y_axis_coord, 0, 0, 2, 2, corner_coord, "NO_LABELS", "")

# Convert the Fishnet to Polygon
gp.FeatureToPolygon_management("Fishnet.shp", "FishnetPoly.shp", "#", "NO_ATTRIBUTES", "")

# Calculate ID Field with Incremental number
gp.CalculateField_management("FishnetPoly.shp", "id", "autoIncrement()", "PYTHON_9.3", "rec=0\\ndef autoIncrement():\\n global rec\\n pStart = 1 #adjust start value, if req'd \\n pInterval = 1 #adjust interval value, if req'd\\n if (rec == 0): \\n  rec = pStart \\n else: \\n  rec = rec + pInterval \\n return rec")

# Add and Calculate Text Field (SplitField) With That Same Number
gp.AddField_management("FishnetPoly.shp", "SplitField", "TEXT", "#", "#", 1, "", "NULLABLE", "NON_REQUIRED", "")
gp.CalculateField_management("FishnetPoly.shp", "SplitField", '"id"', "VB", "")

# Loop Through Each FishnetPoly Cell and Select Those Catchments That Have Their Center In The Given Cell
for i in range (1,5,1):
    ### Make Feature Layer From First Fishnet Cell
    gp.MakeFeatureLayer_management ("FishnetPoly.shp", "FishnetCell", '"SplitField" = ' + str(i), gp.Workspace, "")

    ### Make Feature Layer From Catchments
    gp.MakeFeatureLayer_management (catchments, "CatchmentsLayer", "", gp.Workspace, "")

    ### Select Catchments Which Have Their Center in the Feature Layer Cell
    gp.SelectLayerByLocation_management ("CatchmentsLayer", "HAVE_THEIR_CENTER_IN", "FishnetCell")

It returns the following error at gp.SelectLayerByLocation_management...

ExecuteError: ERROR 999999: Error executing function.
An invalid SQL statement was used.
An invalid SQL statement was used.
A locator with this name does not exist.
Failed to execute (SelectLayerByLocation).

I have no idea why. I don't know what SQL statement it could be referring to.
Any ideas?

Re: Invalid SQL Statement SelectLayerByLocation

LornaMurison — Thu, 20 Oct 2011 19:46:27 GMT

Turns out that the first make feature layer method was using an SQL statement that was resulting in an empty layer.
I changed:
gp.MakeFeatureLayer_management ("FishnetPoly.shp", "FishnetCell", '"SplitField" = ' + str(i), gp.Workspace, "")
to:
gp.MakeFeatureLayer_management ("FishnetPoly.shp", "FishnetCell", '"SplitField" = ' + "'" + str(i) + "'", gp.Workspace, "")
because the field I was selecting is a text field, even though it contains a number, str(i) needed single quotes.
That solved the problem.

Re: Invalid SQL Statement SelectLayerByLocation

DaveJohnson — Fri, 14 Sep 2012 19:09:05 GMT

Thanks...struggled with this one for a while before I found your post. Pulling a number from a text column required extra single quote around the number.

topic Re: Invalid SQL Statement SelectLayerByLocation in Python Questions

Invalid SQL Statement SelectLayerByLocation

Re: Invalid SQL Statement SelectLayerByLocation

Re: Invalid SQL Statement SelectLayerByLocation