Trails Munich Horizon

Trails Munich Horizon

This is part 3 of the DIY Location mapping. This time we will integrate with google earth to see live updates from our devices, we tracked with part 1.

Therefore I have had the tracking running for some days with an update interval of 15 minutes, so that I does not drain my battery too much. By enhancing the update frequency you will get fine grained location data.

We need to add the following PHP script to deliver a valid KML file, which is loaded by another KML file. The second KML file is loaded into Google Earth and does poll data via the PHP scripts to enable the live updates.

This needs to be running on the linux web server, where we collected the data from part 1 and 2.

Trails Munich Helicopterview

Trails Munich Helicopterview

$coords = array();

$db = mysql_connect('MYSQL_HOST', 'MYSQL_USER', 'MYSQL_PASS');
mysql_select_db('MYSQL_DBNAME', $db) or die(mysql_error());

$query = "SELECT * FROM (SELECT * FROM history WHERE user = 'YOUR_USERNAME' AND dt > '2011-09-01 00:00:00' AND dt < '2011-11-01 00:00:00' ORDER BY dt  DESC LIMIT 500) AS tbl ORDER BY tbl.dt";
$result = mysql_query($query, $db) or die(mysql_error());
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
                                               // in ein Array $data gelesen
    $remap = array(
    'zeit' => $row['dt'],
    'lat' => $row['lat'],
    'lon' => $row['lng']

  $coords[$row['user']][] = $remap;



$lat = '';
$lon = '';
$alt = '';
$kml ='
<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="">
    <description>Examples of paths. Note that the tessellate tag is by default
      set to 0. If you want to create tessellated lines, they must be authored
      (or edited) directly in KML.</description>
    <Style id="yellowLineGreenPoly">
$kml .='    <Placemark>
      <name>Your Trail</name>
      <description>Your trail as it is recorded from his iPhone app</description>
if (Count($coords) > 0 ) {
  foreach ( $coords['mat'] AS $time => $coord) {
    $zeit = $coord['zeit'];
    $lat = $coord['lat'];
    $lon = $coord['lon'];
    $alt = "995.0";
    $kml .= $coord['lon'].",";
    $kml .= $coord['lat'].",";
    $kml .= "995.0,";
  $kml = trim($kml);
$kml .='

foreach ( $coords AS $username => $location ) {
$num = count($location) - 1;
$kml .= '

$kml .='  </Document>
Header('Content-Type: application/');
echo $kml;

This is the KML file, which you open with Google Earth.

Trails Hollywood Bowling

Trails Hollywood Bowling

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="">
    <name>Your Testlab</name>
    <description>You are experimenting with GPS and iPhone</description>
      <name>Selected Placemarks</name>
      <description>These are generated and refreshed automagically</description>

Have fun.