[postgis-users] selecting a time window
Nicolas Ribot
nicolas.ribot at gmail.com
Tue Jan 15 10:48:54 PST 2013
Hi,
Yes surely, there must be a simpler solution.
Here are some explanations:
-- This first query creates the time windows (or range), asking to generate
a timestamp
-- each 2 seconds, from the first time in the table, to the latest one.
-- This may be replaced by a "group by 2 seconds" range. (I don't know how
to express that)
with buckets as (
select generate_series(min(utc_time), max(utc_time), interval '2') as t
from msg
)
-- This second query uses the first one and matches each record in the msg
table
-- to a 2 seconds time range. To create a time range, it adds 2 seconds to
each time generated
-- in the first query
, 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
)
-- the final query uses the range subquery and counts the number of
utc_times from msg table
-- falling in each time range
select count(utc_time), t
from ranges
group by t
;
By the way, it should be pretty easy to do it in C#, iterating over records
to see it they fall in a give time range.
Nicolas
On 15 January 2013 18:28, tasneem dewaswala <tasneem.europe at gmail.com>wrote:
> 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
>>
>>
>
> _______________________________________________
> 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/768e3c2b/attachment.html>
More information about the postgis-users
mailing list