Are spatial queries possible using Python and cx_Oracle?

Question

I am trying to execute a spatial query on an Oracle spatial table via python using the cx_Oracle package.

I can make generic queries successfully, but when I try a spatial query it results in errors.

This is what I have tried:

import cx_Oracle
...

lon = -120.494352
lat = 36.585289

# open a connection to oracle
con = cx_Oracle.connect('myuser/mypass@spatialdb')

# create a cursor
cur = con.cursor()

# Create and populate Oracle objects
typeObj = con.gettype("MDSYS.SDO_GEOMETRY")
elementInfoTypeObj = con.gettype("MDSYS.SDO_ELEM_INFO_ARRAY")
ordinateTypeObj = con.gettype("MDSYS.SDO_ORDINATE_ARRAY")
obj = typeObj.newobject()
obj.SDO_GTYPE = 2001
obj.SDO_SRID = 8307
obj.SDO_ELEM_INFO = elementInfoTypeObj.newobject()
obj.SDO_ELEM_INFO.extend([1, 1, 1])
obj.SDO_ORDINATES = ordinateTypeObj.newobject()
obj.SDO_ORDINATES.extend([lon, lat])
print("Created object", obj)

# set up a distance-calculating sql statement
sql = "select id into :id from spatialtbl s where sdo_nn(s.geometry, :obj, 'sdo_num_res=1', 1) = 'TRUE'"

try:
    # execute the distance sql
    cur.execute(sql, id=id, obj=obj)
    print(f'The id is {id.getvalue()}')
except cx_Oracle.Error as error:
    print(error)

which results in the error:

ORA-01036: illegal variable name/number

Can anyone tell me what I may be doing wrong code-wise or if spatial queries are even possible using Python and cx_Oracle? The cx_Oracle documentation doesn't specifically address this as far as I can tell/find.

Answer 1

There is a brief mention in the documentation:

• Binding Spatial Datatypes

Here are two examples from the cx_Oracle source code repository:

• InsertGeometry.py
• SpatialToGeoPandas.py

Here's a presentation from the recent Oracle Conference:

• Analyzing Location-based Patterns with Python and Oracle Database The download links are there but may not be obvious pdf and zip.

In your example, you probably need to do at least id = cursor.var(int), see Bind Direction so cx_Oracle knows what to do with the value you are getting from the DB.

Answer 2

I think the "select into" was the problem (reserved for pl/sql?).

By doing the following I was able to obtain the answer:

# set up a distance-calculating sql statement
sql = """select id from spatialtbl s where sdo_nn(s.geometry, :ob, 'sdo_num_res=1', 1) = 'TRUE'"""

try:
    # execute the distance sql
    cur.execute(sql, ob=obj)
    id = cur.fetchone()
    print(f'The id is {id}')
except cx_Oracle.Error as error:
    print(error)