Community discussions

MikroTik App
 
Bomber67
Member
Member
Topic Author
Posts: 383
Joined: Wed Nov 08, 2006 10:36 am

GNSS tracking based on KNOT - PHP-script fails?

Mon Feb 06, 2023 2:15 pm

Exploring NB-IOT and GNSS tracking I have a setup based on the following Wiki article: https://wiki.mikrotik.com/wiki/Manual:GPS-tracking
Basically, the KNOT runs a script that obtains LAT/LON/ELE from the BG77 and posts it via http to a web server running the PHP part of the script in the article.
The http part of the script (called when viewing in a web browser) is slightly modified for using a wms feed from the Norwegian mapping autohority Statkart instead of the somewhat coarse OSM.
The script has ran for months, but at some time (in the past 1-2 months) it stopped adding records to the SQLite DB.

-Routeros script runs every 2 minutes and according to the log it obtains correct GNSS data, establishes a NB-IOT connection and posts successfully to the server
-PHP script appears to be executed, evidence is that whenever coord.db is deleted, upon next ROS script execution it is re-created
-No record is ever added to the DB
-Map viewing using the http part of the script works flawlessly. If I copy an old backup of coord,db to the server, it is viewed correctly in the web browser.

My PHP skills are very limited, so there is not much I can do.
I have ensured 777 attributes on both folder and coords.db.
-Is there a way to do some logging from the PHP execution to verify that LAT/LON data is valid, or to reveal why they are not posted to the DB?
-Sometimes some weeks ago ISP upgraded web hotel to PHP 8.2. Can that possibly prevent the script from working properly?


Log from KNOT: (error logging type is used only for extended visibility in Winbox logging window. LAT/LON anonymized)
 
 12:59:39 script,error enabling priority for GPS
 12:59:39 script,error Delaying 32 secs to ensure GNSS is available
 13:00:11 script,error reading GPS coordinates
 13:00:11 script,error LAT: *9.393053  LON: *0.308038  SPEED: 0.000000  ALT: 103.099998 
 13:00:11 script,error re-enabling priority for WWAN
 13:00:11 script,error re-enabling WWAN
 13:00:11 system,info device changed by admin
 13:00:16 script,error posting coordinates to server via fetch
 13:00:17 info fetch: file "index.php" downloaded
 13:00:17 script,error posting coordinates completed
Script running on server:
<?php
$loc = dirname(__FILE__).'/sqlite_db/coord.db';
$db = new SQLite3($loc,SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE);
$raw = file_get_contents('php://input');
$raw = preg_replace('/\\x00/','',$raw);
$data = json_decode($raw);

if (!empty($data) && is_object($data) && property_exists($data,'lat') && property_exists($data,'lon')){
    if(file_exists($loc)) echo 'exists!'.chr(0xa);
    $src = 'SELECT name FROM sqlite_master WHERE type=\'table\' AND name=\'coordinates\'';
    $res = $db->querySingle($src);
    if (count($res)==0){
            $db->exec('CREATE TABLE coordinates (latitude TEXT, longitude TEXT, time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, added TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ');
    }
    
$regex = '/^(|\-)([0-9]{1,3}\.[0-9]{0,8})$/';

if (preg_match($regex,$data->lat) && preg_match($regex,$data->lon) )
	{
		$lat = $data->lat;
		$lon = $data->lon;
	}
	$ins = 'INSERT INTO coordinates (latitude,longitude) VALUES (\''.SQLite3::escapeString($lat).'\',\''.SQLite3::escapeString($lon).'\')';
	$db->exec($ins);
	die();
}
?>

<!DOCTYPE html>
<html>
<head>
  <link rel="stylesheet" href="https://unpkg.com/leaflet@1.3.1/dist/leaflet.css" integrity="sha512-Rksm5RenBEKSKFjgI3a41vrjkw4EVPlJ3+OiI65vTjIdo9brlAacEuKOiQ5OFh7cOI1bkDwLqdLw3Zg0cRJAAQ==" crossorigin=""/>
  <script src="https://unpkg.com/leaflet@1.3.1/dist/leaflet.js" integrity="sha512-/Nsx9X4HebavoBvEBuyp3I7od5tA0UzAxs+j83KgC8PU0kgB4XiK4Lfe4y4cgBtaRJQEIFCW+oC506aPT2L1zw==" crossorigin=""></script>
</head>
<body>
<div id="map" style="width: 1024px; height: 768px;"></div>
<script>
var map = L.map('map').setView([0,0], 4);

var wmsLayer = L.tileLayer.wms('https://openwms.statkart.no/skwms1/wms.topo4?service=wms', {layers: 'TOPO4_WMS'}).addTo(map);

<!--L.tileLayer('http://{s}.tile.osm.org/{z}/{x}/{y}.png', {attribution: '<a href="http://osm.org/copyright">OSM</a>'}).addTo(map);-->

<?php
    if($result = $db->query('SELECT latitude,longitude FROM coordinates')){
    echo ' var latlngs = [ ';
    while($obj = $result->fetchArray()){
    	if (!is_array($obj) || !isset($obj['latitude']) || !isset($obj['longitude']) || empty($obj['latitude']) || empty($obj['longitude'])) continue;
    	echo '["'. $obj['latitude'].'","'.$obj['longitude'].'"],';
    }
    echo ']; ';
    } else
     echo('//'.$db->lastErrorMsg().chr(0xa));  
	 echo($data);


    if($result = $db->query('SELECT latitude,longitude,time FROM coordinates')){
    echo ' var latlngtime = [ ';
    while($obj = $result->fetchArray()){
    	if (!is_array($obj) || !isset($obj['latitude']) || !isset($obj['longitude']) || !isset($obj['time']) || empty($obj['latitude']) || empty($obj['longitude']) || empty($obj['time'])) continue;
    	echo '["'. $obj['latitude'].'","'.$obj['longitude'].'","'.$obj['time'].'"],';
    }
    echo ']; ';
    } else
     echo('//'.$db->lastErrorMsg().chr(0xa));  
	 echo($data);

?>
var polyline = L.polyline(latlngs, {color: 'red'}).addTo(map);
map.fitBounds(polyline.getBounds());

for ($i= 0; $i < latlngtime.length; $i++){
  var circle = L.circle([latlngs[$i][0], latlngs[$i][1]], {
    color: 'black',
    fillColor: '#f03',
    fillOpacity: 0.5,
    radius: 10
}).addTo(map);
  circle.bindPopup("Lat: " + latlngtime[$i][0] + "<br>Lon: " + latlngtime[$i][1] + "<br>Time: " + latlngtime[$i][2])
}



</script>
</body>
</html>

ROS script running on KNOT: (anonymized)
:global lat
:global lon
:global spd
:global alt

log error ("Starting script")

log error ("disabling WWAN to get GPS coordinates")
/interface ppp-client set ppp-out1 disabled=yes

log error ("enabling priority for GPS")
/interface ppp-client at-chat ppp-out1 input="AT+QGPSCFG=\"priority\",0"

log error ("Delaying 32 secs to ensure GNSS is available")
:delay 32000ms

log error ("reading GPS coordinates")
/system gps monitor once do={
:set $lat $("latitude")
:set $lon $("longitude")
:set $spd $("speed")
:set $alt $("altitude")
}

#:set $lon "10.000000"

:set $spd [:pick $spd 0 [find $spd " km"]]
:set $alt [:pick $alt 0 [find $alt "m"]]
:log error ("LAT: $lat  LON: $lon  SPEED: $spd  ALT: $alt")


log error ("re-enabling priority for WWAN")
/interface ppp-client at-chat ppp-out1 input="AT+QGPSCFG=\"priority\",1"
log error ("re-enabling WWAN")
/interface ppp-client set ppp-out1 disabled=no


if ($lat != "none") do={

#the delay below waits for 5 seconds for the ppp connection to get established - this time can differ based on the signal strength
delay 5000ms
:log error ("posting coordinates to server via fetch")

/tool fetch mode=http url="http://www.<myserver>/<myfolder>/index.php" port=80 http-method=post http-data=("{\"lat\":\"" . $lat . "\",\"lon\":\"" . $lon . "\"}") http-header-field="Content-Type: application/json" 


:log error ("posting coordinates completed")


} else={
:log error ("failed to acquire position via GNSS")

}

 
Bomber67
Member
Member
Topic Author
Posts: 383
Joined: Wed Nov 08, 2006 10:36 am

Re: GNSS tracking based on KNOT - PHP-script fails?

Mon Feb 06, 2023 3:28 pm

Temporarily downgrading to PHP 7.4 made it work :D
Any thoughts on what must be done to the script to be compliant with PHP 8.2 ?

Who is online

Users browsing this forum: loloski, xrlls and 17 guests