Encode Geomtery into Oracle Format in Text File from Geodatabase

Hi,

 

 

SDO_GEOMETRY is a binary format that's only used internally in Oracle, so it's not necessarily a good idea / trivial to write the contents to a text file. For a text file, I would recommend you use WKB (Well-Known Binary). This text representation of a geometry object is standardized and can be read back into Oracle, e.g.

 

 

insert into MY_SDO_TABLE (objectid, shape) values (1, SDO_UTIL.FROM_WKTGEOMETRY('...'));

 

 

Where '...' should be your WKT string.

 

 

The GeometryExtractor can convert the FME geometry into a WKT string for you.

 

 

Just be aware that while WKT works very well for basic geometries (point, line, polygon), you might run into difficulties for some geometry types, such as huge multipart donuts, etc.

 

 

David

Thanks David, I'll try this out. The issue I have is that I need to extract some SDE layers into sql ctrl files to be uploaded by a 3rd party into their oracle DB offsite and they have specified it needs tobe in SDO_GEOMETRY format. 

 

 

Theoretically, what would be the best way to do this? Each written sql ctrl file needs to contain a couple of attributes from the SDE layer and the geometry in this SDO_GEOMETRY format? 

Would WKT work in this case?

Hi,

 

 

I have never tried to load Oracle spatial data with control files, so unless someone else can chime in, I think the best solution is to make a small test case and see if it works for you.

 

 

I would also suggest taking an existing table with a spatial column and export it to a control file and see what the result is.

 

 

David

You can extract all the parameters needed to construct a SDO_GEOMETRY string from your shapefile.

 

 

For that i advise you to read up on the issue on docs.oracle.com

 

This is tricky for more complex objects, be aware.

 

 

Maybe of interest concerning this:

 

http://www.tolon.co.uk/2012/09/geometry-objects-across-dblink/

 

 

I agree, extracting WKT or WKB using fme and importing it in oracle could be easier.

 

I understand though, that importing this requires Oracle SDO_UTIL