SDO_Geometry to St_Geometry

[SreenivasaRaoPigili1]

Hi Team,

    I am trying to convert SDO_Geometry to ST_Geometry by using 

SDE.ST_GEOMFROMTEXT(SDO_GEOMETRY.GET_WKT(SHAPE),4326)    and 

SDE.ST_GEOMFROMWKB(SDO_GEOMETRY.GET_WKB(SHAPE)),4326).

When i used WKT - i am getting error as Ora-20004 Error generating shape from text (-2035)

Whereas if i used WKB - not getting any error, but polygon area is coming as 0. It means conversion not happened.

Please help me to understand - why we are getting this error and what exactly the differenece between those WKT and WKB function usage.

Thanks in advance.

[George_Thompson]

You could use the GP tool to migrate from SDO --> ST_Geometry: 

Tool : https://pro.arcgis.com/en/pro-app/latest/tool-reference/data-management/migrate-storage.htm

Information: https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-oracle/data-migration.htm

It would do the entire table in place. Make sure that you fully review the documentation above, you CANNOT go backwards once done.

Hope this helps!

[SreenivasaRaoPigili1]

Thank you George for your response. Here I need to do the migration for a single feature during some business process. For that I am using this SQL process. Can you please help me in this line. Thanks again.

[George_Thompson]

Ah for a single feature, I would look at Vince's post below. The tool I recommended does the entire table at once, which is nice if needed.

[VinceAngelo]

You haven't mentioned the Oracle release in use, but I've always used SDO_UTIL.TO_WKTGEOMETRY to generate Well-Known Text out of SDO_GEOMETRY.

SELECT  objectid,
        SDO_UTIL.TO_WKTGEOMETRY(geom) as wkt
FROM    schema.tablename

At one point I ran into a bug (in an Oracle 19c patch release) where the SDO_UTIL function would produce invalid scientific notation near the Equator or Prime Meridian (e.g., "0.12345e-1.0" instead of  "0.12345e-1"), and I had to wrap the arcpy.FromWKT parser with a repair function.

You might want to assure that the source SDO_GEOMETRY is valid before passing it into SDE.ST_GEOMETRY.

- V

[SreenivasaRaoPigili1]

Hi VinceAngelo, Thank you for providing details. Apologies for missing Oracle version. We are using Oracle v19.8.0.0.0

Before conversion - yes i am checking whether SDO_Geometry is valid or not. For valid SDO_Geometries only i am trying for the conversion. But no luck.

Also, can you please tell what is that error number 2035. I didn't get any details about this error code in https://help.arcgis.com/en/geodatabase/10.0/sdk/arcsde/api/capi/returncodes_incl.htm 

Thank you.

[SreenivasaRaoPigili1]

Hi VinceAngelo, Along the previously mentioned details - used sdo_util.to_wktgeometry to get the WKT of the geometry. Even after when we tried to convert into ST_GEOMETRy - getting below error.  

select SDE.ST_GEOMFROMTEXT(SDO_UTIL.TO_WKTGEOMETRY(SHAPE),4326) from schema.sdoTable where condition;

ORA-20004: Error generating shape from text: (-2008).
ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 12
ORA-06512: at "SDE.ST_GEOMFROMTEXT", line 61

Please do needful to understand these error code 2008.

Thank you.

[SreenivasaRaoPigili1]

Hi VinceAngelo,, If i add SDO_UTIL.RECTIFY_GEOMETRY for the geometry, i am getting original error message.

SDE.ST_GEOMFROMTEXT(SDO_UTIL.TO_WKTGEOMETRY(SDO_UTIL.RECTIFY_GEOMETRY(shape,0.05)),4326)

ORA-20004: Error generating shape from text: (-2035).
ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 12
ORA-06512: at "SDE.ST_GEOMFROMTEXT", line 61

Please do needful to understand these error codes.

Thank you.

[VinceAngelo]

-2035 is a low-level "SG_SELF_INTERSECTING" error. 
-2008 is "SG_SHAPE_INTEGRITY_ERROR".

You should probably convert the SDO geometries to file geodatabase, then do a Repair Geometry there, then tie them back across.

- V

PS: Note that there are TWO validation procedures (at least). One uses the SDO function. The other is the ArcPy Check Geometry (and its sibling, Repair Geometry) Note that some "repairs" will corrupt the original intended shape (as when a "bowtie" rectangle is converted into two touching triangles, not a properly ordered quadrilateral), so you should review any repairs.

[SreenivasaRaoPigili1]

Thank you VinceAngelo. Will check this option and will update here.

Meanwhile - is there any location we can see these error codes related information? Just for future issues debugging, in case if any.