[postgis-users] grouping points by time offset

[Kurt Schwehr]

Hi Rodrigo,

I am not sure how to dump a view in a way that is useful, so here is  
what makes up the data set and answer for one ship.

select key,userid,cg_sec,cg_timestamp from oneShip;
key |  userid   |   cg_sec   |    cg_timestamp
-----+-----------+------------+---------------------
251 | 366725230 | 1178661410 | 2007-05-08 21:56:50
252 | 366725230 | 1178661421 | 2007-05-08 21:57:01
253 | 366725230 | 1178661561 | 2007-05-08 21:59:21
254 | 366725230 | 1178661710 | 2007-05-08 22:01:50
255 | 366725230 | 1178661900 | 2007-05-08 22:05:00
256 | 366725230 | 1178661940 | 2007-05-08 22:05:40
257 | 366725230 | 1178663311 | 2007-05-08 22:28:31
258 | 366725230 | 1178663360 | 2007-05-08 22:29:20
259 | 366725230 | 1178663441 | 2007-05-08 22:30:41
260 | 366725230 | 1178663540 | 2007-05-08 22:32:20
-- here is the time jump between the two that I need to split on
266 | 366725230 | 1178729461 | 2007-05-09 16:51:01
267 | 366725230 | 1178730221 | 2007-05-09 17:03:41
(12 rows)

Where the results should be this for the separated transits:

userid           | startKey | endKey
366725230 |      251    |    260
366725230 |      266    |    267

I've also put a dump of the db up on the web of "pg_dump --create  
ais> ais_pgdump.sql" in case that might be easier to deal with.

http://vislab-ccom.unh.edu/~schwehr/ais/tmp/

I've written a little python/psycopg2 script, but the full database  
has 4.5M entries and the processing is going to take the whole day  
(which I think implies that I need to tell postgresql that userid  
should be a key).

Thanks much!
-kurt

On Jul 1, 2007, at 9:05 AM, Rodrigo Martín LÓPEZ GREGORIO wrote:

> 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
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users