[postgis-users] grouping points by time offset

[Kurt Schwehr]

Hi All,

This is only sort of a postgis question, but I figured I'd try here  
in addition
to the postgres newsgroup.

I am pretty new to SQL and having trouble trying to formulate a query
from what I have been reading in a couple of books (and got a
suggestion from a friend that included cursors and is totally over my
head).  I would really appreciate any help getting this figured out.

I am trying to group track positions from a ship into sections split
by gaps of no recorded position from a particular ship for a period of
time (say 60 minutes).  I have a table "position_reports" something
like this:

key SERIAL PRIMARY KEY, ship_id INTEGER, position GEOMETRY, received
TIMESTAMP, received_sec INTEGER

position is a postgis POINT.  The received_sec is the seconds since
the epoc from the original logs that I have converted to a TIMESTAMP.
Figure this query might be easier with one over the other?

The goal is to be able to generate LINESTRINGs from each ship by time
block that I record the ship.  Right now, I end up doing a MakeLine
for each ship which joins different passes of the ship through an area
(which has ships flying over land quite frequently :)

1  123 someXY 2007-Jan-05 12:01
2  123 someXY 2007-Jan-05 12:04
3  123 someXY 2007-Jan-05 12:13

# more than 60 minutes gap, so split groups here

4  123 someXY 2007-Jan-05 15:23
5  123 someXY 2007-Jan-05 15:46
6  123 someXY 2007-Jan-05 15:53
7  123 someXY 2007-Jan-05 16:01

Thanks,
-Kurt