[postgis-users] Accumulated segment length of a linestring

[Ralf Suhr]

Hello Stefan,

use a trigger on table romy_2010 for fast calculating. You need only to add 
the new segment length to the last summary length.

And you can't use st_makeline() on a point table without ordering the points.

Gr
Ralf

Am Donnerstag 21 Juli 2011, 11:47:53 schrieb Stephan Holl:
> Dear PostGIS-users,
> 
> I would like to calculate the accumulated lengths of each
> linestring-segments into a new column, so that every segment holds the
> real length from the beginning of the linestring. The last segment of
> the linestring shows the overall length of the linestring then.
> 
> My data (GPS POINT-data):
> CREATE TABLE romy_2010 (
>     gid integer NOT NULL,
>     id double precision,
>     tag_zeit date,
>     the_geom public.geometry,
>     veroeffentlicht smallint DEFAULT 0,
>     bild text,
>     link text,
>     beschreibung text,
>     CONSTRAINT enforce_dims_the_geom CHECK ((public.ndims(the_geom) =
> 2)), CONSTRAINT enforce_geotype_the_geom CHECK
> (((public.geometrytype(the_geom) = 'POINT'::text) OR (the_geom IS
> NULL))), CONSTRAINT enforce_srid_the_geom CHECK ((public.srid(the_geom)
> = 4326)) );
> 
> My current linestring representing the vertexes and additional
> information:
> CREATE OR REPLACE VIEW v_romy_2010_foo AS
> SELECT
>     'Romy'::text as storkname,
>     n AS gid,
>     n AS id,
>     to_char(s.tag_zeit::timestamp, 'DD.MM.YYYY') AS zeit,
>     s.beschreibung,
>     s.bild,
>     s.link,
>     -- segments
>     st_makeline(st_PointN(m.the_geom,n), st_PointN(m.the_geom, n+1)) AS
> the_geom,
>     round(st_length_spheroid(st_makeline(st_PointN(m.the_geom,n),
>       st_PointN(m.the_geom, n+1)), 'SPHEROID["WGS
> 84",6378137,298.257223563]')/1000) AS length_sphero_inKM
>  FROM
>   (SELECT
>     st_makeline(the_geom) AS the_geom
>    FROM
>     (SELECT
>         gid,
>         beschreibung,
>         the_geom
>      FROM romy_2010 WHERE gid >= 1 AND veroeffentlicht = 1 ORDER
> BY gid ) AS t
>   ) AS m
>  CROSS JOIN generate_series(1,1000) AS n
>  LEFT JOIN romy_2010 s ON s.id = n
>  WHERE n < st_npoints(m.the_geom);
> 
> 
> I appreciate any pointers how to solve this.
> 
> TIA
> 
> Best regards
> 
> 	Stephan