Can this query be simplified? Calculate cumulative length of segments and collapse coordinates into linestring

I have a road_vertices table:

create table road_vertices
(
road_id number,
vertex_index number,
x number,
y number
);

insert into road_vertices values ('100',1,0,5);
insert into road_vertices values ('100',2,10,10);
insert into road_vertices values ('100',3,30,0);
insert into road_vertices values ('100',4,50,10);
insert into road_vertices values ('100',5,60,10);

select * from road_vertices;

   ROAD_ID VERTEX_INDEX             X          Y
---------- --------------- ---------- ----------
       100 1                        0          5
       100 2                       10         10
       100 3                       30          0
       100 4                       50         10
       100 5                       60         10

I need to:

• Calculate the cumulative length of line segments (as shown in the grey text in the image above).

• Collapse the coordinates and cumulative lengths into a linestring.

This is the end-goal:

ROAD_ID     LINESTRING
----------------------------------------------------------------------------
100         LINESTRING M ( 0 5 0, 10 10 11.18, 30 0 33.54, 50 10 55.9, 60 10 65.9)

I've figured out a way to do it:

```
--Step #3: Collapse the coordinates and cumulative lengths into a linestring
SELECT
ROAD_ID,
'LINESTRING M ( ' || LISTAGG(CUMULATIVE_LENGTH, ', ')
WITHIN GROUP (ORDER BY VERTEX_INDEX) || ')' AS LINESTRING
FROM
(
--Step #2: Calculate each line segment's length using the Pythagorean theorem, and add together to get cumulative length
SELECT
ROAD_ID,
VERTEX_INDEX,
X || ' ' || Y || ' ' || ROUND(SUM(NVL(SQRT(POWER((X - PREV_X),2) + POWER((Y - PREV_Y),2)),0))
OVER (PARTITION BY ROAD_ID ORDER BY ROAD_ID,VERTEX_INDEX),2)
AS CUMULATIVE_LENGTH
FROM
(
--Step #1: Get the previous X and previous Y for Step #2's Pythagorean theorem calculation
SELECT
ROAD_ID,
VERTEX_INDEX,

A combination of a function and a query/view may be another option. The function fulfils your first requirement: " Calculate each line segment's length ... the portions of lines between vertices." (The function will need exception handling and testing!)

-- -----------------------------------------------------------------------------
--   function: calculate the segment length
-- -----------------------------------------------------------------------------
create or replace function seglength(
  x_ number
, oldx_ number
, y_ number
, oldy_ number
)
return number as
begin
  if oldx_ = 0 or oldy_ = 0 then  -- vertex_index 1, no "previous"/old values
    return 0;
  else 
    return round( 
        sqrt( 
          power( ( x_ - oldx_ ), 2 ) 
        + power( ( y_ - oldy_) , 2 ) 
        )
      , 2 
    );
  end if;
end seglength;
/

Then, we can use a modified version of your original query, like so:

select
  d.roadid
, 'LINESTRING M ( ' 
  || listagg( ( round(x,2) || ' '  || round(y,2) || ' ' 
           || seglength(x, d.old_x, y, d.old_y) ) , ', ' ) 
     within group ( order by d.vertexindex )
  || ')' linestring
from (
  select
    roadid
  , vertexindex
  , x
  , y
  , case 
      when vertexindex = 1 then 0 -- zero instead of NULL
      else ( lag (x,1) over ( partition by roadid order by vertexindex ) )
    end old_x
  , case 
      when vertexindex = 1 then 0
      else ( lag (y,1) over ( partition by roadid order by vertexindex ) )
    end old_y  
  from rdvx
  ) d 
group by d.roadid;

output:

500100  LINESTRING M ( 670113.32 4863724.94 0, 670122.42 4863728.94 9.94, 670259.91 4863776.23 145.39)   
507200  LINESTRING M ( 670147.94 4863628.42 0, 670158.74 4863632.98 11.72, 670298.55 4863680.65 147.72)

Note: the last values in the "LINESTRING" are smaller than the ones in your question. Can it be the case that your original query actually calculates the distance between the vertices 1 and 3? My understanding is that the "SEGMENT LENGTHS" are supposed to be: distance v1-v1 ie 0, distance v1-v2, distance v2-v3. dbfiddle here

UPDATE

Function:

create or replace function rlength(
  x number
, prev_x number
, y number
, prev_y number
)
return number as
begin
  if prev_x is null or prev_y is null then
    return 0 ;
  else 
    return round( 
      sqrt( 
        power( ( x - prev_x ), 2 ) 
      + power( ( y - prev_y ), 2 ) 
      )
    , 2 
    );
  end if;
end rlength;
/

Query:

with roads_ as (
  select
    road_id
  , vertex_index
  , round( x, 2 ) x
  , round( y, 2 ) y
  , sum ( rlen ) over ( partition by road_id order by road_id, vertex_index )  clength
  from (
    select
      road_id
    , vertex_index
    , x
    , y
    , rlength( 
        x 
      , lag( x,1 ) over ( partition by road_id order by vertex_index )
      , y
      , lag( y,1 ) over ( partition by road_id order by vertex_index )
      ) rlen
    from road_vertices 
    )
)
select 
   road_id
, 'LINESTRING M ( ' 
  || listagg( x || ' ' || y || ' ' || clength , ', ' )
     within group ( order by vertex_index )
  || ' )' linestring
from roads_
group by road_id;

Test data:

create table road_vertices
(
road_id number,
vertex_index number,
x number,
y number
);

begin
  insert into road_vertices values ('100',1,0,5);
  insert into road_vertices values ('100',2,10,10);
  insert into road_vertices values ('100',3,30,0);
  insert into road_vertices values ('100',4,50,10);
  insert into road_vertices values ('100',5,60,10);
end;
/

Output:

ROAD_ID  LINESTRING                                                               
100      LINESTRING M ( 0 5 0, 10 10 11.18, 30 0 33.54, 50 10 55.9, 60 10 65.9 )

StackExchange Database Administrators Q#177818, answer score: 2