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 _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
