Azure Stream Analytics - Query along the stream

Question

I am using Azure Stream Analytics. I have data coming into event hubs. The data coming in looks as below:

[
    {
        "id": "a2b8bcd8-ff79-4bb7-a86f-615556104c1f",
        "mechanism": "geo",
        "datetimereporting": "2017-07-23 11:08:00",
        "geometry": {
            "type": "Point",
            "coordinates": [-85.78378, 38.68679]
        }
    },
    {
        "id": "a2b8bcd8-ff79-4bb7-a86f-615556104c1f",
        "mechanism": "geo",
        "datetimereporting": "2017-07-23 11:09:00",
        "geometry": {
            "type": "Point",
            "coordinates": [-85.79378, 38.68679]
        }
    },
    {
        "id": "a2b8bcd8-ff79-4bb7-a86f-615556104c1f",
        "mechanism": "geo",
        "datetimereporting": "2017-07-24 14:08:00",
        "geometry": {
            "type": "Point",
            "coordinates": [-85.78378, 38.68679]
        }
    },
    {
        "id": "a2b8bcd8-ff79-4bb7-a86f-615556104c1f",
        "mechanism": "geo",
        "datetimereporting": "2017-07-24 14:09:00",
        "geometry": {
            "type": "Point",
            "coordinates": [-85.79378, 38.68679]
        }
    },
    {
        "id": "a2b8bcd8-ff79-4bb7-a86f-615556104c1f",
        "mechanism": "geo",
        "datetimereporting": "2017-07-24 14:10:00",
        "geometry": {
            "type": "Point",
            "coordinates": [-85.80378, 38.68679]
        }
    },
    {
        "id": "8bb76874-5b91-400d-b0cb-04c8e6c48d26",
        "mechanism": "geo",
        "datetimereporting": "2017-07-24 14:09:00",
        "geometry": {
            "type": "Point",
            "coordinates": [-115.17281, 36.11464]
        }
    },
    {
        "id": "31453016-067f-4664-ade9-244a1d7b769c",
        "mechanism": "geo",
        "datetimereporting": "2017-07-24 14:10:00",
        "geometry": {
            "type": "Point",
            "coordinates": [-85.76477, 38.32873]
        }
    }   
]

The Stream Analytics task is to look at the data and find out if the coordinates coming in are in a particular polygon. I already have the ST_WITHIN query working. I have a reference blob that contains all polygons I want. The trouble is this. I need to detect when the coordinates are in the polygon and for how long it's been in the polygon.

The data is streaming about once per minute. I get a new coordinate every minute. I know how to detect when it's initially in the polygon. My struggle is how can I tell how long it has been in the polygon? I have tried LAST, LAG, ISFIRST, but to no avail. The goal is as follows:

1. Data comes in
2. Are you in a polygon?
3. Yes? How long have you been in the polygon? I know here that I need to understand when it was first in the polygon. However, as you can see from the data above, the data could have been in the polygon 24 hours ago and now it's in there again. I just don't know how to structure a query to find out when I'm in the polygon and for how long. Can anyone assist?

Answer 1

I just don't know how to structure a query to find out when I'm in the polygon and for how long. Can anyone assist?

What you need is getting a linear result from a set of dispersed data. I suggest your query out whether the man is in a polygon for recent period order by date time desc. The data could be as following.

| id                          | Is in polygon | date time           | 
|-----------------------------|---------------|-------------------- |
| a2b8bcd8-ff79-4bb7-a86f-xxx | true          | 2017-07-23 11:08:00 | 
| a2b8bcd8-ff79-4bb7-a86f-xxx | true          | 2017-07-23 11:07:00 | 
| a2b8bcd8-ff79-4bb7-a86f-xxx | false         | 2017-07-23 11:06:00 | 

After got the data, you could store it in any place(Azure Redis or Blob Storage). Finally, you could process the temporary data using Azure Function. Sample code below is for your reference.

var totalMinutes = 0;
foreach (var data in collection)
{
    if (data.IsInPolygon)
    {
        totalMinutes++;
    }
    else
    {
        break;
    }
}

For how to output data to Azure Function, link below is for your reference.

How to store data from Azure Stream Analytics in an Azure Redis Cache using Azure Functions

Answer 2

You can also put all the logic in Stream Analytics to get this in real time. I would use some query similar to the one described in our "typical query patterns" here. Your case is similar to the "Detect duration of a condition" example. Below some quick modification of the example to calculate the total duration in a polygon:

    WITH SelectPreviousEvent AS
(
SELECT
*,
    LAG([time]) OVER (LIMIT DURATION(hour, 24)) as previousTime,
    LAG(ST_WITHIN(mypoint,mypolygon)) OVER (LIMIT DURATION(hour, 24)) as previousST_WITHIN
FROM input TIMESTAMP BY [time]
)

SELECT 
    LAG(time) OVER (LIMIT DURATION(hour, 24) WHEN ST_WITHIN(mypoint,mypolygon) = 0 ) [StartFault],
    previousTime [EndFault]
FROM SelectPreviousEvent
WHERE
    ST_WITHIN(mypoint,mypolygon) = 0
    AND previousST_WITHIN = 1

You will need some customization of this query. Let me know how it goes.

Thanks,

JS