PHP – Storing GPS Track Points In MySQL

Posted on by Gowtham

Garmin GPSMap 60CSxThough the term waypoint has come into fairly common use in recent times, the concept has existed for as long as we have been navigating. Waypoints have traditionally been associated with distinctive features of the physical world – such as mountains, rock formations, lakes, buildings, and so on. With the advancement of technology and times, waypoints have become increasingly abstract, often having no obvious relationship to any distinctive feature of the physical world. Such waypoints are used to define invisible routing paths for navigation. And for the sake of this discussion/article, we will stick to the following definition of a waypoint: a set of co-ordinates – latitude & longitude, and sometimes altitude – to uniquely identify a point in our physical universe.

Extending this concept a bit further, one can define a Track as a collection of such waypoints in time – and when mapped using an appropriate software, it will look as follows:


Track Points Map

Track (dotted yellow line) displayed on a map (MapSource Application)


Most GPS units of today, at least the modern hand-held models, record such waypoints & tracks and sometimes provide the facility to export them as a GPX file to a computer. I use Garmin GPSMap 60CSx and the GPX file, for a recorded track, looks as follows (let us call it TRKFile.gpx):

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

<!--?xml version="1.0" encoding="UTF-8" standalone="no" ?-->
 
 
 
 
      Garmin International
 
    <time>2008-11-14T21:23:44Z</time>
 
 
 
 
    ACTIVE LOG
 
 
        Transparent
 
 
 
 
 
        -108.9722900
        <time>2008-06-27T01:34:06Z</time>
 
 
 
        -113.7789307
        <time>2008-06-27T01:34:09Z</time>
 
 
 
        -118.5854492
        <time>2008-06-27T01:34:12Z</time>
 
 
 
        -109.4528809
        <time>2008-06-27T01:34:14Z</time>

Why store them in MySQL?

As I mentioned in a previous post, I like to keep [a very detailed] track of where I have been and use that data for a variety of purposes – navigating, Google! mapping and geotagging my photographs to name just a few. Storing them in a MySQL database also makes it possible to do a variety of different calculations and visualizations.

How to convert GPX format to MySQL format?

It is my practice that I do a full/maximal installation of any linux distribution and that takes care of installing Apache (with all the required modules), PHP, MySQL, etc. I bet there are tons of documents online that you can refer and install them if you don’t already have them. Now that the requirements are taken care of, we need to decide what all information need to be extracted from the GPX file. It’s not too difficult to note that all the required information about each track point is stored within <trkpt> and </trkpt>. Let us suppose that we are interested in latitude, longitude, altitude (elevation) and date-time. Before proceeding ahead to extract this information from each track point, we need to create a MySQL table to hold this information:

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`);

Using a unique index which is a combination of latitude and longitude can help us prevent from making duplicate entries into the database. Now, save the following code into a file called trk2sql.php (I based the code on couple of my previous write-ups – Importing Comments From flickr.com and Importing Comments From flickr.com Into Pixelpost – they are well commented):

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

<!--?php class TRKParser { var $insideitem = false; var $tag = ""; var $ele = ""; var $lat = ""; var $lon = ""; var $datetime = ""; function startElement($parser, $tagName, $attrs) { if ($this-&gt;insideitem) {
      $this-&gt;tag = $tagName;
    } elseif ($tagName == "TRKPT") {
      $this-&gt;insideitem = true;
 
      $lat = $attrs['LAT'];
      $lon = $attrs['LON'];
 
      # This will write the first part of INSERT statment
      echo "INSERT IGNORE INTO `DATABASE`.`TRK` VALUES ('', '$lat', '$lon', ";
 
    }
  }
 
  function endElement($parser, $tagName) {
 
    if ($tagName == "TRKPT") {
      $ele      = htmlspecialchars(trim($this-&gt;ele));
      $datetime = htmlspecialchars(trim($this-&gt;time));
 
      # This will split date-time into date &amp; time
      list($date,$mytime) = split("T", $datetime);
      list($time,$null)   = split("Z", $mytime);
 
      # This will write the last part of INSERT statment
      echo "'$ele', '$date', '$time');\n";
 
      $this-&gt;ele         = "";
      $this-&gt;lat         = "";
      $this-&gt;lon         = "";
      $this-&gt;time        = "";
      $this-&gt;insideitem  = false;
    }
  }
 
  function characterData($parser, $data) {
    if ($this-&gt;insideitem) {
      switch ($this-&gt;tag) {
        case "ELE":
        $this-&gt;ele .= $data;
        break;
        case "TIME":
        $this-&gt;time  .= $data;
        break;
      }
    }
  }
}
 
$xml_parser = xml_parser_create();
$rss_parser = new TRKParser();
xml_set_object($xml_parser,&amp;$rss_parser);
xml_set_element_handler($xml_parser, "startElement", "endElement");
xml_set_character_data_handler($xml_parser, "characterData");
 
$fp = fopen("TRKFile.gpx","r")
      or die("Error reading Track Point data.");
 
while ($data = fread($fp, 4096))
  xml_parse($xml_parser, $data, feof($fp))
    or die(sprintf("XML error: %s at line %d",
      xml_error_string(xml_get_error_code($xml_parser)),
      xml_get_current_line_number($xml_parser)));
 
fclose($fp);
 
xml_parser_free($xml_parser);
 
?-->

How to use it?

Let us suppose that we decide to keep the extracted data in a file named TRKInfo.sql. Then, the above php script can be used as:

1

php trk2sql.php &gt;&gt; TRKInfo.sql

‘>>’ is important to make sure we don’t over-write the contents of the file. The TRKInfo.sql will look like

1
2
3

INSERT IGNORE INTO `DATABASE`.`TRK` VALUES ('', '40.7697914', '-74.0381743', '-108.9722900', '2008-06-27', '01:34:06');
INSERT IGNORE INTO `DATABASE`.`TRK` VALUES ('', '40.7694440', '-74.0373474', '-113.7789307', '2008-06-27', '01:34:09');
INSERT IGNORE INTO `DATABASE`.`TRK` VALUES ('', '40.7691001', '-74.0365290', '-118.5854492', '2008-06-27', '01:34:12');

Basic MySQL commands can be used to import the contents of this SQL file into a MySQL database. Once stored in the database, the data could be used as the need may be. If you find this script useful and/or have ways to make it better, I would greatly appreciate your thoughts as comments. I am sure other readers will do too.

I Like This But Don’t Have A Linux Box To Play With!

Well, for those of us who face this problem, here is a web based utility. Just save the GPX file from the GPS to your computer, copy and paste the contents into the text box to get the appropriate stuff.

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

<!--?php class TRKParser { var $insideitem = false; var $tag = ""; var $ele = ""; var $lat = ""; var $lon = ""; var $datetime = ""; function startElement($parser, $tagName, $attrs) { if ($this-&gt;insideitem) {
      $this-&gt;tag = $tagName;
    } elseif ($tagName == "TRKPT") {
      $this-&gt;insideitem = true;
 
      $lat = $attrs['LAT'];
      $lon = $attrs['LON'];
 
      # This will write the first part of INSERT statment
      echo "INSERT IGNORE INTO `DATABASE`.`TRK` VALUES ('', '$lat', '$lon', ";
 
    }
  }
 
  function endElement($parser, $tagName) {
 
    if ($tagName == "TRKPT") {
      $ele      = htmlspecialchars(trim($this-&gt;ele));
      $datetime = htmlspecialchars(trim($this-&gt;time));
 
      # This will split date-time into date &amp; time
      list($date,$mytime) = split("T", $datetime);
      list($time,$null)   = split("Z", $mytime);
 
      # This will write the last part of INSERT statment
      echo "'$ele', '$date $time');\n";
 
      $this-&gt;ele         = "";
      $this-&gt;lat         = "";
      $this-&gt;lon         = "";
      $this-&gt;time        = "";
      $this-&gt;insideitem  = false;
    }
  }
 
  function characterData($parser, $data) {
    if ($this-&gt;insideitem) {
      switch ($this-&gt;tag) {
        case "ELE":
        $this-&gt;ele .= $data;
        break;
        case "TIME":
        $this-&gt;time  .= $data;
        break;
      }
    }
  }
}
 
$xml_parser = xml_parser_create();
$rss_parser = new TRKParser();
xml_set_object($xml_parser,&amp;$rss_parser);
xml_set_element_handler($xml_parser, "startElement", "endElement");
xml_set_character_data_handler($xml_parser, "characterData");
 
$fp = fopen("TRKFile.gpx","r")
      or die("Error reading Track Point data.");
 
while ($data = fread($fp, 4096))
  xml_parse($xml_parser, $data, feof($fp))
    or die(sprintf("XML error: %s at line %d",
      xml_error_string(xml_get_error_code($xml_parser)),
      xml_get_current_line_number($xml_parser)));
 
fclose($fp);
 
xml_parser_free($xml_parser);
?-->

7 Replies to “PHP – Storing GPS Track Points In MySQL”

  2. One of the applications I found recently is to use this utility to organize Tracks / Track Points and export them back as a GPX file – which, in turn, can be imported into Google Earth (Pro). I have tested it with v5.0 and it works like a charm!!

    Reply
  3. Pingback: Gowtham :: Brain Dump v1.0 » PHP – Calculating Distance Between Two Locations Given Their GPS Coordinates
  4. Pingback: Gowtham :: Brain Dump v1.0 » MySQL – Finding Locations Nearest To A Given Pair Of GPS Coordinates
  5. Pingback: Gowtham :: Brain Dump v1.0 » MySQL – Finding Nearest Record In Date/Time
  6. Pingback: Gowtham :: Brain Dump v1.0 » PHP – GPS Tracks In Google KML Format
  7. Pingback: Gowtham » Seventh Sense » PHP – Computing Total Travel Distance From GPS Tracks

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.