Import ORACLE SDO_GEOMETRY to SQL Server Geometry

Question

I'm building a service which selects a number of geometries from a system that stores its geometries in an Oracle 10g, and then save a copy of those into a SQL Server database for use by another system.

Looked first at SDO_UTIL.TO_WKTGEOMETRY(). However, the geometries is stored as 3d geometries (Even though the z-layer is always 0, so it will not work as WKT only works if in 2d).

Option number two was then SDO_UTIL.TO_GMLGEOMETRY(), this however returns GMLs in V2, and SQL Server would like them in GML V3.1.1(from what I could read)(And have not found a simple way to convert these).

Does anyone have an idea of ​​other options, maybe some third-party libraries that can be used for this?

Answer 1

One possibility is to use the TOWKB? function in Oracle spatial to convert the SDO_Geometry to WKB. Then use something like below with a linked server to Oracle from SQL Server.

WITH A AS (SELECT MI_PRINX, STREET,geometry::STGeomFromWKB(WKB,4283).MakeValid() as geom from SISTDB..GIPS.WKB_ROADS_TEST_V)insert into sde.TRA_LAN_QueenslandRoadsSELECT MI_PRINX as ID, STREET,geography::STGeomFromWKB(geom.STAsBinary(),4283) as geog from A;

Answer 2

I found an old thread(oracle forum link) discussing a similar issue, As the sdo_geoms z-layer wasnt actually used, this, SDO_LRS.CONVERT_TO_STD_GEOM(), made it into 2D.

So in the end I got a WKT with this: SDO_UTIL.TO_WKTGEOMETRY(SDO_LRS.CONVERT_TO_STD_GEOM(GEOMETRY_C)) AS wkt,