Hi Tomas,
You need to use aggregates to combine the points into sets using SQL.
Here is what I can do with PostgreSQL 8.3 / PostGIS 1.3:
CREATE TEMP TABLE pts(id integer PRIMARY KEY, geom geometry);
INSERT INTO pts(id, geom)
VALUES
(1, ST_MakePoint(0, 0, 0)),
(2, ST_MakePoint(2, 0, 1)),
(3, ST_MakePoint(3, 2, 2));
-- Sum of the length of the entire linestring (more than two points)
SELECT ST_Length3D(ST_LineFromMultiPoint(geom2))
FROM (SELECT ST_Collect(geom) AS geom2 FROM pts) AS foo;
-- Sum of the groups of two points, which I simply define as 1 id off
from each other (i.e., p1.id=p2.id-1)
SELECT ST_AsEWKT(geom), ST_Length3D(ST_LineFromMultiPoint(geom))
FROM (
SELECT ST_Collect(p1.geom, p2.geom) AS geom
FROM pts p1, pts p2
WHERE p1.id=p2.id-1) AS foo;
You might need to filter the table for the correct IDs and sort the
points in a subquery before the ST_Collect aggregate functions; this
depends on your data organization (how id=1 relates to id=2, etc.).
Also, I'm using older software .. I'd bet there are more elegant
solutions using the nicer aggregate functions in PostGIS 1.4 and window
functions in PostgreSQL 8.4, but I'm still depending on older
technologies, so the new is a bit oblivious to me.
-Mike
On 2010-01-03 16:26, Tomas Lanczos wrote:
Dear all,
I am not sure whether am I using correct terminology in the subject, so
I will try to explain my problem:
I am trying to write a spatial query to measure a a 3D distance between
two neighboring points. The basic query I prepared is the following:
"UPDATE particle_set_3dps set distance = (SELECT
ST_length3d(ST_MakeLine((SELECT particle_set_3dps.the_geom from
particle_set_3dps where id = 1), (SELECT particle_set_3dps.the_geom
from particle_set_3dps WHERE id = 2)))) where id = 1; "
What I need further is the distance between points with id = 2 and id =
3, between id=3 and id=4, between id=4 and id=5 etc. ...
I assume that it is not so complicated to write a query for that, but I
never needed it until now and I don't know what therms to use as
keywords to find an answer somewhere, also due to my poor English (shame
on me, I know ... ;)).
Best regards and many thanks in advance
Tomas
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
