This content has been marked as final. Show 5 replies
Have you tried what is described in the online manual?
If you have large datasets, you will find that SDO_GEOMETRY is significantly quicker that either SDE.ST_GEOMETRY or SDE Binary.
Thank you for your quick reply. I have seen those documents but they imply that migration is only from SDO_GEOMETRY to SDE.ST_GEOMETRY which would be consistent as it is an ESRI tool. The following is from the ESRI documentation and shows only migration to SDE.ST_GEOMETRY and not to SDO_GEOMETRY. I suppose I could give it a try.
LONG RAW to BLOB
LONG RAW (SDEBINARY) to BLOB (SDELOB)
LONG RAW (SDEBINARY) to ST_GEOMETRY
BLOB (SDELOB) to ST_GEOMETRY
SDO_GEOMETRY to ST_GEOMETRY
It would look like that there is no easy option for bulk conversion.
But given that SDE.ST_Geometry is supposed to be OGC/SFS compliant, why not try ST_AsText() or ST_AsBinary() as follows?
(Taken from http://webhelp.esri.com/arcgisserver/9.3/java/index.htm#geodatabases/configu-330573467.htm)
There may/will be a SRID difference between SDE and SDO. So it may be better to not ask the SDE.ST_Geometry what its SRID is
SELECT site_id, name, SDO_GEOMETRY(sde.st_astext (location),sde.st_srid(location)) SDO_Location FROM hazardous_sites;
but to give it the Oracle equivalent directly.
Of course, it may be faster to use ST_AsBinary() to convert via WKB.
SELECT site_id, name, SDO_GEOMETRY(sde.st_astext (location),8307) SDO_Location FROM hazardous_sites;
This question probably belong over at the ESRI site at
I think you are referring to the in-place Migrate Storage tool in ArcCatalog which is indeed branded for "upgrading" and slyly notes that migrating to SDO_GEOMETRY is not supported (in the tool). But you can certainly change storage type in any direction using other ESRI tools, just make a copy using the appropriate dbtune keyword.
In ArcCatalog you can just copy and then paste the layer changing the Config. Keyword to your SDO_GEOMETRY keyword. The downside is you don't have the in-place part of it done for you. But keeping the previous layer around for troubleshooting is not a bad idea anyhow. If not afterwards you have to delete the source layers and rename the destination layers yourself. Not really a daunting task. For bulk actions, its far quicker to use sdeexport and sdeimport command line tools setting the keyword on the import to whatever SDO_GEOMETRY keyword you like. Running the tools directly on the server is the way to go when just punting layers about if its a very large layer.
I've never actually run that upgrade tool. My guess is it just automates the process of copying the layers and renaming the layers.
We have a clients data pump export installed in an Oracle 11g database with ARCSDE 10 to fix some bugs with our application.It occurs to me that you haven't told us some things. Does the "clients [sic] data pump export" contain SDE.ST_Geometry objects?
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?
Also, has your 11g instance the ArcSDE EXTPROC?
You will, as you probably already know, need a valid SDE.ST_Geometry (arcsde) license to be able to process any SDE.ST_Geometry objects in the data pump file.
If yes, to both of these, then my suggestion should work. Otherwise, I agree with Paul that your question should be directed to the appropriate ESRI forum as your situation is as a result of ESRI making design decisions with their own competing spatial data type to create sundry tools that are deliberately restricted so as to make interoperability anything other than one way.
If you don't have an SDE.ST_Geometry license to run the ESRI EXTPROC then there are no tools available to the database to access ESRI's proprietary format and so extract the data.
Do you have any ESRI tools or non-ESRI tools at your disposal? FME might FME read the binary SDE.ST_Geometry format directly without the need for any licensed ESRI software.
Such stories continue to reinforce the reality that using the Oracle database's native data types is the only sensible thing to do. (Aren't SQL Server users lucky ESRI couldn't deploy their data type to their Microsoft's database.)