[postgis-users] sequential query

[Kevin Neufeld]

I think you can do this with a simple UPDATE statement.

postgis=# select id, asewkt(geom), distance from pts;
  id |    asewkt     | distance
----+---------------+----------
   1 | POINT(0 1 7)  |
   2 | POINT(0 3 7)  |
   3 | POINT(0 6 7)  |
   4 | POINT(0 12 7) |
   5 | POINT(0 55 7) |
(5 rows)


UPDATE pts a
   SET distance = st_length3d(st_makeline(a.geom, b.geom))
   FROM pts b
   WHERE a.id + 1 = b.id;


postgis=# select id, asewkt(geom), distance from pts order by id;
  id |    asewkt     | distance
----+---------------+----------
   1 | POINT(0 1 7)  |        2
   2 | POINT(0 3 7)  |        3
   3 | POINT(0 6 7)  |        6
   4 | POINT(0 12 7) |       43
   5 | POINT(0 55 7) |
(5 rows)

-- Kevin

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