geo_line_to_s2cells()

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]])

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 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))