Forum Discussion
Find nearest distance from GPS coordinates
Hi everyone,
I've arrived at the solution on my own in power query, with the following steps:
I created a custom column which a trivial value and self joined the table on that value creating a row for each existing row as follows:
Id1 LatRad1 LonRad1 Id2 LatRad2 LonRad2 1 0.633824 -1.93531 1 0.633824 -1.93531 1 0.633824 -1.93531 2 0.645031 -1.91023 1 0.633824 -1.93531 3 0.645697 -1.90968 2 0.645031 -1.91023 1 0.633824 -1.93531 2 0.645031 -1.91023 2 0.645031 -1.91023 2 0.645031 -1.91023 3 0.645697 -1.90968 3 0.645697 -1.90968 1 0.633824 -1.93531 3 0.645697 -1.90968 2 0.645031 -1.91023 3 0.645697 -1.90968 3 0.645697 -1.90968 I used the following formula in a custom column to calculate the distance from each set of cooridnates
Number.Acos(Number.Cos([LatRad1])*Number.Cos([LatRad2])+Number.Sin([LatRad1])*Number.Sin([LatRad2])*Number.Cos([LonRad1]-[LonRad2]))*6371Then made another custom column to filter out when the distance to a work site would be measured against itself:
if [Id1] = [Id2] then 1 else nullThen grouped all rows by the first 3 columns to achieve the following:
Id1 LatRad1 LonRad1 AllRowsGroup 1 0.633824 -1.93531 [Table] 2 0.645031 -1.91023 [Table] 3 0.645697 -1.90968 [Table] I created another custom column to claulcate the minium distnace in the grouped rows
Table.Min([AllRowsGroup], "DistanceToTank")Finally i expanded the column to achieve the final result.
Id1 LatRad1 LonRad1 MidDistToSite.Id2 MidDistToSite.DistanceToTank 1 0.633824 -1.93531 2 119.1155 2 0.645031 -1.91023 3 4.737717 3 0.645697 -1.90968 2 4.737717 Thank you to everyone who gave their recomendations. Just an FYI to those that might attempt this themselves, that i had to convert the GPS cooridnates to radians before i was able to use this formula. If i hadn't done this first you could easilly augment the distance formula above to convert those in-line.
Thanks again everyone!
Hi everyone,
I've arrived at the solution on my own in power query, with the following steps:
I created a custom column which a trivial value and self joined the table on that value creating a row for each existing row as follows:
| Id1 | LatRad1 | LonRad1 | Id2 | LatRad2 | LonRad2 |
| 1 | 0.633824 | -1.93531 | 1 | 0.633824 | -1.93531 |
| 1 | 0.633824 | -1.93531 | 2 | 0.645031 | -1.91023 |
| 1 | 0.633824 | -1.93531 | 3 | 0.645697 | -1.90968 |
| 2 | 0.645031 | -1.91023 | 1 | 0.633824 | -1.93531 |
| 2 | 0.645031 | -1.91023 | 2 | 0.645031 | -1.91023 |
| 2 | 0.645031 | -1.91023 | 3 | 0.645697 | -1.90968 |
| 3 | 0.645697 | -1.90968 | 1 | 0.633824 | -1.93531 |
| 3 | 0.645697 | -1.90968 | 2 | 0.645031 | -1.91023 |
| 3 | 0.645697 | -1.90968 | 3 | 0.645697 | -1.90968 |
I used the following formula in a custom column to calculate the distance from each set of cooridnates
Number.Acos(Number.Cos([LatRad1])*Number.Cos([LatRad2])+Number.Sin([LatRad1])*Number.Sin([LatRad2])*Number.Cos([LonRad1]-[LonRad2]))*6371
Then made another custom column to filter out when the distance to a work site would be measured against itself:
if [Id1] = [Id2] then 1 else null
Then grouped all rows by the first 3 columns to achieve the following:
| Id1 | LatRad1 | LonRad1 | AllRowsGroup |
| 1 | 0.633824 | -1.93531 | [Table] |
| 2 | 0.645031 | -1.91023 | [Table] |
| 3 | 0.645697 | -1.90968 | [Table] |
I created another custom column to claulcate the minium distnace in the grouped rows
Table.Min([AllRowsGroup], "DistanceToTank")
Finally i expanded the column to achieve the final result.
| Id1 | LatRad1 | LonRad1 | MidDistToSite.Id2 | MidDistToSite.DistanceToTank |
| 1 | 0.633824 | -1.93531 | 2 | 119.1155 |
| 2 | 0.645031 | -1.91023 | 3 | 4.737717 |
| 3 | 0.645697 | -1.90968 | 2 | 4.737717 |
Thank you to everyone who gave their recomendations. Just an FYI to those that might attempt this themselves, that i had to convert the GPS cooridnates to radians before i was able to use this formula. If i hadn't done this first you could easilly augment the distance formula above to convert those in-line.
Thanks again everyone!