Forum Discussion

Dmitry_D's avatar
Dmitry_D
Frequent Visitor
9 years ago
Solved

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 i...
Need Help
  • Dmitry_D's avatar
    Dmitry_D
    9 years ago

    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

Dmitry_D's avatar
Dmitry_D
Frequent Visitor
9 years ago

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's avatar
DAQEY
New Member
8 years ago

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