[postgis-users] grouping points by time offset

[Rodrigo Martín LÓPEZ GREGORIO]

Hi Kurt, can you send me some example data that I can use to test the
function?

Rodrigo.

On 7/1/07, Kurt Schwehr <schwehr at ccom.unh.edu> wrote:
>
> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070701/b0843dc5/attachment.html>