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
<tbody>
</tbody>
Table 2
<tbody>
</tbody>
Desired Output
<tbody>
</tbody>
Thank you in advance for the help.
Jon
Please find below sample tables (1 and 2) including a sample desired output.
Table 1
Location_ID_Table1 | LATITUDE_Table1 | LONGITUDE_Table1 |
Table1_0000001 | 32.7875116 | -96.8042723 |
Table1_0000002 | 32.9506823 | -97.0399645 |
Table1_0000003 | 32.9979846 | -96.7823867 |
Table1_0000004 | 32.8288923 | -97.4046827 |
Table1_0000005 | 32.8047396 | -96.6839268 |
Table1_0000006 | 32.804776 | -96.6839919 |
Table1_0000007 | 32.8048567 | -96.6839509 |
Table1_0000008 | 32.9463226 | -96.7004484 |
Table1_0000009 | 32.9554295 | -96.7115144 |
Table1_0000010 | 33.1568095 | -96.844646 |
<tbody>
</tbody>
Table 2
Location_ID_Table2 | LATITUDE_Table2 | LONGITUDE_Table2 |
Table2_0000001 | 32.7716152 | -97.1064349 |
Table2_0000002 | 32.6356708 | -96.9003413 |
Table2_0000003 | 32.6356984 | -96.9002808 |
Table2_0000004 | 32.6357211 | -96.9002707 |
Table2_0000005 | 32.6357226 | -96.9003323 |
Table2_0000006 | 32.6357466 | -96.9003532 |
Table2_0000007 | 32.6357724 | -96.9002676 |
Table2_0000008 | 32.6357726 | -96.9004123 |
Table2_0000009 | 32.6357788 | -96.9002719 |
Table2_0000010 | 32.6076538 | -97.0925508 |
<tbody>
</tbody>
Desired Output
Location_ID_Table1 | LATITUDE_Table1 | LONGITUDE_Table1 | Distance from closest point from Table 2 | Closest Location_ID_Table2 |
Table1_0000001 | 32.7875116 | -96.8042723 | 0.01 | Table2_0000014 |
Table1_0000002 | 32.9506823 | -97.0399645 | 1.00 | Table2_0000034 |
Table1_0000003 | 32.9979846 | -96.7823867 | 2.15 | Table2_0000678 |
Table1_0000004 | 32.8288923 | -97.4046827 | 0.15 | Table2_0000659 |
Table1_0000005 | 32.8047396 | -96.6839268 | 5.00 | Table2_0000012 |
Table1_0000006 | 32.804776 | -96.6839919 | 0.35 | Table2_0000032 |
Table1_0000007 | 32.8048567 | -96.6839509 | 10.56 | Table2_0000958 |
Table1_0000008 | 32.9463226 | -96.7004484 | 3.25 | Table2_0000026 |
Table1_0000009 | 32.9554295 | -96.7115144 | 1.65 | Table2_0001032 |
Table1_0000010 | 33.1568095 | -96.844646 | 0.89 | Table2_0000020 |
<tbody>
</tbody>
Thank you in advance for the help.
Jon