Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Thread: Minimum Distance Calculation of Geographic Coordinates Using Array

1. I agreed to these rules
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. 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?

3. I agreed to these rules
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.

Excel Video Tutorials / Excel Dashboards Reports

4. I agreed to these rules
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

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.

Last edited by geobatman; June 9th, 2006 at 22:56.

Excel Video Tutorials / Excel Dashboards Reports

5. Established Member
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. I agreed to these rules
Join Date
9th June 2006
Posts
14

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

Excel Video Tutorials / Excel Dashboards Reports

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 at 00:38.

8. I agreed to these rules
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. 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. I agreed to these rules
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

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

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