Calculate nearest points by geographical coordinates

[Dmitry_D]

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

[Anonymous]

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.

[Greg_Deckler]

You might take a look at the new ArcGIS map functionality just released this month for Power BI Desktop.

[Dmitry_D]

Unfortunatly, I do not understand how ArcGIS map could solve my task

[ImkeF]

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.

[Dmitry_D · Accepted solution]

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

[DAQEY]

Thanks for sharing your solution!  I have the exact same issue with two tables of Latitude and Longitudes and a need to find the nearest location like finding the nearest shop to each customer.  Can you please explain a little more of the syntax in your solution and how you implemented it?

Did you use that code to create a new column in your table containing the customer locations or did you create a 3rd table?  Is thise done with just 1 column?  Would you be able to share an example file so I can follow how your solution was implemented?

Thank you

[ImkeF]

Yes , of course thats the best way as you don't have to group (and the tables to operate on are shorter).

How many times faster does it make your query?

[Dmitry_D]

Input data

Customer_table = 13 mln rows

Shop_table = 540 rows

The 1st method (with Group) was endless. After 20 minutes of waiting I stopped id

The 2nd method (with user function) takes about 7 seconds

[ImkeF]

Thx - very impressive!