Forum Discussion
Calculate nearest points by geographical coordinates
Hello!
I have two tables. Table "Customers address" with my customers id, latitude and longitude (8 mln rows). And "Shop_table" with my shops id, latitude and longitude (500 rows).
The goal is to find the nearest shop to each of my customers. Fortunatly, I also have column "city" in both my tables. And fortunatly I know the formula to calculate the distance between two geographical points.
I take table "Customers address" and make Table.NestedJoin with "Shop_table" by column "city". And expand it. After that I add column "Distance" with formula distance calculation . Finaly, I make Table.Group and calculate the minimal distance for each customer id.
But it takes a lot of time. Could you advise me a better way?
Dmitry
It seems, I figured how to do it
I made the user function
(reg,x_cast,y_cast)=>
let
Source = Table.SelectRows(shop,each [region]=reg),
formula = Table.AddColumn(Sorce, "Distance", each
6371 * 2 *Number.Asin(Number.Sqrt(Number.Power(Number.Sin((y_cast-[y_shop])*Number.PI/180/2),2)+Number.Cos(y_cast*Number.PI/180)*Number.Cos([y_shop]*Number.PI/180)*Number.Power(Number.Sin((x_cast-[x_shop])*Number.PI/180/2),2)))),
minimum = List.Min(formula[Distance]),
fnl_tbl= Table.SelectRows(formula,each [Custom]=minimum)
in
fnl_tbl
9 Replies
You might take a look at the new ArcGIS map functionality just released this month for Power BI Desktop.
Unfortunatly, I do not understand how ArcGIS map could solve my task
How many distinct cities are in your customers table? If significantly less than your number of customers, you could perform the distance calculation based on a list of just the distinct cities of your customers table. Then buffer that result and join back to the customers.
This might be useful to others, I've put togheter a sample PBIX that calculates the closest Shop to Costumers using Dmitry_D's original code. Sample on dropbox here.
