Spatial Join

[John896478]

Bonjour,

Sur Power Bi, je voudrai s'il vous plaît faire une jointure spatiale entre les coordonnées géographiques des clients (table 1) et les coordonnées géograhiques des points enregistrés dans une balise GPS sur un véhicule de livraison de marchandise (table 2), en utilisant aussi la formule de Haversine (au plus 10m) + Plus-proche-voisin afin de géolocaliser les clients livrés à partir de la balise GPS dans le but de calculer les couts réels de livraison.

Merci d'avance.

[v-pgoloju]

Hi @John896478,

Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[v-pgoloju]

Hi @John896478,

Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to @lbendlin and @tharunkumarRTK  for prompt and helpful responses.

Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[lbendlin]

geographical proximity may not mean much (imagine both locations being on separate islands with no bridges and no ferry service).  You will likely want to calculate actual distance based on road networks and road conditions. That's something you need to do with custom visuals or outside of Power BI.

[tharunkumarRTK]

@John896478 

To calculate the distance between two geograpical latitude and longitude locations using Haversine formula, you can follow the below dax pattern

FromLat = SELECTEDVALUE('From City'[lat]) 
FromLon = SELECTEDVALUE('From City'[lng]) 

ToLat = SELECTEDVALUE('To City'[lat]) 
Tolon = SELECTEDVALUE('To City'[lng]) 


Distance (km) = 
// Haversine/great-circle distance calculation adapted from Stack Overflow: https://stackoverflow.com/questions/27928/calculate-distance-between-two-latitude-longitude-points-haversine-formula
VAR __latSelected = [FromLat]
VAR __lonSelected = [FromLon]
VAR __radius = 6371
VAR __multiplier = PI()/180
VAR __latDiff = (KeyMeasuresTable[ToLat]-__latSelected) * __multiplier
VAR __lonDiff = (KeyMeasuresTable[Tolon]-__lonSelected) * __multiplier
VAR __formula1 =
    SIN(__latDiff/2) * SIN(__latDiff/2) +
    COS(KeyMeasuresTable[ToLat] * __multiplier) * COS(__latSelected * __multiplier) *
    SIN(__lonDiff/2) * SIN(__lonDiff/2)
VAR __formula2 = 2 * ATAN(DIVIDE(SQRT(__formula1),SQRT(1-__formula1)))
VAR __distance = __radius * __formula2
RETURN __distance 

By calculating the distance between the cutomer and the delivery partner as per their geo location, you will able to identify the deliver partner who is nearest to the cusotmer. I believe this way you will be able to perform the spatial join. 

Connect on LinkedIn

read my blogs here: techietips.co.in

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!