Compare list of Lat Long

[mleikin]

I have a list of 800 lat/long coordinates. I need to see how many of those 800 are within 3 miles of another list of 7,000 lat/long coordinates.

I have found the following formular to find the distance between 2 lat/longs:
=ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-Lat2)) +SIN(RADIANS(90-Lat1)) *SIN(RADIANS(90-Lat2)) *COS(RADIANS(Long1-Long2))) *3958.756

Is there a formula that would allow me to run each of the 800 Lat/longs against the list of 7,000 and indicate how many on the original list have a match within 3 miles?

I was thinking a combination of countif with an array formula, but I can get the order of operations correct. Any help?

 

[GlennUK]

If you have named ranges for all 7000 lats and longs, like LatAll and LongAll, then use an array formula ( entered using Ctrl-Shift-Enter ), like:

=MIN(ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-LatAll)) +SIN(RADIANS(90-Lat1)) *SIN(RADIANS(90-LatAll)) *COS(RADIANS(Long1-LongAll))) *3958.756)

That will get the closest location in the 7000 for Lat1/Long1 ... so use that on each row of the 800, then use COUNTIF on those results to see how many are within 3 miles.

 

[mleikin]

That worked great. Thank you!

 

[GlennUK]

That's great! I'm so glad it worked.

 

[skratshee]

When I tried that equation it gives me a distance.. what if I want it to give me the name of the place from column A for example.. ?

 

[GlennUK]

Are you doing that formula against a list, for a single point? If so, then are you trying to find the placename that has the shortest distance result? To do that you'd probably calc the minimum result from the list, and then do an INDEX(MATCH on that, like:
=INDEX(A1:A7000,MATCH(MIN(B1:B7000),B1:B7000,0))

... but that's a guess, as you haven't given much information.

 

[skratshee]

Here is a sample of my sheet. I need to compare each row in sheet Origin with all the rows in sheet station and then return the name of the closest station from column A sheet station to column H sheet Origin.
What do you think ?

Sheet Origin

Alabama Abanda -85.527029 33.091627
Alabama Abbeville -85.259634 31.564216
Alabama Adamsville -86.97465 33.605751
Alabama Addison -87.177901 34.202689
Alabama Akron -87.741757 32.879417
Alabama Alabaster -86.82308 33.214355
Alabama Albertville -86.21066 34.26313
Alabama Alexander City -85.937122 32.92724
Alabama Alexandria -85.879596 33.765175
Alabama Aliceville -88.159445 33.123744


Sheet Stations

AQW00061705 -170.7136 -14.3306
CAW00064757 -79.7811 44.2325
CQC00914080 145.7497 15.2136
CQC00914801 145.2428 14.1717
FMC00914395 162.9533 5.3544
FMC00914419 153.8167 5.5167
FMC00914446 138.1786 9.6053
FMC00914482 158.3064 6.8456
FMC00914720 160.7 6.2167
FMC00914761 149.2 7.35
FMC00914831 138.15 9.55
FMC00914892 139.8 10.0333
FMC00914898 162.9742 5.2739
FMC00914911 143.9167 7.3833
FMW00040308 138.0833 9.4833

 

[GlennUK]

Assuming that you already have the distance result in cell E1, then this array formula ( entered using Ctrl-Shift-Enter ) may work:

=INDEX(StationsList,MATCH(E1,ACOS(COS(RADIANS(90-C1)) *COS(RADIANS(90-StationLats)) +SIN(RADIANS(90-C1)) *SIN(RADIANS(90-StationLats)) *COS(RADIANS(D1-StationLongs))) *3958.756,0))

I've used defined names for the list of stations, and the list of station latitudes and station longitudes.

 

[skratshee]

Actually that array formula didn't work.
This is a sample of the sheets I have. May be that will give you a better idea.

https://rapidshare.com/files/2455467557/sample.xlsm

Sorry I couldn't load it directly here.

Thank for your help.

 

[GlennUK]

It works for me:

Excel Workbook
	A	B	C	D	E	F	G	H
1	1	AL	Alabama	Abanda	-85.527	33.09163	3.247743	USC00010430
2	2	AL	Alabama	Abbeville	-85.2596	31.56422	5.648678	USC00013761
3	3	AL	Alabama	Adamsville	-86.9747	33.60575	2.414256	USC00010764
4	4	AL	Alabama	Addison	-87.1779	34.20269	0.30073	USC00010063
5	5	AL	Alabama	Akron	-87.7418	32.87942	2.668638	USC00018178
Origin