Can't use cx_Oracle LOB in Spatialite WKB insert statement

Vote count:

0

I have some Python code the selects data from Oracle spatial and inserts into Spatialite. My problem is that the cursor contains the geometry in binary and I can’t figure out how to read the binary into the Spatialite insert statement. Just to added this all works if I use WKT but some of the geometries are too long hence the reason for the binary format.

Can anyone help please?

# Import system modules
import cx_Oracle
from pyspatialite import dbapi2 as sl_db

def db_connect():
    # Build connect from TNS names
    o_db = cx_Oracle.connect("xxxxx", "xxxxx", "xxxxx_gl_dev")
    cursor = o_db.cursor()
    return cursor

def db_lookup(cursor):
    # Select records using SDO_RELATE
    sql = "SELECT TO_CHAR(sdo_util.to_wkbgeometry(a.shape)), a.objectid FROM span a WHERE a.objectid = 1382372"

    cursor.execute(sql)
    row = cursor.fetchall()
    return row

def db_insert(row):
    # Insert Rows in new spatailite table
    database_name = 'C:\\Temp\\MYDATABASE.sqlite'
    db_connection = sl_db.connect(database_name)
    db_cursor = db_connection.cursor()
    sql = 'INSERT INTO "SPAN_OFL" ("geometry", "OBJECTID") Values GeomFromWKB(?,27700),?);'
    db_cursor.executemany(sql, row)
    db_connection.commit()
    db_connection.close()

# main code
cursor = db_connect()
row = db_lookup(cursor)
db_insert(row)

asked 28 secs ago

user3178147

1

Can't use cx_Oracle LOB in Spatialite WKB insert statement