Though 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.
Most GPS units of today, at least the modern hand-held models, record such waypoints 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 set of recorded waypoints, looks as follows (let us call it GPXFile.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 39 40 41 42 43 44 45 46 | <!--?xml version="1.0" encoding="UTF-8" standalone="no" ?--> Garmin International <time>2008-11-14T21:23:44Z</time> 56.0986752 NJ Grocery Store Grocery Store Grocery Store Restaurant SymbolAndName 124.1231231 VA Great Falls Great Falls National Park Great Falls National Park Park SymbolAndName 49.8737793 WDC Kenilworth Kenilworth Gardens Kenilworth Gardens Park SymbolAndName |
Why store them in MySQL?
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. One such case will be calculating the distance between any two given waypoints and confirm that straight line is [not] the shortest [or easiest] path between them. Another could be used to verify that the latitude of a given place [on northern hemisphere] is approximately equal to the angle above the horizon where North/Pole Star appears [looking in northern direction]. There are more, but you get the idea.
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 waypoint is stored within <wpt> and </wpt>. Let us suppose that we are interested in name, latitude, longitude, altitude (elevation) and description. Before proceeding ahead to extract this information from each waypoint, 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`.`GPX` ( `id` int( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY , `latitude` float NULL , `longitude` float NULL , `altitude` float NULL , `name` varchar( 254 ) NULL , `description` varchar( 254 ) NULL ) ENGINE = MYISAM DEFAULT CHARSET latin1 AUTO_INCREMENT=1 ; CREATE UNIQUE INDEX lat_lon ON `DATABASE`.`GPX` (`latitude`,`longitude`); |
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 gpx2sql.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 70 71 72 73 74 75 76 77 | <!--?php class GPXParser { var $insideitem = false; var $tag = ""; var $name = ""; var $cmt = ""; var $desc = ""; var $ele = ""; var $lat = ""; var $lon = ""; function startElement($parser, $tagName, $attrs) { if ($this->insideitem) { $this->tag = $tagName; } elseif ($tagName == "WPT") { $this->insideitem = true; $lat = $attrs['LAT']; $lon = $attrs['LON']; # This will write the first part of INSERT statment echo "INSERT IGNORE INTO `DATABASE`.`GPX` VALUES ('', '$lat', '$lon', "; } } function endElement($parser, $tagName) { if ($tagName == "WPT") { # elevation is in 'meters'. Multiply it by 3.2808399 to convert to 'feet' $ele = htmlspecialchars(trim($this->ele)); $name = htmlspecialchars(trim($this->name)); # $cmt = htmlspecialchars(trim($this->cmt)); $desc = htmlspecialchars(trim($this->desc)); # This will write the last part of INSERT statment echo "'$ele', '$name', '$desc');\n"; $this->name = ""; $this->cmt = ""; $this->desc = ""; $this->ele = ""; $this->lat = ""; $this->lon = ""; $this->insideitem = false; } } function characterData($parser, $data) { if ($this->insideitem) { switch ($this->tag) { case "NAME": $this->name .= $data; break; case "CMT": $this->cmt .= $data; break; case "DESC": $this->desc .= $data; break; case "ELE": $this->ele .= $data; break; } } } } $xml_parser = xml_parser_create(); $rss_parser = new GPXParser(); xml_set_object($xml_parser,&$rss_parser); xml_set_element_handler($xml_parser, "startElement", "endElement"); xml_set_character_data_handler($xml_parser, "characterData"); # GPX filename (can be a URL too, if the GPX file resides on a remote web server) $fp = fopen("GPXFile.gpx","r") or die("Error reading Waypoint 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 GPXInfo.sql. Then, the above php script can be used as:
1 | php gpx2sql.php >> GPXInfo.sql |
‘>>’ is important to make sure we don’t over-write the contents of the file. The GPXInfo.sql will look like
1 2 3 | INSERT IGNORE INTO `DATABASE`.`GPX` VALUES ('', '40.5589419', '-74.4173447', '56.0986752', 'NJ Grocery Store', 'Grocery Store'); INSERT IGNORE INTO `DATABASE`.`GPX` VALUES ('', '38.9945126', '-77.2554302', '124.1231231', 'VA Great Falls', 'Great Falls National Park'); INSERT IGNORE INTO `DATABASE`.`GPX` VALUES ('', '38.9130006', '-76.9386502', '49.8737793', 'WDC Kenilworth', 'Kenilworth Gardens'); |
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.
One of the applications I found recently is to use this utility to organize Way 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!!
I need some help with a GPX file from geocaching.com and was wondering if you could help. I want to create the event list like on the home page but today is is stored in XLM and a JS runns to load the info. The programer left that was working on this and now I am left hanging. Are you willing to help for free?