[postgis-users] Accumulated segment length of a linestring

[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

-- 
Stephan Holl <stephan.holl at intevation.de> | Tel.: +49 (0)541-33 508 3663
Intevation GmbH, Neuer Graben 17, 49074 OS  |  AG Osnabrück - HR B 18998
Geschäftsführer:  Frank Koormann, Bernhard Reiter, Dr. Jan-Oliver Wagner
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 198 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110721/f7ba6121/attachment.pgp>