[postgis-users] selecting a time window

[Nicolas Ribot]

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/20130113/42a4c829/attachment-0001.html>