Hello Stefan,
Am Donnerstag 21 Juli 2011, 13:47:46 schrieb Stephan Holl: > Hello Ralf, > > "Ralf Suhr" <[email protected]>, [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 _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
