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