geo_polygon_lookup plugin (preview)

Applies to: ✅ Azure Data Explorer

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

Syntax

T | evaluate geo_polygon_lookup( LookupTable , LookupPolygonKey , SourceLongitude , SourceLatitude , [ radius ] , [ return_unmatched ] , [ lookup_area_radius ] , [ return_lookup_key ] )

Parameters

Returns

The geo_polygon_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.

Point containment in polygon is tested via geo_point_in_polygon(), or if radius is set, then geo_distance_point_to_polygon().

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.

Polygon definition and constraints

dynamic({"type": "Polygon","coordinates": [ LinearRingShell, LinearRingHole_1, ..., LinearRingHole_N ]})

dynamic({"type": "MultiPolygon","coordinates": [[LinearRingShell, LinearRingHole_1, ..., LinearRingHole_N ], ..., [LinearRingShell, LinearRingHole_1, ..., LinearRingHole_M]]})

• LinearRingShell is required and defined as a counterclockwise ordered array of coordinates [[lng_1,lat_1],...,[lng_i,lat_i],...,[lng_j,lat_j],...,[lng_1,lat_1]]. There can be only one shell.
• LinearRingHole is optional and defined as a clockwise ordered array of coordinates [[lng_1,lat_1],...,[lng_i,lat_i],...,[lng_j,lat_j],...,[lng_1,lat_1]]. There can be any number of interior rings and holes.
• LinearRing vertices must be distinct with at least three coordinates. The first coordinate must be equal to the last. At least four entries are required.
• Coordinates [longitude, latitude] must be valid. Longitude must be a real number in the range [-180, +180] and latitude must be a real number in the range [-90, +90].
• LinearRingShell encloses at most half of the sphere. LinearRing divides the sphere into two regions. The smaller of the two regions, is chosen.
• LinearRing edge length must be less than 180 degrees. The shortest edge between the two vertices is chosen.
• LinearRings must not cross and must not share edges. LinearRings might share vertices.

Setting lookup_area_radius (if needed)

Setting lookup area radius overrides internal mechanism for matching locations to their respective polygons. The value is a distance in meters. Ideally, lookup area radius should represent a distance from polygon center, such that within that distance a point matches to exactly one polygon in one-to-one manner and within that distance, there are no more than a single polygon. Because the polygons data might be large, polygons might vary greatly in size and shape compared to each other and the proximity of the polygon 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.

PolygonsTable | project value = sqrt(geo_polygon_area(polygon)) | summarize min = min(value), avg = avg(value), max = max(value)

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

Examples

The following example returns only matching rows.

let polygons = datatable(polygon_name:string, polygon:dynamic)
[
    "New York", dynamic({"type":"Polygon", "coordinates":[[[-73.97375470114766,40.74300078124614],[-73.98653921014294,40.75486501361894],[-73.99910622331991,40.74112695466084],[-73.97375470114766,40.74300078124614]]]}),
    "Paris", dynamic({"type":"Polygon","coordinates":[[[2.57564669886321,48.769567764921334],[2.420098611499384,49.05163394896812],[2.1016783119165723,48.80113794475062],[2.57564669886321,48.769567764921334]]]}),
];
let locations = datatable(location_name:string, longitude:real, latitude:real)
[
    "Empire State Building", -73.98567337898565, 40.74842629977377,
    "National Museum of Mathematics", -73.98778501496217, 40.743565232771545,
    "Eiffel Tower", 2.294489426068907, 48.858263476169185,
    "London", -0.13245599272019604, 51.49879464208368,
    "Statue of Liberty",  -74.04462223203123, 40.689195627512674,
];
locations
| evaluate geo_polygon_lookup(polygons, polygon, longitude, latitude, return_lookup_key = true)

Output

The following example returns both matching and nonmatching rows.

let polygons = datatable(polygon_name:string, polygon:dynamic)
[
    "New York", dynamic({"type":"Polygon", "coordinates":[[[-73.97375470114766,40.74300078124614],[-73.98653921014294,40.75486501361894],[-73.99910622331991,40.74112695466084],[-73.97375470114766,40.74300078124614]]]}),
    "Paris", dynamic({"type":"Polygon","coordinates":[[[2.57564669886321,48.769567764921334],[2.420098611499384,49.05163394896812],[2.1016783119165723,48.80113794475062],[2.57564669886321,48.769567764921334]]]}),
];
let locations = datatable(location_name:string, longitude:real, latitude:real)
[
    "Empire State Building", -73.98567337898565, 40.74842629977377,
    "NY National Museum of Mathematics", -73.98778501496217, 40.743565232771545,
    "Eiffel Tower", 2.294489426068907, 48.858263476169185,
    "London", -0.13245599272019604, 51.49879464208368,
    "Statue of Liberty",  -74.04462223203123, 40.689195627512674,
];
locations
| evaluate geo_polygon_lookup(polygons, polygon, longitude, latitude, return_unmatched = true, return_lookup_key = true)

Output

The following example returns both matching and nonmatching rows where radius is set to 7 km.

let polygons = datatable(polygon_name:string, polygon:dynamic)
[
    "New York", dynamic({"type":"Polygon", "coordinates":[[[-73.97375470114766,40.74300078124614],[-73.98653921014294,40.75486501361894],[-73.99910622331991,40.74112695466084],[-73.97375470114766,40.74300078124614]]]}),
    "Paris", dynamic({"type":"Polygon","coordinates":[[[2.57564669886321,48.769567764921334],[2.420098611499384,49.05163394896812],[2.1016783119165723,48.80113794475062],[2.57564669886321,48.769567764921334]]]}),
];
let locations = datatable(location_name:string, longitude:real, latitude:real)
[
    "Empire State Building", -73.98567337898565, 40.74842629977377,
    "NY National Museum of Mathematics", -73.98778501496217, 40.743565232771545,
    "Eiffel Tower", 2.294489426068907, 48.858263476169185,
    "London", -0.13245599272019604, 51.49879464208368,
    "Statue of Liberty",  -74.04462223203123, 40.689195627512674,
];
locations
| evaluate geo_polygon_lookup(polygons, polygon, longitude, latitude, radius = 7000, return_unmatched = true, return_lookup_key = true)

Output

The following example counts locations by polygon.

let polygons = datatable(polygon_name:string, polygon:dynamic)
[
    "New York", dynamic({"type":"Polygon", "coordinates":[[[-73.97375470114766,40.74300078124614],[-73.98653921014294,40.75486501361894],[-73.99910622331991,40.74112695466084],[-73.97375470114766,40.74300078124614]]]}),
    "Paris", dynamic({"type":"Polygon","coordinates":[[[2.57564669886321,48.769567764921334],[2.420098611499384,49.05163394896812],[2.1016783119165723,48.80113794475062],[2.57564669886321,48.769567764921334]]]}),
];
let locations = datatable(location_name:string, longitude:real, latitude:real)
[
    "Empire State Building", -73.98567337898565, 40.74842629977377,
    "NY National Museum of Mathematics", -73.98778501496217, 40.743565232771545,
    "Eiffel Tower", 2.294489426068907, 48.858263476169185,
    "London", -0.13245599272019604, 51.49879464208368,
    "Statue of Liberty",  -74.04462223203123, 40.689195627512674,
];
locations
| evaluate geo_polygon_lookup(polygons, polygon, longitude, latitude)
| summarize count() by polygon_name

Output

Related content

• Overview of geo_point_in_polygon()
• Overview of geo_distance_point_to_polygon()
• Overview of geo_polygon_to_s2cells()