enter image description here

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;
  -- 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
    -- 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;

    -- 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;
      -- check to see if the running total is past the midpoint
      if distanceAlong >= 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
    end loop;
  end loop;

  return null;

   raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
grant execute on ST_MidPoint to public;

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


I have abandoned this function. It works, but it's horrendously inefficient because it's not possible to use the SDE functions & 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).

  • \$\begingroup\$ This is the reason why the INSERT INTOS were used: gis.stackexchange.com/q/344294/135445 \$\endgroup\$ – User1973 Dec 8 '19 at 0:20
  • \$\begingroup\$ I think we should close this question. The function is so slow and poorly structured that it's not really useable. I'm going to go with another option (pre-compute with python). \$\endgroup\$ – User1973 Dec 8 '19 at 0:24
  • 2
    \$\begingroup\$ You can write your own review explaining what makes the function poorly structured. There doesn't seem to be a valid reason to close the question, the other option is that you could delete it yourself. \$\endgroup\$ – pacmaninbw Dec 8 '19 at 14:47
  • \$\begingroup\$ @pacmaninbw Yeah, I tried to delete the question, but I couldn't because there's an answer. I also added a note to the question about why the code can't be improved. \$\endgroup\$ – User1973 Dec 15 '19 at 20:02

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
wkt_geometry clob;
ora_geometry sdo_geometry;
mid_x number(10,6);
mid_y number(10,6);
mid_point_geom sde.st_geometry;

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_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;

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

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

   raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
  • 1
    \$\begingroup\$ Well, this changes things! Spatial now free with all editions of Oracle Database. \$\endgroup\$ – User1973 Dec 20 '19 at 5:11
  • \$\begingroup\$ Thanks for letting me know. \$\endgroup\$ – Ashok Vanam Dec 20 '19 at 5:16
  • \$\begingroup\$ You have presented an alternative solution, but haven't reviewed the code. Please edit to show what aspects of the question code prompted you to write this version, and in what ways it's an improvement over the original. It may be worth (re-)reading How to Answer. \$\endgroup\$ – Toby Speight Jan 6 at 11:55
  • \$\begingroup\$ The procedure posted works fine. @TobySpeight are you asking the user who posted the question? \$\endgroup\$ – Ashok Vanam Jan 7 at 23:43
  • 1
    \$\begingroup\$ I posted a related answer on Stack Overflow: Get midpoint of SDO.GEOMETRY polyline. \$\endgroup\$ – User1973 Jan 12 at 22:46

I would recommend to try re-writing the procedure with Buffer and Centroid functions as below.

procedure mindpoint (in_line_geometry)

line_buffer_geom = sde.st_buffer (in_line_geometry, 0.05)
cenrtroid_buffer_geom = sde.st_centroid (line_buffer_geom )
return cenrtroid_buffer_geom 
  • \$\begingroup\$ (Down-voters please comment.) \$\endgroup\$ – greybeard Dec 7 '19 at 4:00
  • \$\begingroup\$ The cenrtroid in cenrtroid_buffer_geom looks accidental. \$\endgroup\$ – greybeard Dec 7 '19 at 4:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.