How to calculate cumulative length of a linestring
Keywords vectorize,cumulative length,linestring,getVertices,Oracle SpatialSummary Calculating cumulative length of a linestring (or polygon ring) is fairly straightforwards as this article shows.
Sometimes one needs to be able to calculate the cumulative length of a linestring. That is, the contribution of the vertex-to-vertex segments of a linestring to its total length.
This can be achieved by two different methods.
The first uses ordinary Locator functionality as follows:
SELECT segment_id, segment_length,
SUM ( segment_length) OVER ( partition BY 1 ORDER BY segment_id) AS cumulative_length
FROM (
SELECT segment_id,
sdo_geom. sdo_distance(
sdo_geometry( 2001 , 8307 , sdo_point_type( f. x1, f. y1, NULL ) , NULL , NULL ) ,
sdo_geometry( 2001 , 8307 , sdo_point_type( f. x2, f. y2, NULL ) , NULL , NULL ) , 0.05 ) AS segment_length
FROM ( SELECT v. id AS segment_id,
v. x AS x1, v. y AS y1,
lead( v. x, 1 ) OVER ( ORDER BY v. id) AS x2,
lead( v. y, 1 ) OVER ( ORDER BY v. id) AS y2
FROM TABLE ( sdo_util. getvertices(
SDO_GEOMETRY( 2002 , 8307 , NULL ,
SDO_ELEM_INFO_ARRAY( 1 , 2 , 1 ) ,
SDO_ORDINATE_ARRAY( 2.543362 , 49.019445 ,
2.543362 , 49.01940 ,
2.543037 , 49.01935 ) ) ) ) v
) f
WHERE f. x2 IS NOT NULL AND f. y2 IS NOT NULL
) g;
-- Results
--
SEGMENT_ID SEGMENT_LENGTH CUMULATIVE_LENGTH
---------- -------------- -----------------
1 5.00479112866628 5.00479112866628
2 24.4133261026822 29.41811723134848
The other method is to use my GetVector or ST_Vectorize functions as follows:
SELECT segment_id, segment_length,
SUM ( segment_length) OVER ( partition BY 1 ORDER BY segment_id) AS cumulative_length
FROM ( SELECT v. id AS segment_id,
sdo_geom. sdo_length( v. AsSdoGeometry( 8307 ) , 0.005 ) AS segment_length
FROM TABLE ( LINEAR. ST_Vectorize(
SDO_GEOMETRY( 2002 , 8307 , NULL ,
SDO_ELEM_INFO_ARRAY( 1 , 2 , 1 ) ,
SDO_ORDINATE_ARRAY( 2.543362 , 49.019445 ,
2.543362 , 49.01940 ,
2.543037 , 49.01935 ) ) ) ) v
) ;
-- Results
--
SEGMENT_ID SEGMENT_LENGTH CUMULATIVE_LENGTH
---------- -------------- -----------------
1 5.00479112866628 5.00479112866628
2 24.4133261026822 29.41811723134848
I hope this is of help to someone.
SpatialDB Advisor
Home
Comment