Geospatial joins

Applies to: ✅ Azure Data Explorer ✅ Azure Monitor ✅ Microsoft Sentinel

Kusto Query Language (KQL) provides tools for geospatial joins.

The following tools and capabilities are useful:

• Converting points (longitude, latitude) to one of the supported geo hashes S2, H3 or Geohash. Geo hash can be used as a join key. Two nearby points are converted to the same hash value or they are neighbors, which can be accounted for as well. Learn more about different geo hash algorithms. See examples below.

• Buffer capabilities geo_point_buffer(), geo_polygon_buffer(), and geo_line_buffer() can help geospatial conditional joins whenever match is a proximity condition. See example below.

• Polygon\Line lookup plugin capabilities geo_polygon_lookup() and geo_line_lookup() allow easy classification of locations to their respective polygons\lines based on containment and\or proximity.

• Shape covering functions geo-polygon-to-s2cells() and geo-line-to-s2cells() are advanced shape covering utilities that can transform shapes to a collection of hashes that can be persisted and used for joins and indexing.

Examples

The following example illustrates join on locations using S2.

let locations1 = datatable(name: string, longitude: real, latitude: real)
[
    "a", -0.12433080766874127, 51.51115841361647,
    "b", -0.12432651341458723, 51.511160848670585,
    "c", -0.12432466939637266, 51.51115959669167,
    "d", 1,                    1,
];
let locations2 = datatable(id: string, longitude: real, latitude: real)
[
    "1", -0.12432668105284961, 51.51115938802832
];
let s2_join_level = 22; // More about join levels: https://learn.microsoft.com/en-us/kusto/query/geo-point-to-s2cell-function?view=azure-data-explorer
locations1
| extend hash = geo_point_to_s2cell(longitude, latitude, s2_join_level)
| join kind = inner (locations2 | extend hash = geo_point_to_s2cell(longitude, latitude, s2_join_level)) on hash
| project name, id  

Output

The following example illustrates join on locations using H3 while accounting for a case where two nearby locations may be neighbors.

let locations1 = datatable(name: string, longitude: real, latitude: real)
[
    "a", -0.12433080766874127, 51.51115841361647,
    "b", -0.12432651341458723, 51.511160848670585,
    "c", -0.12432466939637266, 51.51115959669167,
    "d", 1,                    1,
];
let locations2 = datatable(id: string, longitude: real, latitude: real)
[
    "1", -0.12432668105284961, 51.51115938802832
];
let to_hash = (lng: real, lat: real)
{
    let h3_hash_level = 14; // More about join levels: https://learn.microsoft.com/en-us/kusto/query/geo-point-to-h3cell-function?view=azure-data-explorer
    let h3_hash = geo_point_to_h3cell(lng, lat, h3_hash_level);
    array_concat(pack_array(h3_hash), geo_h3cell_neighbors(h3_hash))
};
locations1
| extend hash = to_hash(longitude, latitude)
| mv-expand hash to typeof(string)
| join kind = inner (
    locations2
    | extend hash = to_hash(longitude, latitude)
    | mv-expand hash to typeof(string))
    on hash
| distinct name, id, longitude, latitude

Output

The following example illustrates join of locations from locations1 table with locations from locations2 table if the points from locations1 are within 300 meters of points from locations2 table.

let locations1 = datatable(name: string, longitude: real, latitude: real)
[
    "O2 Entrance", 0.005889454501716321, 51.50238626916584,
    "O2 Entrance", 0.0009625704125020596,51.50385432770013,
    "Greenwich Park", 0.0009395106042404677, 51.47700456557013,
];
let locations2 = datatable(id: string, longitude: real, latitude: real)
[
    "O2 Arena", 0.003159306017352037, 51.502929224128394
]
| extend buffer = geo_point_buffer(0.003159306017352037, 51.502929224128394, 300, 0.1); // Create a radius of 300 meters from O2 center location
locations1
| evaluate geo_polygon_lookup(locations2, buffer, longitude, latitude)
| project name, id, longitude, latitude

Output