Patrick

Solved

Posted on 2009-12-30

Hello,

I am trying to calculate a bearing/heading between a fixed origin GPS point and a Rover GPS. I am able to calculate the proper distances into feet, but I need to be able to place this in a grid system similar to that of the Cartesian coordinate system. To do so, I would like to be able to calculate the heading (degrees) and then convert it so I could properly plot the values. My current calculations place all of my values in the first quadrant, and this is incorrect.

I am trying to calculate a bearing/heading between a fixed origin GPS point and a Rover GPS. I am able to calculate the proper distances into feet, but I need to be able to place this in a grid system similar to that of the Cartesian coordinate system. To do so, I would like to be able to calculate the heading (degrees) and then convert it so I could properly plot the values. My current calculations place all of my values in the first quadrant, and this is incorrect.

16 Comments

In which quadrant the figures are placed depends on their relative values. See attached file for diagram. I'm sure you know it already but a picture can sometimes help.

Patrick

grid-references-01.xls

If it would be helpful, I can post some of my actual data values.

GPS-comparision---Measured-vs-Ca.PDF

>If it would be helpful, I can post some of my actual data values.

That would be very helpful. Only a small sample of data will do the job.

Patrick

I have found this formula, but I can't seem to get it to work yet.

Initial bearing=mod(atan2(sin(lon2

cos(lat1)*sin(lat2)-sin(la

2*pi)

Taken from: http://mathforum.org/libra

Also attached is the file from the previous images

Haase--2--17-Sept-09--L2-T16-22.xls

Thanks for the file. I not yet had a look - been working on other problems. Now it's time for this question...

I'll be back.

Patrick

The calculations are in the attached file. I have run all the calculations relative to the origin which I have assumed is defined by the GPS coordinates in row 4. I have left everything visible. If you don;t want to see the workings, then I suggest you hide the columns - that's the quickest and easiest method.

Hope it helps

Patrick

Lat-Long-02.xls

Let me look over this and I will let you know. Sorry for the delay in the response. I can blame it on the holiday!

No hurry. I am not at all sure that I have the sectors NESW correct - worth checking carefully.

Patrick

I'm not quite sure, but the Decimal Degrees you have for the Lat/Long are different than my original equations. I checked both on maps.google to confirm location.

On a different note, I found that using the equation below yields the proper data (in radians) and when converted to degrees, provide me the 'direction' i require.

=ATAN2(COS(lat1)*SIN(lat2)

I can then use an 'IF' condition in Excel to correct for direction. For example, if the angle is <90, the distance is +. =IF(x<90,"-1","1")

I have successfully calculated the distance from the origin to any other point and I can create a correctly scaled map. The last step would be to create a condition for 360 degrees from the origin. This way I could properly assign a - value to the lat or long, depending on which quadrant it would be in.

Coordinates-vs-distance--ft-.PDF

Haase--2--17-Sept-09--L2-T16-22-.xls

>The last step would be to create a condition for 360 degrees from the origin. This way I could properly assign a - value to the lat or long, depending on which quadrant it would be in.

From the website you have quoted: "Since atan2 returns values in the range -Ã€ ... +Ã€ (that is, -180Â° ... +180Â°), to normalise the result to a compass bearing (in the range 0Â° ... 360Â°, with -ve values transformed into the range 180Â° ... 360Â°), convert to degrees and then use (Â¸+360) % 360, where % is modulo."

Patrick

Title | # Comments | Views | Activity |
---|---|---|---|

How to pull text from inside a formula in Excel 2010? | 6 | 26 | |

Excel remove Chars in Conditional formating. | 6 | 19 | |

Dynamic Bar chart in Excel | 5 | 16 | |

Help adding more columns to my pivot table | 19 | 17 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**27** Experts available now in Live!