+ Reply to Thread
Results 1 to 6 of 6

Longtitude and Latitude

  1. #1
    Registered User
    Join Date
    04-18-2012
    Location
    Rogers, AR
    MS-Off Ver
    Excel 2010
    Posts
    4

    Longtitude and Latitude

    I am currently working on this project and I need help. I have about 100 different variables with lats and long assigned to them. I used the equation below to find the distance from point a to point b, but now I am trying to come up with an additional equation that when I pick one of the variables, I can enter whatever miles I want and it will return all variables with lats and long within that circumference of the distance that I've entered. The equation I used for finding the miles is below:

    =ACOS(COS(RADIANS(90- LATITUDE OF A)) *COS(RADIANS(90- LATITUDE OF B)) +SIN(RADIANS(90- LATITUDE OF A)) *SIN(RADIANS(90- LATITUDE OF B)) *COS(RADIANS(LATITUDE OF A- LONGTITUDE OF B))) *3958.756


    Thanks to whomever read/response..

  2. #2
    Forum Contributor tkowal's Avatar
    Join Date
    11-20-2010
    Location
    Miami, Fl
    MS-Off Ver
    Excel 2010
    Posts
    150

    Re: Longtitude and Latitude

    This is accurate for small distances: http://williams.best.vwh.net/avform.htm#Dist

    or a more accurate formula use: http://en.wikipedia.org/wiki/Vincenty%27s_formulae
    Ted
    "Live Long and Prosper"

  3. #3
    Forum Contributor tkowal's Avatar
    Join Date
    11-20-2010
    Location
    Miami, Fl
    MS-Off Ver
    Excel 2010
    Posts
    150

    Re: Longtitude and Latitude

    Generally speaking you need not only a distance but a bearing.... So guessing you are trying to find Places within a distance of a know point.....

    I use GIS....so I do not have any excel examples.

    However suppose you want to find places within a distance d= 1000 km from M=(lat, long)=(1.3963, -0.6981). Lat long coordinates are in radians. A test to see whether or not a specifice LAT Long is within the distance from the point could be determined:

    if this is true then the point is within the circle described by d away from M
    acos(sin(1.3963) * sin(Lat_being_tested) + cos(1.3963) * cos(Lat_being_tested) * cos(Long_being_tested - (-0.6981))) * 6371 <= d

    d = distance = in this example would be 1000


    Hope this helps somewhat...

  4. #4
    Registered User
    Join Date
    04-18-2012
    Location
    Rogers, AR
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Longtitude and Latitude

    Thanks Ted for your response. But it seems that is trying to find the distance from point A to point B and not tell me what I am trying to solve.

  5. #5
    Registered User
    Join Date
    04-18-2012
    Location
    Rogers, AR
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Longtitude and Latitude

    Thanks again Ted from your prompt response. The new formula you just posted narrows it down for what I am trying to do, but I notice that you have Lat_being_tested and long_being_tested in the formula. What if I don't have Lat and Long being tested and all I want to do is to find places within 1000 miles radius from M(1.3963, -0.6981). But I have lats and longs for like 100 different variable and the mileage from one variables to the other. I am hoping I can find a formular that will tell me how many variables are within a certain one and will also return the mileage as well. Example of the answer will be:

    Store A
    Lat 2.3664 Long -74.6464

    Store Z 10 miles away
    Store T 17.3 Miles away
    Store D 45.5 Miles away
    etc
    Last edited by Mastermind4real; 04-18-2012 at 04:55 PM.

  6. #6
    Forum Contributor tkowal's Avatar
    Join Date
    11-20-2010
    Location
    Miami, Fl
    MS-Off Ver
    Excel 2010
    Posts
    150

    Re: Longtitude and Latitude

    The Lat/Long Tested in you example would be the lat long of the stores Z,T & D the formula will calculate the distance between A and those stores. Then from that distance you can determine which ones you are interested in...

    The M is simple your point of reference you want to base you distances from say from your example Store A

    Note that you will need to convert the metric values in the formula I gave to english units if you are interested miles.

    Maybe sending me a small sample of data you are working with along with your expected results would help me better understand what you are looking for.
    Last edited by tkowal; 04-19-2012 at 04:41 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1