Blog

Location - Finding the closest location and and sorting records by distance, based on the current location of the user

Given a data source that contains a list of locations, which record matches the location that is nearest to an input location? This is typical challenge we face when we want to build a feature that, for example, returns a list of stores that is sorted in distance relative to the current location of a customer. In this post, we'll walk through the formula that enables us to carry out this calculation.

Given a data source that contains a list of locations how do we find the record that matches the location nearest to where the user currently is? Also, how can we sort the records in a data source by the relative distance?

In this post, we'll walk through the formula that enables us to carry out this task

Demonstration of technique

To demonstrate this technique, let's take a map of the following underground stations. The latitude and longitude values are as follows:

Bayswater 51.512254, -0.187522
Queensway 51.510473, -0.186959
Lancaster gate 51.511721, -0.175290


Let's now assume that we're at the given co-ordinate:
51.512611, -0.182105

How do we find the closest underground station, and return a list of the stations sorted in terms of relative distance?

Setting-up our source data

In this example, we'll use the following collection as a data source. In practice, we could data from a SharePoint list or any other data source.

ClearCollect(colLocations,
             {StationName:"Bayswater", StationLat:51.512254, StationLong:-0.187522},
             {StationName:"Queensway", StationLat:51.510473, StationLong:-0.186959},
             {StationName:"Lancaster Gate", StationLat:51.511721, StationLong:-0.175290}
)

Building a formula to calculate and to sort records by distance

To carry out the distance calculation, we apply the Haversine formula that I described in my earlier post here.


We call the AddColumns function to add a column called "Distance" to our data source. We use the Haversine formula to caulate the relative distance between the static input coordinate, and the coordinates from the data source.

We can then apply the SortByColumns function around the return value of AddColumns to return the records sorted by distance ascending order. Here's our final formula. 

SortByColumns(
    AddColumns(
        colLocations,
        "Distance",
        With(
            {
                r: 6371,
                p: (Pi() / 180),
                latA: 51.512611,
                lonA: -0.182105,
                latB: StationLat,
                lonB: StationLong
            },
            (2 * r) * 
               Asin(Sqrt(0.5 - Cos((latA - latB) * p)/2 + Cos(latB * p) * 
               Cos(latA * p) * (1 - Cos((lonA - lonB) * p)) / 2))
        )
    ),
    "Distance",
    Ascending
)

Here, latA and lonA refer to the input coordinate. We can substitute this as required, or substitute these values with calls to Location.Latitude and Location.Longitude to return the current location of the user.

Testing this formula

To test this formula, the screenshot beneath shows the result when we set the items property of a data table to this formula. As we can see, the closest underground station is Bayswater, with a distance of 0.3km.

Conclusion

In this post, we looked at how to apply the Haversine formula to return a list of records, sorted by the distance relative to an input coordinate.
Previous
Next
Related posts