Get midpoint of SDO.GEOMETRY polyline

Question

I have a table in Oracle 18c that has an SDO_GEOMETRY column with polylines.

I want to query the X and Y coordinates of the polyline midpoints using SQL.

Is there a way to do this with Oracle Spatial?

Answer 1

Oracle Spatial has a linear referencing package called SDO_LRS. It can be used to find the midpoint coordinates of a polyline.

--In this case, 'sdo' is the name of the sdo_geometry column.

sdo_lrs.convert_to_std_geom(sdo_lrs.locate_pt(sdo_lrs.convert_to_lrs_geom(sdo,3)
    ,sdo_geom.sdo_length(sdo,3)/2)).sdo_point.x as midpoint_x,

sdo_lrs.convert_to_std_geom(sdo_lrs.locate_pt(sdo_lrs.convert_to_lrs_geom(sdo,3)
    ,sdo_geom.sdo_length(sdo,3)/2)).sdo_point.y as midpoint_y

---

For bonus points:

This is how ESRI's SDE.ST_GEOMETRY can be converted to SDO_GEOMETRY for the purpose of getting the midpoint coordinates:

select
    sdo_lrs.convert_to_std_geom(sdo_lrs.locate_pt(sdo_lrs.convert_to_lrs_geom(sdo,3),sdo_geom.sdo_length(sdo,3)/2)).sdo_point.x as midpoint_x,
    sdo_lrs.convert_to_std_geom(sdo_lrs.locate_pt(sdo_lrs.convert_to_lrs_geom(sdo,3),sdo_geom.sdo_length(sdo,3)/2)).sdo_point.y as midpoint_y
from
    (select
        sdo_util.from_wktgeometry(sde.st_astext(shape)) as sdo
    from
        roads)

---

This answer was inspired by an answer on Code Review: Calculate the midpoint of a polyline.