developers

    Forum Stats

  • 3,873,824 Users
  • 2,266,678 Discussions
  • 7,911,636 Comments

Discussions

Code Review: Update polyline vertices with cumulative length of line

User_1871
User_1871 Member Posts: 247 Red Ribbon
edited Jun 24, 2022 3:37AM in SQL & PL/SQL

I 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:

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


Tagged:

Answers

  • Paulzip
    Paulzip Member Posts: 8,801 Blue Diamond

    It seems to me you'd be better off encapsulating such methods in a descendant object type, but anyway...

    Code seems ok, but a couple of points :

    Replace power(..), with a squaring multiplication. Power is great, has its place, but it's relatively slow.

    You seem to be overly assigning everything to variables. Whilst that's good for instrumentation if you're outputting everything, it slows code down as values need to be loaded in registers, range and type checked and it makes code more verbose.

    I think your code can be rewritten as this :

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


  • _jum
    _jum Member Posts: 553 Bronze Trophy
    edited Apr 1, 2022 11:26AM

    Have a look at the fine article"How to calculate cumulative length of a linestring" by The Spatial Database Advisor


  • User_1871
    User_1871 Member Posts: 247 Red Ribbon

    @Paulzip Thanks! That helps. And of course, your revised function produces the correct result:

    MULTILINESTRING M ((0 5 0, 10 10 11.18, 30 0 33.54),(50 10 33.54, 60 10 43.54))
    

    Side question:

    Regarding your comment "It seems to me you'd be better off encapsulating such methods in a descendant object type...". Is there any chance you could elaborate on that?

    Related:

    Thanks again.

  • _jum
    _jum Member Posts: 553 Bronze Trophy
    edited Apr 1, 2022 1:52PM

    If you use (free) LRS-functions you can directly achieve the result:

    SELECT SDO_LRS.CONVERT_TO_LRS_GEOM(
            SDO_CS.make_2d(
               SDO_UTIL.FROM_WKTGEOMETRY('MULTILINESTRING (( 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))')
             )) lrsline 
       FROM dual;
    


    { "spatialdimension" : 2,
    "geometrycollection" : {"geometries" :
     [{"line" : {"datapoints" : [[0,5,0],[10,10,11.180339887499],[30,0,33.5410196624969]]}},
      {"line" : {"datapoints" : [[50,10,33.5410196624969],[60,10,43.5410196624969]]}}]}
    } 
    

    Btw. if you code a function DETERMINISTIC clause could improve perfomance.

  • Paulzip
    Paulzip Member Posts: 8,801 Blue Diamond

    I don't have that spatial library installed, but can assume ST_GEOMETRY appears to be an object type. As such, you could inherit from it and add a method "m_as_length" to that object type.

  • User_1871
    User_1871 Member Posts: 247 Red Ribbon
    edited Apr 1, 2022 4:50PM

    @_jum Nice! That LRS-function solution is very simple. I like it.

    Out of curiosity, how did you output the geometry to JSON? Just curious what technique you used...

  • _jum
    _jum Member Posts: 553 Bronze Trophy
  • User_1871
    User_1871 Member Posts: 247 Red Ribbon

    Side note:

    In my original function (and in @Paulzip 's code review), we defined the variables as:

    result       varchar2(32767);
    vertex_set   varchar2(32767);
    

    Alternatively, I suppose we could have defined those variables a CLOBs:

    result       clob;
    vertex_set   clob;
    


developers