geo_line_to_s2cells()

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

Calculates S2 cell tokens that cover a line or multiline on Earth. This function is a useful geospatial join tool.

Read more about S2 cell hierarchy.

Syntax

geo_line_to_s2cells(lineString [, level[ , radius]])

Learn more about syntax conventions.

Parameters

Returns

Array of S2 cell token strings that cover a line or a multiline. If the radius is set to a positive value, then the covering will be of both input shape and all points within the radius of the input geometry.

If any of the following: line, level, radius is invalid, or the cell count exceeds the limit, the query will produce a null result.

Choosing the S2 cell level

• Ideally we would want to cover every line with one or just a few unique cells such that no two lines share the same cell.
• In practice, try covering with just a few cells, no more than a dozen. Covering with more than 10,000 cells might not yield good performance.
• Query run time and memory consumption might differ greatly because of different S2 cell level values.

Performance improvement suggestions

• If possible, reduce coordinates table size before join, by grouping coordinates that are very close to each other by using geospatial clustering or by filtering out unnecessary coordinates due to nature of the data or business needs.
• If possible, reduce lines count due to nature of the data or business needs. Filter out unnecessary lines before join, scope to the area of interest or unify lines.
• In case of very big lines, reduce their size using geo_line_simplify().
• Changing S2 cell level may improve performance and memory consumption.
• Changing join kind and hint may improve performance and memory consumption.
• In case positive radius is set, reverting to radius 0 on buffered shape using geo_line_buffer() may improve performance.

Examples

The following query finds all tube stations within 500 meters of streets and aggregates tubes count by street name.

let radius = 500;
let tube_stations = datatable(tube_station_name:string, lng:real, lat: real)
[
    "St. James' Park",        -0.13451078568013486, 51.49919145858172,
     "London Bridge station", -0.08492752160134387, 51.504876316440914,
     // more points
];
let streets = datatable(street_name:string, line:dynamic)
[
    "Buckingham Palace", dynamic({"type":"LineString","coordinates":[[-0.1399656708283601,51.50190802248855],[-0.14088438832752104,51.50012082761452]]}),
    "London Bridge",    dynamic({"type":"LineString","coordinates":[[-0.087152,51.509596],[-0.088340,51.506110]]}),
    // more lines
];
let join_level = 14;
let lines = materialize(streets | extend id = new_guid());
let res = 
    lines
    | project id, covering = geo_line_to_s2cells(line, join_level, radius)
    | mv-expand covering to typeof(string)
    | join kind=inner hint.strategy=broadcast
    (
        tube_stations
        | extend covering = geo_point_to_s2cell(lng, lat, join_level)
    ) on covering;
res | lookup lines on id
| where geo_distance_point_to_line(lng, lat, line) <= radius
| summarize count = count() by name = street_name

In case of invalid line, a null result will be returned.

let line = dynamic({"type":"LineString","coordinates":[[[0,0],[0,0]]]});
print isnull(geo_line_to_s2cells(line))