dev-chiefworks
329 lines
11 KiB
PHP
329 lines
11 KiB
PHP
<?php
|
|
require '../backend.php';
|
|
|
|
//PostgreSQL
|
|
$gps_db_host = $savvyext->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);
|
|
|
|
?>
|
|
<!DOCTYPE html>
|
|
<html>
|
|
<head>
|
|
<meta charset="utf-8">
|
|
<title>GPS slicing test</title>
|
|
<style>
|
|
/* Always set the map height explicitly to define the size of the div
|
|
* element that contains the map. */
|
|
#map {
|
|
height: 625px;
|
|
}
|
|
/* Optional: Makes the sample page fill the window. */
|
|
html, body {
|
|
height: 100%;
|
|
margin: 0;
|
|
padding: 0;
|
|
}
|
|
.container div {
|
|
display: inline-block;
|
|
vertical-align: top; /* here */
|
|
}
|
|
.grey {
|
|
background-color: rgba(128,128,128,.25);
|
|
text-align: right!important;
|
|
}
|
|
.white {
|
|
background-color: rgba(255,255,255,.25);
|
|
}
|
|
</style>
|
|
<script type='text/javascript' src='https://cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js'></script>
|
|
<script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.5.0/Chart.min.js"></script>
|
|
</head>
|
|
<body>
|
|
<hr size=-1>
|
|
<div id="map"></div>
|
|
<hr size=-1>
|
|
<div class="container">
|
|
<div class = "one">
|
|
<sup>*</sup><b>speed</b> - is the current speed at second point<br/>
|
|
<? foreach ($trips as $i=>$trip) { $trip_data = $trips_data[$i]; $n = count($trip)-1; ?>
|
|
<h1>Trip #<?=$i?></h1>
|
|
<ul>
|
|
<li><b>mode:</b> <?= checkMode($trip_data['distance'], $trip_data['duration']) ?></li>
|
|
<li><b>distance:</b> <?=$trip_data['distance'] ?></li>
|
|
<li><b>duration:</b> <?=$trip_data['duration']/1000 ?></li>
|
|
<li><b>speed:</b> <?=3600*$trip_data['distance']/$trip_data['duration']?></li>
|
|
<li><b>avg. speed:</b> <?=$trip_data['speed']/$trip_data['total']?></li>
|
|
<li><b>lov. from:</b> <?= $trip[0]['lat'].",".$trip[0]['lng']?></li>
|
|
<li><b>loc. to:</b> <?= $trip[$n]['lat'].",".$trip[$n]['lng']?></li>
|
|
<li><b>from time:</b> <?= $trip[0]['first_time']?></li>
|
|
<li><b>to time:</b> <?= $trip[$n]['first_time']?></li>
|
|
</ul>
|
|
<table>
|
|
<colgroup>
|
|
<col class="grey" />
|
|
<col class="white" />
|
|
<col class="grey" />
|
|
<col class="white" />
|
|
<col class="grey" />
|
|
<col class="white" />
|
|
<col class="grey" />
|
|
<col class="white" />
|
|
</colgroup>
|
|
<thead>
|
|
<tr>
|
|
<th>ID</th>
|
|
<th>GPS</th>
|
|
<th>Duration</th>
|
|
<th>Seconds</th>
|
|
<th>Speed<sup>*</sup></th>
|
|
<th>Syn.speed</th>
|
|
<th>Distance</th>
|
|
<th>Time</th>
|
|
</tr>
|
|
</thead>
|
|
<tbody>
|
|
<? foreach ($trip as $f) { ?>
|
|
<tr>
|
|
<td><?=$f["id"]?></td>
|
|
<td><?=sprintf("%0.03f",$f["lat"])?>,<?=sprintf("%0.03f",$f["lng"])?></td>
|
|
<td><?=strtok($f["ms"],'.')?></td>
|
|
<td><?=strtok($f["seconds"],'.')?></td>
|
|
<td><?=sprintf("%0.02f",$f["speed"])?></td>
|
|
<td><?=$f["travel_time"]>0?sprintf("%0.02f",$f["distance"]/$f["travel_time"]):0?></td>
|
|
<td><?=sprintf("%0.02f",$f["distance"])?></td>
|
|
<td><?=strtok($f["first_time"],'.')?></td>
|
|
</tr>
|
|
<? } ?>
|
|
</table>
|
|
<? } ?>
|
|
</div>
|
|
<div class = "two">
|
|
<canvas id="line-chart" width="800" height="450"></canvas>
|
|
<br/>
|
|
<canvas id="line-chart2" width="800" height="450"></canvas>
|
|
<br/>
|
|
<canvas id="line-chart3" width="800" height="450"></canvas>
|
|
<br/>
|
|
<? foreach ($trips as $i=>$trip) { $trip_data = $trips_data[$i]; $n = count($trip)-1; ?>
|
|
<h1>Trip #<?=$i?></h1>
|
|
<ul>
|
|
<li><b>mode:</b> <?= checkMode($trip_data['distance'], $trip_data['duration']) ?></li>
|
|
<li><b>distance:</b> <?=$trip_data['distance'] ?></li>
|
|
<li><b>duration:</b> <?=$trip_data['duration']/1000 ?></li>
|
|
<li><b>speed:</b> <?=3600*$trip_data['distance']/$trip_data['duration']?></li>
|
|
<li><b>avg. speed:</b> <?=$trip_data['speed']/$trip_data['total']?></li>
|
|
<li><b>lov. from:</b> <?= $trip[0]['lat'].",".$trip[0]['lng']?></li>
|
|
<li><b>loc. to:</b> <?= $trip[$n]['lat'].",".$trip[$n]['lng']?></li>
|
|
<li><b>from time:</b> <?= $trip[0]['first_time']?></li>
|
|
<li><b>to time:</b> <?= $trip[$n]['first_time']?></li>
|
|
</ul>
|
|
<table>
|
|
<? foreach ($trip as $f) { ?>
|
|
<!-- tr>
|
|
<td></td>
|
|
<td></td>
|
|
</tr -->
|
|
<? } ?>
|
|
</table>
|
|
<? } ?>
|
|
</div>
|
|
</div>
|
|
<script>
|
|
var map;
|
|
function initMap() {
|
|
map = new google.maps.Map(document.getElementById('map'), {
|
|
zoom: 13,
|
|
center: {lat: <?=$center_lat?>, lng: <?=$center_lng?>},
|
|
mapTypeId: 'roadmap'
|
|
});
|
|
var flightPlanCoordinates = [
|
|
<? for ($i=0; $i<count($res)-1;$i++) {
|
|
echo " {lat: ".$res[$i]["lat"].", lng: ".$res[$i]["lng"]."},\n";
|
|
} echo " {lat: ".$res[count($res)-1]["lat"].", lng: ".$res[count($res)-1]["lng"]."}\n";?>
|
|
];
|
|
var flightPath = new google.maps.Polyline({
|
|
path: flightPlanCoordinates,
|
|
geodesic: true,
|
|
strokeColor: '#FF0000',
|
|
strokeOpacity: 1.0,
|
|
strokeWeight: 2
|
|
});
|
|
flightPath.setMap(map);
|
|
<? for ($i=0; $i<count($res);$i++) { ?>
|
|
var marker<?=$i?> = new google.maps.Marker({
|
|
position: {lat: <?=$res[$i]['lat']?>, lng: <?=$res[$i]['lng']?>},
|
|
map: map,
|
|
<? if ($res[$i]["seconds"]>14) { ?>
|
|
icon: {
|
|
url: "http://maps.google.com/mapfiles/ms/icons/yellow-dot.png"
|
|
},
|
|
<? } else if ($res[$i]['id']>215825 || $res[$i]['id']<215663) { ?>
|
|
icon: {
|
|
url: "http://maps.google.com/mapfiles/ms/icons/blue-dot.png"
|
|
},
|
|
<? } ?>
|
|
title: '<?=$res[$i]["first_time"]?> - <?=$res[$i]["duration"]?>'
|
|
});
|
|
marker<?=$i?>.addListener('click', function() {
|
|
//map.setZoom(8);
|
|
//map.setCenter(marker.getPosition());
|
|
alert(marker<?=$i?>.getTitle());
|
|
});
|
|
<? } ?>
|
|
}
|
|
</script>
|
|
<script>
|
|
$(function() {
|
|
let chart = new Chart(
|
|
document.getElementById("line-chart"), {
|
|
type: 'line',
|
|
data: {
|
|
labels: [''<? foreach ($res as $f) echo ",'".strtok($f["last_time"],'.')."'"; ?>],
|
|
datasets: [{
|
|
data: [0<? foreach ($res as $f) echo ",".strtok($f["seconds"],"."); ?>],
|
|
label: "Duration",
|
|
borderColor: "#3e95cd",
|
|
fill: false
|
|
}],
|
|
},
|
|
options: {
|
|
title: {
|
|
display: true,
|
|
text: 'Duration'
|
|
}
|
|
}
|
|
}
|
|
);
|
|
let chart2 = new Chart(
|
|
document.getElementById("line-chart2"), {
|
|
type: 'line',
|
|
data: {
|
|
labels: [''<? foreach ($res as $f) echo ",'".strtok($f["last_time"],'.')."'"; ?>],
|
|
datasets: [{
|
|
data: [0<? foreach ($res as $f) echo ",".(strtok($f["seconds"],".")>120?120:strtok($f["seconds"],".")); ?>],
|
|
label: "Duration",
|
|
borderColor: "#3e95cd",
|
|
fill: false
|
|
}],
|
|
},
|
|
options: {
|
|
title: {
|
|
display: true,
|
|
text: 'Duration (capped)'
|
|
}
|
|
}
|
|
}
|
|
);
|
|
let chart3 = new Chart(
|
|
document.getElementById("line-chart3"), {
|
|
type: 'line',
|
|
data: {
|
|
labels: [''<? foreach ($res as $f) echo ",'".strtok($f["last_time"],'.')."'"; ?>],
|
|
datasets: [{
|
|
data: [0<? foreach ($res as $f) echo ",".$f["speed"]; ?>],
|
|
label: "Speed",
|
|
borderColor: "#3e95cd",
|
|
fill: false
|
|
}],
|
|
},
|
|
options: {
|
|
title: {
|
|
display: true,
|
|
text: 'Speed'
|
|
}
|
|
}
|
|
}
|
|
);
|
|
});
|
|
</script>
|
|
<script async defer
|
|
src="https://maps.googleapis.com/maps/api/js?key=AIzaSyDvjiRTxngOQyBP4zpqFlZuiquc0ROvo9c&libraries=visualization&callback=initMap">
|
|
</script>
|
|
</body>
|
|
</html>
|
|
<?
|
|
function distanceBetweenTwoGpsCoordinates($lat1,$lon1,$lat2,$lon2,$unit) {
|
|
//error_log("public function distanceBetweenTwoGpsCoordinates($lat1,$lon1,$lat2,$lon2,$unit)");
|
|
if (($lat1 == $lat2) && ($lon1 == $lon2)) {
|
|
return 0;
|
|
}
|
|
$theta = $lon1 - $lon2;
|
|
$dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) + cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta));
|
|
$dist = acos($dist);
|
|
$dist = rad2deg($dist);
|
|
$miles = $dist * 60 * 1.1515;
|
|
$unit = strtoupper($unit);
|
|
if ($unit == "K") {
|
|
return ($miles * 1.609344);
|
|
}
|
|
if ($unit == "N") {
|
|
($miles * 0.8684);
|
|
}
|
|
return $miles;
|
|
}
|
|
|
|
function checkMode($distance,$duration) {
|
|
$speed = 3600 * $distance / $duration;
|
|
if ($speed<5) return "Walking";
|
|
if ($speed>20) return "Driving";
|
|
return "5to20";
|
|
}
|