cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Frequent Visitor

## 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

1 ACCEPTED SOLUTION
Frequent Visitor

It seems, I figured how to do it

(reg,x_cast,y_cast)=>
let
Source = Table.SelectRows(shop,each [region]=reg),
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 9
Frequent Visitor

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.

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

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

Super User

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

Frequent Visitor

It seems, I figured how to do it

(reg,x_cast,y_cast)=>
let
Source = Table.SelectRows(shop,each [region]=reg),
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

New Member

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

Super User

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?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

Frequent Visitor

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

Super User

Thx - very impressive!

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors