Select specific attribute from SDE.ST_Geometry UDT? (without using accessor functions)

Question

Oracle 18c; ArcGIS 10.7.1

---

I have an SDE.ST_Geometry polyline feature class.

As an experiment/learning exercise, I am trying to learn how to interact directly with the user-defined type (UDT) SHAPE column.

I can select the SHAPE column in an SQL client which returns a collection of attributes from the UDT:

select 
    shape 
from 
    a_test_fc

SHAPE                                                                           
--------------------------------------------------------------------------------
ST_GEOMETRY(4,37,671828.7852,4861707.1775,671839.2487,4861717.6411,NULL,NULL,NULL,NULL,0,32.9250330071201,26917,(BLOB))                                         

1 rows selected.

---

And I can see that the docs describe the UDT attributes as follows:

The attributes of the spatial type represent the following information:

• Entity: The type of geometric feature stored in the spatial column (linestring, multilinestring, multipoint, multipolygon, point, or polygon), the value of which is a bit mask derived from the st_geom_util stored procedure.
• Numpts: The number of points defining the geometry; for multipart geometries, this includes the separators between each part, one point for each separator.
• Minx, miny, maxx, maxy: The spatial envelope of the geometry
• Area: The area of the geometry
• Len: The perimeter length of the geometry
• SRID: Contains the identifier for the geometry that links it to its associated spatial reference (coordinate system) record in the ST_Spatial_References table
• Points: Contains the byte stream of the point coordinates that define the geometry

---

Question:

Is there a way to select specific attributes from the ST_Geomtery UDT?

I'm aware that there are "accessor functions that take a single ST_Geometry as input and return the requested property value as a number." So there isn't a practical need to directly select individual attributes from the UDT — I could just use those accessor functions if required. I'd just like to determine if it's possible to interact directly with the UDT attributes as a way to learn more about spatial database development.

I tried researching non-GIS UDTs, and how to select their attributes (example: I tried selecting SHAPE.POINTS). But that just resulted in an error: ORA-00904: "SHAPE"."POINTS": invalid identifier. And yes, I'm aware that the POINTS attribute is stored as a byte stream and won't be useful in any practical sense.

Answer 1

Use Oracle's TREAT() function.

select 
    objectid,
    treat(shape as sde.st_geometry).numpts as num_points,
    treat(shape as sde.st_geometry).points as byte_stream
from 
    a_test_fc

---

Related:

How to SELECT from object type column in Oracle 11g?

What is the ST_Geometry storage type?

Answer 2

It turns out, all we need to do is use a table alias (I've used an alias of "a").

select 
    objectid,
    a.shape.numpts as num_points,
    a.shape.points as byte_stream
from 
    a_test_fc a

---

That technique also works for SDO_GEOMETRY methods:

select 
    a.shape.get_wkt()
from 
    table a