Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table rdvx (
  roadid varchar2(32)
, vertexindex number
, x number(16, 9)
, y number(16, 9)
);
begin
  Insert into rdvx (roadid,vertexindex,x,y) values ('507200',1,670147.939539111,4863628.42112579);
  Insert into rdvx (roadid,vertexindex,x,y) values ('507200',2,670158.738160522,4863632.97808487);
  Insert into rdvx (roadid,vertexindex,x,y) values ('507200',3,670298.552315403,4863680.6505552);
  Insert into rdvx (roadid,vertexindex,x,y) values ('500100',1,670113.321819876,4863724.93612511);
  Insert into rdvx (roadid,vertexindex,x,y) values ('500100',2,670122.424652261,4863728.93856436);
  Insert into rdvx (roadid,vertexindex,x,y) values ('500100',3,670259.908118496,4863776.23199953);
end;
/
1 rows affected

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;
/
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
      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;


ROADID
LINESTRING


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)


-- -----------------------------------------------------------------------------
--   compare: current x/y and old x/y
-- -----------------------------------------------------------------------------
  select
    roadid
  , vertexindex
  , x
  , y
  , case 
      when vertexindex = 1 then 0
      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 ;


ROADID
VERTEXINDEX
X
Y
OLD_X
OLD_Y


500100
1
670113.321819876
4863724.93612511
0
0

500100
2
670122.424652261
4863728.93856436
670113.321819876
4863724.93612511

500100
3
670259.908118496
4863776.23199953
670122.424652261
4863728.93856436

507200
1
670147.939539111
4863628.42112579
0
0

507200
2
670158.738160522
4863632.97808487
670147.939539111
4863628.42112579

507200
3
670298.552315403
4863680.6505552
670158.738160522
4863632.97808487

ROADID	LINESTRING
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)

ROADID	VERTEXINDEX	X	Y	OLD_X	OLD_Y
500100	1	670113.321819876	4863724.93612511	0	0
500100	2	670122.424652261	4863728.93856436	670113.321819876	4863724.93612511
500100	3	670259.908118496	4863776.23199953	670122.424652261	4863728.93856436
507200	1	670147.939539111	4863628.42112579	0	0
507200	2	670158.738160522	4863632.97808487	670147.939539111	4863628.42112579
507200	3	670298.552315403	4863680.6505552	670158.738160522	4863632.97808487