Minimum Distance Calculation of Geographic Coordinates Using Array

[geobatman]

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

[Derk]

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?

[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,

[geobatman]

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

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,

[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

[geobatman]

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

[Derk]

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.

[geobatman]

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?

[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

[geobatman]

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.