Azure Stream Analytics built-in geospatial functions

Azure Stream Analytics has had severe important updates the last few months. And these updates make the Azure Stream Analytics job even more powerful.

Microsoft has added the ability for adding your own JavaScript functions. And the job is integrated into Visual Studio (2013 and 2015) using advanced tooling.

Working with Geo coordinates has alway intrigued me. So the third major update is my favorite: built-in geospatial functions.

There are three functions for supporting GeoJSON objects:

  1. CreatePoint. Returns a GeoJSON Point record
  2. CreatePolygon. Returns a GeoJSON Polygon record
  3. CreateLineString. Returns a GeoJSON LineString record

So we have points, lines and areas.

And there are several functions for checking out the relation between these records:

  1. ST_WITHIN. Check if a point lays within an area
  2. ST_OVERLAPS. Check if an area lays within another area
  3. ST_INTERSECTS. Check if two lines overlap
  4. ST_DISTANCE. Calculate the distance between two points in meters

Let check this out using Stream Analytics.

Calling these function is easy, no extra effort is needed to use these function. In these examples, I have hard coded the points just to make them more readable:

SELECT
    ST_WITHIN(
      CreatePoint(15.0,15.0),
      CreatePolygon(
        CreatePoint(10.0, 10.0),
        CreatePoint(10.0, 20.0),
        CreatePoint(20.0, 20.0),
        CreatePoint(20.0, 10.0),
        CreatePoint(10.0, 10.0))
        ) as withinTrue
    ,
    ST_WITHIN(
      CreatePoint(76.6,10.1),
      CreatePolygon(
        CreatePoint(0.0, 0.0),
        CreatePoint(0.0, 10.0),
        CreatePoint(10.0, 10.0),
        CreatePoint(10.0, 0.0),
        CreatePoint(0.0, 0.0))
        ) as withinFalse
    ,
    ST_WITHIN(
      CreatePoint(15.0,15.0),
      CreatePolygon(
        CreatePoint(10.0, 10.0),
        CreatePoint(20.0, 10.0),
        CreatePoint(20.0, 20.0),
        CreatePoint(10.0, 20.0),
        CreatePoint(10.0, 10.0)) --righthanded, clockwise
        ) as rightHandNotWithin
    ,
    ST_OVERLAPS(
      CreatePolygon(
        CreatePoint(0.0, 0.0),
        CreatePoint(0.0, 10.0),
        CreatePoint(10.0, 10.0),
        CreatePoint(10.0, 0.0),
        CreatePoint(0.0, 0.0)),
      CreatePolygon(
        CreatePoint(30.0, 30.0),
        CreatePoint(30.0, 40.0),
        CreatePoint(40.0, 40.0),
        CreatePoint(40.0, 30.0),
        CreatePoint(30.0, 30.0))
        ) as overlapFalse
    ,
    ST_OVERLAPS(
      CreatePolygon(
        CreatePoint(0.0, 0.0),
        CreatePoint(0.0, 20.0),
        CreatePoint(20.0, 20.0),
        CreatePoint(20.0, 0.0),
        CreatePoint(0.0, 0.0)),
      CreatePolygon(
        CreatePoint(10.0, 10.0),
        CreatePoint(10.0, 40.0),
        CreatePoint(40.0, 40.0),
        CreatePoint(40.0, 10.0),
        CreatePoint(10.0, 10.0))
        ) as overlapTrue
    ,
    ST_OVERLAPS(
      CreatePolygon(
        CreatePoint(0.0, 0.0),
        CreatePoint(10.0, 0.0),
        CreatePoint(10.0, 10.0),
        CreatePoint(0.0, 10.0),
        CreatePoint(0.0, 0.0)), --righthanded, clockwise
      CreatePolygon(
        CreatePoint(30.0, 30.0),
        CreatePoint(40.0, 30.0),
        CreatePoint(40.0, 40.0),
        CreatePoint(30.0, 40.0),
        CreatePoint(30.0, 30.0)) --righthanded, clockwise
        ) as rightHandOverlaps
    ,
    ST_INTERSECTS(
      CreateLineString(
        CreatePoint(-10.0, 0.0),
        CreatePoint(0.0, 0.0),
        CreatePoint(10.0, 0.0)),
      CreateLineString(
        CreatePoint(0.0, 10.0),
        CreatePoint(0.0, 0.0),
        CreatePoint(0.0, -10.0))
        ) as intersectTrue
    ,
    ST_INTERSECTS(
      CreateLineString(
        CreatePoint(-10.0, 0.0),
        CreatePoint(0.0, 0.0),
        CreatePoint(10.0, 0.0)),
      CreateLineString(
        CreatePoint(-10.0, 10.0),
        CreatePoint(0.0, 10.0),
        CreatePoint(10.0, 10.0))
        ) as intersectFalse
    ,
    ST_DISTANCE(
      CreatePoint(0.0, 0.0),
      CreatePoint(0.0, 179.99)
      ) as distanceEquator
    ,
    ST_DISTANCE(
      CreatePoint(89.99, 0.0),
      CreatePoint(89.99, 179.99)
      ) as distanceNorthpole
INTO
    Output
FROM
    input

This result in this output (indented for readability):
geo02

Just to be sure, a point on the map is a combination of a latitude value (South-North) and a longitude value (West-East). Microsoft seems to forget this once in a while, so do not always trust the documentation.

So what we see is pretty straightforward. You can draw the solution on a piece of paper and calculate the outcome for yourself But there are two strange outcomes:

  • The value ‘rightHandNotWithin’ show a point NOT within an area? Although this seems to be a copy of ‘withinTrue’?
  • The value ‘rightHandOverlaps’ shows two different areas overlapping? Although this seems to be a copy of ‘overlapFalse’?

What is happening?

Left-handed versus Right-handed

Be aware when working with polygons, there are a number of rules! One of them is that you will have to check if a polygon is drawn ‘left-handed’.  When you draw it on the map and walk along the line of the polygon which draws the area, your left hand has to point to the (inner) area. It’s like counter-clockwise or anti-clockwise.

Note: This is the opposite of what is described here! Please check the examples provide for yourself.

If you ignore this orientation, it’s difficult to predict the outcome. Why? Because, if you describe a right-handed polygon, not the area within the polygon is taken into account, just the opposite: the rest of the world! (it’s like wearing your underpants inside out: the whole world is wearing your underpants, but you are not 🙂 )

Note: this orientation issue is pretty common. Maybe you can come up with a custom function?

Distance calculation

The last two calculations are not that hard to understand. First, we see the distance between two points, following the equator:

geo03

The perimeter of out planet is approx. 40.000 kilometers. So halfway (the longitude goes from zero to almost 180), we end up with 20,036,395.03 meters.

The second distance could be hard to interpret. Why is this distance just 2.2 kilometers, although the longitude is almost the same as out previous calculation? The explanation is pretty simple: we are on the top of the world, near the north pole. So all the same latitudes come together, whatever the longitude is.

Testing in Visual Studio

Working with queries in Stream Analytics is pretty cumbersome. If you are testing live, you have to stop the current job, change the query, restart the job and check the output. This could take a lot of time. Microsoft has made it a bit easier when they introduced test functionality within the browser. But even that is not optimal.

I want to end this blog with a nice feature: all the functions can be tested in Visual Studio.

Step 1: Install the Stream Analytics Tools for Visual Studio.

Step 2: Start a new empty Stream Analytics Project:

geo07

Step 3: Add a new Script item to the project and copy into it, the script above:

geo08

Step 4: Just outside Visual Studio, create some dummy data using Visual Studio code. Take your attention to the JSON formatting, close the values as an array:

geo06

Note: we do not actually use these values, but we need just one input line to execute the Stream Analytics script, later on

Step 5: Add this file as local input. Right-click the project and select ‘Add local input’:

geo04

Note: the name of the alias is the same as the input in the script

That’s all! Now execute the project:

geo05

After closing this message, an output.csv is shown in an output folder.

I can open it with Excel and I get the expected result:

geo01

Conclusion

The tooling is still a bit sensitive (as in unstable) and the documentation needs some polishing, though. I have only shown a little part of all abilities of the tooling but with these new features, Stream Analytics has become an even more powerful tool.

Advertenties