[postgis-users] sequential query

[Mike Toews]

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
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>