Convert from SDE.ST_GEOMETRY to SDO_GEOMETRY easily?

[MiltonStemmler]

We have a clients data pump export installed in an Oracle 11g database with ARCSDE 10 to fix some bugs with our application. We would like to do some testing with this same data set but as SDO_GEOMETRY instead of the SDE.ST_GEOMETRY. Is there any way to easily convert?

Thanks,

Milton S.

[MarcoBoeringa]

ArcGIS supports so called "Configuration Keywords" that determine the spatial storage type in an ArcSDE geodatabase. You set the configuration keyword when creating a new Feature Class. If you create a new Feature Class, and set the spatial type to SDO by using the "SDO_GEOMETRY" configuration keyword, and than import the existing Feature Class's geometries / features to the new one, you will have converted the data to SDO_GEOMETRY.

[VinceAngelo]

There are tools that support migration, but not in the direction you've requested.
There's a dozen different ways to accomplish this, but they all involve exporting
the existing table and importing it into a new table with SDO_GEOMETRY storage
(DBTUNE keywords, as Marco has suggested, is the easiest way to accomplish
this, but you could do it using SQL as well [via Well-Known Text]).

- V

[Tharuunyad]

are there any sample  sql programs which enable the conversion from SDE.ST_Geometry to SDO_GEometry ?

[VinceAngelo]

This isn't a "conversion" exercise as much as a "recreation" one.  As such, I doubt there's much to be found (I would never use a script found on the web for my data population needs, so I've never looked).  The core of data transfer could be very simple (an INSERT INTO with a SELECT), but creation of the target tables shouldn't be left to chance.

- V

[MarcoBoeringa]

In addition, if you have older datasets (originally created prior to ArcGIS 9.2) in "basic precision", take notice of the following warning ESRI makes in the Help link Vince posted, it is probably also wise to take this into account when doing a simple "manual" transition as I suggested by creating a new Feature Class with different storage type:

"If you are converting the spatial column type, the data must be stored in high precision. If your data is currently stored with basic precision, you must first migrate it to high precision before you migrate the storage type. This can be done with either the Upgrade Spatial Reference geoprocessing tool or the sdelayer command with the alter operation. See Migrating to high precision for information on migrating the precision of a dataset."