<html><body><div style="color:#000; background-color:#fff; font-family:times new roman, new york, times, serif;font-size:12pt"><div><span>Hi Lorenzo,</span></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;"><span><br></span></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;"><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 style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;"><span><br></span></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color:
transparent; font-style: normal;"><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 style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;"><span><br></span></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;"><span>Turn your session points into a (multi?)linestring.</span></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;"><br><span></span></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;"><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="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;"><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="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;"><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="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;"><span><span>If not, </span>you have a positive result.</span></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;"><br><span></span></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;"><span>HTH,</span></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;"><br><span></span></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;"><span> Brent
Wood<br></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 size="2" face="Arial"> <b><span style="font-weight:bold;">From:</span></b> Lorenzo Perone <lorenzo.perone@gmail.com><br> <b><span style="font-weight: bold;">To:</span></b> postgis-users@lists.osgeo.org <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 class="y_msg_container"><br><div id="yiv7806479948"><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">Lorenzo Perone<div><div>photoblog: <a rel="nofollow" target="_blank" href="http://lorenzoperone.wordpress.com/">http://lorenzoperone.wordpress.com</a></div>
<div>website: <a rel="nofollow" target="_blank" href="http://blog.spaziogis.it/" style="color:rgb(17,85,204);">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 rel="nofollow" target="_blank" href="http://bit.ly/GEOplus" style="color:rgb(17,85,204);">http://bit.ly/GEOplus</a></u></span></font><br>
</div></div></div>
</div></div><br>_______________________________________________<br>postgis-users mailing list<br><a ymailto="mailto:postgis-users@lists.osgeo.org" 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><br></div> </div> </div> </div></body></html>