Mid Point function in SDE.ST_GEOMETRY

[AshokVanam1]

I could not able to find the way to calculate the mid-point of the line geometry in the SDE database where the geometries are stored in SDE.ST_GEOMETRY object.

There needs to be a function in ST_GEOMETRY object to create a mid point of a line and polyline. 

Currently the midpoint calculating is possible in ArcGIS Geoprocessing tool. The similar function should be made available in SDE.ST_Geometry functions (SQL functions used with ST_Geometry—Help | ArcGIS Desktop ) which helps in processing the data within the database server.

[AshokVanam1]

Work around solution to achieve this is to use Oracle Spatial function as below

create or replace function get_line_midpoint
(line_in IN sde.st_geometry)
-- RETURN sde.st_geometry
RETURN VARCHAR2
IS
wkt_geometry clob;
ora_geometry sdo_geometry;
mid_x number(10,6);
mid_y number(10,6);
mid_point_geom sde.st_geometry;
BEGIN

SELECT sde.ST_AsText(line_in) INTO wkt_geometry FROM DUAL;
ora_geometry := SDO_UTIL.FROM_WKTGEOMETRY(wkt_geometry);

--mid_x:= sdo_cs.transform(SDO_LRS.CONVERT_TO_STD_GEOM(SDO_LRS.LOCATE_PT(SDO_LRS.CONVERT_TO_LRS_GEOM(ora_geometry, 3), SDO_GEOM.SDO_LENGTH(ora_geometry,3)/2)),8307).SDO_POINT.X;
mid_x:= SDO_LRS.CONVERT_TO_STD_GEOM(SDO_LRS.LOCATE_PT(SDO_LRS.CONVERT_TO_LRS_GEOM(ora_geometry, 3), SDO_GEOM.SDO_LENGTH(ora_geometry,3)/2)).SDO_POINT.X;

--mid_y:= sdo_cs.transform(SDO_LRS.CONVERT_TO_STD_GEOM(SDO_LRS.LOCATE_PT(SDO_LRS.CONVERT_TO_LRS_GEOM(ora_geometry, 3), SDO_GEOM.SDO_LENGTH(ora_geometry,3)/2)),8307).SDO_POINT.Y;
mid_y:= SDO_LRS.CONVERT_TO_STD_GEOM(SDO_LRS.LOCATE_PT(SDO_LRS.CONVERT_TO_LRS_GEOM(ora_geometry, 3), SDO_GEOM.SDO_LENGTH(ora_geometry,3)/2)).SDO_POINT.Y;

ora_geometry := SDO_UTIL.FROM_WKTGEOMETRY('point ('|| mid_x || ' ' || mid_y ||')');

return 'point ('|| mid_x || ' ' || mid_y ||')';

EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;