Longitude Latitude Question
Results 1 to 5 of 5

Thread: Longitude Latitude Question

  1. #1
    New Member
    Join Date
    Jun 2010
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Longitude Latitude Question

    Hi,

    I am trying to find a formula that would determine the distance (in feet) that these two coordinates varied from. In this case, to be expressed as "X" (amount of feet south) and "X" (amount of feet west) or original point. Then total distance as the crow flies.

    45.5099086,-73.5638463
    45.4893274,-73.5844949

    Thanks.

  2. #2
    Board Regular DRSteele's Avatar
    Join Date
    Mar 2015
    Location
    Calgary
    Posts
    1,973
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Longitude Latitude Question

    If I understand correctly, what you want to do is calulate the Great Circle Distance, as in this thread

    https://www.mrexcel.com/forum/excel-questions/966498-create-territories.html#post4639578

    references:
    http://www.mrexcel.com/forum/excel-q...st-office.html
    http://en.wikipedia.org/wiki/Great-circle_distance
    BlueMM: Excel formula to calculate distance between 2 latitude, longitude (lat/lon) points (GPS positions)
    Latitude And Longitude

    Windows10, Excel 365 Insider
    If you don't declare what version of Excel, we will assume
    Windows10, Excel 365.
    Formulas here are always in
    Green background
    You can get the HTML Maker: https://www.mrexcel.com/forum/excel-...ins-links.html.

  3. #3
    New Member
    Join Date
    Jun 2010
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Longitude Latitude Question

    Thanks for the reply DRSteele. No, actually I am just trying to figure out how many feet South and West these two coordinates end up at. I want to end an argument that has been going on for years with regards to St.Catherine street in Montreal. The street goes Northeast to Southwest with a dividing line at St.Laurent. All addresses "West" of St.Laurent (going towards Atwater), are called St.Catherine West. The argument goes that St.Catherine is actually more in line with North / South. This would finally end the argument. You might be familiar with this - being from Calgary.

    https://www.reddit.com/r/montreal/co...es_north_west/

  4. #4
    Board Regular
    Join Date
    Dec 2017
    Location
    UK
    Posts
    827
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Longitude Latitude Question

    If you want a reasonable flat earth type of calculation you can do this easily by using the equivalence between one minute of arc and 1 nautical mile which is about 6076 feet
    To find the difference in Northing subtract one latitude from the other :
    0.0205812
    The multiply by 60 to get minutes and then by 6076 to get feet:
    =7503.082272 feet
    To find the Easting you need to take account of the fact the lines of longitude get closer together as you go North. We can do the approximately by multiplying by the cosine of the Average latitude
    The average latitude is:
    45.499618
    To find the cosine in EXCEL we need to convert this to radians by multiplying by PI/180
    The equation for this assuming the average is in cell D7is :
    =Cos(PI()*D7/180)
    This is: 0.700914
    WE now find the difference between the two longitudes=
    0.020649

    We now multiply this by 60 * 6076 and the cosine of the average latitudes to give:
    5276.238 feet

    This is only approximate because the earth isn’t really flat. However for distance such as these the error is not large ( I haven’t Calculated what it is)
    To get a more accurate answer you can use a Spherical earth model which is much more complex and even then is not absolutely accurate. A more accurate model of the earth is the elliptical model which is really hard work calculating, then you get a choice of which elliptical model you want to use.
    Note I have used 6076 feet to the Nautical mile , this is an approximation too
    Speed up your code use variant arrays and NEVER ACCESS THE WORKSHEET IN A LOOP

  5. #5
    New Member
    Join Date
    Jun 2010
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Longitude Latitude Question

    For all intents and purposes - this is a "flat" earth case, as we are talking about 25-30 city blocks.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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