Finding path length along points in postgis

Question

I have a postgis database (postgres 8.4.11, postgis 1.5.2) with gps-observations where I would like to calculate some travel distances.

I calculate the travelled distance from one point to another one hour later with select observedid,ST_Distance(p1.shape_utm,p2.shape_utm) from gpspoint p1, gpspoint p2 where p2.datetime=p1.datetime+interval '1 hour'

(and of course a bit more, but this is the distance part in it) I have defined this as a view so that I can easily do a select * from movement_view where observedid=42

Question 1: Is it possible to make the view so that I can define the time interval as well?

e.g. select * form movement_view where observedid=42 and interval='15 minutes' ?

Question 2: Is it possible in SQL/postgis to calculate the total distance travelled for a period of time? I have a gpspoint each 5th second and would like to calculate travelled distance for e.g. 1 hour following each gps-point. I can calculate the travelled distance for each point using the same method as above, and then I may be able to use the sum() function, but how?

Table definition: id integer,
gpsstatus character(2),
datetime timestamp without time zone,
lat numeric(9,6),
lon numeric(9,6),
alt numeric(9,4),
time integer,
datafileid integer,
shape geometry,
speed double precision,
dist double precision,
shape_utm geometry,
lokalitet character(128),
cowid integer

Id: Primary key There are indexes on datetime,lokalitet,cowid,gpsstatus, gist-index on shape and shape_utm.

Sorry, slightly wron title, a better one would have been "finding length of path along points in postgis" — MortenSickel
bah, tried to remove the comment after I discovered that I could edit the title, but too late... — MortenSickel
answer is yes to both, it's relatively easy to create a subquery that has key, distance between two points for each set of points and then sum those results. I added a postgresql tag as your answer is more sql then it is geo...more views this way. If you give table schema info, you'll probably get a working answer. — Twelfth
Thanks @Twelfth. You're probably right about the postgres-tag. I was thinking postgis due to the distance, but in reallity, it's just a "normal" summing (as soon as I have done the st_distance part) — MortenSickel

Thomas Thomas · Accepted Answer · 2012-05-23T09:59:24

Question 1:

CREATE VIEW movement_view AS
SELECT observedid, ST_Distance(p1.shape_utm,p2.shape_utm) as distance, p1.datetime as datetime1, p2.datetime as datetime2
FROM gpspoint p1, gpspoint p2;

SELECT * from movement_view where observedid = 42 and datetime2 = datetime1 + interval '15 minutes'

Question 2:

Create a line from your points and calculate the length:

SELECT hours,  st_Length(ST_MakeLine(t.shape_utm)) as distance FROM
    (SELECT date_trunc('hour', datetime::timestamp) as hours, shape_utm FROM gpspoint order by datetime) as t
GROUP by hours

Finding path length along points in postgis

1 Answers