Okk,<div><br></div><div>thanks a lot<br><br><div class="gmail_quote">On Wed, Jan 16, 2013 at 12:18 AM, Nicolas Ribot <span dir="ltr"><<a href="mailto:nicolas.ribot@gmail.com" target="_blank">nicolas.ribot@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">Hi,<div>Yes surely, there must be a simpler solution.</div><div><br></div><div>Here are some explanations:</div>
<div><br></div><div><div style="font-family:arial,sans-serif;font-size:12.727272033691406px">
<font face="courier new, monospace">-- This first query creates the time windows (or range), asking to generate a timestamp</font></div><div style="font-family:arial,sans-serif;font-size:12.727272033691406px"><font face="courier new, monospace">-- each 2 seconds, from the first time in the table, to the latest one.</font></div>
<div style="font-family:arial,sans-serif;font-size:12.727272033691406px"><font face="courier new, monospace">-- This may be replaced by a "group by 2 seconds" range. (I don't know how to express that) </font></div>
<div class="im">
<div style="font-family:arial,sans-serif;font-size:12.727272033691406px"><font face="courier new, monospace">with buckets as (</font></div><div style="font-family:arial,sans-serif;font-size:12.727272033691406px"><font face="courier new, monospace"><span style="white-space:pre-wrap"> </span>select generate_series(min(utc_time), max(utc_time), interval '2') as t</font></div>
<div style="font-family:arial,sans-serif;font-size:12.727272033691406px"><font face="courier new, monospace"><span style="white-space:pre-wrap"> </span>from msg</font></div><div style="font-family:arial,sans-serif;font-size:12.727272033691406px">
<font face="courier new, monospace">) </font></div></div><div style="font-family:arial,sans-serif;font-size:12.727272033691406px"><font face="courier new, monospace">-- This second query uses the first one and matches each record in the msg table </font></div>
<div style="font-family:arial,sans-serif;font-size:12.727272033691406px"><font face="courier new, monospace">-- to a 2 seconds time range. To create a time range, it adds 2 seconds to each time generated</font></div><div style="font-family:arial,sans-serif;font-size:12.727272033691406px">
<font face="courier new, monospace">-- in the first query</font></div><div class="im"><div style="font-family:arial,sans-serif;font-size:12.727272033691406px"><span style="font-family:'courier new',monospace">, ranges as (</span><br>
</div><div style="font-family:arial,sans-serif;font-size:12.727272033691406px"><font face="courier new, monospace"><span style="white-space:pre-wrap"> </span>select <a href="http://m.id/" target="_blank">m.id</a>, m.utc_time, m.lon, m.lat, b.t</font></div>
<div style="font-family:arial,sans-serif;font-size:12.727272033691406px"><font face="courier new, monospace"><span style="white-space:pre-wrap"> </span>from msg m , buckets b</font></div><div style="font-family:arial,sans-serif;font-size:12.727272033691406px">
<font face="courier new, monospace"><span style="white-space:pre-wrap"> </span>where utc_time >= t and utc_time < (t + interval '2')</font></div><div style="font-family:arial,sans-serif;font-size:12.727272033691406px">
<font face="courier new, monospace"><span style="white-space:pre-wrap"> </span>order by utc</font></div><div style="font-family:arial,sans-serif;font-size:12.727272033691406px"><font face="courier new, monospace">) </font></div>
</div><div style="font-family:arial,sans-serif;font-size:12.727272033691406px"><font face="courier new, monospace">-- the final query uses the range subquery and </font><span style="font-family:'courier new',monospace">counts the number of utc_times from msg table </span></div>
<div style="font-family:arial,sans-serif;font-size:12.727272033691406px"><span style="font-family:'courier new',monospace">-- falling in each time range</span></div><div class="im"><div style="font-family:arial,sans-serif;font-size:12.727272033691406px">
<font face="courier new, monospace">select count(utc_time), t</font></div><div style="font-family:arial,sans-serif;font-size:12.727272033691406px"><font face="courier new, monospace">from ranges </font></div><div style="font-family:arial,sans-serif;font-size:12.727272033691406px">
<font face="courier new, monospace">group by t</font></div><div style="font-family:arial,sans-serif;font-size:12.727272033691406px"><font face="courier new, monospace">; </font></div></div></div><div><br></div><div>
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.</div><span class="HOEnZb"><font color="#888888"><div><br></div><div>Nicolas</div></font></span></div><div class="HOEnZb">
<div class="h5"><div class="gmail_extra"><br><br><div class="gmail_quote">
On 15 January 2013 18:28, tasneem dewaswala <span dir="ltr"><<a href="mailto:tasneem.europe@gmail.com" target="_blank">tasneem.europe@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Thanks for you replies,<div><br></div><div>Francois, yes it is a regular time window from beginning to end,</div><div>but i din't understand clearly Nicolas solution, is there any other simpler way to do this.<div><div>
<br><br><div class="gmail_quote">
On Mon, Jan 14, 2013 at 5:02 PM, Francois Hugues <span dir="ltr"><<a href="mailto:hugues.francois@irstea.fr" target="_blank">hugues.francois@irstea.fr</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<u></u>
<div>
<div dir="ltr" align="left"><font color="#0000ff" face="Arial"><span>Hello,</span></font></div>
<div dir="ltr" align="left"><font color="#0000ff" face="Arial"><span></span></font> </div>
<div dir="ltr" align="left"><font color="#0000ff" face="Arial"><span>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
:</span></font></div>
<div dir="ltr" align="left"><font color="#0000ff" face="Arial"><span>1. a regular time from the beginning to the end of
your data as Nicolas solution ?</span></font></div>
<div dir="ltr" align="left"><font color="#0000ff" face="Arial"><span>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 ?</span></font></div>
<div dir="ltr" align="left"><font color="#0000ff" face="Arial"><span></span></font> </div>
<div dir="ltr" align="left"><font color="#0000ff" face="Arial"><span>In the second case, I think you should use a for
loop.</span></font></div>
<div dir="ltr" align="left"><font color="#0000ff" face="Arial"><span></span></font> </div>
<div dir="ltr" align="left"><font color="#0000ff" face="Arial"><span>Hugues.</span></font></div>
<div><font color="#0000ff" face="Arial"></font> </div>
<div align="left"><font face="Arial"></font> </div><br>
<div dir="ltr" lang="fr" align="left">
<hr>
<font face="Tahoma"><b>From:</b> <a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>
[mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>] <b>On Behalf Of </b>Nicolas
Ribot<br><b>Sent:</b> Sunday, January 13, 2013 8:12 PM<br><b>To:</b> PostGIS
Users Discussion<br><b>Cc:</b> PostGIS Users Discussion<br><b>Subject:</b> Re:
[postgis-users] selecting a time window<br></font><br></div><div><div>
<div></div>
<div dir="ltr">Hi,
<div><br></div>
<div>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.</div>
<div>You could then group values by interval, or generate linestrings based on
the grouped values.</div>
<div><br></div>
<div>The following example uses CTE to express intermediate tables:</div>
<div><br></div>
<div>The mgs table has the following structure:</div>
<div><br></div>
<div>
<div> <font face="courier new, monospace">
Table « public.msg »<br></font></div>
<div><font face="courier new, monospace"> Colonne |
Type |
Modificateurs </font></div>
<div><font face="courier new, monospace">----------+--------------------------+---------------</font></div>
<div><font face="courier new, monospace"> id | integer
| non
NULL</font></div>
<div><font face="courier new, monospace"> utc_time | timestamp with time
zone | </font></div>
<div><font face="courier new, monospace"> lat | double
precision | </font></div>
<div><font face="courier new, monospace"> lon | double
precision | </font></div>
<div><font face="courier new, monospace">Index :</font></div>
<div><font face="courier new, monospace"> "msg_pkey" PRIMARY KEY,
btree (id)</font></div>
<div><br></div></div>
<div> 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, </div>
<div> similarly 5,6,7,8 in next 2 sec.</div>
<div><br></div>
<div><font face="courier new, monospace">-- generates time buckets of 2 seconds
in the range of available times </font></div>
<div><font face="courier new, monospace">with buckets as (</font></div>
<div><font face="courier new, monospace"><span style="white-space:pre-wrap"></span>select generate_series(min(utc_time),
max(utc_time), interval '2') as t</font></div>
<div><font face="courier new, monospace"><span style="white-space:pre-wrap"></span>from msg</font></div>
<div><font face="courier new, monospace">) </font></div>
<div><font face="courier new, monospace">-- associates times with
buckets</font></div>
<div><font face="courier new, monospace">, ranges as (</font></div>
<div><font face="courier new, monospace"><span style="white-space:pre-wrap"></span>select <a href="http://m.id" target="_blank">m.id</a>,
m.utc_time, m.lon, m.lat, b.t</font></div>
<div><font face="courier new, monospace"><span style="white-space:pre-wrap"></span>from msg m , buckets b</font></div>
<div><font face="courier new, monospace"><span style="white-space:pre-wrap"></span>where utc_time >= t and utc_time < (t +
interval '2')</font></div>
<div><font face="courier new, monospace"><span style="white-space:pre-wrap"></span>order by utc</font></div>
<div><font face="courier new, monospace">) </font></div>
<div><font face="courier new, monospace">-- counts the number of utc_times in
each time range</font></div>
<div><font face="courier new, monospace">select count(utc_time), t</font></div>
<div><font face="courier new, monospace">from ranges </font></div>
<div><font face="courier new, monospace">group by t</font></div>
<div><font face="courier new, monospace">; </font></div>
<div><br></div>
<div>
<div> 2) draw a linestring using lat/lon values in that 2 sec i.e line from
msg 1 to msg 4:</div>
<div><br></div>
<div>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.</div>
<div><br></div>
<div><font face="courier new, monospace">with buckets as (</font></div>
<div><font face="courier new, monospace"><span style="white-space:pre-wrap"></span>select generate_series(min(utc_time),
max(utc_time), interval '2') as t</font></div>
<div><font face="courier new, monospace"><span style="white-space:pre-wrap"></span>from msg</font></div>
<div><font face="courier new, monospace">) </font></div>
<div><font face="courier new, monospace">-- flags times in the range of 2s from
given times</font></div>
<div><font face="courier new, monospace">, ranges as (</font></div>
<div><font face="courier new, monospace"><span style="white-space:pre-wrap"></span>select <a href="http://m.id" target="_blank">m.id</a>,
m.utc_time, m.lon, m.lat, b.t</font></div>
<div><font face="courier new, monospace"><span style="white-space:pre-wrap"></span>from msg m , buckets b</font></div>
<div><font face="courier new, monospace"><span style="white-space:pre-wrap"></span>where utc_time >= t and utc_time < (t +
interval '2')</font></div>
<div><font face="courier new, monospace"><span style="white-space:pre-wrap"></span>order by utc</font></div>
<div><font face="courier new, monospace">) select
st_makeLine(array_agg(st_makePoint(lon, lat))) as geom, t</font></div>
<div><font face="courier new, monospace">from ranges </font></div>
<div><font face="courier new, monospace">group by t;</font></div>
<div><br></div>
<div>Nicolas</div></div></div>
<div class="gmail_extra"><br><br>
<div class="gmail_quote">On 12 January 2013 15:42, tasneem dewaswala <span dir="ltr"><<a href="mailto:tasneem.europe@gmail.com" target="_blank">tasneem.europe@gmail.com</a>></span> wrote:<br>
<blockquote style="BORDER-LEFT:#ccc 1px solid;MARGIN:0px 0px 0px 0.8ex;PADDING-LEFT:1ex" class="gmail_quote">Hello,
<div><br></div>
<div>I have following data with me</div>
<div><br></div>
<div>Msg UTC_Time
Lat
Lon</div>
<div><br></div>
<div>
<div> 1 133552.25
56.670042 12.862099</div>
<div> 2 133552.75
56.6700403333 12.8621025</div>
<div> 3 133553.25
56.670037 12.862107</div>
<div> 4 133553.5
56.670035 12.8621096667</div>
<div> 5 133554.25
56.6700311667 12.8621146667</div>
<div> 6 133554.75
56.6700303333 12.8621158333</div>
<div> 7 133555.25
56.6700295 12.8621173333</div>
<div> 8 133555.75
56.6700286667 12.8621181667</div></div>
<div><br></div>
<div>I need to do following operations</div>
<div>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, </div>
<div> similarly 5,6,7,8 in next 2 sec.</div>
<div>2) draw a linestring using lat/lon values in that 2 sec i.e line from msg
1 to msg 4.</div>
<div><br></div>
<div>i don't know how can i do this, please give me some suggestions</div>
<div>i am using c# for programming and my postgresql version is</div>
<div>"PostgreSQL 8.4.14, compiled by Visual C++ build 1400, 32-bit"</div>
<div><br></div><br>_______________________________________________<br>postgis-users
mailing list<br><a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br><a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div></div></div></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div></div></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div>