Transferring data from PostGIS to Oracle Spatial

Question

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 to use it in a cx_oracle insert.

In my opinion geometry is to large for this syntaxe inserting of records.

Do you have any idea how do it differently?

• Lafleur: 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.

Answer 1

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.

If this is not a one-off, 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".

• skedge: 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.