pythoncx-oraclenearest-neighborsdo

Are spatial queries possible using Python and cx_Oracle?

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.

Solution

  • There is a brief mention in the documentation:

    Here are two examples from the cx_Oracle source code repository:

    Here's a presentation from the recent Oracle Conference:

    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.