Storing ForeRunner GPS data

Question

Hello,

I would like to import data from my GPS watch into a SQL Server database. The purpose would be to be able to do the following types of comparisons:

1. What routes in my database are the same (including elevation)
2. How do these routes compare in time

An example of the data I am planning to use for this:

<?xml version="1.0" encoding="utf-8"?>
<gpx xmlns:tc2="http://www.garmin.com/xmlschemas/TrainingCenterDatabase/v2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:tp1="http://www.garmin.com/xmlschemas/TrackPointExtension/v1" xmlns="http://www.topografix.com/GPX/1/1" version="1.1" creator="TC2 to GPX11 XSLT stylesheet" xsi:schemaLocation="http://www.topografix.com/GPX/1/1 http://www.topografix.com/GPX/1/1/gpx.xsd http://www.garmin.com/xmlschemas/TrackPointExtension/v1 http://www.garmin.com/xmlschemas/TrackPointExtensionv1.xsd">
<trk>
<name>2011-01-12T16:39:37Z</name>
<trkseg>
<trkpt lat="51.0695494" lon="4.4953712">
<ele>-26.7795410</ele>
<time>2011-01-12T16:39:37Z</time>
</trkpt>
<trkpt lat="51.0695457" lon="4.4953625">
<ele>-27.7409668</ele>
<time>2011-01-12T16:39:38Z</time>
</trkpt>

Since I'm new to working with spatial data I've read through the first 4 chapters of "Beginning Spatial" and I am now familiar with the datatypes that are available. However, I am still not sure how I should store this data. At first I thought of using the "z" coordinate for elevation and the "m" coordinate for time. But I don't see any builtin functions in SQL Server that would allow me to perform comparison operations on these values once they are in the database. How would I best store the data if I wanted to perform the types of comparisons listed above? Or would I be better off working with another RDBMS such as PostgreSQL?

---

dba72

Answer 1

Hi Brian,

You can certainly store your data easily in SQL Server - since it's measured in GPS coordinates of latitude/longitude, the most appropriate method would be to use the geography datatype, using SRID 4326.

I imagine your GPS watch records samples at certain intervals in time, in which case I would consider using either the MultiPoint geometry and recording each sample as an individual element with associated M and Z values as you suggest, or alternatively storing each sample as a Point geometry in a table, using another column (let's say "TrackId") to show that they are part of the same recorded track.

The problem comes in your request to "compare" different sets of data with respect to elevation and time - although Z and M values can be stored and retrieved there are no inbuilt methods that use them for comparison purposes, so you'll have to code your own. This isn't actually too hard, but you need to give some thought as to exactly how you want the comparison to work - what are the allowed tolerances between different routes to regards them as "the same", for example?

- If you ran exactly the same route but at a different time of day, is that no longer the same?

- GPS samplings will never match exactly - what about if every point in one sample lay within, say 0.5 mile of another route - would that count as "the same"?

- What about if you ran the same route, but paused for a half-an-hour break in the middle before continuing?

- What about if you ran the exact same route but in reverse?

- What about if you ran the same route, but for some reason your GPS unit only recorded every other sample, or you couldn't get any GPS reception for the last few miles - i.e. the points that were in the data set matched, but there were far fewer samples?

These are application-design questions rather than technology-specific questions - SQL Server is based on the same OGC standards as, say, Oracle and PostGIS, so you'll have exactly the same issues on any of those platforms as well. Please tell us more about the sort of comparisons you want to make for more specific suggestions.

---

Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290

Answer 2

Hello Tanoshimi,

First of all, thanks for your response and great work on this forum. I like your idea of "storing each sample as a Point geometry in a table, using another column (let's say "TrackId") to show that they are part of the same recorded track". As oposed to storing linestrings of the GPS routes as single records. This gives me the following benefits:

• I would be able to store additional data about each point. For example, speed, heart rate, watts ...
• Taking the approach one step further, I could find common "paths" by performing some type of buffer around each point. Whenever a new track would be uploaded, it could be matched to the existing path and this would allow me to mark each point with a matching "PathID". This would give me the benefit of easily knowing which points are on which path and should give me faster response times when asking the following type of question "Give me all previous performances for someone biking up Mt Washington". Which is essentially what I would like to get out of the database.

The main drawback I see to this approach is the algorhythm to decide if an uploaded GPS path is the same as an existing path in the database would need to run at upload time which could mean slow upload performance.

 

 

---

dba72

Answer 3

I have no experience with SQL-Server, but some with Excel. Since 2003 version, Excel has a built-in method to read xml data. I import in a excel Sheet data from my Garmin Forerunner  205  very easily.  I download information from the watch using  “Garmin Training Center”, then I export a file with the format GPX as an option in the Garmin software. The only trick,  is you have to modify the extension from .gpx to .xml… Excel needs the extension XML to accept the importation….You has to accept Excel create the xsd: scheme, no need to give him the gpx style sheet…

You can save the xsd:scheme  for later use, and then map the field of interest

Data in a Excel sheet, you can write a VBA routine to perform all  the calculation you whish!