PHP – Storing GPS Track Points In MySQL.

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. 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:

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):

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 and . 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:

Garmin International

2008-11-14T21:23:44Z

ACTIVE LOG

Transparent

-108.9722900
2008-06-27T01:34:06Z

-113.7789307
2008-06-27T01:34:09Z

-118.5854492
2008-06-27T01:34:12Z

-109.4528809
2008-06-27T01:34:14Z