[postgis-users] Accumulated segment length of a linestring

[Stephan Holl]

Hello Ralf,

"Ralf Suhr" <Ralf.Suhr at itc-halle.de>, [20110721 - 13:43:59]

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

the table is a point-table, but maybe I do not get your point.
 
> And you can't use st_makeline() on a point table without ordering the
> points.

I do not want. They are ordered by gid (which is a serial though, see
below).

Best
	Stephan

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



-- 
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/a9fa925d/attachment.pgp>