Find the nearest location for a customer

I have a table with customer names, customer location (latitude, longitude) and another list with store names and store location (latitude, longitude). For every customer I would like to get the name of the closest store and the distance to that store.

I have found a formula to calculate dynamic distances (from Phil Seamark) as follows:

Distance in Kilometers =
var Lat1 = MIN('From '[lat])
var Lng1 = MIN('From '[lng])

var Lat2 = MIN('To '[lat])
var Lng2 = MIN('To '[lng])
---- Algorithm here -----
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

The approach I was thinking of would go something like....create a calculated column on the customer table that passes the customer's location to a calculation that determines the distances to each of the stores and returns the store name and distance to the closest one using the formula above.

Would I somehow crossjoin all the store locations with the individual customer's location and then calculate the distance?

Hoping this solution may be of interest to others.

Thanks,

DaxAmateur

You're right - for each Customer you will have to iterate over the Stores table to find the closest one.

You can use MINX to do this iteration and return the distance to the closest store, and TOPN to return the name of the closest store.

I uploaded a dummy model here to illustrate.

Assume you have Customers and Stores tables with columns as follows:

Customers
- Customer, Latitude, Longitude
Stores
- Store, Latitude, Longitude

Then you can use the formula you've quoted in these calculated columns (I reorganised slightly so that 𝜋/180 is evaluated once per measure):

Distance to Closest Store (km) = 
VAR Lat1 = Customers[Latitude]
VAR Lng1 = Customers[Longitude]
VAR P =
    DIVIDE ( PI (), 180 )
RETURN
    MINX (
        Stores,
        VAR Lat2 = Stores[Latitude]
        VAR Lng2 = Stores[Longitude]
        //---- Algorithm here -----
        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
    )

Closest Store = 
VAR Lat1 = Customers[Latitude]
VAR Lng1 = Customers[Longitude]
VAR P =
    DIVIDE ( PI (), 180 )
RETURN
    CALCULATE (
        FIRSTNONBLANK ( Stores[Store], 0 ),
        // Arbitrary tie-break
        TOPN (
            1,
            Stores,
            VAR Lat2 = Stores[Latitude]
            VAR Lng2 = Stores[Longitude]
            //---- Algorithm here -----
            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,
            ASC
        )
    )

These could be re-written as measures to get the closest store to any of the currently selected customers.

9 Replies

OwenAuger
Super User
9 years ago
DaxAmateur

You're right - for each Customer you will have to iterate over the Stores table to find the closest one.
You can use MINX to do this iteration and return the distance to the closest store, and TOPN to return the name of the closest store.

I uploaded a dummy model here to illustrate.

Assume you have Customers and Stores tables with columns as follows:

Customers
Customer, Latitude, Longitude
Stores
Store, Latitude, Longitude
Then you can use the formula you've quoted in these calculated columns (I reorganised slightly so that 𝜋/180 is evaluated once per measure):

Distance to Closest Store (km) = VAR Lat1 = Customers[Latitude] VAR Lng1 = Customers[Longitude] VAR P = DIVIDE ( PI (), 180 ) RETURN MINX ( Stores, VAR Lat2 = Stores[Latitude] VAR Lng2 = Stores[Longitude] //---- Algorithm here ----- 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 )

Closest Store = VAR Lat1 = Customers[Latitude] VAR Lng1 = Customers[Longitude] VAR P = DIVIDE ( PI (), 180 ) RETURN CALCULATE ( FIRSTNONBLANK ( Stores[Store], 0 ), // Arbitrary tie-break TOPN ( 1, Stores, VAR Lat2 = Stores[Latitude] VAR Lng2 = Stores[Longitude] //---- Algorithm here ----- 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, ASC ) )

These could be re-written as measures to get the closest store to any of the currently selected customers.
- supporterer
  Regular Visitor
  8 years ago
  Thank you very much. I tweaked this a little to find the zip code instead. works great and saved me a lot of extra work.
  - Anonymous
    Not applicable
    6 years ago
    Can you share the zip code solution you created?
- DaxAmateur
  Frequent Visitor
  9 years ago
  I came up with a similar solution to the topN using cross join but your solution is way more elegant and avoids the circular reference bug I encountered. Thanks for spending time on this problem - really appreciate it. Now just trying to fully understand how it works!
- MAAbdullah47
  Helper V
  7 years ago
  Thank You So Much for your dedication.