Closest point for each point in a same table based on Lat and Long

[Postman848]

Hi,

I would like to calculate the distance to the closest point for each point in my dataset based on Lat and Long. 

My data (example below) has three columns Name, Latitude and Longitude. I would like to add 4th column "Distance to the closest point".

The closest point of course cannot be the distance to the point itself (=0km)

Name	Latitude	Longitude	Distance to the closest point
A	62.1716286	43.865615
B	62.2159089	43.7522112
C	62.209831	43.7682273
D	62.1682624	43.8669002
E	64.002309	43.8041308
F	65.1678661	44.3022492
G	65.5869692	43.6146272

All the help is more than welcome.

Thanks in advance!

[parry2k]

@Postman848 tweak this code:

//change following line to whatever you feel file
ALLSELECTED ( 'Table' ), 

FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Type] = "Your Value" ) 

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@Postman848 and this if you want it as a measure and I prefer measures but will leave it up to you

Measure 2 = 
VAR __baseLat = MIN ( 'Table'[Latitude] )
VAR __baseLng = MIN ( 'Table'[Longitude] )
VAR __distance = 
ADDCOLUMNS ( 
    ALLSELECTED ('Table' ), 
    "Distance",
    var Lat1 = __baseLat
    var Lng1 = __baseLng
    var Lat2 = 'Table'[Latitude]
    var Lng2 = 'Table'[Longitude]
    var P = DIVIDE ( PI(), 180 )
    var A = 0.5 - COS ( ( Lat2-Lat1 ) * p ) /2 + 
        COS ( Lat1 * p ) * COS ( lat2 * P ) * ( 1 - COS ( ( Lng2- Lng1 ) * p ) ) / 2
    var final = 12742 * ASIN ( ( SQRT ( A ) ) )
    return final
)
return MINX ( __distance, IF ( [Distance] > 0, [Distance] ) )

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k · Accepted solution]

@Postman848 try this, if you want to add a shortest distance as a column

Shortest Distance as Column = 
VAR __baseLat = 'Table'[Latitude]
VAR __baseLng = 'Table'[Longitude] 
VAR __distance = 
ADDCOLUMNS ( 
    ALLSELECTED ( 'Table' ), 
    "Distance",
    var Lat1 = __baseLat
    var Lng1 = __baseLng
    var Lat2 = 'Table'[Latitude]
    var Lng2 = 'Table'[Longitude]
    var P = DIVIDE ( PI(), 180 )
    var A = 0.5 - COS ( ( Lat2-Lat1 ) * p ) /2 + 
        COS ( Lat1 * p ) * COS ( lat2 * P ) * ( 1 - COS ( ( Lng2- Lng1 ) * p ) ) / 2
    var final = 12742 * ASIN ( ( SQRT ( A ) ) )
    return final
)
return 
MINX ( __distance, IF ( [Distance] > 0, [Distance] ) )

[Postman848]

Thanks for the solution!! Works very nicely! I am actually having one extra column that is telling the type of the location. Is there easy way to modify code to calculate the distance to the nearest of certain type?

[parry2k]

@Postman848 does this looks correct:

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Postman848]

I guess so. Data values in the table are modified from the original dataset. 

Could you share your solution?

[parry2k]

@Postman848 that's pretty small, I was thinking in millions. Anyhow, jumping on a few meetings, if someone doesn't provide a solution in the next few hours I will be on it. Hang tight.

[parry2k]

@Postman848 I have a solution in mind but before I post the solution, can you tell me how large is your dataset? Want to make sure the solution performs well.

[Postman848]

Looking for your solution eagerly. Dataset is a bit over 1000 rows. Thus, not a massive set.