[postgis-users] Analyze a timeline of geographical events

[Lorenzo Perone]

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*
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130801/45851837/attachment.html>