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
    sql = "SELECT 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)
user3178147
  • 31
  • 1
  • 5
  • Read the [documentation](http://cx-oracle.readthedocs.org/en/latest/lob.html). And why are you using TO_CHAR() on binary data? – CL. Mar 06 '15 at 15:02
  • Sorry my mistake, I'd been trying various things and the TO_CHAR was left from TO_CHAR(sdo_util.to_wktgeometry(a.shape)), but as I said the geometries are too long to convert to a TO_CHAR. hence the reason I went for WKB. I've corrected it now thanks for spotting it. – user3178147 Mar 07 '15 at 18:48

0 Answers0