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:

Data comes in
Are you in a polygon?
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?

score 1 · Answer 1 · answered Jul 27 '17 at 06:40

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

Thank you, Amor, for the response. I understand what you are saying, but are you suggesting that all data coming into the stream analytics job output into some storage mechanism that is then acted upon using Azure functions? So data comes onto event hub, stream analytics job fires off, outputs the data to storage, azure function acts on the data. Wouldn't that just be a passthrough of all data from the event hubs right into storage? — ajones, Jul 27 '17 at 11:58
Never mind, Amor, I see what you're saying. I think I have a full architecture worked out. I will try to post my results once I get things working. — ajones, Jul 27 '17 at 17:59

score 1 · Answer 2 · answered Jul 28 '17 at 19:06

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

This is great but I'm actually needing real time analysis. As each point comes in and is ST_WITHIN = 1 I need to know how long it was in that event. I've tried all day. I don't think it's possible. E.g. timeline would be 1a, 1b, 1c, 0, 0, 0, 1a, 1b, 0. Two events there but need to know time differential from 1b-1a, then 1c-1a as the 1's are coming in. Any way to do that? — ajones, Jul 31 '17 at 20:35

Azure Stream Analytics - Query along the stream

2 Answers2