Find nearest distance from GPS coordinates

[Anonymous]

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!

[Anonymous · Accepted solution]

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!

[Greg_Deckler]

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

[amitchandak]

@Anonymous 

Refer :

https://stackoverflow.com/questions/27928/calculate-distance-between-two-latitude-longitude-points-haversine-formula

If this not what you are looking for @Greg_Deckler , Can help more

[Anonymous]

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!