[postgis-users] selecting a time window

[tasneem dewaswala]

Thanks for you replies,

Francois, yes it is a regular time window from beginning to end,
but i din't understand clearly Nicolas solution, is there any other simpler
way to do this.

On Mon, Jan 14, 2013 at 5:02 PM, Francois Hugues
<hugues.francois at irstea.fr>wrote:

> **
> 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
>>
>>
>
> _______________________________________________
> 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/20130115/8f6ca0c9/attachment.html>