# Blog

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

January 24. 2021

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.

__Related posts__

- Formula - What to do when the If statement doesn't work?
- Formula - Boolean And / Or operators - What is the order of precedence?
- Controls - How to set the data source of a Combo Box to a comma separated string
- Numbers - 10 examples of how to round numbers
- Formula - Difference between round, square, and curly brackets
- Top 3 highlights of upcoming enhancements to the Power Apps language (Power FX)
- Email - Sending email attachments with the Office 365 Outlook connector
- Formula - What to try when numbers don't format correctly
- Controls - How to convert HTML to Text
- Formulas - how to return all days between two dates
- Formula - How to create comma separated (CSV) list of items
- Formula - How to use the IF and Switch functions - 3 common examples
- Formulas - How to cope with weekends and public holidays in date calculations