# 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 >= 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 & operators to directly assign values to variables. I needed to wrap them in queries instead.

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).

• This is the reason why the INSERT INTOS were used: gis.stackexchange.com/q/344294/135445 – User1973 Dec 8 '19 at 0:20
• 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). – User1973 Dec 8 '19 at 0:24
• 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. – pacmaninbw Dec 8 '19 at 14:47
• @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. – 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
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;

• Well, this changes things! Spatial now free with all editions of Oracle Database. – User1973 Dec 20 '19 at 5:11
• Thanks for letting me know. – Ashok Vanam Dec 20 '19 at 5:16
• 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. – Toby Speight Jan 6 at 11:55
• The procedure posted works fine. @TobySpeight are you asking the user who posted the question? – Ashok Vanam Jan 7 at 23:43
• I posted a related answer on Stack Overflow: Get midpoint of SDO.GEOMETRY polyline. – 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

• (Down-voters please comment.) – greybeard Dec 7 '19 at 4:00
• The cenrtroid in cenrtroid_buffer_geom looks accidental. – greybeard Dec 7 '19 at 4:01