Skip to content
Advertisement

Convert SDO_GEOMETRY’s sdo_point attribute to text

I have SDO_GEOMETRY objects in Oracle 18c:

In a query, I want to select the SDO_GEOMETRY’s sdo_point attribute as literal text (for concatenation purposes).

Example: (fails)

I don’t know how to convert that object attribute to text. I’ve tried using the SDO_UTIL.TO_WKTGEOMETRY() function. But that only seems to work on a point geometry as a whole, not on the specific sdo_point attribute.


How can I select the SDO_GEOMETRY’s sdo_point attribute as text?

Advertisement

Answer

Just extract each X, Y and Z component part of the point and convert those to strings:

Which outputs:

DESCRIPTION
the geometry sdo_point attribute is: NULL
the geometry sdo_point attribute is: (-79, 37, NULL)

On later Oracle versions, you could convert the SDO_POINT to JSON and then translate the JSON to your format:

Which has the similar output to above.

db<>fiddle here

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement