2

I would like transfer spatial data from PostGIS to Oracle Spatial. I want to use Python's modules psycopg2 and cx_oracle. Transfer Oracle > PostGIS is OK, but in opposite version I have problem with a geometry column. The important part is:

cursorP.execute("select fid, kod, st_asBinary(geom) as geom from ruian.vusc_g")
while 1:
    a = cursorP.fetchmany()
    if not a:
        break
    val = list()
    for row in a:
        dictionary = dict()
        dictionary['1']=row[0]
        dictionary['2']=row[1]
        dictionary['3']=cx_oracle.Binary(row[2])
        val.append(dictionary)
    orcl_inserttext = '''INSERT INTO test_Postgres VALUES (:1, :2, SDO_UTIL.FROM_WKBGEOMETRY(:3))'''
    cursorO.executemany(orcl_inserttext,val)

Wihout geometry it's OK. But in this case I receive Error ORA-01461: can bind a LONG value only for insert into a LONG column. Psycopg2 gives me WKB like buffer, but i don't know, how use it in cx_oracle insert.

EDIT: In my opinion geometry is to large for this syntaxe inserting of records. Do you have any idea how do it differently? Thanks

  • 1
    You might want to take a look at gdal/ogr. More specifically the ogr2ogr tool. I've used to load data into postgres. Pretty sure it can be used to migrate data from postgres/gis to oracle spatial. – Lafleur Feb 5 '14 at 0:03
2

The SDO_UTIL.FROM_WKB_GEOMETRY() function expects a BLOB as input. But it looks like cx_oracle.Binary() returns a LONG. You could try going via WKT instead. The SDO_UTIL.FROM_WKT_GEOMETRY() function takes a string (VARCHAR2) as input or a CLOB.

Is this a one-off ? Or do you need to move data back and forth between the two databases ? You may want to consider using ogr2ogr as Lafleur suggested. Just make sure you use a build that has both a postgis and an Oracle plugin. Note that the Oracle plugin is called "OCI".

  • Thanks. WKT geometry has same problem. I will use OGR library. It is not ideal, but better then Cx_Oracle. This module is not build for large geometry data. – skedge Feb 6 '14 at 13:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.