Calculate the midpoint of a polyline I have polylines in a GIS database. The polylines are stored using a user-defined type called ST_GEOMETRY.

ST_GEOMETRY has lots of spatial functions. However, it does not have a polyline midpoint function.

Therefore, I have written a custom PL/SQL midpoint function to fill this gap.

As a novice programmer, I'm wondering, how can the code be improved to be as fast and robust as possible?



-- execute this as the SDE user
create or replace function ST_MidPoint
   (line_in IN sde.st_geometry)
   RETURN sde.st_geometry
IS midpoint sde.st_geometry;
  srid integer;
  line_length number(38);
  num_parts integer;
  num_points integer;
  partNum integer;
  distanceAlong number(38);
  segmentLength number(38);
  part sde.st_geometry;
  p1 sde.st_geometry;
  p2 sde.st_geometry;
  x1 double precision;
  y1 double precision;
  x2 double precision;
  y2 double precision;
BEGIN
  -- get the SRID of the line for later use in constructing the midpoint geometrey
  select sde.st_srid (line_in)
  into srid
  from dual;

  -- calculate the total length of the line
  select sde.st_length(line_in)
  into line_length
  from dual;

  -- get the number of parts that make up the line
  select sde.st_numgeometries(line_in)
  into num_parts
  from dual;

  distanceAlong := 0;
  for partNum in 1..num_parts loop
    --dbms_output.put_line(partNum);
    -- get the geometry for this part
    select sde.st_geometryn(line_in, partNum)
    into part
    from dual;

    -- get the number of points that make up this part
    select sde.st_numpoints(part)
    into num_points
    from dual;
    --dbms_output.put_line(num_points);

    -- get the first point (the "from" point) for the part
    select sde.st_pointn (part, 1)
    into p1
    from dual;

    -- iterate along the line until the section that contains the midpoint is found
    for pointNum in 2..num_points loop
      -- get the "to" point of the segment
      select sde.st_pointn(part, pointNum)
      into p2
      from dual;

      -- calculate the distance between the from point and the to point
      select sde.st_distance(p1, p2)
      into segmentLength
      from dual;

      -- add the distance along this segment to the running total
      distanceAlong := distanceAlong + segmentLength;
      --dbms_output.put_line(distanceAlong);
      -- check to see if the running total is past the midpoint
      if distanceAlong &gt;= line_length/2.0 then
        -- the two current points encompass the midpoint of the line
        -- determine the midpoint geometry and return it
        select sde.st_x(p1)
        into x1
        from dual;
        select sde.st_y(p1)
        into y1
        from dual;
        select sde.st_x(p2)
        into x2
        from dual;
        select sde.st_y(p2)
        into y2
        from dual;
        select sde.st_point((x1+x2)/2.0, (y1+y2)/2.0, srid)
        into midpoint
        from dual;
        -- the midpoint has been found, not need to interogate the rest of the line
        RETURN midpoint;
      end if;
      -- save the endpoint as the first point and continue down the line looking for the midpoint
      p1:=p2;
    end loop;
  end loop;

  return null;

EXCEPTION
WHEN OTHERS THEN
   raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
/
grant execute on ST_MidPoint to public;


--select objectid,sde.ST_MidPoint(shape) geom
--from gis.line_test
--order by objectid;


Update:

I have abandoned this function. It works, but it's horrendously inefficient because it's not possible to use the SDE functions &amp; operators to directly assign values to variables. I needed to wrap them in queries instead.

More information here:
Use SDE.ST_GEOMETRY functions in a custom function

Originally, I had thought that the code could be improved. However, now I realize that this is not possible--due to the aforementioned problem.

It would be my preference that this question be deleted (I tried but couldn't because there's an answer).
Check this function which returns the mid point X,Y in WKT format. Please note this function uses the SDO_LRS of Oracle which is part of Oracle Spatial.

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;