Hi all,
I have a bucket containing metadata coming from license plate cams. All records have a tag “camid” and a field “plate” containing the numberplate.
I have been sweating for a few hours without success to come up with a querie that counts the number of unique plates/vehicles that first passes camera A with camid “camA” and then cameraB with camid “camB” within a timeframe of 5 minutes or less for the current day.
Anyone who can point me in the right direction?
Wkr,
Kris
I made some progress and my querie result is now a querie with records matching the camID and different timestamp, i also used the duration function to calculate the timedifference between the 2 timestamps. Downside is that to place it in a table i have to convert to string.
Any idea on how i can filter out the records that have a positive time difference smaller or equal to 300s
q1 = from(bucket: “recognitions”)
|> range(start: today())
|> filter(fn: (r) => r._measurement == “transit” and r.camid == “camA” and r._field == “plate”)
|> map(fn: (r) => ({ plate: r._value, time: r._time, camid: r.camid }))
q2 = from(bucket: “recognitions”)
|> range(start: today())
|> filter(fn: (r) => r._measurement == “transit” and r.camid == “camB” and r._field == “plate”)
|> map(fn: (r) => ({ plate: r._value, time: r._time, camid: r.camid }))
join(tables: {key1: q1, key2: q2}, on: [“plate”], method:“inner”)
|> map(fn: (r) => ({plate: r.plate, timeA: r.time_key1, timeB: r.time_key2, camidA: r.camid_key1, camidB: r.camid_key2, time_diff: string(v: duration(v: uint(v: r.time_key2) - uint(v: r.time_key1)))}))
Hi @Kaaimann
Very interesting application! After your first post, I thought about it some more and felt you would need some sort of join() function would be needed and also a distinct() or unique() function (since you said you need to count the number of unique plates).
Anyhow, to filter out the records which have a positive time difference <= 300s, can you just insert a filter function, such as this?
|> filter(fn: (r) => r._value <= 300)
Using unique() was nog the way forward due to the fact that a specific plate can occur more than once a day. My querie above does the thing i want it to do and make a new with table that matches each record on camA with timestampA to each record with the same plate on camB with timestampB.
However the column time_diff nu spits out data like this in string format
Anybody an idea how i can filter this so that i am only left with a positive time difference that is smaller or equal to 300 seconds?
I am not very well-versed in regex, but it seems you could build a series of filters with regex that could do things like:
I will play around with this if I have time.
