Arduino GPS SQL/PHP/Javascript Google Maps API

gps_ajax.php

<?php
ini_set("display_errors", "1");
error_reporting(E_ALL);

require("connection.php");


// Opens a connection to a MySQL server
$connection=mysql_connect ($dbhost, $dbuser, $dbpass);
if (!$connection) {
  die('Not connected : ' . mysql_error());
}

// Set the active MySQL database
$db_selected = mysql_select_db($db, $connection);
if (!$db_selected) {
  die ('Can\'t use db : ' . mysql_error());
}

// Select all the rows in the markers table
$query = "SELECT * FROM markers WHERE 1 ORDER BY timestamp";
$result = mysql_query($query);
if (!$result) {
  die('Invalid query: ' . mysql_error());
}
// Iterate through the rows, adding XML nodes for each
while ($row = @mysql_fetch_assoc($result)){
	$row['lat'] = doubleval($row['lat']);
	$row['lng'] = doubleval($row['lng']);
	$row['alt'] = floatval($row['alt']);
	$row['hdop'] = intval($row['hdop']);
	$row['timestamp'] = strtotime($row['timestamp']);
	$row['format'] = date('d.m.Y H:i:s', $row['timestamp']);
	$json[] = $row;
}

echo json_encode($json);
?>

gps_logger_to_serial_csv.ino

#include <SoftwareSerial.h>
#include <TinyGPS.h>

TinyGPS gps;
SoftwareSerial ss(3, 2);
unsigned long pattern = 0x00000000; //0000 speed, 0000 error

static void smartdelay(unsigned long ms);
static void print_float(float val, float invalid, int len, int prec);
static void print_int(unsigned long val, unsigned long invalid, int len);
static void print_date(TinyGPS &gps);
static void print_str(const char *str, int len);
long readVcc();

void setup()
{
  Serial.begin(115200);
  pinMode(13, OUTPUT);/*
  Serial.print("Testing TinyGPS library v. "); Serial.println(TinyGPS::library_version());
  Serial.println("by Mikal Hart");
  Serial.println();
  Serial.println("Date, Latitude,  Longitude, Altitude, HDOP, Sats");
  Serial.println("------------------------------------------------");
*/
  ss.begin(9600);
}

  float flat, flon;
  unsigned long age, date, time, chars = 0, chars2 = 0;
  unsigned short sentences = 0, sentences2 = 0, failed = 0;

void loop()
{
  gps.stats(&chars, &sentences, &failed);
  if(gps.satellites() != TinyGPS::GPS_INVALID_SATELLITES && sentences != sentences2) {
    gps.f_get_position(&flat, &flon, &age);
    Serial.print(",");
    print_date(gps);
    Serial.print(",");
    Serial.print(flat,6);
    Serial.print(",");
    Serial.print(flon,6);
    Serial.print(",");
    Serial.print(gps.f_altitude(),1);
    Serial.print(",");
    Serial.println(gps.hdop());

    //Serial.println(gps.satellites());
  } else {
  }

  
  //print_int(gps.satellites(), TinyGPS::GPS_INVALID_SATELLITES, 5);
  //print_int(gps.hdop(), TinyGPS::GPS_INVALID_HDOP, 5);
  
  /*print_float(flat, TinyGPS::GPS_INVALID_F_ANGLE, 10, 6);
  print_float(flon, TinyGPS::GPS_INVALID_F_ANGLE, 11, 6);
  print_int(age, TinyGPS::GPS_INVALID_AGE, 5);
  print_date(gps);
  print_float(gps.f_altitude(), TinyGPS::GPS_INVALID_F_ALTITUDE, 7, 2);
  print_float(gps.f_course(), TinyGPS::GPS_INVALID_F_ANGLE, 7, 2);
  print_float(gps.f_speed_kmph(), TinyGPS::GPS_INVALID_F_SPEED, 6, 2);
  print_str(gps.f_course() == TinyGPS::GPS_INVALID_F_ANGLE ? "*** " : TinyGPS::cardinal(gps.f_course()), 6);
  print_int(flat == TinyGPS::GPS_INVALID_F_ANGLE ? 0xFFFFFFFF : (unsigned long)TinyGPS::distance_between(flat, flon, HOME_LAT, HOME_LON) , 0xFFFFFFFF, 9);
  print_float(flat == TinyGPS::GPS_INVALID_F_ANGLE ? TinyGPS::GPS_INVALID_F_ANGLE : TinyGPS::course_to(flat, flon, HOME_LAT, HOME_LON), TinyGPS::GPS_INVALID_F_ANGLE, 7, 2);
  print_str(flat == TinyGPS::GPS_INVALID_F_ANGLE ? "*** " : TinyGPS::cardinal(TinyGPS::course_to(flat, flon, HOME_LAT, HOME_LON)), 6);
  */
  

  if(chars == chars2) pattern = 0xffff;
  else if(sentences == sentences2) pattern = chars % 2;
  else if(readVcc()<4000) pattern = 0;
  else pattern = (gps.speed() << 16) + gps.satellites()-4;
  
  /*print_int(chars, 0xFFFFFFFF, 6);
  print_int(sentences, 0xFFFFFFFF, 10);
  print_int(failed, 0xFFFFFFFF, 9);
  */
  chars2 = chars;
  sentences2 = sentences;
  //Serial.println();
  
  smartdelay(1000);
}
long readVcc() { 
  long result; // Read 1.1V reference against AVcc 
  ADMUX = _BV(REFS0) | _BV(MUX3) | _BV(MUX2) | _BV(MUX1);
  delay(2); // Wait for Vref to settle
  ADCSRA |= _BV(ADSC); // Convert
  while (bit_is_set(ADCSRA,ADSC));
  result = ADCL;
  result |= ADCH<<8;
  result = 1126400L / result; // Back-calculate AVcc in mV
  return result;
}
static void smartdelay(unsigned long ms)
{
  unsigned long start = millis();
  do 
  {
    digitalWrite(13, (pattern >> ((millis() % 1000) * 32 / 1000)) & 0x1 );
    while (ss.available())
      gps.encode(ss.read());
  } while (millis() - start < ms);
}

static void print_float(float val, float invalid, int len, int prec)
{
  if (val == invalid)
  {
    while (len-- > 1)
      Serial.print('*');
    Serial.print(' ');
  }
  else
  {
    Serial.print(val, prec);
    int vi = abs((int)val);
    int flen = prec + (val < 0.0 ? 2 : 1); // . and -
    flen += vi >= 1000 ? 4 : vi >= 100 ? 3 : vi >= 10 ? 2 : 1;
    for (int i=flen; i<len; ++i)
      Serial.print(' ');
  }
  smartdelay(0);
}

static void print_int(unsigned long val, unsigned long invalid, int len)
{
  char sz[32];
  if (val == invalid)
    strcpy(sz, "*******");
  else
    sprintf(sz, "%ld", val);
  sz[len] = 0;
  for (int i=strlen(sz); i<len; ++i)
    sz[i] = ' ';
  if (len > 0) 
    sz[len-1] = ' ';
  Serial.print(sz);
  smartdelay(0);
}

static void print_date(TinyGPS &gps)
{
  int year;
  byte month, day, hour, minute, second, hundredths;
  unsigned long age;
  gps.crack_datetime(&year, &month, &day, &hour, &minute, &second, &hundredths, &age);
  if (age == TinyGPS::GPS_INVALID_AGE)
    Serial.print("********** ******** ");
  else
  {
    char sz[32];
    sprintf(sz, "%02d-%02d-%02d %02d:%02d:%02d",
        year, month, day, hour, minute, second);
    Serial.print(sz);
  }
  //print_int(age, TinyGPS::GPS_INVALID_AGE, 5);
  smartdelay(0);
}

static void print_str(const char *str, int len)
{
  int slen = strlen(str);
  for (int i=0; i<len; ++i)
    Serial.print(i<slen ? str[i] : ' ');
  smartdelay(0);
}

index.html

<!DOCTYPE html >
  <head>
    <meta name="viewport" content="initial-scale=1.0, user-scalable=no" />
    <meta http-equiv="content-type" content="text/html; charset=UTF-8"/>
    <title>Arduino/PHP/MySQL & Google Maps</title>
		<script language="javascript" type="text/javascript" src="../flot/jquery.js"></script>
    <script src="https://maps.googleapis.com/maps/api/js?key=<KEY>"
            type="text/javascript"></script>
    <script type="text/javascript">
    //<![CDATA[

		$(window).resize(function() {
			$('#map').height($(window).height());
			$('#map').width($(window).width());
		});

    function load() {
				$('#map').height($(window).height());
				$('#map').width($(window).width());
      var map = new google.maps.Map(document.getElementById("map"), {
        center: new google.maps.LatLng(51.44, 11.0),
        zoom: 13,
        mapTypeId: 'terrain'
      });
			var bikeLayer = new google.maps.BicyclingLayer();
			bikeLayer.setMap(map);
      var infoWindow = new google.maps.InfoWindow;
			$.ajax({
				type: 'POST',
				url: 'gps_ajax.php',
				data: {},
				success: function(txt) {
					var data = JSON.parse(txt);
					var labels = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
					var labelIndex = 0, startIndex = 0, distances = [0], timeDiff = [1], speed = [0];
					
					for (var i = 1; i < data.length; i++) {

						distances[i] = distance(data[i-1].lat, data[i-1].lng, data[i].lat, data[i].lng);
						if(isNaN(distances[i])) distances[i] = 0;
						timeDiff[i] = data[i].timestamp - data[i-1].timestamp;
						speed[i] = distances[i] / timeDiff[i] * 3.6;
						
						if(timeDiff[i] > 60 || i == data.length - 1) {
							
							var label = labels[labelIndex++ % labels.length];
							var html = "Start: <b>" + data[startIndex].format + "</b><br/>" + data[startIndex].alt + "m<br/>Finish: <b>" + data[i-1].format + "</b><br/>" + data[i-1].alt + "m";
							var startMarker = new google.maps.Marker({
								map: map,
								position: data[startIndex],
								label: label
							});
							bindInfoWindow(startMarker, map, infoWindow, html);
							
							var color = getRandomColor();
							var path = new google.maps.Polyline({
								map: map,
								path: data.slice(startIndex, i),
								strokeColor: color,
								strokeOpacity: 1,
								strokeWeight: 4,
								zIndex: 1
							});
							google.maps.event.addListener(path, 'mouseover', function(event) {
							 this.setOptions({
									 zIndex: 2,
									 strokeWeight: 6
							 });
							});	
							google.maps.event.addListener(path, 'mouseout', function(event) {
							 this.setOptions({
									 zIndex: 1,
									 strokeWeight: 4
							 });
							});
							var dis = distances.slice(startIndex, i).reduce((a, b) => a + b, 0).toFixed(1);
							var tsp = Math.max.apply(Math, speed.slice(startIndex, i)).toFixed(2);
							var avg = (dis / (data[i-1].timestamp - data[startIndex].timestamp) * 3.6).toFixed(2);
							$("#info").html($("#info").html()+"<div style='border-radius: 25px;opacity:.7;padding:5px;margin:5px;display: inline-block;background-color:"+color+"'>"+html+"<br/>Distance: "+dis+"m<br/>top speed: "+tsp+"km/h<br/>average speed: "+avg+"km/h</div>");
							startIndex = i;
						}
						var circle = new google.maps.Circle({
							strokeColor: '#FF0000',
							strokeOpacity: 0.0,
							strokeWeight: 0,
							fillColor: '#FF0000',
							fillOpacity: 0.01,
							map: map,
							center: data[i],
							radius: data[i].hdop / 100,
							zIndex: 0
						});
					}
				}
			});
    }
    function bindInfoWindow(marker, map, infoWindow, html) {
      google.maps.event.addListener(marker, 'click', function() {
        infoWindow.setContent(html);
        infoWindow.open(map, marker);
      });
    }
		function getRandomColor() {
			var letters = '3456789ABCDEF'.split('');
			var color = '#';
			for (var i = 0; i < 6; i++ ) {
				color += letters[Math.floor(Math.random() * 13)];
			}
			return color;
		}
		/*function convertTimestamp(sqlTs) {
			var t = sqlTs.split(/[- :]/);
			return {
				ts: new Date(t[0], t[1]-1, t[2], t[3], t[4], t[5]),
			  format: t[2]+"."+t[1]+"."+t[0]+" "+t[3]+":"+t[4]+":"+t[5]
			};
		}*/
	function distance (lat1, lon1, lat2, lon2) {
	 
		// Convert degrees to radians
		lat1 = lat1 * Math.PI / 180.0;
		lon1 = lon1 * Math.PI / 180.0;
	 
		lat2 = lat2 * Math.PI / 180.0;
		lon2 = lon2 * Math.PI / 180.0;
	 
		// radius of earth in metres
		var r = 6378100;
	 
		// P
		var rho1 = r * Math.cos(lat1);
		var z1 = r * Math.sin(lat1);
		var x1 = rho1 * Math.cos(lon1);
		var y1 = rho1 * Math.sin(lon1);
	 
		// Q
		var rho2 = r * Math.cos(lat2);
		var z2 = r * Math.sin(lat2);
		var x2 = rho2 * Math.cos(lon2);
		var y2 = rho2 * Math.sin(lon2);
	 
		// Dot product
		var dot = (x1 * x2 + y1 * y2 + z1 * z2);
		var cos_theta = dot / (r * r);
	 
		var theta = Math.acos(cos_theta);
	 
		// Distance in Metres
		return r * theta;
	}

    //]]>

  </script>

  </head>

  <body onload="load()" style="margin: 0; padding: 0;">
    <div id="map" style="width: 800px; height: 800px"></div>
	<div id="info" style="position:absolute; bottom:0; left:0;"></div>
  </body>

</html>

table.sql

CREATE TABLE IF NOT EXISTS `markers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `lat` float(10,6) NOT NULL,
  `lng` float(10,6) NOT NULL,
  `alt` float(5,1) NOT NULL,
  `hdop` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;