這個查詢可以簡化嗎？計算線段的累積長度並將座標折疊成線串

我有一張road_vertices桌子：

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

我需要：

1. 計算線段的累積長度（如上圖中的灰色文本所示）。
2. 將座標和累積長度折疊成線串。

這是最終目標：

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)

我想出了一個辦法：

--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,
           ROUND(X,2) AS X,
           ROUND(Y,2) AS Y,
           LAG (X,1) OVER (PARTITION BY ROAD_ID ORDER BY VERTEX_INDEX) AS PREV_X,
           LAG (Y,1) OVER (PARTITION BY ROAD_ID ORDER BY VERTEX_INDEX) AS PREV_Y
       FROM
           INFRASTR.ROAD_VERTICES
       )
   ) 
GROUP BY 
   ROAD_ID;

然而，這個解決方案相當複雜。它可以簡化/改進嗎？

函式和查詢/視圖的組合可能是另一種選擇。該函式滿足您的第一個要求：“計算每個線段的長度……頂點之間的線段。” （該功能需要異常處理和測試！）

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

然後，我們可以使用原始查詢的修改版本，如下所示：

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;

輸出：

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) 

注意：“LINESTRING”中的最後一個值小於您問題中的值。您的原始查詢是否會實際計算頂點 1 和 3 之間的距離？我的理解是“段長度”應該是：距離 v1-v1 即 0，距離 v1-v2，距離 v2-v3。dbfiddle在這裡

更新

功能：

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

詢問：

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;

測試數據：

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

輸出：

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 ) 

引用自：https://dba.stackexchange.com/questions/177818