Azure Stream Analytics - Request by Stream
I am using Azure Stream Analytics. I have data flowing into event hubs. The incoming data looks like this:
[
{
"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 task of Stream Analytics is to look at the data and find out if the coordinates are in a certain polygon. I already have an ST_WITHIN query. I have a link that contains all the polygons I want. The trouble is this. I need to determine when the coordinates are in the polygon and how long it is in the polygon.
Data is transferred approximately once a minute. I get new coordination every minute. I know how to detect when it is initially in the polygon. My fight is how can I tell how much time has been in the range? I've tried LAST, LAG, ISFIRST but to no avail. The goal is as follows:
- Data arrives
- Are you at the training ground?
- Yes? How long have you been at the training ground? I know here what I need to understand when he was first in the range. However, as you can see from the data above, the data could have been in the polygon 24 hours ago and is now there again. I just don't know how to structure the query to find out when I am at the polygon and for how long. Can anyone please help?
source to share
I just don't know how to structure the query to find out when I am at the polygon and for how long. Can anyone please help?
You need to get a linear result from a distributed dataset. I suggest your query is the person in the polygon for the last order of the period by date time. The data can be as follows.
| 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 getting the data, you can save it anywhere (Azure Redis or Blob Storage). Finally, you can process temporary data using an Azure Function. Sample code below for your reference.
var totalMinutes = 0;
foreach (var data in collection)
{
if (data.IsInPolygon)
{
totalMinutes++;
}
else
{
break;
}
}
How to output data to Azure Function, link below for reference.
How to store data from Azure Stream Analytics in Azure Redis cache using Azure Functions
source to share
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 "Determine Condition Duration" example. Below is a slight 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 for this query. Let me know how it goes.
Thank,
Js
source to share