Get ArcGIS to recognize ST_POINT

Question

I have a SDE.ST_GEOMETRY line feature class. I've created a view on it that returns an st_point column.

SELECT OBJECTID, SDE.ST_STARTPOINT(SHAPE) AS START_POINT FROM ENG.ROAD

There is a known Oracle bug that is preventing me from using my view : Unable to define a query layer in ArcGIS where the data source uses an st_geometry subtype in Oracle.

The bug description states:

...the binding used in Oracle to fetch the geometry binds an st_geometry type. Because the attribute or values returned from the attribute do not map to st_geometry, the Oracle internal error is encountered. By definition, a type binding should support its subtype definitions. This limitation is currently a bug within Oracle's server.

The workaround is:

Convert the geometry attribute field from the subtype (for example, st_point) to the supertype st_geometry.

However, it doesn't say how to do the conversion; I don't know how to convert from the subclass to the superclass.

Question:

How can I get ArcMap to recognize an ST_POINT subclass column?

Answer 1

I don't have an SDE to play with. I think you would have to do something like this:

With point As (
 select sde.st_point (10.01, 20.03, 4326) as pt from dual
)
select sde.st_geometry(sde.ST_X(p.pt), sde.ST_Y(p.pt), sde.ST_Z(p.pt), sde.ST_M(p.pt), sde.ST_SRID(p.pt)) as ptGeom 
 from point p;

Or you could use WKB.

My packages and objects have implementations of ST_StartPoint that return SDO_GEOMETRY objects. You could create a function that does the same thing but return a point geometry:

create function ST_Point2Geom(p_pt in sde.st_point)
returns sde.st_geometry deterministic
As
begin
  return case when p_pt is null 
              then null 
              when sde.ST_GeometryType(p_pt) <> 'ST_POINT'
              then p_pt 
              else sde.st_geometry(sde.ST_X(p_pt), sde.ST_Y(p_pt),
                                   sde.ST_Z(p_pt), sde.ST_M(p_pt),
                                   sde.ST_SRID(p_pt)) 
          end;
end st_point2geom;
/
show errors

select st_point2Geom(sde.st_startPoint(myGeom)) from dual;

Answer 2

From a quick look, Oracle's TREAT() function seems to solve the issue:

select
    treat(  sde.st_pointonsurface(sde.st_buffer(shape,0.05)  ) as sde.st_geometry) as midpoint 
from
    active_transportation

When I use TREAT(), I don't get the error when adding the layer to ArcMap.