Get XML from Oracle sde.st_geometry SHAPE column using SQL

Question

How can I access the XML contents of the SHAPE column of a feature class in sde.st_geometry/Oracle? I want to access the XML using SQL so that I can ultimately directly query the vertices that make up the geometry.

This page says that the POINTS attribute of ST_GEOMETRY is stored as a BLOB, which "contains the byte stream of the point coordinates that define the geometry". But I haven't found much more info than that. And it doesn't tell me how the SHAPE column is stored as a whole.

The XML Schema of the Geodatabase white paper talks about the XML of the geometry/SHAPE column on pages 44-46, 49-50 and in Apendix A. But there's nothing related to accessing it in the database.

• Vince: No, there is no XML in an SDE.ST_GEOMETRY. Never has been. The binary stream is documented. Geodatabase XML is generated by ArcObjects, not within the geometry.
• User1974: @Vince Just to be clear, a feature class' SHAPE column is stored differently than say, the DEFINITION column in SDE.GDB_ITEM_TYPES, which DOES have XML?
• Vince: Absolutely. It would be insane to store ST_Geometry as XML (geometry performance is closely linked to storage size -- increasing storage by two orders of magnitude was never an option).
• User1974: Now I'm left wondering how the st_geometry functions manage to read the byte stream, and if I could write a GetVertices function.
• Vince: There's a code library to read the bytes (it's one of the primary things that DLL does), but there are no public signatures.

Answer 1

There is no XML within SDE.ST_GEOMETRY. Furthermore, neither the SDE.ST_GEOMETRY type nor the underlying st_geometry DLL, which implements that custom type, nor even the SgShape library beneath that supports a method for XML conversion. XML Workspace generation is a capability of ArcObjects.

The binary POINTS BLOB is partially documemted (all except the CAD object array) in earlier media kits and in documentation on the web (though the conversion from 32-bit to 64-bit base integers with HIGH precision may take a little reverse engineering/experimentation).

PostGIS has an ST_Dump method to generate tuples from higher order objects, but that's not part of the type specification to which Esri's implementation adheres, and I'm not even sure if it's possible in Oracle.

I'd recommend you convert to point (multipoint, really), generate well-known text, and parse the comma-delimited array of X{space}Y pairs.

• User1974: One alternative I've come up with is cross joining on a numbers table to list the vertices. But it's not as fast or elegant as I'd like. More info here: dba.stackexchange.com/questions/154995/… and here: gis.stackexchange.com/questions/217507/…
• Vince: It's rare that I recommend ugly and slow solutions, but LOBs are none too sprightly, so you likely don't have many options.