Forum Stats

  • 3,722,427 Users
  • 2,244,304 Discussions
  • 7,849,827 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

How to convert a geometry field from Oracle to SQL Server

Bilal-ADEK
Bilal-ADEK Member Posts: 1 Green Ribbon
edited April 8 in Spatial Discussions

How to convert a geometry field from Oracle to SQL Server?

I have an ArcGIS maps data saved in a table in Oracle and I want to load that table in SQL SERVER. How can I convert the field of datatype "st_geometry" in oracle to equivalent SQL Server?

Answers

  • _jum
    _jum Member Posts: 492 Silver Badge
    edited 6:17AM

    First I misread the question and then corrected the answer. Would use WKT / WKB as interface.

    There is an ESRI SDE-package in your ArcGIS ORACLE, you can find good whitepapers from ESRI...

    You can use the SDE-package from ESRI to convert ST-geometry to WKT /WKB load them to your SQL Server and then use the SQL Server functions StGeomFromText / StGeomFromWKB to build up a SQLGeometry.

    This should work as with the matching ORACLE SDO_UTIL.from_wktgeometry / SDO_UTIL.from_wkbgeometry:

    SELECT SDO_UTIL.from_wktgeometry(sde.st_astext(st))
      FROM
     (SELECT sde.st_geomfromtext('POINT ( 629867.20697820 5765881.90401331)',0) st
        FROM dual);
    
    SELECT SDO_UTIL.from_wktgeometry(sde.st_astext(st))
      FROM
     (SELECT sde.st_geomfromtext('LINESTRING (10.01 20.01, 10.01 30.01, 10.01 40.01)', 4326) st
        FROM dual);
    
    SELECT SDO_UTIL.from_wkbgeometry(sde.st_asbinary(st))
      FROM
     (SELECT sde.st_geomfromtext('POINT ( 629867.20697820 5765881.90401331)',0) st
        FROM dual);
    
    SELECT SDO_UTIL.from_wkbgeometry(sde.st_asbinary(st))
      FROM
     (SELECT sde.st_geomfromtext('LINESTRING (10.01 20.01, 10.01 30.01, 10.01 40.01)', 4326) st
        FROM dual);
    

    HTH

    [Edit, corrected version]

Sign In or Register to comment.