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 )
Best Answer
Just a little modification to save 1 table access: