Question :
I have a road_vertices table:
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
I need to:
- Calculate the cumulative length of line segments (as shown in the grey text in the image above).
- 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
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;
However, this solution is quite complicated. Can it simplified/improved?
Answer :
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 )
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 (
SELECT road_id
, vertex_index
, x
, y
, SUM(segment_length) OVER (PARTITION BY road_id ORDER BY vertex_index) AS cumulative_length
FROM (
SELECT rv1.road_id road_id
, 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
FROM road_vertices rv1
LEFT JOIN road_vertices rv2
ON (rv1.road_id = rv2.road_id AND
rv1.vertex_index = rv2.vertex_index + 1)
WHERE rv1.road_id = 100
)
)
GROUP BY road_id;
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)
With road_vertices as (
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
FROM road_vertices
)
)
GROUP BY ROAD_ID
ORDER BY ROAD_ID;
-- Create and validate Oracle SDO_GEOMETRY M linestring directly
With road_vertices as (
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 (
SELECT road_id,vertex_index,X,Y,M
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
FROM road_vertices
)
)
ORDER BY ROAD_ID, VERTEX_INDEX
)
SELECT ROAD_ID,
substr(sdo_geom.validate_geometry(linestring,0.005),1,5) AS vLine,
linestring
FROM (SELECT c.ROAD_ID,
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
WHERE a.ROAD_ID = c.ROAD_ID
ORDER BY a.VERTEX_INDEX)
AS mdsys.sdo_ordinate_array)) AS linestring
FROM road_vertices c
GROUP BY c.ROAD_ID
ORDER BY c.ROAD_ID
) f;
I know this is changing your table and premise, but how about using the power of virtual columns?
create table road_vertices
(
road_id number,
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);
insert into road_vertices (road_id,vertex_index,x,y, prev_x, prev_y) values ('100',2,10,10,0,5);
insert into road_vertices (road_id,vertex_index,x,y, prev_x, prev_y) values ('100',3,30,0,10,10);
I don’t think there is any predefined Pythagorean function in Oracle, so you have to use SQRT etc. And if you want to refer to previous values you have to use LAG.
Maybe the virtual column will give you some idea to simplify your code though.
Good luck and interesting question… I learnt from researching this!