SQL query for accumulated sum using window function in postgresql

Question

I've set up a pretty simple table, representing points in a 2D environment. The Id column is the id of each point and geom column is a binary representation of the point into the space:

Table public.foo

 Column |         Type         |                 Modifiers                  
--------+----------------------+--------------------------------------------
 id     | integer              | not null default nextval('mseq'::regclass)
 geom   | geometry(Point,2100) |

Indexes:

    "foo_pkey" PRIMARY KEY, btree (id)
    "foo_index_gist_geom" gist (geom)

To find the distance from each point to the next I am using this window function :

select 
     id,  
     st_distance(geom,lag(geom,1) over (order by id asc))  distance 
from 
     foo;

which results the following ( st_distance(geom,geom) gives the distance between two geom data type):

 id |     distance     
----+------------------
  1 |                 
  2 | 27746.1563439608
  3 | 57361.8216245281
  4 | 34563.3607734946
  5 | 23421.2022073633
  6 | 41367.8247514439
....

distance(1) ->  null since its the first point
distance(2) ->  ~28km from point 1 to point 2
distance(3) ->  ~57km from point 2 to point 3 
and etc..

My objective is to find the accumulative distance from each point to the next from the start for each node. eg like this mock table below:

 id |     distance     | acc 
----+------------------+-----
  1 |                  |   
  2 | 27746.1563439608 |   27746.1563439608
  3 | 57361.8216245281 |   85107.97797
  4 | 34563.3607734946 |   119671.33874

where acc(1) is null because it is the first node, 
acc(2) = acc(1) + dist(2)
acc(3) = acc(2) + dist(3)

and etc..

I tried combining the sum and lag functions but postgresql says that windows functions cannot be nested. I'm completely baffled on how to proceed. Anyone who can help me ?

Erwin Brandstetter · Accepted Answer · 2013-07-24 16:00:49Z

4

Since you cannot have a window function over another window function ("cannot be nested"), you need to add a subquery layer (or a CTE):

SELECT id, sum(distance) OVER (ORDER BY id) AS cum_dist
FROM  (
   SELECT id, st_distance(geom, lag(geom, 1) OVER (ORDER BY id)) AS distance 
   FROM   foo
   ) sub
ORDER  BY id;

This assumes that id is unique - which is guaranteed by your primary key.

edited Jul 24, 2013 at 16:00

answered Jul 24, 2013 at 15:55

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL query for accumulated sum using window function in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related