Query for finding the distance between geometries from consecutive rows in postgreSQL

Question

Using ST_Distance would require two geometrical points , wherein i have a single 'geom' column with 50 geometrical records, for which i need to find the distance between the consecutive rows.

Say i need to find the distance between the first and second row, and then the second and third row and so on.

CREATE TABLE "gdata" ( device_ID text, data_ID bigserial NOT NULL, geom geometry )

[Created table]

[http://i.stack.imgur.com/gi9a3.png ]

I have used the following query to order.

select device_ID, geom from "gdata" ORDER BY device_ID

Therefore i obtain,

[http://i.stack.imgur.com/6RWig.png]

The expected output is a separate column named "distance" wherein, i calculate the distance between the first geometrical point and the second, then the second geometrical value and the third, and so on, for the succesive number of points in "geom".

Thanks in advance :) !

Rows in a relational database are not sorted. So there is no such thing as the "first" or "second" row unless you specify an order by - which requires some column that defines the sort order. Please edit your question and add the complete table definition (as create table) some sample data (as formatted text) and the expected output based on that sample data. Ideally create an example at sqlfiddle.com — a_horse_with_no_name
I have no experience with postgis but when you need a value from a preceding row you can use the window function lag as in lag(geom, 1) over (order by device_ID). — Eelke
Thank you :) I tried that out, but dint know how to integrate with the postGIS function. — Angeline Clement

Tom-db Tom-db · Accepted Answer · 2015-10-28T09:08:15

Use the lag function with 1 as offset:

SELECT * FROM ( 
  SELECT (lag(data_ID,1) OVER (order by data_ID)) AS from_id,     
         data_ID as to_id,         
         ST_Distance(geom, lag(geom,1) OVER (order by data_ID )) AS distance 
  FROM gdata 
) AS foo WHERE distance IS NOT NULL;

Not sure if data_ID is the correct field for the order by. Maybe is device_ID?

Query for finding the distance between geometries from consecutive rows in postgreSQL

1 Answers