1

I have a SDE.ST_GEOMETRY polyline FC in an Oracle 18c/10.7.1 geodatabase.

I want to convert the SDE.ST_GEOMETRY shape column to SDO_GEOMETRY using SQL.

Reason: So that I can do linear referencing operations using Oracle Spatial's linear referencing package.

Such a conversion can be done by:

  1. Converting the SDE.ST_GEOMETRY shape column to well-known text using ST_AsText.

    • to_char(sde.st_astext(shape))
    • LINESTRING ( 675939.0727821561 4861635.038876267, 675881.8875467349 4861589.914457718, 675879.1765119368 4861586.041565847, 675877.9556495566 4861580.512319708, 675879.8962382395 4861575.485510156, 675976.9673410757 4861477.697030321, 675982.1957818093 4861474.598758776, 675988.0051615685 4861472.468589587, 675994.20185149 4861471.306795442, 676000.9793965323 4861470.919485278, 676006.788765802 4861471.306795442, 676011.6298439335 4861472.275034143, 676150.6204311419 4861517.834283836)

  2. From there, I can convert the well-known text to SDO_GEOMETRY using SDO_UTIL.FROM_WKTGEOMETRY:

    • sdo_util.from_wktgeometry(sde.st_astext(shape))
    • MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),MDSYS.SDO_ORDINATE_ARRAY(675939.072782156,4861635.03887627,675881.887546735,4861589.91445772,675879.176511937,4861586.04156585,675877.955649557,4861580.51231971,675879.89623824,4861575.48551016,675976.967341076,4861477.69703032,675982.195781809,4861474.59875878,675988.005161569,4861472.46858959,675994.20185149,4861471.30679544,676000.979396532,4861470.91948528,676006.788765802,4861471.30679544,676011.629843934,4861472.27503414,676150.620431142,4861517.83428384,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL))

Question:

What's with all the nulls in the SDO_GEOMETRY result? Can they be avoided?

For what it's worth, I'm using Toad for Oracle (version 12.12.0.39).

|improve this question
  • Read through the ESRI docs on the SDE types; those are geometric predicates like 'has Z Dimension' etc., and likely correct. Here is the ST_GEOMETRY parameter list. – ThingumaBob Jan 12 at 9:57
1

I have seen some application software, like TOAD, display Oracle VARRAYs in ways like this.

The SDO_GEOMETRY may be fine which is why I suggest querying it for sdo_util.GetNumVertices (tests sdo_ordinate_array)and sdo_util.getNumElem() (tests sdo_elem_info_array)

See my answer to your other thread here:

https://stackoverflow.com/questions/59699858/wkt-linestring-to-sdo-geometry-why-are-there-so-many-nulls/59701197#59701197

|improve this answer
  • SDO_GEOMETRY supports an ST_IsValid request, so basic validation would be more useful than retrieving vertex counts. – Vince Jan 12 at 14:26
  • 1
    I agree with Simon. I think you are trying to use Toad to do your spatial work. This is fraught as the Toad data grid GUI presents varrays in an unexpected fashion. The tell is when your SDO_ELEM_INFO varray is padded out to 99 elements with NULLs and when the SDO_ORDINATES varray is padded to 99 or truncated at 99 elements. I believe your process to convert the data from SDE is SDO is just fine but Toad is tricking you into thinking its not. Cast your results into a wkt or json CLOB and all will look as expected. – pauldzy Jan 12 at 14:58
1

I have access to Oracle not ESRI so I can't help with all aspects.

The WKT looks fine, though the space after the first ( is normally not there.

Try using the sdo_geometry(WKT,srid) constructor supplying the SRID rather than the sdo_util function.

Take the WKT as a string and directly constructed the SDO_GEOMETRY with it rather than using st_astext, this will isolate the problem.

|improve this answer
  • Thanks. I tried your suggestion. But unfortunately, I'm still getting all the nulls. Here's a screencap of what I did: i.stack.imgur.com/iUyLq.png – User1973 Jan 11 at 22:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.