Calculating distance in km of a subset of postocodes

[Anonymous]

Hello,

I was looking for a clever solution to calculate distances in PowerQuery of a subset of UK postcodes.

In particular, my problem is to select all postcodes within a 500 meters radius of a subset of the same list of postcodes.

Using this
https://www.doogal.co.uk/UKPostcodes
I can easily have all UK postcodes and their Lat/Long. There's various solutions on this forum and elsewhere to generate a Distance in meters. However, I'm not clear how can I reach my objective.

Say I want to have all postcodes within 500 meters from the following list of postcodes:
TargetPostcode Latitude Longitude
AB101TQ 57.143784 -2.109426
AB115RG 57.144367 -2.096076
AL100XR 51.761353 -0.240357
AL34DA 51.752071 -0.34140199
AL52TH 51.81459 -0.35555899
AL86BJ 51.801224 -0.206747

How would I do that?
Thanks in advance!

[rubayatyasmin · Accepted solution]

Hi, @Anonymous 

Here's a step by step process of how you might go about doing this in Power Query, assuming you already have the list of all UK postcodes with their lat/long from the link you provided and the subset list of postcodes you're interested in. Note that this solution involves approximations and doesn't take into account the Earth's curvature.

1. Join the data: Load your two sets of data into Power Query (i.e., your target postcode data and your full UK postcode data). You can do this using the "Get Data" function in Power BI. Once your data is loaded into Power Query, you should have two separate tables.

2. Create a Cartesian product: Next, you need to create a Cartesian product of the two tables, which essentially pairs every row in the first table with every row in the second. This can be done by adding a new column to each table (called something like "JoinKey") that has the same value for every row (like "1"), and then doing a merge operation on this new column.

3. Calculate distances: Now that you have a table with every combination of target and full postcode, you can calculate the distance between them. You can use the Haversine formula for this purpose. Add a new column to calculate the distance based on the latitude and longitude of each pair.

Here is an example of how you might implement the Haversine formula in Power Query:

#"Added Custom" = Table.AddColumn(#"Merged Queries", "Distance", each Number.Acos(Number.Sin([Latitude1]/57.2958) * Number.Sin([Latitude2]/57.2958) + Number.Cos([Latitude1]/57.2958) * Number.Cos([Latitude2]/57.2958) * Number.Cos(([Longitude2] - [Longitude1])/57.2958)) * 6371)

This formula assumes that your lat/long data is in degrees, so it first converts it to radians (since the trigonometric functions in Power Query expect their input in radians) by dividing by 57.2958. The number 6371 is the approximate radius of the Earth in kilometers.

1. Filter rows based on distance: Once you have the distances calculated, you can filter the rows based on distance. In the "Filter Rows" option of Power Query, you can specify to keep only the rows where the distance is less than or equal to 0.5 (since the distance we calculated is in kilometers and you're interested in a radius of 500 meters).

2. Final steps: At this point, you should have a table with all postcodes within 500 meters of your target postcodes. You might want to remove the added "JoinKey" and "Distance" columns for a cleaner final output. Finally, click "Close & Apply" in Power Query to load your results back into Power BI.

This should give you a list of all the postcodes within 500 meters of your subset list. Be aware that the Haversine formula assumes a perfect sphere, while the Earth is actually an oblate spheroid. For distances of 500 meters, this difference is unlikely to matter, but for very large distances, the Haversine formula can be off by a small percentage.

[Anonymous]

many thanks @rubayatyasmin, I didn't know how to do a Cartesian product by creating a dummy. Every day is a school day! 🙂

[Anonymous]

many thanks @rubayatyasmin, I didn't know how to do a Cartesian product by creating a dummy. Every day is a school day! 🙂

[rubayatyasmin]

Good to know it helped. I used GPT though to generate this much detailed reply. 😂