Creating Geospatial Visualizations with Kusto Query Language (KQL)
Powerful ways to analyze and interpret data based on geographical locations
Geospatial visualizations are a powerful way to analyze and interpret data based on geographical locations. Kusto Query Language (KQL) offers robust tools for performing geospatial clustering and visualizations, making it easier to understand spatial patterns and trends. In this blog post, we'll explore various methods for creating geospatial visualizations using KQL, including plotting points on a map, using GeoJSON values, and finding anomalies based on geospatial data.
Prerequisites
To run the queries in this tutorial, you need access to a query environment with sample data. You can use one of the following:
A Microsoft account or Microsoft Entra user identity
A Fabric workspace with Microsoft Fabric-enabled capacity
Plotting Points on a Map
To visualize points on a map, use the project operator to select the columns containing longitude and latitude. Then, use the render operator to display the results in a scatter chart with the kind set to map.
Example:
StormEvents | take 100 | project BeginLon, BeginLat | render scatterchart with (kind = map)
Plotting Multiple Series of Points
To visualize multiple series of points, select the longitude, latitude, and a third column that defines the series. This allows points to be colored differently based on their series.
Example:
StormEvents | take 100 | project BeginLon, BeginLat, EventType | render scatterchart with (kind = map)
Using GeoJSON Values to Plot Points
Dynamic GeoJSON values provide flexibility for real-time mapping applications. Use geo_point_to_s2cell and geo_s2cell_to_central_point to map storm events in a scatter chart.
Example:
StormEvents | project BeginLon, BeginLat | summarize by hash = geo_point_to_s2cell(BeginLon, BeginLat, 5) | project point = geo_s2cell_to_central_point(hash) | project lng = toreal(point.coordinates[0]), lat = toreal(point.coordinates[1]) | render scatterchart with (kind = map)
Representing Data Points with Variable-Sized Bubbles
Visualize the distribution of data points by aggregating each cluster and plotting the central point with variable-sized bubbles.
Example:
StormEvents | where EventType == "Tornado" | project BeginLon, BeginLat | where isnotnull(BeginLat) and isnotnull(BeginLon) | summarize count_summary = count() by hash = geo_point_to_s2cell(BeginLon, BeginLat, 4) | project geo_s2cell_to_central_point(hash), count_summary | extend Events = "count" | render piechart with (kind = map)
Displaying Points Within a Specific Area
Define a region using a polygon and filter events within that region using geo_point_in_polygon.
Example:
let southern_california = dynamic({"type": "Polygon", "coordinates": [[[-119.5, 34.5], [-115.5, 34.5], [-115.5, 32.5], [-119.5, 32.5], [-119.5, 34.5]]]});
StormEvents | where geo_point_in_polygon(BeginLon, BeginLat, southern_california) | project BeginLon, BeginLat | summarize count_summary = count() by hash = geo_point_to_s2cell(BeginLon, BeginLat, 8) | project geo_s2cell_to_central_point(hash), count_summary | extend Events = "count" | render piechart with (kind = map)
Showing Nearby Points on a LineString
Find nearby storm events along a specified LineString using geo_distance_point_to_line.
Example:
let roadToKeyWest = dynamic({"type": "linestring", "coordinates": [[-81.79595947265625, 24.56461038017685], [-81.595458984375, 24.627044746156027], [-81.52130126953125, 24.666986385216273], [-81.35650634765625, 24.66449040712424], [-81.32354736328125, 24.647017162630366], [-80.8099365234375, 24.821639356846607], [-80.62042236328125, 24.93127614538456], [-80.37872314453125, 25.175116531621764], [-80.42266845703124, 25.19251511519153], [-80.4803466796875, 25.46063471847754]]});
StormEvents | where isnotempty(BeginLat) and isnotempty(BeginLon) | project BeginLon, BeginLat, EventType | where geo_distance_point_to_line(BeginLon, BeginLat, roadToKeyWest) < 500 | render scatterchart with (kind = map)
Showing Nearby Points in a Polygon
Find nearby storm events within a specified polygon using geo_distance_point_to_polygon.
Example:
let roadToKeyWest = dynamic({"type": "polygon", "coordinates": [[[-80.08209228515625, 25.39117928167583], [-80.4913330078125, 25.517657429994035], [-80.57922363281249, 25.477992320574817], [-82.188720703125, 24.632038149596895], [-82.1942138671875, 24.53712939907993], [-82.13104248046875, 24.412140070651528], [-81.81243896484375, 24.43714786161562], [-80.58746337890625, 24.794214972389486], [-80.08209228515625, 25.39117928167583]]});
StormEvents | where isnotempty(BeginLat) and isnotempty(BeginLon) | project BeginLon, BeginLat, EventType | where geo_distance_point_to_polygon(BeginLon, BeginLat, roadToKeyWest) < 500 | render scatterchart with (kind = map)
Finding Anomalies Based on Geospatial Data
Analyze storm events within a particular state using S2 cells and temporal aggregation to identify anomalies.
Example:
let stateOfInterest = "Texas";
let statePolygon = materialize(US_States | extend name = tostring(features.properties.NAME) | where name == stateOfInterest | project geometry = features.geometry);
let stateCoveringS2cells = statePolygon | project s2Cells = geo_polygon_to_s2cells(geometry, 9);
StormEvents | extend s2Cell = geo_point_to_s2cell(BeginLon, BeginLat, 9) | where s2Cell in (stateCoveringS2cells) | where geo_point_in_polygon(BeginLon, BeginLat, toscalar(statePolygon)) | make-series damage = avg(DamageProperty + DamageCrops) default = double(0.0) on StartTime step 7d | extend anomalies = series_decompose_anomalies(damage) | render anomalychart with (anomalycolumns = anomalies)
TLDR
Geospatial visualizations in KQL provide a powerful way to analyze and interpret data based on geographical locations. By leveraging operators like project, render, and geospatial functions such as geo_point_to_s2cell, you can create detailed and insightful visualizations. Whether you're plotting points on a map, analyzing clusters, or finding anomalies, KQL's geospatial capabilities offer the tools needed to unlock the full potential of your data.