Distance (in meter) between two coordinates in Power BI

Data Visualizations
1

I have a data set that comprises of State, Local Government, Pharmacy Address and Longitude/Latitude. What I want is to do a market analysis of the nearest neighbour. That is, I want to know how I can use PowerBI to determine the distances between two geographic points from one Pharmacy outlet coordinate to the next or closer Pharmacy outlet within the closest meter radius, and this needs to be interactive and dynamic based on the user selections.

I have Google how to do it but unfortunately, I did not get a reasonable solution to this problem.

Attached is my PowerBI file.

Nearest Neigbor analysis.pbix (117.4 KB)

2

Hi @GBGAnalyst

I’m not sure if this will help i found this on the net. this what i typed in search bar : distance in meter using coordinates

Here is one of link that click on
Here is the link. https://www.movable-type.co.uk/scripts/latlong.html that might help.

I hope it helps.

thanks
Keith

1 Like
3

Hi @GBGAnalyst

I have looked at your question and the pbix file you share, highligted below are the next steps for you to take to get the result you wanted.

You Need to Create the following measures to ensure interactive and dynamic selection of the calculation.

Distance = MAX(‘From’[From]) & " to " & MAX(‘To’[To])

From Title = IF( HASONEFILTER(‘From’[From]), MAX(‘From’[From]),“No City Seleted” )

---- If you want your result as Kilometer distance----

Kilometers =
var Lat1 = MIN(‘From’[lat])
var Lng1 = MIN(‘From’[lng])

var Lat2 = MIN(‘To’[lat])
var Lng2 = MIN(‘To’[lng])

---- Also assign this as variable -----
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

---- If you want your result as Meter distance----

Meters =
VAR cv = 1000
RETURN
[Kilometers] * cv

SO Now, when you have successfully created this measures, create the following filter column
State
Local government
From Title

Then create a matrix table for your calculation: here are the field you need to takes to your matrix table

To should be drag in to Row
Distance should be drag to Values
Meter should be drag to values after distance

If you want to show all this in map, follow the below procedure:

Pick MAP, from visualization then perform the following,

lat should be drag to Latitude
lng should be drag to Logitude

Distance should be drag to Tooltips
Meters should be drag to Tooltips

Lastly, if you want to Picked, for example a closest outlet which is close to a selected From in the filter table,

Then carryout the folowing filter exercise on the Matrix table and the Map separately by clicking:

FILTER PANEL

Navigate to where To is in the filter:
make Filter Type to be TOP N
Show Item to be Bottom then (if you want it to be 10 closest outlet then type 10)

drag Meter to the By Value
(Note: this is to be done to Matrix table and Map)

Then you are good to go, if you need further help on this don’t hesitate to share.

Please try this out and if it work, Kindly make it a Solution

DAVID

5 Likes
4

Thank you so much @davidcenna. This works.

1 Like
5

Hi,
If I may add to the excellent contribution by @davidcenna with the Haversine formula to calculate the Great Circle distance = straight line distance.

Check out this article and example:

See the map image from a project I did in Germany, with a distance bands theme, based on his technique.

image
image578×619 348 KB

Some thoughts:

  1. You may consider to do the distance calculation in Power Query rather than in DAX, depending on the size of your model (number of calculations to make) and do a lookup in DAX.

  2. Living in Europe (the Netherlands), where we have many rivers crossing cities, the straight line distance may not always give the desired solution. Imagine 2 “nearest” points at opposite sides of a river, nearest river crossing is 5 kilometers away, this would give a bad result.
    So, if your map covers a high density area with no rivers or mountains etc, straight line distance is fine.
    If not consider driving- or walking distance for your project.

  3. Again, depending on the density, you may consider to add a “bearing” so you can identify if a “To” point is North or South etc. from the “From” location

  4. Is it just distance or also attractiveness, check out Huff gravity analysis?
    Paul

4 Likes
6

Amazing stuff @Paul.

That’s great food for thought. Thanks for sharing!

1 Like