How to Calculate Distance on map from one Postcode to Another Postcode

[MYDATASTORY]

Hi Everyone,

Please provide advice/steps the most efficient way to calculate distance from Point A to Point B on Google map.

For example Postcode W1 (W1 is the postcode district in Central).. to W2...  =10miles.

So taxi drivers can also calculate the distance covered by taxi.

Please find below link with the file with Postcode data From and To

https://ln2.sync.com/dl/990703150/t6mqmq23-ex9r6ht5-8wxhc4ex-gtnzn79y 

Thank you.

[Greg_Deckler]

You will need something like the Google Maps API. https://www.youtube.com/watch?v=IGr5oQLm0ls

Not sure what your data looks like, just zip codes? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[MYDATASTORY]

I have   two Postcodes columns | From {W2} To {W3} @Greg_Deckler 

[Greg_Deckler]

Right so your basic formula for short distances is:

dlon = lon2 - lon1
dlat = lat2 - lat1
a = (sin(dlat/2))^2 + cos(lat1) * cos(lat2) * (sin(dlon/2))^2
c = 2 * atan2( sqrt(a), sqrt(1-a) )
d = R * c (where R is the radius of the Earth)

So you could do it (with lots of caveats in terms of the accuracy of the output of the above formula since the Earth isn't a perfect sphere) if you had the longitudes and latitudes of those zip codes.

[MYDATASTORY]

@Greg_Deckler  Thanks for the update, any chance you can have at the look at the  PBix file updated with (hyperlink) as see how will be the best way to add the calculation based on the data. Much appreciated in advance.

[Greg_Deckler]

@MYDATASTORY  - Oh, I will have to take a look at the PBIX. Here I was busy creating a couple of Quick Measures for you that I just published out to the Quick Measure gallery. Here they are:

ATAN2 - https://community.powerbi.com/t5/Quick-Measures-Gallery/ATAN2/td-p/963263

Going the Distance - https://community.powerbi.com/t5/Quick-Measures-Gallery/Going-the-Distance/m-p/963267#M423

I'll take a look at your PBIX. 

[MYDATASTORY]

@Greg_Deckler   Wow this is great and appreciated, have a look at the file.

[Greg_Deckler]

OK, @MYDATASTORY it is finally done uploading. You can grab the file from here:

https://1drv.ms/u/s!ApodhCa32_44o9c1vhYu46mQj5xM8Q?e=KODN0B

One note, I did have to clean up some of your data because there were some errant spaces. Just FYI.

[ToughTake]

Hi @Greg_Deckler This is brilliant!

Sorry for replying 3+ years later, but have just stumbled across this.

Would it be possible to tweak this so rather than having to add in a to and from for every location in a tbale you could select a postcode (slicer) and it give you a list of the top X closest postcodes to your selection?

Any help on this would be greatly appreciated!

[Greg_Deckler]

@ToughTake Possibly. I did something along those lines once: Near - Microsoft Fabric Community

[Kris07]

Hi Greg!

Hope you are well and dont mind me jumping on this thread.

I have a excel document with postcodes in (uk) and i would like to work out the miles between 2.

I have the latitude and longitude and can get the distance between the points but i assume that is as a 'crow flys' not by road? Can you see if i have gone wrong on a formula?

Thank you!

Kris 

[ToughTake]

Thanks @Greg_Deckler 

I have had a quick look but I cannot quite figure out how its working, or how I could get it to work for my use.

If I have a list of postcodes with long and lat for each in a table, how would I use this to produce a table of the closest other postcodes? Ideally I would love to have the actaul distance in miles listed as well.

Any chance you could point me in the right direction please?

[Greg_Deckler]

@ToughTake Sure, can you post a sample of the data you have? Then I can mock things up.

[ToughTake]

Hi @Greg_Deckler 

Just checking if you have had chance to look into this yet?

Happy to provide any more information that you might need?

[ToughTake]

Morning @Greg_Deckler 

Was the sample data enough to work with, did you need anything else from me?

Thank you again for you help.

[ToughTake]

Thanks so much!

Sample wise, please see the below dummy data.

https://file.io/ZCj3MVXwfIZG

Again thanks so much!

[MYDATASTORY · Accepted solution]

@Greg_Deckler Wow this is fantastic, let me have a good look at it tomorrow so I can accept the solution , I'll let you know, but definitely amazing...

[Greg_Deckler]

@MYDATASTORY  - Sorry, posted to the wrong thread

OK, I downloaded the UK Postal Code latitudes and longitudes from here:

https://www.freemaptools.com/download-uk-postcode-lat-lng.htm

You guys sure have a lot of postal codes, apparently about 1.7M of them and since they are unique as well as the latitudes and longitudes, well, it makes for a fairly sizeable file. Anyway, I went ahead and implemented a few columns and such to get the distances.

[amitchandak]

Refer, if this can help

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=a90b6419-...