[postgis-users] grouping points by time offset
schwehr at ccom.unh.edu
Sun Jul 1 05:05:38 PDT 2007
This is only sort of a postgis question, but I figured I'd try here
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
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
More information about the postgis-users