Search code examples
geojsonazure-data-explorerkql

Azure Data Explorer SubQuery KQL GeoJson

I am trying to create a KQL query where I can filter which locations are inside a geojson using function geo_point_in_polygon

I am trying to first, obtain my polygon with this query:

let polygon = adt_dh_FerrovialTwins_westeurope
| where ModelId == "dtmi:ferrovial:domain:models:v1:acopiotemploraltwin;1"
and Key == "localizacion"
| top 1 by Id
|project (Value);
polygon;

PolygonQuery

And with this data, trying to do another query to filter how many times a location is inside this polygon, with this query

let polygon = adt_dh_FerrovialTwins_westeurope
| where ModelId == "dtmi:ferrovial:domain:models:v1:acopiotemploraltwin;1"
and Key == "localizacion"
| top 1 by Id
|project (Value);

adt_dh_FerrovialTwins_westeurope
| where ModelId == "dtmi:ferrovial:domain:models:v1:camiondetierrastwin;1"
    and Key == "localizacion"
| extend lon=(Value).geometry.coordinates[0], lat= (Value).geometry.coordinates[1]
| project todecimal(lon), todecimal(lat), Id
| where geo_point_in_polygon(lon, lat, polygon)
| summarize count() by Id, hash = geo_point_to_s2cell(lon, lat, 7)
| project geo_s2cell_to_central_point(hash), Id, count_
| render piechart with (kind=map) // map rendering available in Kusto Explorer desktop

FilterPolygon

It says it needs a dynamic value, but it is already one. I don´t know how to solve this, because if I try using a Polygon variable as string (dynamic) it works ok:

let polygon = dynamic({
    "type": "Polygon",
    "coordinates": [
    [
    [
    -22.6430674134452,
    -69.1258109131277
    ],
    [
    -22.6430533208934,
    -69.1250474377359
    ],
    [
    -22.6453362953948,
    -69.1243603098833
    ],
    [
    -22.6452658337868,
    -69.1264980409803
    ],
    [
    -22.6431096910912,
    -69.1257803741119
    ],
    [
    -22.6430674134452,
    -69.1258109131277
    ]
    ]
    ]
    });
adt_dh_FerrovialTwins_westeurope
| where ModelId == "dtmi:ferrovial:domain:models:v1:camiondetierrastwin;1"
    and Key == "localizacion"
| extend lon=(Value).geometry.coordinates[0], lat= (Value).geometry.coordinates[1]
| project todecimal(lon), todecimal(lat), Id
| where geo_point_in_polygon(lon, lat, polygon)
| summarize count() by Id, hash = geo_point_to_s2cell(lon, lat, 7)
| project geo_s2cell_to_central_point(hash), Id, count_
| render piechart with (kind=map) // map rendering available in Kusto Explorer desktop

PolygonOK

Solution

  • Here is a minimal, reproducible example:

    let polygon = print dynamic({"type": "Polygon","coordinates": []});
print geo_point_in_polygon(0, 0, polygon)

    Fiddle

    You shared the IntelliSense error:

    A value of type dynamic expected

    However, you didn't share the run-time error:

    Failed to resolve scalar expression named 'polygon'

    As the error suggests, the function expects a scalar as an argument.
    Your tabular expression can be converted to scalar using the toscalar() function.

    let polygon = toscalar(print dynamic({"type": "Polygon","coordinates": []}));
print geo_point_in_polygon(0, 0, polygon)

    Fiddle