Forum Discussion
Find nearest distance from GPS coordinates
Hi Everyone,
I have a table of GPS coordinates for each work site, which I've already converted to radians. What i need to do is calculate the distance to the nearest site from this list of sites. More explicitly, for site 1 i would calculate the distance of the other 2 coordinates, and report the minimum distance and the name of each site. Then repeating the process for each other ID.
My data looks like this:
| Id | LatRad | LonRad |
| 1 | 0.633824 | -1.93531 |
| 2 | 0.645031 | -1.91023 |
| 3 | 0.645697 | -1.90968 |
and i would expect a result like this:
| Id | LatRad | LonRad | MinDist | MinDistSiteId |
| 1 | 0.633824 | -1.93531 | 119.0945 | 2 |
| 2 | 0.645031 | -1.91023 | 4.746879 | 3 |
| 3 | 0.645697 | -1.90968 | 4.746879 | 2 |
I have worked out the DAX already to calculate the distance between 2 coordinate sets, with the following formula, and once it runs though all these for each site, i figure i'd have to take the minimum result. What i am looking for is a way to do this with some DAX or in power query. In reality, my list actually consist of 20,000 work sites. How i can setup a table or a measure to run through every other site ID using the formula i listed, to find the minium value.
=ACOS(COS(LAT1)*COS(LAT2)+SIN(LAT1)*SIN(LAT2)*COS(LON1-LON2))*6371
Any advice you could give me would be appreciated. Thanks so much!
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!
4 Replies
- Hi amitchandak, and thanks for your reply.
I'm actually comfortable with the whole distance calculation bit of this, i'm more wondering how i can setup a table or a measure to run through every other site ID using the formula i listed, to find the minium value.
If you have any tips for me that would be great.
Thanks!
Well, I do have a Going the Distance Quick Measure: https://community.powerbi.com/t5/Quick-Measures-Gallery/Going-the-Distance/td-p/963267 Your formula does not look quite right but I don't know, as long as it works for you.
Also, a Bearing in Mind Quick Measure: https://community.powerbi.com/t5/Quick-Measures-Gallery/Bearing-in-Mind/td-p/984499
But in your case you might be able to use something like:
MinDist Measure = VAR __id = MAX('Table'[Id]) VAR __FromLat = MAX('Table'[LatRad]) VAR __FromLon = MAX('Table'[LonRad]) VAR __Table = ADDCOLUMNS FILTER( ALL('Table'), 'Table'[Id]<>__id ), "__Dist",<your formula for calculating distance goes here> ) RETURN MINX(__Table,[__Dist])And:
MinDistSiteId Measure = VAR __id = MAX('Table'[Id]) VAR __FromLat = MAX('Table'[LatRad]) VAR __FromLon = MAX('Table'[LonRad]) VAR __Table = ADDCOLUMNS FILTER( ALL('Table'), 'Table'[Id]<>__id ), "__Dist",<your formula for calculating distance goes here> ) VAR __MinDist = MINX(__Table,[__Dist]) RETURN MINX(FILTER(__Table,[__Dist] = __MinDist),[Id])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!
