Calculate Closest Point from 2 Large Datasets with Coordinates (Lat / Lon)

[ialvaran]

I would like to calculate the closest distance between two large datasets (tables). Table 1 and Table 2 have location IDs and coordinates (LAT / LON). I would like to know which data point in Table 2 is closest to the data points in Table 1. The calculation should also include the closest (or shortest) distance in meters or feet.

Please find below sample tables (1 and 2) including a sample desired output.

Table 1
[TABLE="width: 502"]
<tbody>[TR]
[TD]Location_ID_Table1[/TD]
[TD]LATITUDE_Table1[/TD]
[TD]LONGITUDE_Table1[/TD]
[/TR]
[TR]
[TD]Table1_0000001[/TD]
[TD]32.7875116[/TD]
[TD]-96.8042723[/TD]
[/TR]
[TR]
[TD]Table1_0000002[/TD]
[TD]32.9506823[/TD]
[TD]-97.0399645[/TD]
[/TR]
[TR]
[TD]Table1_0000003[/TD]
[TD]32.9979846[/TD]
[TD]-96.7823867[/TD]
[/TR]
[TR]
[TD]Table1_0000004[/TD]
[TD]32.8288923[/TD]
[TD]-97.4046827[/TD]
[/TR]
[TR]
[TD]Table1_0000005[/TD]
[TD]32.8047396[/TD]
[TD]-96.6839268[/TD]
[/TR]
[TR]
[TD]Table1_0000006[/TD]
[TD]32.804776[/TD]
[TD]-96.6839919[/TD]
[/TR]
[TR]
[TD]Table1_0000007[/TD]
[TD]32.8048567[/TD]
[TD]-96.6839509[/TD]
[/TR]
[TR]
[TD]Table1_0000008[/TD]
[TD]32.9463226[/TD]
[TD]-96.7004484[/TD]
[/TR]
[TR]
[TD]Table1_0000009[/TD]
[TD]32.9554295[/TD]
[TD]-96.7115144[/TD]
[/TR]
[TR]
[TD]Table1_0000010[/TD]
[TD]33.1568095[/TD]
[TD]-96.844646[/TD]
[/TR]
</tbody>[/TABLE]

Table 2
[TABLE="width: 411"]
<tbody>[TR]
[TD]Location_ID_Table2[/TD]
[TD]LATITUDE_Table2[/TD]
[TD]LONGITUDE_Table2[/TD]
[/TR]
[TR]
[TD]Table2_0000001[/TD]
[TD="align: right"]32.7716152[/TD]
[TD="align: right"]-97.1064349[/TD]
[/TR]
[TR]
[TD]Table2_0000002[/TD]
[TD="align: right"]32.6356708[/TD]
[TD="align: right"]-96.9003413[/TD]
[/TR]
[TR]
[TD]Table2_0000003[/TD]
[TD="align: right"]32.6356984[/TD]
[TD="align: right"]-96.9002808[/TD]
[/TR]
[TR]
[TD]Table2_0000004[/TD]
[TD="align: right"]32.6357211[/TD]
[TD="align: right"]-96.9002707[/TD]
[/TR]
[TR]
[TD]Table2_0000005[/TD]
[TD="align: right"]32.6357226[/TD]
[TD="align: right"]-96.9003323[/TD]
[/TR]
[TR]
[TD]Table2_0000006[/TD]
[TD="align: right"]32.6357466[/TD]
[TD="align: right"]-96.9003532[/TD]
[/TR]
[TR]
[TD]Table2_0000007[/TD]
[TD="align: right"]32.6357724[/TD]
[TD="align: right"]-96.9002676[/TD]
[/TR]
[TR]
[TD]Table2_0000008[/TD]
[TD="align: right"]32.6357726[/TD]
[TD="align: right"]-96.9004123[/TD]
[/TR]
[TR]
[TD]Table2_0000009[/TD]
[TD="align: right"]32.6357788[/TD]
[TD="align: right"]-96.9002719[/TD]
[/TR]
[TR]
[TD]Table2_0000010[/TD]
[TD="align: right"]32.6076538[/TD]
[TD="align: right"]-97.0925508[/TD]
[/TR]
</tbody>[/TABLE]

Desired Output
[TABLE="width: 841"]
<tbody>[TR]
[TD]Location_ID_Table1[/TD]
[TD]LATITUDE_Table1[/TD]
[TD]LONGITUDE_Table1[/TD]
[TD]Distance from closest point from Table 2[/TD]
[TD]Closest Location_ID_Table2[/TD]
[/TR]
[TR]
[TD]Table1_0000001[/TD]
[TD]32.7875116[/TD]
[TD]-96.8042723[/TD]
[TD]0.01[/TD]
[TD]Table2_0000014[/TD]
[/TR]
[TR]
[TD]Table1_0000002[/TD]
[TD]32.9506823[/TD]
[TD]-97.0399645[/TD]
[TD]1.00[/TD]
[TD]Table2_0000034[/TD]
[/TR]
[TR]
[TD]Table1_0000003[/TD]
[TD]32.9979846[/TD]
[TD]-96.7823867[/TD]
[TD]2.15[/TD]
[TD]Table2_0000678[/TD]
[/TR]
[TR]
[TD]Table1_0000004[/TD]
[TD]32.8288923[/TD]
[TD]-97.4046827[/TD]
[TD]0.15[/TD]
[TD]Table2_0000659[/TD]
[/TR]
[TR]
[TD]Table1_0000005[/TD]
[TD]32.8047396[/TD]
[TD]-96.6839268[/TD]
[TD]5.00[/TD]
[TD]Table2_0000012[/TD]
[/TR]
[TR]
[TD]Table1_0000006[/TD]
[TD]32.804776[/TD]
[TD]-96.6839919[/TD]
[TD]0.35[/TD]
[TD]Table2_0000032[/TD]
[/TR]
[TR]
[TD]Table1_0000007[/TD]
[TD]32.8048567[/TD]
[TD]-96.6839509[/TD]
[TD]10.56[/TD]
[TD]Table2_0000958[/TD]
[/TR]
[TR]
[TD]Table1_0000008[/TD]
[TD]32.9463226[/TD]
[TD]-96.7004484[/TD]
[TD]3.25[/TD]
[TD]Table2_0000026[/TD]
[/TR]
[TR]
[TD]Table1_0000009[/TD]
[TD]32.9554295[/TD]
[TD]-96.7115144[/TD]
[TD]1.65[/TD]
[TD]Table2_0001032[/TD]
[/TR]
[TR]
[TD]Table1_0000010[/TD]
[TD]33.1568095[/TD]
[TD]-96.844646[/TD]
[TD]0.89[/TD]
[TD]Table2_0000020[/TD]
[/TR]
</tbody>[/TABLE]


Thank you in advance for the help.

Jon

 

[DRSteele]

I believe what you need to do is create a formula that calculates all of the distances between all of the points. Review these threads and let us know whether or not they help.

https://www.mrexcel.com/forum/excel-questions/966498-create-territories.html

https://www.mrexcel.com/forum/excel...ongitude-coordinates-find-closest-office.html

credit is due here: BlueMM: Excel formula to calculate distance between 2 latitude, longitude (lat/lon) points (GPS positions)

 

[ialvaran]

Mr. Steele,

Thank you for the quick reply. I have been site searching Mr. Excel via Google and was not able to find a viable solution.

Your second link that you provided below (https://www.mrexcel.com/forum/excel-...st-office.html) helped. That is exactly what I am looking for. Good thing you also provided the distance between points in one of your replies.

I appreciate your help to the community.

Thanks again.

Jon

I believe what you need to do is create a formula that calculates all of the distances between all of the points. Review these threads and let us know whether or not they help.

https://www.mrexcel.com/forum/excel-questions/966498-create-territories.html

https://www.mrexcel.com/forum/excel...ongitude-coordinates-find-closest-office.html

credit is due here: BlueMM: Excel formula to calculate distance between 2 latitude, longitude (lat/lon) points (GPS positions)

 

[DRSteele]

You're welcome. I'm glad it helped.

Calculating the Great Circle Distance using those trig formulas allows us to create a grid of distances. Finding the shortest trips between them all is then a simple matter of INDEX and MATCH, I think.