[postgis-users] Accumulated segment length of a linestring

[Ralf Suhr]

Hello Stefan,


Am Donnerstag 21 Juli 2011, 13:47:46 schrieb 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.

Yes, the point-table can have a new column "segment_length" wich will be 
filled by a trigger. The trigger need to know the length from the last segment 
and the distance between the last and the new point. The sum will be stored in 
the new column.

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

indeed st_makeline() is order by gid.

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