geo_line_to_s2cells()

Artikkeli
08/12/2024

Applies to: ✅ Microsoft Fabric ✅ 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.

Syntax

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

Parameters

Name	Type	Required	Description
lineString	`dynamic`	✔️	Line or multiline in the GeoJSON format.
level	`int`		Defines the requested cell level. Supported values are in the range [0, 30]. If unspecified, the default value `11` is used.
radius	`real`		Buffer radius in meters. If unspecified, the default value `0` is used.

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.

Note

Covering the line with S2 cell tokens can be useful in matching coordinates to lines, thus finding points nearby lines.
The line covering tokens are of the same S2 cell level.
The maximum count of tokens per line is 65536.
The geodetic datum used to measure distance on Earth is a sphere. Line edges are geodesics on the sphere.
If input line edges are straight cartesian lines, consider using geo_line_densify() in order to convert planar edges to geodesics.

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.

Run the query

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

name	count
Buckingham Palace	1
London Bridge	1

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