Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Code Review: Update polyline vertices with cumulative length of line

User_1871Mar 31 2022 — edited Jun 24 2022

image.pngI have a multi-part polyline in a row in an Oracle 18c table. The polyline's geometry information is stored in a SHAPE column; the datatype is a user-defined spatial type called SDE.ST_Geometry.
I've written a PL/SQL function call m_as_length() that replaces a coordinate in the polyline's vertices with the cumulative length of the line. The coordinate is called an "M" coordinate (aka a "Measure-value"). M coordinates are similar to X and Y coordinates, but are used for specialized linear referencing purposes.

Input:
MULTILINESTRING M (( 0.0 5.0 -100000.0, 10.0 10.0 -100000.0, 30.0 0.0 -100000.0),( 50.0 10.0 -100000.0, 60.0 10.0 -100000.0))
--select sde.st_astext(shape) from polylines

Output:
MULTILINESTRING M ((0 5 0, 10 10 11.18, 30 0 33.54),(50 10 33.54, 60 10 43.54))
--select m_as_length(shape) from polylines

Question:
I'm a novice when it comes to PL/SQL. Can the m_as_length() function be improved?

with
    function pythagoras(x1 in number, y1 in number, x2 in number, y2 in number) return number is
    begin
        return round(  sqrt(power(x2 - x1, 2) + power(y2 - y1, 2))  ,2);
    end;
   
    function m_as_length(shape in sde.st_geometry) return varchar2
    is
        result              varchar2(32767);
        vertex              varchar2(32767);
        vertex_set          varchar2(32767);
        i                   number;
        j                   number;
        num_parts           number;
        num_points          number;
        oldX                number;
        oldY                number;
        newX                number;
        newY                number;
        line_len            number;
    begin
        num_parts           := sde.st_geometry_operators.st_numgeometries_f(shape); 
        oldX                := sde.st_geometry_operators.st_x_f(sde.st_geometry_operators.st_startpoint_f(sde.st_geometry_operators.st_geometryN_f(shape,1)));
        oldY                := sde.st_geometry_operators.st_y_f(sde.st_geometry_operators.st_startpoint_f(sde.st_geometry_operators.st_geometryN_f(shape,1)));
        line_len            := 0;
       
        for i in 1..num_parts
        loop
            num_points      := sde.st_geometry_operators.st_numpoints_f(sde.st_geometry_operators.ST_GeometryN_f(shape,i));
            vertex_set      := null;
            for j in 1..num_points
            loop
                newX        := sde.st_geometry_operators.st_x_f(sde.st_geometry_operators.st_pointn_f(sde.st_geometry_operators.st_geometryn_f(shape,i),j));
                newY        := sde.st_geometry_operators.st_y_f(sde.st_geometry_operators.st_pointn_f(sde.st_geometry_operators.st_geometryn_f(shape,i),j));
                if j <> 1 then
                   line_len := line_len + pythagoras(oldX, oldY, newX, newY);
                end if;
                oldX        := newX;
                oldY        := newY;
                vertex      := newX || ' ' || newY || ' ' || line_len;
                vertex_set  := vertex_set || vertex || ', ';
            end loop;
            vertex_set      := '(' || rtrim((vertex_set),', ') || '),';
            result          := result || vertex_set;
        end loop;
        result              := 'MULTILINESTRING M (' || rtrim((result),',') || ')';
        return result;
    end;
select
    m_as_length(shape)
from
    polylines

Related:
Use SDE.ST_GEOMETRY functions in a custom function
Explains why the function calls are so verbose: sde.st_geometry_operators.st_numgeometries_f.
ST_GEOMETRY functions (links):
st_numgeometries
st_x
st_startpoint
st_geometryN
st_numpoints
st_pointn
I have a JavaScript expression that serves the same purpose (different use case). It's easier to read than the PL/SQL function.

Comments

Post Details

Added on Mar 31 2022
8 comments
508 views