[postgis-users] sequential query

[Tomas Lanczos]

Hi Kevin,

this is what I needed, thank You very much. Interesting idea to use
aliases this way, I learned something new, thank You for the lesson
also ;)

Tomas

On Mon, 2010-01-04 at 14:35 -0800, Kevin Neufeld wrote:
> 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
>