Need Help: Calculating nearest location distance and location ID

[Shailesh2thakur]

I have two  tables, i.e. Parking & SiteMaster.

The Columns in the Parking Table are as follows: VehicleNo, Start time, End time, lat, long.

The Columns in the SiteMaster are as follows: SiteID, Lat, Long. The SiteMaster contains Unique SiteID of various Sites along with respective Latitude(Lat) and Longitude(Long). There are 12K+ unique rows. 

The Parking Table Contains the Registration Number(VehicleNo), The starting time and ending time between which it was parked at the location with Latitude(lat) & Longitude(long). It has no adddress/city column. There are 750+ rows with only 50+ unique VehicleNo. 

I want to add a column for nearest SiteID in Parking table along with its distance in meters from the nearest SitID.

What steps should I take in the PowerQuery to achieve the objective.

[Shailesh2thakur]

Thanks a Ton @AlienSx 

[v-xinruzhu-msft]

Hi @Shailesh2thakur 

Can you provide some sample data and the output you want so that can provide more suggestion for you.

Best Regards!

Yolo Zhu

[Shailesh2thakur]

The Parking Table is as follows: 

VehicleNo              Start time                   End time                   lat               long
ALD UP72AT-5281 2024-01-17 03:35:28 2024-01-17 06:46:51 25.449985 81.725858
ALD UP72AT-5281 2024-01-17 06:59:06 2024-01-17 07:06:50 25.443323 81.800569
ALD UP72AT-5281 2024-01-17 07:35:43 2024-01-17 08:14:55 25.526458 81.871894
ALD UP72AT-5281 2024-01-17 08:42:48 2024-01-17 08:58:36 25.589825 81.924649
ALD UP72AT-5281 2024-01-17 09:17:32 2024-01-17 09:53:28 25.626707 81.97712

The SiteMaster Table is as under

SiteID   Lat       Long
Site 01 25.383 81.859
Site 02 29.344 81.897
Site 03 25.358 81.863
Site 04 25.350 81.870
Site 05 25.376 81.869
Site 06 25.342 81.883
Site 07 25.378 81.867
Site 08 25.527 81.853

What I need is nearest Site ID & its distance  in Parking table w.r.t. each lat/long pair to be added in new columns in a transformed table. 

[AlienSx · Accepted solution]

@Shailesh2thakur distance is in km. To switch to miles change Earth radius parameter in code accordingly. 

let
    sites = your_SiteMaster_table,
    vehicles = your_Parking_table,
// add geo record into vehicles table
    v_geo = Table.AddColumn(vehicles, "geo", each GeographyPoint.From(Number.From([long]), Number.From([lat]))),
// transform sites table into list of records with geo coordinates
    sg = List.Buffer(
        Table.ToList(
            sites,
            (x) => GeographyPoint.From(Number.From(x{2}), Number.From(x{1})) & [site = x{0}]
        )
    ),
// distance calculation function
    distance = (parking) => (site) =>
        [r = 6371, // this is Earth radius in km. Change to miles if you like
        p = Number.PI / 180,
        a = 0.5 - 
            Number.Cos( (site[Latitude] - parking[Latitude]) * p) / 2 +
            Number.Cos(parking[Latitude] * p) * 
                Number.Cos(site[Latitude] * p) *
                    (1 - Number.Cos( (site[Longitude] - parking[Longitude]) * p)) / 2,
        d = 2 * r * Number.Asin(Number.Sqrt(a))][d],
// this function finds closest site and distance to it
    closest = (v) =>
        [f = distance(v),
        m = List.Min(sg, null, f),
        dist = m & [distance = distance(v)(m)]][dist],
// now we apply closest function to "geo" column with locations
    find_closest_sites = Table.TransformColumns(v_geo, {"geo", (x) => closest(x)}),
// and finally expand site ID and distance to it
    expand_site_info = Table.ExpandRecordColumn(find_closest_sites, "geo", {"site", "distance"})
in
    expand_site_info

[dufoq3]

Great @AlienSx, I'll save this query for future. I didn't know that this is possible in Power Query. Thank you for educating me 🙂