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

<!--?php 
 
# Connect to the database
$host     = 'localhost';
$dbuser   = 'USERNAME';
$dbpasswd = 'PASSWORD';
$database = 'DATABASE';
 
$connect  = mysql_connect($host, $dbuser, $dbpasswd) or
            die('&lt;b&gt;MySQL Connection Error:&lt;/b&gt; ' .
            mysql_errno() . ': ' .
            mysql_error());
 
mysql_select_db($database, $connect) or
            die('&lt;b&gt;Database Connection Error:&lt;/b&gt; ' .
            mysql_errno() . ': ' .
            mysql_error());
 
# The standard header - refer to Google Earth / KML documentation to modify it
print &lt;&lt;&lt;EOF
&lt;?xml version="1.0" encoding="UTF-8"?-->
 
 
    Points with TimeStamps

#check-hide-children\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 <<$datetime#blue-dot$longitude,$latitude,$altitude\n EOF; } } print <<

1

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:

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

<!--?php 
 
# Connect to the database
$host     = 'localhost';
$dbuser   = 'USERNAME';
$dbpasswd = 'PASSWORD';
$database = 'DATABASE';
 
$connect  = mysql_connect($host, $dbuser, $dbpasswd) or
            die('&lt;b&gt;MySQL Connection Error:&lt;/b&gt; ' .
            mysql_errno() . ': ' .
            mysql_error());
 
mysql_select_db($database, $connect) or
            die('&lt;b&gt;Database Connection Error:&lt;/b&gt; ' .
            mysql_errno() . ': ' .
            mysql_error());
 
# The standard header - refer to Google Earth / KML documentation to modify it
print &lt;&lt;&lt;EOF
&lt;?xml version="1.0" encoding="UTF-8"?-->
 
 
    Points with TimeStamps

#check-hide-children\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 <<$datetime#blue-dot$longitude,$latitude,$altitude\n EOF; } } print <<

1

EOF; ?>