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)
Code: Select all
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
Code: Select all
<?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)
Code: Select all
: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")
}