[postgis-users] Analyze a timeline of geographical events

[Brent Wood]

Hi Lorenzo,

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.


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.


Turn your session points into a (multi?)linestring.

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. 

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.  

If these are more than 10 mins apart, check that there are no points within this interval with speeds of > 7km (if so, then this sequence fails to meet your criteria), 

If not, you have a positive result.

HTH,

  Brent Wood



________________________________
 From: Lorenzo Perone <lorenzo.perone at gmail.com>
To: postgis-users at lists.osgeo.org 
Sent: Friday, August 2, 2013 1:09 AM
Subject: [postgis-users] Analyze a timeline of geographical events
 


Hi,
I'm trying to resolve a not easy (for me) problem.
We are developing a GPS tracking system based on android phones.
The waypoints transmitted by the devices are stored in a Postgis table called "waypoint_sessione".
The table has this structure.

TABLE waypoint_sessione
 gid bigserial 
 sessione_id bigint number of the session opened by the device (depends by user, truck, device)
 time_dataora bigint (unix timestamp)
 elevazione double precision
 direzione double precision
 velocita double precision (speed)
 pdop double precision
 precisione integer
 the_geom geometry
 
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.
When a device is stopped his speed is not zero, so I need to use a speed threshold, tipically 7 Km/h.

I've thought to proceed in this way:
- Create a buffer form the known point
- Select the waypoints in the buffer grouping them by sessione_id

Here is the first doubt.
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.
I can't use the gid column because in waypoint_sessione are stored datas from al large number of devices that are transmitting simultaneous. 
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.
Is there a simpler way?

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.
Discover these periods is my goal.

Thanks.
lorenzo

Lorenzo Perone
photoblog: http://lorenzoperone.wordpress.com
website: http://blog.spaziogis.it
GEO+ geomatica in Italia http://bit.ly/GEOplus

_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130801/18633ba8/attachment.html>