+ Reply to Thread
Results 1 to 5 of 5

Minimum Distance

  1. #1
    Registered User
    Join Date
    07-20-2011
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2010
    Posts
    3

    Lightbulb Minimum Distance

    Hello,

    I am trying to write a vba function that finds the smallest distance between two large lists of gps coordinates. I already have a basic formula for calculating each of these, but I need to find this efficient function for determining the next closest gps coordinate and by how much. I have done a little bit of vba programming, though I have done more java in the past. I know I will need some type of loop where it looks to determine whether the newly calculated value is smaller than the current running minimum and if not, skip to the next one. I have some rough "pseudocode" of what i'm looking for.

    minDistance(range,originalCoords)
    define min double = 0
    begin loop
    dist = originalCoords - nextCoords \\The actual calculation is a little more
    if dist < min \\complicated but it should work
    dist = min
    loop
    end loop

    I know there's probably a lot missing, including some reference that stops the loop when it hits the last set of coordinates to calculate and something to return the min value. The value nextCoords would be my next listed coordinates to calculate the distance between from the original. Also, I have one cell for latitude and one for longitude, if that is any help.
    Any feedback and help would be greatly appreciated!
    Thanks!

    P.S. if this is listed on another forum topic already, let me know and I will go to there for help!

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Minimum Distance

    Welcome to the forum.

    How large are the two lists?

    All you want to now is the minimum distance, not the nearest point?

    You want to calculate great-circle distance?

    Post a workbook with some data.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    07-20-2011
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Minimum Distance

    The lists are about 400 locations in total and i'm trying to find the minimum distance to the next coordinate for each. I am using the great-circle distance formula for each of these but there is 399 calculations for each 400 locations, so that's why I feel some vba programming is necessary. Here is an attached worksheet with a sample of 5 locations, using a rudimentary method to find each of the minimum, but with 400 locations, excel runs out of room and this would take quite a while to accomplish.

    test min dist.xls

    Update: I ended up making 400 macros (yes, it took me 5 hours, but I got it done) and got my data, but I still want to know how to write a function in vba to do this for me because I will definitely be using this kind of method in future research of my own.

    Update 2: I started looking further into VBA and I have come up with some code that seems like it should work. Here it is:

    Function FindMinDist(Lat1 As Double, Lon1 As Double)
    Dim Index As Integer, Min As Double, Dist As Double
    Index = 3
    Min = 500
    While Index < 404
    Index = Index + 1
    Dist = WorksheetFunction.Acos(Cos(WorksheetFunction.Radians(90 - Lat1)) * Cos(WorksheetFunction.Radians(90 - Worksheet.Cells(Index, 3))) + Sin(WorksheetFunction.Radians(90 - Lat1)) * Sin(WorksheetFunction.Radians(90 - Worksheet.Cells(Index, 3))) * Cos(WorksheetFunction.Radians(Lon1 - Worksheet.Cells(Index, 3)))) * 3958.756
    If Dist < Min And Dist <> 0 Then
    Min = Dist
    End If
    Wend
    FindMinDist = Min
    End Function

    When I try using the function, it gives me back #VALUE! in the cell. I have my latitude in C4:C403 & longitude in D4:D403. Any ideas on how to make this work properly?
    Last edited by Maddogwoof; 07-22-2011 at 11:06 AM. Reason: Update 2

  4. #4
    Registered User
    Join Date
    07-20-2011
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Minimum Distance

    Figured it out! All good to go! Wrote it myself. Here it is if anyone is interested:

    Please Login or Register  to view this content.
    Last edited by shg; 07-23-2011 at 01:02 PM. Reason: added code tags

  5. #5
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Minimum Distance

    Another way:

    Please Login or Register  to view this content.
    By passing the range containing the other lat-longs, Excel sees a dependency and will automatically recompute when the data changes. Example usage:

    =FindMinDist(D2, E2, D4:E392)

    The value returned is in radians, which means you can multiply by the earth radius in your preferred units (meters, miles, nautical miles, ...) to get linear (great circle) distance.
    Last edited by shg; 07-23-2011 at 01:36 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