3

There are two tables, each with a coordinate pair: longitude and latitude, in decimal degrees (i.e. 37.23222, -121,3433). How do I match table A with its nearest coordinate pair in table B?

ZygD
  • 22,092
  • 39
  • 79
  • 102
Bergen88
  • 157
  • 2
  • 4
  • 14

2 Answers2

11

You could try filling down this formula from G1 as shown below:

=LOOKUP(1,1/FREQUENCY(0,MMULT((B$1:C$10-E1:F1)^2,{1;1})),A$1:A$10)

For a more accurate formula that takes account of the circular distances try filling down from H1:

=LOOKUP(1,1/FREQUENCY(0,SIN((RADIANS(B$1:B$10-E1))/2)^2+SIN((RADIANS(C$1:C$10-F1))/2)^2*COS(RADIANS(B$1:B$10))*COS(RADIANS(E1))),A$1:A$10)

enter image description here

lori_m
  • 5,487
  • 1
  • 18
  • 29
  • The first formula was accurate. I compared the results by mapping and spatial-joining the data. the 1st formula results were 100% match with the mapped results. – Bergen88 Feb 07 '15 at 19:36
  • 2
    The first formula is only accurate if you treat longitude and latitude as a unit x and unit y change. For locations on the surface of the earth this will only be a reasonable approximation near the tropics. The second formula is based on a standard formula for spherical distances: http://en.wikipedia.org/wiki/Haversine_formula – lori_m Feb 07 '15 at 21:54
2

This problem was solved back in 2006 by Tom Ogilvy here, also found here.

The sample that I created:

enter image description here

Original problem:

I have 20 named locations in a coordinates. Every point have x,y.

column a has the location name
column b has the x cooedinate
column c has the y coordinate

Now i have 400 coordinates in 2 columns (column e and f ) and want to have the name of the closest location (of the 20 named in column a) in column g.

Original solution by Tom Ogilvy:

Assuming original data starts in A1, B1 and C1 with first locations in E1 and F1

If you don't want to fill your sheet with formulas, you can put this in G1 committed/entered with Ctrl+Shift+Enter rather than just enter since it is an array formula, then drag fill it down the 400 rows.

=INDEX($A$1:$A$20,MATCH(MIN(SQRT(($B$1:$B$20-E1)^2+($C$1:$C$20-F1)^2)),SQRT( ($B$1:$B$20-E1)^2+($C$1:$C$20-F1)^2),0),1)

ZygD
  • 22,092
  • 39
  • 79
  • 102