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 < [EMAIL PROTECTED]> 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
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
