How to calculate cumulative length of a linestring, or the truck tonnage that accumulates across a set of roads

Introduction

Sometimes one needs to be able to calculate the cumulative length of a linestring. That is, the contribution of each vertex-to-vertex segment of a linestring to its total length. We will demonstrate this with Oracle and SQL Server.

A different but related problem is to compute the cumulative value of a quantity using one or more linestrings. For example, compute the total tonnage that trucks carry over a common set of roads over a time interval eg a year. We will demonstrate with SQL Server Spatial.

Oracle – Cumulative Length (1)

With Oracle we demonstrate how to compute cumulative length via 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

Oracle – Cumulative Length (2)

Another method is to use my ST_Vectorize function 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

Oracle – Cumulative Length (3)

And finally, one can use SDO_LRS to populate the Measure attribute with length values as follows (hat tip to Jörn-Uwe Müller):

SELECT id as segment_id,
       z - lag(z)  OVER (ORDER BY id) as segment_length,
       z as cumulative_length
  FROM (SELECT 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)) line
          FROM dual), 
       sdo_util.getvertices(SDO_LRS.CONVERT_TO_LRS_GEOM(line));

-- Results
--
SEGMENT_ID SEGMENT_LENGTH CUMULATIVE_LENGTH
---------- -------------- -----------------
         1                                0
         2     5.00479113        5.00479113
         3     24.4133261        29.4181172

SQL Server – Cumulative Length (1)

This SQL Server Spatial calculation only uses native database methods (except the generate_series TSQL function available with the package of TSQL functions available from the Shop on this website).

Note that the calculations are done using a geography object.

WITH data AS ( 
  SELECT geography::STGeomFromText('LINESTRING(2.543362  49.019445,2.543362  49.01940, 2.543037 49.01935)',4326) as geom
)
SELECT f.segment_id, 
       f.segment_length,
       SUM(f.segment_length) OVER (PARTITION BY 1 ORDER BY f.segment_id) AS cumulative_length,
       SUM(f.segment_length) OVER (PARTITION BY 1 ORDER BY f.segment_id) / f.total_length * 100.0 AS cumulative_percentage
  FROM (SELECT gs.IntValue AS segment_id,
               a.geom.STPointN(gs.IntValue).STDistance(a.geom.STPointN(gs.IntValue+1)) as segment_length,
               a.geom.STLength() as total_length
          FROM data as a
               CROSS APPLY
               [dbo].[generate_series](1,a.geom.STNumPoints()-1,1) as gs
       ) f
 ORDER BY cumulative_length;
GO

-- Results
segment_id segment_length   cumulative_length cumulative_percentage
1          5.00445514271847 5.00445514271847  17.011690661358
2          24.413286117332  29.4177412600505  100

SQL Server – Cumulative Length (2)

This method uses the STSegmentize function which is one of the TSQL functions available from the shop at this website. This function segments the supplied linestring returning each segment and other metadata such as segment length and cumulative length.

With data as ( 
  select 1 as gid, geometry::STGeomFromText('LINESTRING(-1 0, 0 1, 0 2, -1 3, -1 4, 0 5, 0 8, -1 9)',0) as geom
)
SELECT s.id,
       s.cumulative_length,
       s.cumulative_length / a.geom.STLength() * 100.0 as cumulative_percentage,
       s.segment
  FROM data as a
       cross apply
       [dbo].[STSegmentize](a.geom,'ALL',null,null,null,null,3,3,3) as s;
GO

-- Results
id cumulative_length cumulative_percentage segment
1  1.4142135623731   13.2704598304932      0x000000000114000000000000F0BF00000000000000000000000000000000000000000000F03F
2  2.41421356237309  22.6540919660986      0x0000000001140000000000000000000000000000F03F00000000000000000000000000000040
3  3.82842712474619  35.9245517965918      0x00000000011400000000000000000000000000000040000000000000F0BF0000000000000840
4  4.82842712474619  45.3081839321973      0x000000000114000000000000F0BF0000000000000840000000000000F0BF0000000000001040
5  6.24264068711928  58.5786437626905      0x000000000114000000000000F0BF000000000000104000000000000000000000000000001440
6  9.24264068711928  86.7295401695068      0x0000000001140000000000000000000000000000144000000000000000000000000000002040
7  10.6568542494924  100                   0x00000000011400000000000000000000000000002040000000000000F0BF0000000000002240

Visually:

SQL Server – Cumulative Length (3)

This method uses the STAddMeasure function which is one of the TSQL functions available from the shop at this website. This function creates a measure value for each vertex based on the length of the line. The STDumpPoints returns return each point and other metadata such as the Measure (M) which is used to compute segment and cumulative length.

use devdb
go

With data as ( 
select 1 as gid,
       [lrs].[STAddMeasure](geometry::STGeomFromText('LINESTRING(-1 0, 0 1, 0 2, -1 3, -1 4, 0 5, 0 8, -1 9)',0),null,null,3,3) as geom
)
SELECT s.uid,
       s.m - (lag(s.m,1) over (order by s.uid)) as segment_length,
       s.m as cumulative_length,
       s.m / a.geom.STLength() * 100.0 as cumulative_percentage
  FROM data as a
       cross apply
       [dbo].[STDumpPoints](a.geom) as s;

-- Results
--
uid segment_length cumulative_length cumulative_percentage
1   NULL           0                 0
2   1.414          1.414             13.2684558397461
3   1              2.414             22.6520879753515
4   1.414          3.828             35.9205438150976
5   1              4.828             45.304175950703
6   1.415          6.243             58.5820154225847
7   3              9.243             86.732911829401
8   1.414          10.657             100.001367669147

SQL Server Spatial – Cumulative Tonnage

Trucks carry goods over shared roads. The total tonnage that is carried over any one road can be computed by recording the individual routes each truck drives (eg create Directions in Google Maps), collect all the routes together (including the tonnages carried for each route), break all the linestring routes into their fundamental segments (a 2 vertex, from-to, linestring) and SUM the tonnage as shown in the following SQL. The SQL uses the [dbo].[STVectorize] function that is shipped with the TSQL function available for download from the Shop on this site.

WITH data as (
select 1 as gid,50 as tonnes, geometry::STGeomFromText('LINESTRING(-1 0, 0 1, 0 2, -1 3, -1 4, 0 5, 0 8, -1 9)',0) as geom
union all
select 2 as gid,200 as tonnes, geometry::STGeomFromText('LINESTRING(0 0, 0 1, 0 2, 0 3, 0 5, 0 8, 0 9)',0) as geom
union all
select 3 as gid,300 as tonnes, geometry::STGeomFromText('LINESTRING(-1 0,0 1, 0 2, 1 3, 1 4, 0 5, 0 7, 1 8)',0) as geom 
)
SELECT dbo.STMakeLineXY(f.sx,f.sy,f.ex,f.ey,0,2).STAsText() as geom,
       sum(f.tonnes) as sum_tonnage,
	   count(*) share_count
  FROM (SELECT a.tonnes,
               v.sx,v.sy,
               v.ex,v.ey
          FROM data as a    
               CROSS APPLY
               [dbo].[STVectorize](a.geom) v	
      ) f
GROUP BY f.sx,f.sy,f.ex,f.ey
ORDER BY f.sx,f.sy;
GO

NOTE: The truck route linestrings have to be defined on the same/common road network as all other routes. If the routes are GPS track logs, substantial spatial processing/editing will need to be carried out on them before running the above SQL.

Visually:

I hope this is of help to someone.