<table cellspacing="12" width="100%">
<tr><td colspan="2">
<table width="100%" bgcolor="#f0f0f8">
<tr><td align="center">
<h1>GPX tracks: a quick intro</h1>

<table width="100%"><tr>
<td width="33%" align="left"></td>
<td align="center"><a to
<td width="33%" align="right"></td>
width="100%" bgcolor="#ffffdf" cellspacing="4" cellpadding="8">
<tr><td><b>What is a GPX file
<tr><td><b>GPX</b> stands for <i><b>GP</b>S E<b>x</b>change Format</i>; it's a very popular file format used in order to exchange GPS data across different devices.<br>
GPX is an open format and is almost universally supported: it's based on a formally defined <a schema</a><br>
Many real word implementations are only loosely conformant to the standard schema and further non-standard tags have been introduced by several vendors.<br><br>
A GPX file is expected to internally store at least one of the following contents:
<ul>
<li><b>Tracks</b>: a Track is an ordered list of points describing a path.
A Track must contain at least one Track Segment holding a list of Track Points which are logically connected in order.<br>
A Track can containing more Segments, and more Tracks can be stored into the same GPX file.</li>
a WayPoint consists of the WGS 84 (GPS) coordinates of a point and possibly other descriptive information.<br>
The same GPX file can contain a collection of many WayPoints with no sequential relationship.</li>
<li><b>Routes</b>: a Route is an ordered list of routepoint(waypoints representing a series of significant turn or stage points) leading to a destination.</li>
</ul><br>
SpatiaLite only supports <b>GPX Tracks</b> and will always silently ignore any other content.<br><br>
<h3>Anatomy of a GPX file containing a GPS Track</h3>
If you are interested into GPS Tracks you'll very probably have lots of GPX files of your own that you can use as test samples.<br>
The following practical examples use this <a sample</a><br>
<verbatim>
<?xml version="1.0"?>
<gpx version="1.0"
  <trk>
    <trkseg>
      <trkpt lat="55.753572" lon="37.808250">
        <ele>135.00</ele>
        <time>2009-05-19T04:00:30Z</time>
      </trkpt>
      <trkpt lat="55.753622" lon="37.808255">
        <ele>135.00</ele>
        <time>2009-05-19T04:00:31Z</time>
      </trkpt>
      <trkpt lat="55.753593" lon="37.808158">
        <ele>135.00</ele>
        <time>2009-05-19T04:00:32Z</time>
      </trkpt>
....
      <trkpt lat="55.758177" lon="37.677802">
        <ele>152.00</ele>
        <time>2009-05-19T04:46:27Z</time>
      </trkpt>
    </trkseg>
  </trk>
</gpx>
</verbatim>
<ul>
<li>the whole GPX file is enclosed within <b>&lt;gpx&gt;</b> and tags.</li>
<li>each Track is delimited by a matching pair of <b>&lt;trk&gt;</b> and tags.</li>
<li>each Track Segment is delimited by a matching pair of <b>&lt;trkseg&gt;</b> and tags.</li>
<li>each Track Point is enclosed within <b>&lt;trkpt&gt;</b> and tags.
<ul>
<li>the <b>lat</b> and <b>lon</b> mandatory attributes are intended to declare the WGS 84 and of the point.</li>
<li>the of the point (measured in meters) can be optionally declared within a pair of <b>&lt;ele&gt;</b> and tags.</li>
<li>the <b><i>date and of the corresponding GPS measure can be optionally declared within a pair of <b>&lt;time&gt;</b> and tags.<br>
Dates and times are not local time, but instead are <a Universal Time</a> (<b>UTC</b>) using <a 8601</a> format.</li>

</ul>

</table><br>
<hr><br>
<h2>Importing GPX tracks into a SpatiaLite GeoTable</h2>
<verbatim>
CREATE TABLE raw_gpx (
    id_gpx INTEGER PRIMARY KEY AUTOINCREMENT,
    gpx BLOB NOT  NULL);

INSERT INTO raw_gpx VALUES (NULL, XB_Create(XB_LoadXML('Gpx-full-sample.gpx')));

SELECT id_gpx, XB_IsGPX(gpx)
FROM raw_gpx;
-------------------
1
</verbatim>
GPX files basically are XML files, so you just have to use the standard SQL functions supporting generic XML.<br>
<b>XB_IsGPX()</b> is a new SQL function introduced by SpatiaLite <b>4.4.0</b>; it will test the actual XML payload and will return <b>1</b> if it really is of the GPX type, <b>0</b> if not, and <b>-1</b> when the passed argument is not a valid XML document.<br>
<verbatim>
CREATE TABLE gpx_tracks (
    id_trk INTEGER PRIMARY KEY AUTOINCREMENT);

SELECT AddGeometryColumn('gpx_tracks', 'geom', 4326, 'MULTILINESTRING', 'XYZM');
-------------
1

INSERT INTO gpx_tracks
SELECT NULL, XB_MLineFromGPX(gpx)
FROM raw_gpx;
</verbatim>
Now we'll create another table including a Geometry column declared of the <b>MULTILINESTRING</b> type and having <b>XYZM</b> dimensions. The SRID for this Geometry must be <b>4326</b> aka <b>WGS 84</b>, the one always adopted by GPS data.<br><br>
We can finally invoke the new <b>XB_MLineFromGPX()</b> SQL function in order to parse the GPX payload possibly building a MultiLinestring corresponding to all  TraksSegments defined by the GPX.<br>
Please note:
<ul>
<li>XB_MLineFromGPX() will return <b>NULL</b> either if the passed argument is not a valid BLOB, or if it's a valid BLOB but not of the GPX type.</li>
<li>anyway XB_MLineFromGPX() could eventually return a <b>NULL</b> value even while parsing a valid GPX payload, if it does not contains any Trak.</li>
<li>in case of success the returned Geometry will always be a MultiLinestring, and a distinct Linestring will be created for every <b>Track Segment</b> found within the GPX.<br>
Each <b>TrakPoint</b> will correspond to a Vertex of the corresponding Linestring
<ul>
<li>the <b>X</b> and <b>Y</b> coordinates of the Vertex will be set accordingly to the declared and
<li>if an <b>ele</b> tag was declared the <b>Z</b> coordinate will be set to corresponding value.<br>
Otherwise the <b>Z</b> coordinate will be set to <b>0.0</b> (default value).</li>
<li>if a <b>time</b> tag was declared, the ISO 8601 timestamp will be transformed into a <a day number</a>, and the <b>M</b> coordinate will be set to the corresponding value.<br>
Otherwise the <b>M</b> coordinate will be set to <b>1721059.5</b>, this corresponding to <b>0000-01-01T00:00:00Z</b> (default value).</li>
</ul>
</li>
</ul><br>
<hr>
<h2>Checking the imported TrackPoints</h2>
<verbatim>
SELECT 'Point #1' AS point, ST_X(a.pt) AS lon, ST_Y(a.pt) AS lat, ST_Z(a.pt) AS ele, DateTime(ST_M(a.pt)) AS time
FROM (SELECT ST_StartPoint(geom) AS pt FROM gpx_tracks) AS a
UNION
SELECT 'Point #2' AS point, ST_X(a.pt) AS lon, ST_Y(a.pt) AS lat, ST_Z(a.pt) AS ele, DateTime(ST_M(a.pt)) AS time
FROM (SELECT ST_PointN(geom, 2) AS pt FROM gpx_tracks) AS a
UNION
SELECT 'Point #3' AS point, ST_X(a.pt) AS lon, ST_Y(a.pt) AS lat, ST_Z(a.pt) AS ele, DateTime(ST_M(a.pt)) AS time
FROM (SELECT ST_PointN(geom, 3) AS pt FROM gpx_tracks) AS a
UNION
SELECT 'Point #N' AS point, ST_X(a.pt) AS lon, ST_Y(a.pt) AS lat, ST_Z(a.pt) AS ele, DateTime(ST_M(a.pt)) AS time
FROM (SELECT ST_EndPoint(geom) AS pt FROM gpx_tracks) AS a;
</verbatim>
<table bgcolor="#d0ffb0" cellspacing="4" cellpadding="4" border="1">
<tr><td
<tr><td>Point #1</td><td align="right">37.808250</td><td
<tr><td>Point #2</td><td align="right">37.808255</td><td align="right">55.753622</td><td align="right">135.000000</td><td>2009-05-19
<tr><td>Point #3</td><td align="right">37.808158</td><td align="right">55.753593</td><td align="right">135.000000</td><td>2009-05-19
<tr><td>Point #N</td><td align="right">37.677802</td><td align="right">55.758177</td><td align="right">152.000000</td><td>2009-05-19
</table><br>
As you can easily check, the MultiLinestring XYZM returned by <b>XB_MLineFromGPX()</b> faithfully preserves both the spatial coordinates and the timestamps originally registered by the GPS sensor.
<br><br>
<table width="100%"><tr>
<td width="33%" align="left"></td>
<td align="center"><a to
<td width="33%" align="right"></td>