cfgReadChar('gpsdatabase.host'); $gps_db_port = $savvyext->cfgReadLong('gpsdatabase.port'); $gps_db_name = $savvyext->cfgReadChar('gpsdatabase.name'); $gps_db_user = $savvyext->cfgReadChar('gpsdatabase.user'); $gps_db_pass = $savvyext->cfgReadChar('gpsdatabase.pass'); $conn_string_gps = "host=${gps_db_host} port=${gps_db_port} dbname=${gps_db_name} user=${gps_db_user} password=${gps_db_pass}"; $pgconn_gps = pg_connect($conn_string_gps); $res = []; $min_lat = PHP_INT_MAX; $min_lng = PHP_INT_MAX; $max_lat = PHP_INT_MIN; $max_lng = PHP_INT_MIN; $center_lat = 0; $center_lng = 0; //$q = "select * from members_tracking where member_id=13 and id>1961766 order by created"; //$q = "select *,extract(EPOCH from duration) AS seconds from members_tracking_duration where member_id=13 order by first_time"; $q = "select a.*,extract(milliseconds from a.duration) as ms, extract(epoch from a.duration) as seconds, c.speed, "; $q.= "ST_Distance(c.gps,b.gps) AS distance, a.inner_duration, a.total, "; $q.= "EXTRACT(epoch FROM c.ttime-b.ttime) AS travel_time "; $q.= "from members_tracking_duration a "; $q.= "left join members_tracking b on (b.id=a.first_id) "; $q.= "left join members_tracking c on (c.id=a.last_id) "; $q.= "where a.member_id=13 "; //and a.first_id>1961278 and a.first_id<1961766 order by a.first_time"; $q.= " and a.id>215643 and a.id<215841 order by a.first_time"; //echo $q; $trip = []; $trips = []; $trip_data = ['distance'=>0,'duration'=>0,'total'=>0,'speed'=>0]; $trips_data = []; $r = pg_query($pgconn_gps, $q); while ($f=pg_fetch_assoc($r)) { $lat = (int)$f["lat"]; $lng = (int)$f["lng"]; if ($lat!=48 && $lat!=49) continue; $res[] = $f; if ($f["lat"]<$min_lat) $min_lat = $f["lat"]; if ($f["lng"]<$min_lng) $min_lng = $f["lng"]; if ($f["lat"]>$max_lat) $max_lat = $f["lat"]; if ($f["lng"]>$max_lng) $max_lng = $f["lng"]; if ($f["inner_duration"]>=120) { // && $f["distance"]<=20) { $trips[] = $trip; $trips_data[] = $trip_data; $trip = []; $trip_data = ['distance'=>0,'duration'=>0,'total'=>0,'speed'=>0]; } else { // We try to skip the stop entry... $trip[] = $f; $trip_data['distance'] += $f["distance"]; $trip_data['duration'] += $f["ms"]; $trip_data['speed'] += $f["ms"]>0 ? (3600 * $f["distance"] / $f["ms"]) : 0; // km/h $trip_data['total'] += 1; } } $trips[] = $trip; $trips_data[] = $trip_data; $center_lat = ($min_lat+$max_lat)/2; $center_lng = ($min_lng+$max_lng)/2; pg_close($pgconn_gps); ?> GPS slicing test
*speed - is the current speed at second point
$trip) { $trip_data = $trips_data[$i]; $n = count($trip)-1; ?>

Trip #

ID GPS Duration Seconds Speed* Syn.speed Distance Time
, 0?sprintf("%0.02f",$f["distance"]/$f["travel_time"]):0?>



$trip) { $trip_data = $trips_data[$i]; $n = count($trip)-1; ?>

Trip #

20) return "Driving"; return "5to20"; }