<div dir="ltr">Hi,<div><br></div><div style>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 style>You could then group values by interval, or generate linestrings based on the grouped values.</div>
<div style><br></div><div style>The following example uses CTE to express intermediate tables:</div><div style><br></div><div style>The mgs table has the following structure:</div><div style><br></div><div style><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 class="" style="white-space:pre"> </span>select generate_series(min(utc_time), max(utc_time), interval '2') as t</font></div>
<div><font face="courier new, monospace"><span class="" style="white-space:pre"> </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 class="" style="white-space:pre"> </span>select <a href="http://m.id">m.id</a>, m.utc_time, m.lon, m.lat, b.t</font></div>
<div><font face="courier new, monospace"><span class="" style="white-space:pre"> </span>from msg m , buckets b</font></div><div><font face="courier new, monospace"><span class="" style="white-space:pre"> </span>where utc_time >= t and utc_time < (t + interval '2')</font></div>
<div><font face="courier new, monospace"><span class="" style="white-space:pre"> </span>order by utc</font></div><div><font face="courier new, monospace">) </font></div><div style><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 style><font face="courier new, monospace">; </font></div>
<div style><br></div><div style><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 style>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 style><br></div><div><font face="courier new, monospace">with buckets as (</font></div><div><font face="courier new, monospace"><span class="" style="white-space:pre"> </span>select generate_series(min(utc_time), max(utc_time), interval '2') as t</font></div>
<div><font face="courier new, monospace"><span class="" style="white-space:pre"> </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 class="" style="white-space:pre"> </span>select <a href="http://m.id">m.id</a>, m.utc_time, m.lon, m.lat, b.t</font></div>
<div><font face="courier new, monospace"><span class="" style="white-space:pre"> </span>from msg m , buckets b</font></div><div><font face="courier new, monospace"><span class="" style="white-space:pre"> </span>where utc_time >= t and utc_time < (t + interval '2')</font></div>
<div><font face="courier new, monospace"><span class="" style="white-space:pre"> </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 style>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 class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
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">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>