I have a featureclass stored in a SDE Geodatabase. How would I go about witing a txt file with one column containing the features geometry in Oracel (MYSYS.SDO_GEOMETRY) format?
I assumed I would use Geometry Extractor, but there doesn't seem to be an oracle geomtery encoder included? Would I need to write the feature to another Oracle Spatial database then extract the information from that?
Many THanks
Bkicker22
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
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?
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
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