nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

Find more posts tagged with

Enterprise geodatabase

Accepted answers

All comments

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.

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.

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.

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

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 .

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.