Seventh Sense Rambling about life's little things, in 7 ≡ 1 (mod 6) fashion

« | »

PHP – GPS Tracks In Google KML Format

August 10th, 2009 @ 23:23:56 | GPS, KML, MySQL, PHP

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.net/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.net/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.net/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.net/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;
?>
Share
divider

Responses to PHP – GPS Tracks In Google KML Format

  1. Whey Protein side Effects says:
    2009.12.03 at 12:26:19

    Very great place.
    The content here is truly important.

    I will invite my friends.

    Cheers

Most of these posts, especially the ones with any hint of technical jargon, are intended to be Note2Self. But if any of them float your boat, then feel free to sail along. If you feel so generous, improve my journey with your comments &/or thoughts!
Looking for MS Thesis or PhD Dissertation Template in LaTeX? Click below!

MTU Create The Future
Twitter

Archives

Planet Kannada

Twitter: @sgowtham Facebook: @sgowtham Linked In: sgowtham

Opinions expressed in these pages are purely personal and do not reflect those of any other institution and/or individual
© 2002-2018 Gowtham • All Rights Reserved