PHP – GPS Tracks In Google KML Format

Amongst other things, I have the habit of keeping a detailed track of where I have been and for this purpose, I use my Garmin GPSMap 60CSx. I have configured this GPS to save track information every 3 seconds. If this information can be written in KML format, then the tracks can be visualized using Google Earth.


Part #0: GPS Tracks → MySQL

Please refer to this post. For the sake of partial completeness, the MySQL table structure is given below:

1
2
3
4
5
6
7
8
9
10
CREATE TABLE IF NOT EXISTS `DATABASE`.`TRK` (
  `id`          int( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `latitude`    float NULL ,
  `longitude`   float NULL ,
  `altitude`    float NULL ,
  `date`        date NULL ,
  `time`        time NULL
) ENGINE = MYISAM DEFAULT CHARSET latin1 AUTO_INCREMENT=1 ;
 
CREATE UNIQUE INDEX lldt ON `DATABASE`.`TRK` (`latitude`,`longitude`,`date`,`time`);


Part #1: MySQL → KML

Save the following into a file, mysql2kml.php and put in appropriate values for username, password, database name, start_date and end_date.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
<?php
 
# Connect to the database
$host     = 'localhost';
$dbuser   = 'USERNAME';
$dbpasswd = 'PASSWORD';
$database = 'DATABASE';
 
$connect  = mysql_connect($host, $dbuser, $dbpasswd) or
            die('<b>MySQL Connection Error:</b> ' .
            mysql_errno() . ': ' .
            mysql_error());
 
mysql_select_db($database, $connect) or
            die('<b>Database Connection Error:</b> ' .
            mysql_errno() . ': ' .
            mysql_error());
 
# The standard header - refer to Google Earth / KML documentation to modify it
print <<<EOF
<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2">
  <Document>
    <name>Points with TimeStamps</name>
    <Style id="blue-dot">
      <IconStyle>
        <Icon>
          <href>http://sgowtham.com/icon/blue-dot.png</href>
        </Icon>
        <hotSpot x="1" y="1" xunits="pixels" yunits="pixels"/>
      </IconStyle>
    </Style>
    <Style id="red-dot">
      <IconStyle>
        <Icon>
          <href>http://sgowtham.com/icon/red-dot.gif</href>
        </Icon>
        <hotSpot x="2" y="2" xunits="pixels" yunits="pixels"/>
      </IconStyle>
    </Style>
    <Style id="paddle-a">
      <IconStyle>
        <Icon>
          <href>http://maps.google.com/mapfiles/kml/paddle/A.png</href>
        </Icon>
        <hotSpot x="32" y="1" xunits="pixels" yunits="pixels"/>
      </IconStyle>
    </Style>
    <Style id="paddle-b">
      <IconStyle>
        <Icon>
          <href>http://maps.google.com/mapfiles/kml/paddle/B.png</href>
        </Icon>
        <hotSpot x="32" y="1" xunits="pixels" yunits="pixels"/>
      </IconStyle>
    </Style>
    <Style id="hiker-icon">
      <IconStyle>
        <Icon>
          <href>http://maps.google.com/mapfiles/ms/icons/hiker.png</href>
        </Icon>
        <hotSpot x="0" y=".5" xunits="fraction" yunits="fraction"/>
      </IconStyle>
    </Style>
    <Style id="check-hide-children">
      <ListStyle>
        <listItemType>checkHideChildren</listItemType>
      </ListStyle>
    </Style>
    <styleUrl>#check-hide-children</styleUrl>
 
    <Folder>\n
EOF;
 
# Set Start & End Date values - set both of them to same
# if you need a specific day
$start_date = '2009-08-07';
$end_date   = '2009-08-09';
 
$sql1     = "SELECT * FROM `DATABASE`.`TRK` ";
$sql1    .= "WHERE date BETWEEN '$start_date' AND '$end_date' ";
$sql1    .= "ORDER BY date, time";
$result1  = mysql_query($sql1) or
            die('Invalid Query : ' .
            mysql_errno() . ' : ' . 
            mysql_error()); 
$nresults = mysql_num_rows($result1);
 
while ($myrow = mysql_fetch_array($result1)) {
  $date        = $myrow['date'];
  $time        = $myrow['time'];
  $latitude    = $myrow['latitude'];
  $longitude   = $myrow['longitude'];
  $altitude    = $myrow['altitude'];
 
  $date        = mysql_real_escape_string($date);
  $time        = mysql_real_escape_string($time);
  $latitude    = mysql_real_escape_string($latitude);
  $longitude   = mysql_real_escape_string($longitude);
  $altitude    = mysql_real_escape_string($altitude);
 
  $datetime    = $date . "T" . $time ."Z";
 
  if( "$date" !== "0000-00-00") {
    print <<<EOF
      <Placemark>
        <TimeStamp>
          <when>$datetime</when>
        </TimeStamp>
        <styleUrl>#blue-dot</styleUrl>
        <Point>
          <coordinates>$longitude,$latitude,$altitude</coordinates>
        </Point>
      </Placemark>\n
EOF;
  }
}
 
print <<<EOF
    </Folder>
  </Document>
</kml>
EOF;
?>

Run this file as


1
`which php` mysql2kml.php > 20090807_20090809.kml



If all goes well, the file 20090807_20090809.kml should be written out and be waiting for you. Try a sample file, if you wish – 20090809.kml should be openable in Google Earth and should display something like below:



20090809.kml


Update (2009.11.30)

Use the following MySQL table structure and accompanying PHP script for better performance.

1
2
3
4
5
6
7
8
9
10
CREATE TABLE IF NOT EXISTS `MyDatabase`.`TRK` (
  `id`          int( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `latitude`    float NOT NULL DEFAULT '0' ,
  `longitude`   float NOT NULL DEFAULT '0' ,
  `altitude`    float NOT NULL DEFAULT '0' ,
  `date_time`   float NOT NULL DEFAULT '0000-00-00 00:00:00' 
) ENGINE = MYISAM DEFAULT CHARSET latin1 AUTO_INCREMENT=1 ;
 
CREATE UNIQUE INDEX dt ON `MyDatabase`.`TRK` (`date_time`);
CREATE UNIQUE INDEX lldt ON `MyDatabase`.`TRK` (`latitude`,`longitude`,`date_time`);


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
<?php
 
# Connect to the database
$host     = 'localhost';
$dbuser   = 'USERNAME';
$dbpasswd = 'PASSWORD';
$database = 'DATABASE';
 
$connect  = mysql_connect($host, $dbuser, $dbpasswd) or
            die('<b>MySQL Connection Error:</b> ' .
            mysql_errno() . ': ' .
            mysql_error());
 
mysql_select_db($database, $connect) or
            die('<b>Database Connection Error:</b> ' .
            mysql_errno() . ': ' .
            mysql_error());
 
# The standard header - refer to Google Earth / KML documentation to modify it
print <<<EOF
<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2">
  <Document>
    <name>Points with TimeStamps</name>
    <Style id="blue-dot">
      <IconStyle>
        <Icon>
          <href>http://sgowtham.com/icon/blue-dot.png</href>
        </Icon>
        <hotSpot x="1" y="1" xunits="pixels" yunits="pixels"/>
      </IconStyle>
    </Style>
    <Style id="red-dot">
      <IconStyle>
        <Icon>
          <href>http://sgowtham.com/icon/red-dot.gif</href>
        </Icon>
        <hotSpot x="2" y="2" xunits="pixels" yunits="pixels"/>
      </IconStyle>
    </Style>
    <Style id="paddle-a">
      <IconStyle>
        <Icon>
          <href>http://maps.google.com/mapfiles/kml/paddle/A.png</href>
        </Icon>
        <hotSpot x="32" y="1" xunits="pixels" yunits="pixels"/>
      </IconStyle>
    </Style>
    <Style id="paddle-b">
      <IconStyle>
        <Icon>
          <href>http://maps.google.com/mapfiles/kml/paddle/B.png</href>
        </Icon>
        <hotSpot x="32" y="1" xunits="pixels" yunits="pixels"/>
      </IconStyle>
    </Style>
    <Style id="hiker-icon">
      <IconStyle>
        <Icon>
          <href>http://maps.google.com/mapfiles/ms/icons/hiker.png</href>
        </Icon>
        <hotSpot x="0" y=".5" xunits="fraction" yunits="fraction"/>
      </IconStyle>
    </Style>
    <Style id="check-hide-children">
      <ListStyle>
        <listItemType>checkHideChildren</listItemType>
      </ListStyle>
    </Style>
    <styleUrl>#check-hide-children</styleUrl>
 
    <Folder>\n
EOF;
 
# Set Start & End Date values - set both of them to same
# if you need a specific day
$start_date = '2009-08-07';
$end_date   = '2009-08-09';
 
$sql1     = "SELECT * FROM `DATABASE`.`TRK` ";
$sql1    .= "WHERE DATE(date_time) BETWEEN '$start_date' AND '$end_date' ";
$sql1    .= "ORDER BY date_time ";
$result1  = mysql_query($sql1) or
            die('Invalid Query : ' .
            mysql_errno() . ' : ' . 
            mysql_error()); 
$nresults = mysql_num_rows($result1);
 
while ($myrow = mysql_fetch_array($result1)) {
  $date_time   = $myrow['date_time'];
  $latitude    = $myrow['latitude'];
  $longitude   = $myrow['longitude'];
  $altitude    = $myrow['altitude'];
 
  $date_time   = mysql_real_escape_string($date_time);
  $latitude    = mysql_real_escape_string($latitude);
  $longitude   = mysql_real_escape_string($longitude);
  $altitude    = mysql_real_escape_string($altitude);
 
  list($date, $time) = split(" ", $date_time);
  $datetime    = $date . "T" . $time ."Z";
 
  if( "$date" !== "0000-00-00") {
    print <<<EOF
      <Placemark>
        <TimeStamp>
          <when>$datetime</when>
        </TimeStamp>
        <styleUrl>#blue-dot</styleUrl>
        <Point>
          <coordinates>$longitude,$latitude,$altitude</coordinates>
        </Point>
      </Placemark>\n
EOF;
  }
}
 
print <<<EOF
    </Folder>
  </Document>
</kml>
EOF;
?>