[postgis-users] selecting a time window

[Francois Hugues]

Hello,
 
I was answering in a similar way to build lines from your data, but I
wondered about the time window strategy and its origin. Is it :
1. a regular time from the beginning to the end of your data as Nicolas
solution ?
2. a time window for each message : one second before and one second
after message of interest or two second before message of interest or
two second after message of interest ?
 
In the second case, I think you should use a for loop.
 
Hugues.
 
 

________________________________

From: postgis-users-bounces at lists.osgeo.org
[mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Nicolas
Ribot
Sent: Sunday, January 13, 2013 8:12 PM
To: PostGIS Users Discussion
Cc: PostGIS Users Discussion
Subject: Re: [postgis-users] selecting a time window


Hi, 

One approach could be to generate 2 seconds intervals from the start
time to the end time and then find records that fall in these intervals.
You could then group values by interval, or generate linestrings based
on the grouped values.

The following example uses CTE to express intermediate tables:

The mgs table has the following structure:

                Table < public.msg >

 Colonne  |           Type           | Modificateurs 
----------+--------------------------+---------------
 id       | integer                  | non NULL
 utc_time | timestamp with time zone | 
 lat      | double precision         | 
 lon      | double precision         | 
Index :
    "msg_pkey" PRIMARY KEY, btree (id)

 1) count the total number of messages within a 2 sec time window, for
e.g. msg 1,2,3,4 falls under 2 sec time window, 
     similarly 5,6,7,8 in next 2 sec.

-- generates time buckets of 2 seconds in the range of available times 
with buckets as (
select generate_series(min(utc_time), max(utc_time), interval '2') as t
from msg
) 
-- associates times with buckets
, ranges as (
select m.id, m.utc_time, m.lon, m.lat, b.t
from msg m , buckets b
where utc_time >= t and utc_time < (t + interval '2')
order by utc
) 
-- counts the number of utc_times in each time range
select count(utc_time), t
from ranges 
group by t
; 

 2) draw a linestring using lat/lon values in that 2 sec i.e line from
msg 1 to msg 4:

Same query as above, except this time points are built from coordinates,
then collected into an array and finally passed to st_makeLine to
generate geometries.

with buckets as (
select generate_series(min(utc_time), max(utc_time), interval '2') as t
from msg
) 
-- flags times in the range of 2s from given times
, ranges as (
select m.id, m.utc_time, m.lon, m.lat, b.t
from msg m , buckets b
where utc_time >= t and utc_time < (t + interval '2')
order by utc
) select st_makeLine(array_agg(st_makePoint(lon, lat))) as geom, t
from ranges 
group by t;

Nicolas


On 12 January 2013 15:42, tasneem dewaswala <tasneem.europe at gmail.com>
wrote:


	Hello, 

	I have following data with me

	Msg         UTC_Time          Lat                      Lon

	  1            133552.25      56.670042           12.862099
	  2            133552.75      56.6700403333    12.8621025
	  3            133553.25      56.670037           12.862107
	  4            133553.5        56.670035           12.8621096667
	  5            133554.25      56.6700311667    12.8621146667
	  6            133554.75      56.6700303333    12.8621158333
	  7            133555.25      56.6700295         12.8621173333
	  8            133555.75      56.6700286667    12.8621181667

	I need to do following operations
	1) count the total number of messages within a 2 sec time
window, for e.g. msg 1,2,3,4 falls under 2 sec time window, 
	    similarly 5,6,7,8 in next 2 sec.
	2) draw a linestring using lat/lon values in that 2 sec i.e line
from msg 1 to msg 4.

	i don't know how can i do this, please give me some suggestions
	i am using c# for programming and my postgresql version is
	"PostgreSQL 8.4.14, compiled by Visual C++ build 1400, 32-bit"


	_______________________________________________
	postgis-users mailing list
	postgis-users at lists.osgeo.org
	http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
	
	


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130114/618164e7/attachment.html>