D 2020-07-18T17:43:55.491
L GPX\stracks
P f501b98785536cc1c61496982f7098b8c945dcd3
U sandro
W 9054
<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>
</td></tr></table>
<table width="100%"><tr>
<td width="33%" align="left"></td>
<td align="center">Back to <a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=5.0.0-doc">5.0.0-doc main page</a></td>
<td width="33%" align="right"></td>
</tr></table><br><table width="100%" bgcolor="#ffffdf" cellspacing="4" cellpadding="8">
<tr><td><b>What is a GPX file ?</b></td></tr>
<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 always supported: it's based on a formally defined <a href="http://www.topografix.com/GPX/1/1/#top">XML 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 contain more Segments, and more Tracks can be stored into the same GPX file.</li>
<li><b>WayPoints</b></b>: 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 routepoints (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 examples use this <a href="http://www.shmuma.ru/wiki/images/5/51/Gpx-full-sample.gpx">GPX sample</a><br>
<verbatim>
<?xml version="1.0"?>
<gpx version="1.0" xmlns="http://www.topografix.com/GPX/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><gpx></b> and <b></gpx></b> tags.</li>
<li>each Track is delimited by a matching pair of <b><trk></b> and <b></trk></b> tags.</li>
<li>each Track Segment is delimited by a matching pair of <b><trkseg></b> and <b></trkseg></b> tags.</li>
<li>each Track Point is enclosed within <b><trkpt></b> and <b></trkpt></b> tags.
<ul>
<li>the <b>lat</b> and <b>lon</b> mandatory attributes are intended to declare the WGS 84 <b><i>latitude</i></b> and <b><i>longitude</i></b> of the point.</li>
<li>the <b><i>elevation</i></b> of the point (measured in meters) can be optionally declared within a pair of <b><ele></b> and <b></ele></b> tags.</li>
<li>the <b><i>date and time</i></b> of the corresponding GPS measure can be optionally declared within a pair of <b><time></b> and <b></time></b> tags.<br>
Dates and times are not local time, but instead are <a href="https://en.wikipedia.org/wiki/Coordinated_Universal_Time">Coordinated Universal Time</a> (<b>UTC</b>) using <a href="https://en.wikipedia.org/wiki/ISO_8601">ISO 8601</a> format.</li>
</ul></li>
</ul>
</td></tr>
</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>
Since GPX files are XML files, you must 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 input 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 <b>must</b> be <b>4326</b> (alias <b>WGS 84</b>), which is <b>always</b> used with GPS data.<br><br>
With this, we can invoke the new <b>XB_MLineFromGPX()</b> SQL function which will parse the GPX input, possibly creating a MultiLinestring corresponding to all of the Track-Segments defined in the GPX-File.<br>
Please note:
<ul>
<li>XB_MLineFromGPX() will return <b>NULL</b> if either the passed argument is not a valid BLOB, or it's a valid BLOB but not of the GPX type.</li>
<li>XB_MLineFromGPX() will return a <b>NULL</b> value, if it does not contains any Tracks.</li>
<li>when successful, the returned Geometry will always be a MultiLinestring, and a distinct Linestring will be created inside for every <b>Track Segment</b> found within the GPX-File.<br>
Each <b>TrackPoint</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 <b><i>lon</i></b> and <b><i>lat</i></b>.</li>
<li>if an <b><i>ele</i></b> tag was declared the <b>Z</b> coordinate will be set to the corresponding value.<br>
Otherwise the <b>Z</b> coordinate will be set to <b>0.0</b> (default value).</li>
<li>if a <b><i>time</i></b> tag was declared, the ISO 8601 timestamp will be transformed into a <a href="https://en.wikipedia.org/wiki/Julian_day">julian 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 bgcolor="#c0ddao"><b>point</b></td><td bgcolor="#c0ddao"><b>lon</b></td><td bgcolor="#c0ddao"><b>lat</b></td><td bgcolor="#c0ddao"><b>ele</b></td><td bgcolor="#c0ddao"><b>time</b></td></tr>
<tr><td>Point #1</td><td align="right">37.808250</td><td alogn="right">55.753572</td><td>135.000000</td><td>2009-05-19 04:00:30</td></tr>
<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 04:00:31</td></tr>
<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 04:00:32</td></tr>
<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 04:46:27</td></tr>
</table><br>
As you can easily see, 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">Back to <a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=5.0.0-doc">5.0.0-doc main page</a></td>
<td width="33%" align="right"></td>
</tr></table>
Z fdeb1dc360eeafdfc406b4970e377f4e