5

enter image description here

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:

  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
    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?

Improve this question
3
  • 1
    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 - Slava Ukraini
    Jul 4, 2017 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.
    – User1974
    Jul 5, 2017 at 2:32
  • 1
    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 - Slava Ukraini
    Jul 5, 2017 at 6:23

4 Answers 4

Reset to default
2
+50

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 )
Improve this answer
0
2

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;
Improve this answer
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)
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;
Improve this answer
0
1

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!

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.