<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=us-ascii" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 8.00.6001.19328"></HEAD>
<BODY>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=080392611-14012013>Hello,</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=080392611-14012013></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=080392611-14012013>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 size=2 face=Arial><SPAN
class=080392611-14012013>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 size=2 face=Arial><SPAN
class=080392611-14012013>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 size=2 face=Arial><SPAN
class=080392611-14012013></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=080392611-14012013>In the second case, I think you should use a for
loop.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=080392611-14012013></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=080392611-14012013>Hugues.</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff size=2 face=Arial></FONT> </DIV>
<DIV align=left><FONT size=2 face=Arial></FONT> </DIV><BR>
<DIV dir=ltr lang=fr class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B> postgis-users-bounces@lists.osgeo.org
[mailto:postgis-users-bounces@lists.osgeo.org] <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 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"></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"></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"></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
style="WHITE-SPACE: pre"></SPAN>from msg m , buckets b</FONT></DIV>
<DIV><FONT face="courier new, monospace"><SPAN
style="WHITE-SPACE: pre"></SPAN>where utc_time >= t and utc_time < (t +
interval '2')</FONT></DIV>
<DIV><FONT face="courier new, monospace"><SPAN
style="WHITE-SPACE: pre"></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"></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"></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"></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
style="WHITE-SPACE: pre"></SPAN>from msg m , buckets b</FONT></DIV>
<DIV><FONT face="courier new, monospace"><SPAN
style="WHITE-SPACE: pre"></SPAN>where utc_time >= t and utc_time < (t +
interval '2')</FONT></DIV>
<DIV><FONT face="courier new, monospace"><SPAN
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>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">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></BODY></HTML>