topic Re: SDO_Geometry to St_Geometry in Geodatabase Questions

SDO_Geometry to St_Geometry

SreenivasaRaoPigili1 — Thu, 24 Aug 2023 14:39:54 GMT

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.

Re: SDO_Geometry to St_Geometry

George_Thompson — Thu, 24 Aug 2023 15:28:13 GMT

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!

Re: SDO_Geometry to St_Geometry

SreenivasaRaoPigili1 — Thu, 24 Aug 2023 16:38:48 GMT

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.

Re: SDO_Geometry to St_Geometry

VinceAngelo — Thu, 24 Aug 2023 17:26:38 GMT

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

Re: SDO_Geometry to St_Geometry

SreenivasaRaoPigili1 — Fri, 25 Aug 2023 14:54:15 GMT

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.

Re: SDO_Geometry to St_Geometry

George_Thompson — Fri, 25 Aug 2023 14:55:32 GMT

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.

Re: SDO_Geometry to St_Geometry

SreenivasaRaoPigili1 — Mon, 28 Aug 2023 11:25:47 GMT

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.

Re: SDO_Geometry to St_Geometry

SreenivasaRaoPigili1 — Mon, 28 Aug 2023 12:00:48 GMT

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.

Re: SDO_Geometry to St_Geometry

VinceAngelo — Mon, 28 Aug 2023 14:45:50 GMT

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

Re: SDO_Geometry to St_Geometry

SreenivasaRaoPigili1 — Mon, 28 Aug 2023 16:31:25 GMT

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.

Re: SDO_Geometry to St_Geometry

SreenivasaRaoPigili1 — Tue, 29 Aug 2023 12:28:30 GMT

FYI: I got the error code information from https://support.esri.com/en-us/knowledge-base/faq-what-are-the-error-codes-returned-from-the-shape-en-000008067 .