Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 20

Thread: Minimum Distance Calculation of Geographic Coordinates Using Array

  1. #1
    Join Date
    9th June 2006
    Posts
    14

    Minimum Distance Calculation of Geographic Coordinates Using Array

    Hello All,

    I am working on two spreadsheets, one we shall call Target Sheet and the other Reference Sheet.

    On the target sheet, I have a set of 200 geographic coordinates longitude and latitude (in decimal degrees). Each pair of coordinates coresponds to a geographic site.

    On the reference sheet, I have a set of 900 geographic coordinates longitude and latitude (in decimal degrees). Each pair of coordinates coresponds to a geographic site. Note that of the 900, 200 are the same from target sheet, other 700 are different sites.

    Using the spherical law of cosines, I can calculate the distance between a set of coordinates on target sheet agianst a set of coordinates on reference sheet. This allows me to see the distance from one geographic site, to the other. This was easy. The formula I use is as follows:

    =acos(sin(lat1*pi()/180)*sin(lat2*pi()/180)+cos(lat1*pi()/180)*cos(lat2*pi()/180)*cos(lon2*pi()/180-lon1*pi()/180)*R

    lat1 = coordinates from Target sheet in decimal degrees
    lat2 = coordinates from reference sheet in decimal degrees
    lon1 = coordinates from Target sheet in decimal degrees
    lon2 = coordinates from reference sheet in decimal degrees
    R = radius of the earth in kilometres

    ULTIMATE GOAL - On the target sheet, in one cell, I want to calculate the distance between ONE set of coordinates from the target sheet AGIANST all 900 coordinates on the reference sheet while ONLY returning the value which is less than or equal to 50km, does not equal zero and is the MINIMUM distance.

    I am certain this involves arrays which i know how to use (Basic ones) however when i try to do this I cannot get this and the flow of the syntax is very tricky for me.

    I am not sure if anyone can help but it would be very appreciated. Thank you if you need clarification, or examples I can provide. Thanks
    Last edited by geobatman; June 9th, 2006 at 08:01.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    15th April 2003
    Location
    USA
    Posts
    7,282

    Re: Minimum Distance Calculation of Geographic Coordinates Using Array

    Welcome to the OzGrid Forum!

    A macro should be able to do that for you. Can you attach a small sample workbook with, say, about 10 target sites and 30 reference sites. Also the value of R.

    What should be returned if no reference site is within 50km?
    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

  3. #3
    Join Date
    9th June 2006
    Posts
    14

    Re: Minimum Distance Calculation of Geographic Coordinates Using Array

    Hello Derk,

    Thanks for the welcome.
    Here is my sample. As you will see the Target and Reference are on different sheets in the same spreadsheet. In the real world, they Reference sheet will be its own file (basically the same thing)

    The value of R = 6378.137 (in kilometers)

    Lats and longs 1 - coming from target
    lats 2 and longs 2 - coming from reference.

    If no site is within 50km I would like it to say NONE, for now. Finding the next closest site (past 50km) would be ideal, however I can image much more complex.

    Thank you graciously for any help you can offer,
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    9th June 2006
    Posts
    14

    Re: Minimum Distance Calculation of Geographic Coordinates Using Array

    Using excel and the same sample above i entered in d2 (below can be c&p) + CSE

    =MIN(IF((ACOS(SIN(B2*PI()/180)*SIN(Reference!B$2:B$905*PI()/180)+COS(B2*PI()/180)*COS(Reference!B$2:B$905*PI()/180)*COS(Reference!C$2:C$905*PI()/180-C2*PI()/180))*6378.137<>0)*(ACOS(SIN(B2*PI()/180)*SIN(Reference!B$2:B$905*PI()/180)+COS(B2*PI()/180)*COS(Reference!B$2:B$905*PI()/180)*COS(Reference!C$2:C$905*PI()/180-C2*PI()/180))*6378.137>=50),1,"None"))

    In my result cell im getting all 1's which is a good sign, but I want to display the actual numerical result. Or perhaps i have the formula wrong all together!

    thnx

    Quote Originally Posted by geobatman
    Hello Derk,

    Thanks for the welcome.
    Here is my sample. As you will see the Target and Reference are on different sheets in the same spreadsheet. In the real world, they Reference sheet will be its own file (basically the same thing)

    The value of R = 6378.137 (in kilometers)

    Lats and longs 1 - coming from target
    lats 2 and longs 2 - coming from reference.

    If no site is within 50km I would like it to say NONE, for now. Finding the next closest site (past 50km) would be ideal, however I can image much more complex.

    Thank you graciously for any help you can offer,
    Last edited by geobatman; June 9th, 2006 at 22:56.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    14th July 2004
    Location
    Berkshire UK
    Posts
    426

    Re: Minimum Distance Calculation of Geographic Coordinates Using Array

    Hi

    before getting into any coding could you tell me how far it is from FREDERICTON CDA CS to Whistler, just so I know I am dealing with an accurate rendition of your formula

    regards

    Robert
    Last edited by Robert B; June 9th, 2006 at 23:28.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    9th June 2006
    Posts
    14

    Re: Minimum Distance Calculation of Geographic Coordinates Using Array

    Quote Originally Posted by Robert B
    Hi

    before getting into any coding could you tell me how far it is from FREDERICTON CDA CS to Whistler, just so I know I am dealing with an accurate rendition of your formula

    regards

    Robert
    Hello Robert,
    Calculating just the GCD between fredericton cda cs to whistler should equal

    4120.415359 km

    I think the best way to test the formula (what i did) is a sample of coordinates that only change 1 degrees difference in latitude. This should yeild around 111.325 km

    thanks for all help

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    15th April 2003
    Location
    USA
    Posts
    7,282

    Re: Minimum Distance Calculation of Geographic Coordinates Using Array

    Hmm. There is a missing ) in your formula. I thouht it belonged just before the *R, but when i put it there I get 17,825km rather than your 4,120. Would you repost your formula please? I do get the 111 for a 1 degree change in latitude.
    Last edited by Derk; June 10th, 2006 at 00:38.
    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

  8. #8
    Join Date
    9th June 2006
    Posts
    14

    Re: Minimum Distance Calculation of Geographic Coordinates Using Array

    the last thing i want to do is waste anyones time. Therefore i ask that no one goes to the trouble to make a macro. Primarly due to me not being familar with VB (never used it but will learn this summer), it would probably complicate things if changes ever needed to be made.

    in addition, i found solved what i wanted such that in in the result column i now have the minimum distance that fits my parameters. In excel, this gives the result:

    =MIN(IF((ACOS(SIN(B2*PI()/180)*SIN(Reference!B$2:B$905*PI()/180)+COS(B2*PI()/180)*COS(Reference!B$2:B$905*PI()/180)*COS(Reference!C$2:C$905*PI()/180-C2*PI()/180))*6378.137<>0)*(ACOS(SIN(B2*PI()/180)*SIN(Reference!B$2:B$905*PI()/180)+COS(B2*PI()/180)*COS(Reference!B$2:B$905*PI()/180)*COS(Reference!C$2:C$905*PI()/180-C2*PI()/180))*6378.137<=50),(ACOS(SIN(B2*PI()/180)*SIN(Reference!B$2:B$905*PI()/180)+COS(B2*PI()/180)*COS(Reference!B$2:B$905*PI()/180)*COS(Reference!C$2:C$905*PI()/180-C2*PI()/180))*6378.137),9999))

    However, I do need help on a vlookup function. Now that i calculated the minimum distance between sites, it cooresponds to a site name. By this i mean:

    If the closest site (determined by computation) to whistler is "xxxx", i want "xxxx" to appear in a column. The reason for this is that although i now have a distance computation, it is still relatively meaningless

    How would i go about displaying which site the computation corresponds to?
    Last edited by geobatman; June 10th, 2006 at 01:00.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    9th November 2005
    Posts
    14

    Re: Minimum Distance Calculation of Geographic Coordinates Using Array

    Geobatman,

    It looks like you should use an "Index" and "Match" combination for this because you are not looking for exact matches but for the location name that is the closest to a certain value away from the original destination. Remember to qualify the 3rd argument in the "Match" function as either "1" or "2" and not "0" which indicates an exact match.

    -Chris

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    9th June 2006
    Posts
    14

    Re: Minimum Distance Calculation of Geographic Coordinates Using Array

    hi chris thank you

    can you go into a bit more detail as to what i want to index and what i want to match?

    from reading the help file i want to index my distance results, and match my site ID with ??

    i'm a little confused.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Geographic Coordinate Data Types / Formats
    By parnuna in forum EXCEL HELP
    Replies: 8
    Last Post: February 21st, 2008, 08:31
  2. Calculate Shortest Distance From Coordinates
    By Heresy in forum EXCEL HELP
    Replies: 18
    Last Post: December 14th, 2007, 19:19
  3. Data type of coordinates of 2-dimensional array
    By wildwichtel in forum EXCEL HELP
    Replies: 4
    Last Post: April 29th, 2007, 16:42
  4. Find Minimum in large array
    By aspenboy in forum EXCEL HELP
    Replies: 5
    Last Post: December 21st, 2005, 21:26
  5. VBA Array calculation
    By lafayette48 in forum EXCEL HELP
    Replies: 1
    Last Post: December 21st, 2005, 01:41

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
  •  
porno