geo_line_lookup plugin (preview)

Applies to: ✅ Azure Data Explorer

The geo_line_lookup plugin looks up a Line value in a lookup table and returns rows with matched values. The plugin is invoked with the evaluate operator.

Syntax

T | evaluate geo_line_lookup( LookupTable , LookupLineKey , SourceLongitude , SourceLatitude , Radius , [ return_unmatched ] , [ lookup_area_radius ] , [ return_lookup_key ] )

Parameters

Returns

The geo_line_lookup plugin returns a result of join (lookup). The schema of the table is the union of the source table and the lookup table, similar to the result of the lookup operator.

Location distance from a line is tested via geo_distance_point_to_line().

If the return_unmatched argument is set to true, the resulting table includes both matched and unmatched rows (filled with nulls).

If the return_unmatched argument is set to false, or omitted (the default value of false is used), the resulting table has as many records as matching results. This variant of lookup has better performance compared to return_unmatched=true execution.

Setting lookup_area_radius length overrides internal matching mechanism and might improve or worsen run time and\or memory consumption. It doesn't affect query correctness. Read more below on how to set this optional value.

LineString definition and constraints

dynamic({"type": "LineString","coordinates": [[lng_1,lat_1], [lng_2,lat_2],..., [lng_N,lat_N]]})

dynamic({"type": "MultiLineString","coordinates": [[line_1, line_2, ..., line_N]]})

• LineString coordinates array must contain at least two entries.
• Coordinates [longitude, latitude] must be valid where longitude is a real number in the range [-180, +180] and latitude is a real number in the range [-90, +90].
• Edge length must be less than 180 degrees. The shortest edge between the two vertices is chosen.

Setting lookup_area_radius (if needed)

Setting lookup area radius overrides internal mechanism for matching locations to their respective lines. The value is a distance in meters. Ideally, lookup area radius should represent a distance from line center, such that within that distance a point matches to exactly one line in one-to-one manner and within that distance, there are no more than a single line. Because the lines data might be big, lines might vary greatly in size and shape compared to each other and the proximity of the line one to another, it might be challenging to come up with the radius that performs the best. If needed, here's a sample that might help.

LinesTable | project value = geo_line_length(line) | summarize min = min(value), avg = avg(value), max = max(value)

Try using lookup radius starting from average value towards either minimum (If the lines are close to each other) or maximum by multiples of 2.

Examples

The following example returns only matching rows.

let roads = datatable(road_name:string, road:dynamic)
[
    "5th Avenue NY", dynamic({"type":"LineString","coordinates":[[-73.97291864770574,40.76428551254824],[-73.99708638113894,40.73145135821781]]}),
    "Palace Ave", dynamic({"type":"LineString","coordinates":[[-0.18756982045002246,51.50245944666557],[-0.18908519740253382,51.50544952706903]]}),
];
let locations = datatable(location_name:string, longitude:real, latitude:real)
[
    "Empire State Building", -73.98567337898565, 40.74842629977377,
    "Grand Central Terminal", -73.97713140725149, 40.752730320824895,
    "Statue of Liberty",  -74.04462223203123, 40.689195627512674,
    "Kensington Palace", -0.1885272501232862,  51.504906159672316
];
locations
| evaluate geo_line_lookup(roads, road, longitude, latitude, 100, return_lookup_key = true)

Output

The following example returns both matching and nonmatching rows.

let roads = datatable(road_name:string, road:dynamic)
[
    "5th Avenue NY", dynamic({"type":"LineString","coordinates":[[-73.97291864770574,40.76428551254824],[-73.99708638113894,40.73145135821781]]}),
    "Palace Ave", dynamic({"type":"LineString","coordinates":[[-0.18756982045002246,51.50245944666557],[-0.18908519740253382,51.50544952706903]]}),
];
let locations = datatable(location_name:string, longitude:real, latitude:real)
[
    "Empire State Building", -73.98567337898565, 40.74842629977377,
    "Grand Central Terminal", -73.97713140725149, 40.752730320824895,
    "Statue of Liberty",  -74.04462223203123, 40.689195627512674,
    "Kensington Palace", -0.1885272501232862,  51.504906159672316
];
locations
| evaluate geo_line_lookup(roads, road, longitude, latitude, 100, return_unmatched = true, return_lookup_key = true)

Output

The following example returns both matching and nonmatching rows, with radius set to 350m.

let roads = datatable(road_name:string, road:dynamic)
[
    "5th Avenue NY", dynamic({"type":"LineString","coordinates":[[-73.97291864770574,40.76428551254824],[-73.99708638113894,40.73145135821781]]}),
    "Palace Ave", dynamic({"type":"LineString","coordinates":[[-0.18756982045002246,51.50245944666557],[-0.18908519740253382,51.50544952706903]]}),
];
let locations = datatable(location_name:string, longitude:real, latitude:real)
[
    "Empire State Building", -73.98567337898565, 40.74842629977377,
    "Grand Central Terminal", -73.97713140725149, 40.752730320824895,
    "Statue of Liberty",  -74.04462223203123, 40.689195627512674,
    "Kensington Palace", -0.1885272501232862,  51.504906159672316
];
locations
| evaluate geo_line_lookup(roads, road, longitude, latitude, 350, return_unmatched = true, return_lookup_key = true)

The following example counts locations by proximity to road.

let roads = datatable(road_name:string, road:dynamic)
[
    "5th Avenue NY", dynamic({"type":"LineString","coordinates":[[-73.97291864770574,40.76428551254824],[-73.99708638113894,40.73145135821781]]}),
    "Palace Ave", dynamic({"type":"LineString","coordinates":[[-0.18756982045002246,51.50245944666557],[-0.18908519740253382,51.50544952706903]]}),
];
let locations = datatable(location_name:string, longitude:real, latitude:real)
[
    "Empire State Building", -73.98567337898565, 40.74842629977377,
    "Grand Central Terminal", -73.97713140725149, 40.752730320824895,
    "Statue of Liberty",  -74.04462223203123, 40.689195627512674,
    "Kensington Palace", -0.1885272501232862,  51.504906159672316
];
locations
| evaluate geo_line_lookup(roads, road, longitude, latitude, 350)
| summarize count() by road_name 

Output

Related content

• Overview of geo_distance_point_to_line()
• Overview of geo_line_to_s2cells()