5 Replies Latest reply: Feb 11, 2013 9:20 PM by Simon Greener RSS

    Is there an easy way to convert SDE.ST_GEOMETRY to SDO_GEOMETRY.

    893152
      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.
        • 1. Re: Is there an easy way to convert SDE.ST_GEOMETRY to SDO_GEOMETRY.
          B Hall
          Milton,

          Have you tried what is described in the online manual?

          http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//002n00000076000000

          http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/Migrating_geometry_from_one_storage_type_to_another_using_ArcSDE_commands/002n00000078000000/

          If you have large datasets, you will find that SDO_GEOMETRY is significantly quicker that either SDE.ST_GEOMETRY or SDE Binary.

          Bryan
          • 2. Re: Is there an easy way to convert SDE.ST_GEOMETRY to SDO_GEOMETRY.
            893152
            BHall,

            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.

            Migrate from/to
            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
            • 3. Re: Is there an easy way to convert SDE.ST_GEOMETRY to SDO_GEOMETRY.
              Simon Greener
              Milton,

              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)
              SELECT site_id, name, SDO_GEOMETRY(sde.st_astext (location),sde.st_srid(location)) SDO_Location
              FROM hazardous_sites;
              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
              but to give it the Oracle equivalent directly.
              SELECT site_id, name, SDO_GEOMETRY(sde.st_astext (location),8307) SDO_Location
              FROM hazardous_sites;
              Of course, it may be faster to use ST_AsBinary() to convert via WKB.

              regards
              Simon
              • 4. Re: Is there an easy way to convert SDE.ST_GEOMETRY to SDO_GEOMETRY.
                Paul Dziemiela
                Hi folks,

                This question probably belong over at the ESRI site at
                http://forums.arcgis.com/forums/32-Geodatabase-amp-ArcSDE

                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.

                Cheers,

                Paul
                • 5. Re: Is there an easy way to convert SDE.ST_GEOMETRY to SDO_GEOMETRY.
                  Simon Greener
                  Milton,
                  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?
                  It occurs to me that you haven't told us some things. Does the "clients [sic] data pump export" contain SDE.ST_Geometry objects?

                  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.)

                  regards
                  Simon