# 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
(
vertex_index number,
x number,
y number
);

---------- --------------- ---------- ----------
100 1                        0          5
100 2                       10         10
100 3                       30          0
100 4                       50         10
100 5                       60         10
``````

I need to:

1. Calculate the cumulative length of line segments (as shown in the grey text in the image above).
2. 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
'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
VERTEX_INDEX,
X || ' '  || Y || ' ' || ROUND(SUM(NVL(SQRT(POWER((X - PREV_X),2) + POWER((Y - PREV_Y),2)),0))
AS CUMULATIVE_LENGTH
FROM
(
--Step #1: Get the previous X and previous Y for Step #2's Pythagorean theorem calculation
SELECT
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
)
)
GROUP BY
``````

However, this solution is quite complicated. Can it simplified/improved?

• I think your solution is rather straight forward. I did not look at your solution but ended up with almost the same implementation (I used a CTE instead of nested selects). I think you can remove `ROAD_ID` from `ORDER BY ROAD_ID,VERTEX_INDEX` since it is part of `PARTITION` – Lennart Jul 4 '17 at 8:42
• @Lennart I'm no database expert, but I'm a bit surprised that there isn't a more succinct way of doing Pythagorean theorem calculations in Oracle. – Wilson Jul 5 '17 at 2:32
• I think the reason is that it's quite unusual to do it at the database level. Perhaps there are `GIS` or `RDF` functions that simplify it, I don't know. On the other hand you can easily create your own set of functions for special needs like the `seglength` function below. – Lennart Jul 5 '17 at 6:23

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
, '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
, 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
``````

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
, 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
, 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
)
)
select
, 'LINESTRING M ( '
|| listagg( x || ' ' || y || ' ' || clength , ', ' )
within group ( order by vertex_index )
|| ' )' linestring
``````

Test data:

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

begin
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 )
``````

I solved it a similar way to yours (two levels deep of subquery) http://sqlfiddle.com/#!4/8bde2/26. If you want to stay in pure Oracle SQL (not PL/SQL or other 3G), this will be about as short as it can get:

``````SELECT road_id
, 'LINESTRING M (' || LISTAGG(
' ' ||
x
|| ' ' ||
y
|| ' ' ||
ROUND(cumulative_length,2)
, ','
) WITHIN GROUP (ORDER BY vertex_index)
|| ')' linestring
FROM (
, vertex_index
, x
, y
, SUM(segment_length) OVER (PARTITION BY road_id ORDER BY vertex_index) AS cumulative_length
FROM (
, rv1.vertex_index vertex_index
, rv1.x x
, rv1.y y
, COALESCE(
SQRT(
POWER(rv2.x - rv1.x,2)
+ POWER(rv2.y - rv1.y,2))
,0) segment_length
rv1.vertex_index = rv2.vertex_index + 1)
)
)
``````

To avoid context swapping try not to use a PLSQL function with SQL. Yes, it might help readability, but if you are converting a lot of linestrings try to do the work in PLSQL or SQL and mix only if you have to.

There really isn't any improvements available to you. You can only construct an ESRI ST_GEOMETRY measured linestring via WKT. The OGC SFS does not give us practitioners a decent api to construct or edit geometry objects.

Oracle's SDO_GEOMETRY is better as it exposes its underlying arrays and geometry structure (both SQL3 Compliant) directly to the developer. That exposure allows you to do more things directly, but having an Oracle SDO_GEOMETRY with measures doesn't allow you to export it in either WKT or WKB (best) to give to ESRI's ST_GEOMETRY.

My two attempts at your issue are done only with Oracle Locator (who can afford ESRI's ST_GEOMETRY!!!).

``````-- Output ready for ESRI ST_GEOEMTRY(WKT)
select '100' as road_id,1 as vertex_index, 0 as x, 5 as y from dual union all
select '100' as road_id,2 as vertex_index,10 as x,10 as y from dual union all
select '100' as road_id,3 as vertex_index,30 as x, 0 as y from dual union all
select '100' as road_id,4 as vertex_index,50 as x,10 as y from dual union all
select '100' as road_id,5 as vertex_index,60 as x,10 as y from dual
)
select 'LINESTRING M (' || LISTAGG(X||' '||Y||' '||M,',') WITHIN GROUP (ORDER BY VERTEX_INDEX) || ')' AS WKT
FROM (SELECT ROAD_ID, VERTEX_INDEX, X, Y, round(SUM(M) OVER (PARTITION BY ROAD_ID ORDER BY VERTEX_INDEX),3) AS M
FROM (SELECT road_id, vertex_index, x, y, case when vertex_index = 1 then 0 else SQRT(power(ABS((lag(x,1) over (partition by road_id order by vertex_index))-x),2)+power(ABS((lag(y,1) over (partition by road_id order by vertex_index))-y),2)) end AS M
)
)

-- Create and validate Oracle SDO_GEOMETRY M linestring directly
select '100' as road_id,1 as vertex_index, 0 as x, 5 as y from dual union all
select '100' as road_id,2 as vertex_index,10 as x,10 as y from dual union all
select '100' as road_id,3 as vertex_index,30 as x, 0 as y from dual union all
select '100' as road_id,4 as vertex_index,50 as x,10 as y from dual union all
select '100' as road_id,5 as vertex_index,60 as x,10 as y from dual
), AGG_LINE AS (
FROM (SELECT ROAD_ID, VERTEX_INDEX, X, Y, round(SUM(M) OVER (PARTITION BY ROAD_ID ORDER BY VERTEX_INDEX),3) AS M
FROM (SElECT road_id, vertex_index, x, y, case when vertex_index = 1 then 0 else SQRT(power(ABS((lag(x,1) over (partition by road_id order by vertex_index))-x),2)+power(ABS((lag(y,1) over (partition by road_id order by vertex_index))-y),2)) end AS M
)
)
)
substr(sdo_geom.validate_geometry(linestring,0.005),1,5) AS vLine,
linestring
SDO_GEOMETRY(3302,NULL, NULL, SDO_ELEM_INFO_ARRAY(1,2,1),
CAST(MULTISET(SELECT b.COLUMN_VALUE
FROM AGG_LINE a,
TABLE(mdsys.sdo_ordinate_array(a.x,a.y,a.m)) b
ORDER BY a.VERTEX_INDEX)
AS mdsys.sdo_ordinate_array)) AS linestring
) f;
``````

I know this is changing your table and premise, but how about using the power of virtual columns?

``````create table road_vertices
(
vertex_index number,
x number,
y number,
prev_x number,
prev_y number,
distance number generated always as (SQRT(Power(x - prev_x, 2) + Power(y - prev_y, 2))) VIRTUAL
)
``````

Then insert like this:

``````insert into road_vertices (road_id,vertex_index,x,y, prev_x, prev_y) values ('100',1,0,5,0,0);