[postgis-users] selecting a time window
tasneem dewaswala
tasneem.europe at gmail.com
Thu Jan 17 05:31:43 PST 2013
Okk,
thanks a lot
On Wed, Jan 16, 2013 at 12:18 AM, Nicolas Ribot <nicolas.ribot at gmail.com>wrote:
> 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
>>
>>
>
> _______________________________________________
> 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/20130117/37081751/attachment.html>
More information about the postgis-users
mailing list