GPS Data Parser – Use of the Data Table with
Spreadsheets (FAQ)
This page provides answers on Frequently Asked Questions regarding the ´GPS Data Parser Program´.
1 Content of the data table produced by GPS Data
Parser
1.1 Samples of GPGLL or GPRMC data table:
* 054189.68 53.0589000 008.8978633 (with decimal point option)
* 054189,68 53,0589000 008,8978633 (with decimal comma option)
The first column contains time converted into seconds, the second latitude and the third longitude.
1.2. Samples of GPGGA, GPX or IGC data table
* 054189.68 53.0589000 008.8978633 0007.76 (with decimal point option)
* 054189,68 53,0589000 008,8978633 0007,76 (with decimal comma option)
The first column contains time converted into seconds, the second latitude, the third longitude, and the fourth altitude in metres.
You will find help for the processing of the table data in a spread sheet here.
2 How do I open the datatable file with MS EXCEL?
1) Open Excel
2) Open file load
3) Select to show all files
4) Select the *.prn file produced by GPS Data Parser you want to process
5) Excel will come up with a dialogue to import tables. Select fixed column width and columns separated by blanks.
6) Press ok and the file should be imported correctly.
7) Save it as an EXCEL file.
2) Open file load
3) Select to show all files
4) Select the *.prn file produced by GPS Data Parser you want to process
5) Excel will come up with a dialogue to import tables. Select fixed column width and columns separated by blanks.
6) Press ok and the file should be imported correctly.
7) Save it as an EXCEL file.
3 How do I calculate distance between fixes?
In order to derive distance travelled from two position fixes you must take the following into account:
a) One minute
of
latitude always corresponds to a distance of one nautical mile (1852 m).
b) Due to the meridian convergence, one minute of longitude corresponds to a distance of (1852 m * Cosine(Latitude)).
c) If you change latitude, you can use the mean latitude between the two positions as an approximation.
d) Excel needs to see degrees converted into radians (degrees*pi/180°).
e) Distance travelled between fixes can then be calculated from two distances, Northing and Easting differences, using the Pythagorean formula.
f) This calculation is an approximation and should only be used, if the latitude difference between two position fixes is relatively small. At the author's latitude the longitude error for a latitude difference of 1° is approx. 2%. With a series of GPS fixes at seconds intervals, the use of this approximation should not be a problem. For more exact solutions or larger longitude distances between fixes the Great Circle calculus has to be used.
b) Due to the meridian convergence, one minute of longitude corresponds to a distance of (1852 m * Cosine(Latitude)).
c) If you change latitude, you can use the mean latitude between the two positions as an approximation.
d) Excel needs to see degrees converted into radians (degrees*pi/180°).
e) Distance travelled between fixes can then be calculated from two distances, Northing and Easting differences, using the Pythagorean formula.
f) This calculation is an approximation and should only be used, if the latitude difference between two position fixes is relatively small. At the author's latitude the longitude error for a latitude difference of 1° is approx. 2%. With a series of GPS fixes at seconds intervals, the use of this approximation should not be a problem. For more exact solutions or larger longitude distances between fixes the Great Circle calculus has to be used.
Copy the following formula in cell E1 of the spread sheet:
+SQRT(((B2-B1)*60*1852)^2+((C2-C1)*60*1852*COS(B1*PI()/180))^2)
.
This formula calculates the distance in metres between the positions in row 1 and row 2. Copy the furmula and paste it in the cells below E1, except for the last position. Now you can sum up the distances in this column to get the total distance travelled.
Users with a German Excel version replace "SQRT" by "WURZEL".
As GPS receivers sometimes react to reflections of the radio waves , measured positions may occasionally fall offside the track. Such track spikes provide a distance that is too large. Be aware of such errors.
4 How do I calculate speed between fixes?
Once you have calculated the distances in the way, as described above, you can easily calculate speed by dividing the distance between two position fixes by the time difference derived from the first column. Copy the following formula to Cell F1
+E1/(A2-A1).
This formula will calculate the speed in m/s between the positions in row 1 and row 2. To get the speed in km/h, add *3600/1000 to the formula, to get knots, add *3600/1852.
Please be aware in your calculations that GPS resets the clock at midnight (UTC).
5 How do I plot a speed or altitude profile over time?
You can do so by using the diagram function of your spread sheet program. You could graph altitude (column D) or speed (column F) over time (column A) . View examples here.
You could also add a column for cumulative distance travelled. Then you could graph altitude or speed over distance travelled.
You could graph altitude variations between two fixes. Other calculations only depend on your imagination (and on your spread sheet skills).
Ingo Harre, Bremen, Germany, Content last modified on 2009/04/25
