?
Solved

Use GPS to calculate bearing, then convert into a Cartesian coordinate system

Posted on 2009-12-30
16
Medium Priority
?
1,541 Views
Last Modified: 2013-11-10
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.  
0
Comment
Question by:rivercity
  • 10
  • 6
16 Comments
 
LVL 45

Expert Comment

by:patrickab
ID: 26153046
Please provide examples of the GPS data that you have.

Patrick
0
 
LVL 45

Expert Comment

by:patrickab
ID: 26153399
rivercity,

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
0
 

Author Comment

by:rivercity
ID: 26154087
Sorry for the delay in the response.  Your provided diagram is correct.  I have attached a pdf file with two pages.  Page One is the measured GPS data from a series of points (the origin is at the top), while page two shows how my calculation is skewing the data and placing it all in the first quadrant.

If it would be helpful, I can post some of my actual data values.
GPS-comparision---Measured-vs-Ca.PDF
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:rivercity
ID: 26155284
A better way to phrase the above question would be to have excel assign a + or - value to a pair of cells, dependent on what a bearing value may be.  For example, if the bearing value was 45 degrees, it would assign a value of + to the lat cell and a value of + to the long cell.  If the value was 315 degrees, it would assign a - value to the lat, but a + value to the long.  I have been able to individually calculate the individual components of the vector.  This process would allow me to properly plot the values in the coordinate system.
0
 
LVL 45

Expert Comment

by:patrickab
ID: 26155503
Hi rivercity,

>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
0
 

Author Comment

by:rivercity
ID: 26155633
I still need to calculate the initial bearing from the origin to the final point, but I am working on it.

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

Initial bearing=mod(atan2(sin(lon2-lon1)*cos(lat2),
           cos(lat1)*sin(lat2)-sin(lat1)*cos(lat2)*cos(lon2-lon1)),
           2*pi)

Taken from:   http://mathforum.org/library/drmath/view/55417.html

Also attached is the file from the previous images

Haase--2--17-Sept-09--L2-T16-22.xls
0
 
LVL 45

Expert Comment

by:patrickab
ID: 26157469
rivercity,

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
0
 
LVL 45

Expert Comment

by:patrickab
ID: 26157654
rivercity,

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
0
 
LVL 45

Expert Comment

by:patrickab
ID: 26157755
rivercity,

I think the attached file has the headings' sectors correct.

Patrick
Lat-Long-03.xls
0
 
LVL 45

Expert Comment

by:patrickab
ID: 26157759
rivercity,

Smaller file attached.

Patrick
Lat-Long-03.xls
0
 

Author Comment

by:rivercity
ID: 26171768
Ok,

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!
0
 
LVL 45

Expert Comment

by:patrickab
ID: 26171796
rivercity,

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

Patrick
0
 

Author Comment

by:rivercity
ID: 26173227
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)-SIN(lat1)*COS(lat2)*COS(lon2-lon1), SIN(lon2-lon1)*COS(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
0
 

Author Comment

by:rivercity
ID: 26174061
Where I found the equation I needed:

http://www.movable-type.co.uk/scripts/latlong.html
0
 
LVL 45

Accepted Solution

by:
patrickab earned 2000 total points
ID: 26175055
rivercity,

>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
0
 
LVL 45

Expert Comment

by:patrickab
ID: 26185843
rivercity - Thanks for the generous grade - Patrick
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

829 members asked questions and received personalized solutions in the past 7 days.

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

Join & Ask a Question