Getting Wrong Result using Spatial Filter in SQL Query

RavindraSingh — Sat, 11 Dec 2021 18:36:42 GMT

Hi,

I have a python script where I am executing SELECT SQL statement, using Spatial filter.

The geometry I am using in Spatial filter is being passed as parameter to the python Script.

Now the Issue IS::

Query is running fine. But:

for some of the Geometries,SQL is not giving correct output, as compared to the same SpatialFilter applied in ArcMap. E.g. SQL Return 3 where as ArcMap Returns 4. and I can clearly visualize in ArcMap, that feature is overlaping.
So to overcome above issue, I commented out the Code of SimplifyPolygon in my Python, and I used original Geom. Now the Result is coming Correct, same as What I am getting in ArcMap.
Now this leads to another issue.:: For some of the geometries. It gives me below error while executing SQL Query:
ArcSDESQLExecute: StreamPrepareSQL ArcSDE Extended error 1704 ORA-01704: string literal too long

scratch = arcpy.env.scratchGDB
polyLayer = os.path.join(scratch,"inputPoly")
simplePolyLayer = os.path.join(scratch,"simplePoly")
arcpy.MakeFeatureLayer_management(parameters[0].valueAsText, polyLayer)
arcpy.cartography.SimplifyPolygon(polyLayer, simplePolyLayer,"POINT_REMOVE", 5)
count = int(arcpy.GetCount_management(simplePolyLayer).getOutput(0))
arcpy.AddMessage("count: " + str(count))
if count > 1:
     #need to merge into a multipolygon then get the WKT
     merged = os.path.join(scratch,"mergedPoly")
     arcpy.Dissolve_management(simplePolyLayer, merged)
     for row in arcpy.da.SearchCursor(merged, ["SHAPE@WKT"]):
          WKT = row[0]
else:
     for row in arcpy.da.SearchCursor(simplePolyLayer, ["SHAPE@WKT"]):
          WKT = row[0]

SQL Query code.

"SELECT count(*) FROM " +<My_FeatureClass> +" a WHERE served_id = 0 and (sdo_anyinteract(a.shape,sdo_geometry('" + WKT + "',4326)) = 'TRUE')"

Let me know any solutions.

Thanks

Message was edited by: Ravindra Singh

Re: Getting Wrong Result using Spatial Filter in SQL Query

RavindraSingh — Mon, 24 Aug 2015 11:34:28 GMT

Hi,

I found the solution regarding this.

Following are the steps you need to do.

Download most Suitable version as per your system of cx_Oracle
Install the above . It will be installed at default location- "C:\Python27\ArcGIS10.2\Lib\site-packages" . Let it install at here only.
NOTE:: If any ESRI product is already opened. Then close it, then Open ArcMap, to let the changes take effect.
now modify your script as below:

# Import the package as below

import arcpy, os, sys, arcpy.cartography

#Importing package for making SQL Connection with Oracle

import cx_Oracle

# -- [Strart] Code to get the Geometry as WKT .

scratch = arcpy.env.scratchGDB

polyLayer = os.path.join(scratch,"inputPoly")

arcpy.MakeFeatureLayer_management(parameters[0].valueAsText, polyLayer)

count = int(arcpy.GetCount_management(polyLayer).getOutput(0))

arcpy.AddMessage("count: " + str(count))

if count > 1:

#need to merge into a multipolygon then get the WKT

merged = os.path.join(scratch,"mergedPoly")

arcpy.Dissolve_management(polyLayer, merged)

for row in arcpy.da.SearchCursor(merged, ["SHAPE@WKT"]):

WKT = row[0]

else:

for row in arcpy.da.SearchCursor(polyLayer, ["SHAPE@WKT"]):

WKT = row[0]

# -- [END] Code to get the Geometry as WKT .

# Create Oracle Connection.

connection = cx_Oracle.connect('<Database_UserID>/<pwd>@<Service_Name>')

cursor = connection.cursor()

mySpatialSQLQuery = "SELECT count(*) FROM PLOTS a WHERE served_id = 0 and (sdo_anyinteract(a.shape,sdo_geometry(:WKTGeom,4326)) = 'TRUE')"

#in the above query ':WKYGeom' is the place holder whose value we will pass in next.

WKTGeom = WKT

cursor.setinputsizes(WKTGeom=cx_Oracle.CLOB) # prepare memory for operation parameters

cursor.execute(plotCountSQL, {'WKTGeom':WKTGeom}) # Executing using Binding Variable using CLOB

res = cursor.fetchall()

plotCount = res[0][0] # It Denotes First Row's First Column

#arcpy.AddMessage("plotCount using cx_Oracle== "+ str(plotCount))

cursor.close()

Like this you can pass the huge geometry as WKT format.

Hope this will help someone.

Cheers !

topic Re: Getting Wrong Result using Spatial Filter in SQL Query in Python Questions

Getting Wrong Result using Spatial Filter in SQL Query

Re: Getting Wrong Result using Spatial Filter in SQL Query