clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 756632 fiddles created (13593 in the last week).

create table rdvx ( roadid varchar2(32) , vertexindex number , x number(16, 9) , y number(16, 9) );
 hidden batch(es)


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
 hidden batch(es)


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; /
 hidden batch(es)


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)
 hidden batch(es)


-- ----------------------------------------------------------------------------- -- 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
 hidden batch(es)