<div dir="ltr"><br><div class="gmail_extra"><div class="gmail_quote">2013/8/1 Brent Wood <span dir="ltr"><<a href="mailto:pcreso@pcreso.com" target="_blank">pcreso@pcreso.com</a>></span><br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
<div><div style="font-size:12pt;font-family:'times new roman','new york',times,serif"><div><span>Hi Lorenzo,</span></div><div style="font-style:normal;font-size:16px;background-color:transparent;font-family:'times new roman','new york',times,serif">
<span><br></span></div><div style="font-style:normal;font-size:16px;background-color:transparent;font-family:'times new roman','new york',times,serif"><span>It is not clear what the geometry type of a waypoint_sessione is. I'm guessing it is a point, with each point joined by sessione_id to a sessione table.<br>
</span></div></div></div></blockquote><div style><br></div><div style>It's right :)</div><div><br></div><div> </div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
<div><div style="font-size:12pt;font-family:'times new roman','new york',times,serif"><div style="font-style:normal;font-size:16px;background-color:transparent;font-family:'times new roman','new york',times,serif">
<span></span></div><div style="font-style:normal;font-size:16px;background-color:transparent;font-family:'times new roman','new york',times,serif"><span><br></span></div><div style="font-style:normal;font-size:16px;background-color:transparent;font-family:'times new roman','new york',times,serif">
<span>The polyline approach you mention should work for you. It is not that difficult to implement as a script, might be trickier as a Postgres UDF.<br></span></div></div></div></blockquote><div><br></div><div style>I don't know Postgres UDF, I'll take a look at it.</div>
<div> </div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><div><div style="font-size:12pt;font-family:'times new roman','new york',times,serif">
<div style="font-style:normal;font-size:16px;background-color:transparent;font-family:'times new roman','new york',times,serif"><span></span></div><div style="font-style:normal;font-size:16px;background-color:transparent;font-family:'times new roman','new york',times,serif">
<span><br></span></div><div style="font-style:normal;font-size:16px;background-color:transparent;font-family:'times new roman','new york',times,serif"><span>Turn your session points into a (multi?)linestring.</span></div>
</div></div></blockquote><div style>I've put them in LINESTRING, after the clip (I'm trying to resolve the problem into several steps) I've put the in a MULTILINESTRING geometry.</div><div><br></div><div> </div>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><div><div style="font-size:12pt;font-family:'times new roman','new york',times,serif">
<div style="font-style:normal;font-size:16px;background-color:transparent;font-family:'times new roman','new york',times,serif"><span></span></div><div style="font-style:normal;font-size:16px;background-color:transparent;font-family:'times new roman','new york',times,serif">
<span>Clip the linestring by the buffer
around the point. This will give a linestring for each seperate period the session track came within the buffer distance of the point. <br></span></div><div style="font-style:normal;font-size:16px;background-color:transparent;font-family:'times new roman','new york',times,serif">
<span>For each returned linestring, select the points that match your requirements (<7km/hr) that intersect the linestring (or perhaps a very small buffer of it - point/line intersections can be tricky - but this case should be OK), and from these, select the min/max times. </span><span><br>
</span></div><div style="font-style:normal;font-size:16px;background-color:transparent;font-family:'times new roman','new york',times,serif"><span>If these are more than 10 mins apart, c</span><span><span>heck that there are no points within this interval with speeds of > 7km (if so, then this sequence fails to meet your criteria),
<br></span></span></div><div style="font-style:normal;font-size:16px;background-color:transparent;font-family:'times new roman','new york',times,serif"><span><span>If not, </span>you have a positive result.</span></div>
</div></div></blockquote><div><br></div><div style>Great, I was not able to find the solution of this step, I'll try.</div><div style> <br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
<div><div style="font-size:12pt;font-family:'times new roman','new york',times,serif"><div style="font-style:normal;font-size:16px;background-color:transparent;font-family:'times new roman','new york',times,serif">
<br><span></span></div><div style="font-style:normal;font-size:16px;background-color:transparent;font-family:'times new roman','new york',times,serif"><span>HTH,</span></div><div style="font-style:normal;font-size:16px;background-color:transparent;font-family:'times new roman','new york',times,serif">
<br><span></span></div><div style="font-style:normal;font-size:16px;background-color:transparent;font-family:'times new roman','new york',times,serif"><span> Brent
Wood<br></span></div></div></div></blockquote><div><br></div><div style>Thanks a lot Brent.</div><div style>l.</div><div style><br></div><div style><br></div><div style><br clear="all"><div><div dir="ltr">Lorenzo Perone<div>
<div>photoblog: <a href="http://lorenzoperone.wordpress.com/" target="_blank">http://lorenzoperone.wordpress.com</a></div><div>website: <a href="http://blog.spaziogis.it/" target="_blank">http://blog.spaziogis.it</a><br>
</div>
GEO+ <font color="#000000"><span style="line-height:18px">geomatica in Italia </span></font><font color="#1155cc"><span style="line-height:18px"><u><a href="http://bit.ly/GEOplus" target="_blank">http://bit.ly/GEOplus</a></u></span></font><br>
</div></div></div><br></div><div><br></div><div> </div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><div>
<div style="font-size:12pt;font-family:'times new roman','new york',times,serif"><div style="font-style:normal;font-size:16px;background-color:transparent;font-family:'times new roman','new york',times,serif">
<span></span></div><div><br></div> <div style="font-family:'times new roman','new york',times,serif;font-size:12pt"> <div style="font-family:'times new roman','new york',times,serif;font-size:12pt">
<div dir="ltr"> <hr size="1"> <font face="Arial"> <b><span style="font-weight:bold">From:</span></b> Lorenzo Perone <<a href="mailto:lorenzo.perone@gmail.com" target="_blank">lorenzo.perone@gmail.com</a>><br> <b><span style="font-weight:bold">To:</span></b> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a> <br>
<b><span style="font-weight:bold">Sent:</span></b> Friday, August 2, 2013 1:09 AM<br> <b><span style="font-weight:bold">Subject:</span></b> [postgis-users] Analyze a timeline of geographical events<br> </font> </div> <div>
<div><div class="h5"><br><div><div dir="ltr"><div>Hi,</div><div>I'm trying to resolve a not easy (for me) problem.</div><div>We are developing a GPS tracking system based on android phones.</div><div>The waypoints transmitted by the devices are stored in a
Postgis table called "waypoint_sessione".</div>
<div>The table has this structure.</div><div><br></div><div>TABLE waypoint_sessione</div><div> gid bigserial </div><div> sessione_id bigint number of the session opened by the device (depends by user, truck, device)</div>
<div> time_dataora bigint (unix timestamp)</div><div> elevazione double precision</div><div> direzione double precision</div><div> velocita double precision (speed)</div><div> pdop double precision</div><div> precisione integer</div>
<div> the_geom geometry</div><div> </div><div>My scope is to discover for each session if a user have spent a lot of time, more than 10 minutes, stopped whitin a 150 m buffer from known point.</div><div>When a device is stopped his speed is not zero, so I need to use a speed threshold, tipically 7 Km/h.</div>
<div><br></div><div>I've thought to proceed in this way:</div><div>- Create a buffer form the known point</div><div>- Select the waypoints in the buffer grouping them by sessione_id</div><div><br></div><div>Here is the first doubt.</div>
<div>If the user, it's easy, pass through the buffer more than one time for each session I've to group the waypoints non only by session_id but also for each passage.</div><div>I can't use the gid column because in waypoint_sessione are stored datas from al large number of devices that are transmitting simultaneous. </div>
<div>I could create a polyline for each session and then trim it by the buffer and select the waypoints that are the same of the node of the polyline for each part.</div><div>Is there a simpler way?</div><div><br></div><div>
The step forward is, for each group of waypoints, to discover if I have a consecutive period of more than 10 minutes during wich the speed is below the threshold of 7 Km/h.</div><div>Discover these periods is my goal.</div>
<div><br></div><div>Thanks.</div><div>lorenzo</div><div><br></div><div><div dir="ltr"><br></div></div></div></div></div></div><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><br></div> </div> </div> </div></div></blockquote></div><br>
</div></div>