Just shipped: Geospatial functions support in Azure Data Explorer

cosh23

Microsoft

Nov 07, 2019

We are excited to announce that we just released the first set of geospatial functions in Azure Data Explorer!

Geohash support

Geohashes are a beautiful way to store locations in a single column with a precision based on the length of the hash. This enables the user to summarize data on geographical buckets. We are supporting the transformation from points to geohashes and back.

Contains operations

One of the most exciting new capabilities of KQL is the way to analyze if a given point is contained in a circle or a polygon. The geospatial coordinates are based on the WGS-84 coordinate reference system and the geodetic datum used for measurements on earth is a sphere. Polygon edges are geodesics on the sphere. Over time we are going to extend the functionality even further.

Example

Let's try to get all interesting buildings given on a polygon like this:

The following query leverages the geo-point-in-polygon function and the GeoJSON represenation of Manhattan island without Central Park in order to project all places in the polygon.

datatable(longitude:real, latitude:real, place:string)
[
    real(-73.985654), 40.748487, 'Empire State Building',           // In Polygon 
    real(-73.963249), 40.779525, 'The Metropolitan Museum of Art',  // In exterior of polygon
    real(-73.874367), 40.777356, 'LaGuardia Airport',               // In exterior of polygon
]
| where geo_point_in_polygon(longitude, latitude, dynamic({"type":"Polygon","coordinates":[[[-73.92597198486328,40.87821814104651],[-73.94691467285156,40.85069618625578],[-73.94691467285156,40.841865966890786],[-74.01008605957031,40.7519385984599],[-74.01866912841797,40.704586878965245],[-74.01214599609375,40.699901911003046],[-73.99772644042969,40.70875101828792],[-73.97747039794922,40.71083299030839],[-73.97026062011719,40.7290474687069],[-73.97506713867186,40.734510840309376],[-73.970947265625,40.74543623770158],[-73.94210815429688,40.77586181063573],[-73.9434814453125,40.78080140115127],[-73.92974853515625,40.79691751000055],[-73.93077850341797,40.804454347291006],[-73.93489837646484,40.80965166748853],[-73.93524169921875,40.837190668541105],[-73.92288208007812,40.85770758108904],[-73.9101791381836,40.871728144624974],[-73.92597198486328,40.87821814104651]],[[-73.95824432373047,40.80071852197889],[-73.98206233978271,40.76815921628347],[-73.97309303283691,40.76422632379533],[-73.94914627075195,40.796949998204596],[-73.95824432373047,40.80071852197889]]]}))
| project place

The result is: "Empire State Building". Besides the polygons it's also possible to define regions based on circles and make use of geo-point-in-circle .

What to do next?

Go out and try it yourself on exciting open datasets like NYC-taxi or NOAA integrated surface data . You could start by exploring the global warming of earth by analyzing the average temperature of November based on geohashes over the course of the last 10 years and create beautiful PowerBI visualizations like this: