Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    6 years ago

    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: 

    Id1LatRad1LonRad1Id2LatRad2LonRad2
    10.633824-1.9353110.633824-1.93531
    10.633824-1.9353120.645031-1.91023
    10.633824-1.9353130.645697-1.90968
    20.645031-1.9102310.633824-1.93531
    20.645031-1.9102320.645031-1.91023
    20.645031-1.9102330.645697-1.90968
    30.645697-1.9096810.633824-1.93531
    30.645697-1.9096820.645031-1.91023
    30.645697-1.9096830.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:

     

    Id1LatRad1LonRad1AllRowsGroup
    10.633824-1.93531[Table]
    20.645031-1.91023[Table]
    30.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.

     

    Id1LatRad1LonRad1MidDistToSite.Id2MidDistToSite.DistanceToTank
    10.633824-1.935312119.1155
    20.645031-1.9102334.737717
    30.645697-1.9096824.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!

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

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])