Find geographical locations which are close to others (distance)

[mluce]

Hi,

I have two tables of data.  One with a list of places (placename, latitude, longitude) which is approximately 10,000 records in size.  The other has operation details (operation, placename).  I have a map control which is nicely displaying the operations on the map using the lat / long data from the other table.

What I need is a list (or map display) of other placenames which are near the operation places.  For example, if I have an operation shown on the map at a certain lat / long, I am needing to find any other places that are close to it (say, within 100 meters?).

I do not really know how to begin to approach this 😞  Any pointers would be greatly appriciated!

Thanks,

[v-qiuyu-msft]

Hi @mluce,

From Dmitry_D 's suggestion here, you can create a Power Query below to return distance for each city to a specific city.

(x_cast,y_cast)=>
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JYwxCsMwDEWvEjSnwrJly7pAxy7pUoyH0GYoARvSofj2ddzt897jpwRLe5WtwQwX5zAG8X2SJ7RGIM8J7nVvtTPnMUQd1ikGpb9e1jJdj7V83s9RCYqwnndkLTIpj+y2fadHPfYu2KCQjWcijMaoQs4/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [City = _t, Lat = _t, Long = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"City", type text}, {"Lat", type number}, {"Long", type number}}),
formula = Table.AddColumn(#"Changed Type", "Distance", each
6371 * 2 *Number.Asin(Number.Sqrt(Number.Power(Number.Sin((y_cast-[Long])*Number.PI/180/2),2)+Number.Cos(y_cast*Number.PI/180)*Number.Cos([Long]*Number.PI/180)*Number.Power(Number.Sin((x_cast-[Lat])*Number.PI/180/2),2))))
//minimum = List.Min(formula[Distance]),
//fnl_tbl= Table.SelectRows(formula,each [City]=minimum)
in
formula

Then invoke the function, enter London location (51.5074, -0.1278) for parameters then generate a table to return Distance.

Go to the report, drag a map visual, place Lat and Long to locate, and drag Distance to Size, the smallest one is the nearest to London.

Best Regards,
Qiuyun Yu