Announcement

Collapse
No announcement yet.

Minimum Distance Calculation of Geographic Coordinates Using Array

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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, 08:01.

  • #2
    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.

    Comment


    • #3
      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

      Comment


      • #4
        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

        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, 22:56.

        Comment


        • #5
          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, 23:28.

          Comment


          • #6
            Re: Minimum Distance Calculation of Geographic Coordinates Using Array

            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

            Comment


            • #7
              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, 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.

              Comment


              • #8
                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, 01:00.

                Comment


                • #9
                  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

                  Comment


                  • #10
                    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.

                    Comment


                    • #11
                      Re: Minimum Distance Calculation of Geographic Coordinates Using Array

                      maybe this is easier...i'm not a fan of letting other people do the work but in this case, i think i would learn more about match + index by seeing the end result

                      Here it is:
                      Attached Files

                      Comment


                      • #12
                        Re: Minimum Distance Calculation of Geographic Coordinates Using Array

                        Originally posted by geobatman
                        maybe this is easier...i'm not a fan of letting other people do the work but in this case, i think i would learn more about match + index by seeing the end result

                        Here it is:

                        bump - still need help getting station name to show up.
                        In words i can describe what the if statment should be but cant get it to work

                        =if(distance formula=already calculated distance in d cell,station id,0)

                        Comment


                        • #13
                          Re: Minimum Distance Calculation of Geographic Coordinates Using Array

                          Hi Geobatman (and Derk)
                          ri
                          you certainly started a very interesting hare running with your original question. I respect the fact that you have found an alternative solution but I could not understand why both Derk and I came up with the same, incorrect, solution to the equation using the bearings for Whistler and Fredericton. Having exhausted all possible causes in the formula I checked the bearings for Fredericton and found them to be latitude 45.9167 and longitude -66.6, which improved things no end.

                          I only report this in case it was bugging Derk as much as it was bugging me and also, of course, it might help you in your current solution.

                          Thanks for creating so much amusement for me.

                          regards

                          Robert

                          Comment


                          • #14
                            Re: Minimum Distance Calculation of Geographic Coordinates Using Array

                            hello robert/derk

                            please post your EXCEL formula so that is able to be copy and pasted into a sample workbook...Or attach a sample workbook.

                            This way i can verify much easier

                            OR

                            Open the second workbook i attached and verify the formula in d2, in reference to your formula. thanks in advance
                            Last edited by geobatman; June 12th, 2006, 21:37.

                            Comment


                            • #15
                              Re: Minimum Distance Calculation of Geographic Coordinates Using Array

                              Originally posted by mariachichris
                              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
                              I have been told this cannot work.

                              Comment

                              Working...
                              X