Consulting

Results 1 to 7 of 7

Thread: Evaluating Lat Long Equations

  1. #1
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,536
    Location

    Evaluating Lat Long Equations

    Just recently I came across the following statement
    Suppose we are discussing latitude and longitude in WGS84 and we want to be accurate. Both meridian radius of curvature M (latitude length) and radius of curvature of parallels R (longitude length) in WGS84 depend only on latitude phi and are defined as:

    M = a(1 - e^2) / (1 - e^2 sin(phi)^2)^(3/2)

    R = a cos(phi) / (1 - e^2 sin(phi)^2)^(1/2)

    where

    e is the eccentricity of the referenced ellipsoid of WGS84
    a is the semi-major axis of the ellipsoid.
    WGS 84 defines the semi-major axis of the WGS 84 ellipsoid a and the flattening factor of the Earth f as:

    a=6378137m

    1/f=298.257223563

    With these defining parameters in WGS 84, we can obtain the eccentricity:

    e=sqrt(2f-f^2)=0.081819191

    Verification of the above equations

    To verify whether the above eq. (1) and eq. (2) are correct, we will check them against the results at USGS which said, at 38 degrees North latitude, one degree of latitude equals approximately 364,000 feet (69 miles) and one degree of longitude equals 288,200 feet (54.6 miles).

    To find the meridian radius of curvature M (latitude length) and radius of curvature of parallels (longitude length) at 38 degrees North latitude, we plug in a=6378137, e=0.081819191 and phi=38 into eq. (1) and eq. (2):

    M = 6359629.652 m/rad

    R = 5032429.322 m/rad

    Furthermore, to find length of 1 of latitude and longitude, we multiply the above equations by the radian of 1:

    Length of 1 latitude = M * 1/180 * pi = 6359629.652*1*pi/180 = 110996.4766 m = 68.970013 miles

    Length of 1 longitude= R * 1/180 * pi = 5032429.322*1*pi/180 = 87832.46103 m = 54.57656101 miles

    The results are in line with the results of USGS.

    Answer to your question

    Repeat the above steps but plug in phi=3715.8298′ = 37+15.8298/60=37.26383, we can obtain:

    Length of 1 latitude=68.96139 miles

    Length of 1 longitude=55.11761 miles
    I am trying firstly understand the equations and secondly evaluate them for accuracy. In using Office 365 for Mac, Excel refuses to recognise the formulas for M & R. Could this be because the formulas might be missing a Math Operator? Simply replacing "PHI" with the Latitude (in Decimal Degrees) still results in an error message " are you trying to enter a formula?". Next I run into an error when I tried to check the validity of the value of "e". The formula as written in the statement above returns a "Num error because we haven't define the value of "f". When I replaced "f" in the equation to determine "e", it returned the #Num error message. What am I doing wrong?

    If I understand the 1/f =298.257223563 statement correctly, by transposing f= 298.257223563*1, or is this assumption incorrect?

    Eventually I'd like to test the formulas to determine the lengths of 1 of Latitude and or Longitude. Any assistance would be greatly appreciated. On a side note. Supporters of Office 365 often say that the improvements under 365, significantly reduce the need to resort to VBA. Given that we are dealing with values with 9 decimals places can this overcome the limitation of floating point error, or is Office 365 still a fair way off this accuracy required?
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,501
    Location
    Did you remember to convert Degrees to Radians (cell C9)?

    Capture.JPG

    Otherwise the ws formulas seem to work
    Attached Files Attached Files
    Last edited by Paul_Hossler; 03-24-2023 at 10:42 AM.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,536
    Location
    Thank you Paul. I see that there were two math Operator symbols missing from the M & R formulas, and errr....... sadly no I did not remember the C9 bit. Must have been one of those days when I should have been in Maths class but was sitting in a chair outside the headmaster's office awaiting for some behavioural correction to be applied.
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,501
    Location
    I never even noticed that there were *'s missing from

    M = a(1 - e^2) / (1 - e^2 sin(phi)^2)^(3/2)
    
    R = a cos(phi) / (1 - e^2 sin(phi)^2)^(1/2)
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,536
    Location
    I have followed on with your worksheet Paul. Firstly I determined the actual length in meters (actually it's less than a meter) for each unit of 0.000001. The six decimal places is determined by that which is provided in Google Earth decimal degrees co-ordinates. Why you ask.... well just because.

    Think of it like this. The local orienteering club has a task master who sets tasks(courses) for the members to follow to find clues. Last Tuesday, he said from your start position at 38 N and 143 East, travel 1200 mtrs north then 2300 mtrs East, and look for a clue under a rock to find the next set of clues. Could you do it? Well it depends on your methodology. I mean you could try to walk the distance using a measuring wheel, and maybe, be somewhere in the right area, but I doubt you could walk the correct heading for long enough.

    If you use the standard GPS units that read in DMS, the variance between the length of a degree @38 latitude (110996.4766mtrs/3600, equates to 30.832 mtrs per unit and conversely the length of a degree at 143 longitude (87832.4610/3600, equates to 24.397mtrs which means you are looking for a rock in 752.45 sq mtrs. Not an entirely hopeless task but definitely one that would be time consuming.

    If you were just a little bit smarter and had a GPS tracking unit which used decimal degrees, you end up looking for a rock inside an area less than 1 mtr. See the worksheet for the example.

    The next task the next task is to write some code that determines whether you are in the Northern or Southern hemisphere and which side of the Greenwich mark you are. This then determines if the unit values are added or subtracted from the starting Lat/Lon position depending on the travel direction used to arrive at your new position.

    The more astute of you will argue that this will work for small distances travelled, but will fail (less accurate) if travelling more than 1 degree, and yes I agree.
    Attached Files Attached Files
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,501
    Location
    Do you have C27 and C28 reversed?


    Capture.JPG

    Took a shot at N/S and E/W in the workbook

    Also attached some modules I had stored away from NOAA dealing with coordinates and stuff

    Might be some stuff you can 'borrow'
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #7
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,536
    Location
    Thank you Paul. Yes for some reason I did have the cells reversed but for the life of me I cannot recall why ( just very tired I guess). I have also noticed the modules have converted the functions from a simple two dimensional to three dimensional function. Now to go have some fun with Google Earth.
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •