Grouping measurements by location (lat/lng measurement) and value

[Jens]

Hello,
I am running Influx 1.8 (and Grafana 7) on a Raspberry PI 4. Among other things, Influx receives datapoint from our electric vehicle when it drives or is getting charged. Influx receives data into various ‘measurements’, for example one measurement (with timestamp) each for latitude, longitude, odometer, speed, energy used, and charge added (while charging).

I would like to create a table of locations where I charged, with the total sum of energy charged at each respective locations, to be displayed in Grafana. The procedure I have in mind is

• In the charge_added measurement, look for zeros (because every charge starts at zero) and then take the previous value > 0 (which was the charge added the last time). Unfortunately, the charge_added measurement receives multiple values per charge process, one per minute.
• Get the lat/lng values with the same timestamp (rounded to 1min possibly) as the charge_added value.
• Sum up the charge_added values, grouped by lat/lng values.
• (possibly) label the lat/lng pairs in the table. Or use the map plugin and display the values on a map.

I would know how to script this in Ruby or Javascript, but is something like this also possible directly in Influx, or Grafana?
I think the main question is how to separate (group) the charge_added values by zero values, instead of time range. If I just “GROUP BY (time)” the charge_added, and then take last() or max(), I will miss multiple charge actions.

Thank you!

[Jay_Clifford]

Hi Jens,
Nice to meet you digitally and an interesting use case btw. So let’s break the problem down and see what we can achieve together. Firstly I think the function you are looking for can be achieved in Flux and this is called cumulativesum. Before we get to that point we need to prepare your data.

I have made a crude attempt at recreating some sample data of your use case. From this data I then produced this flux query:

raw = from(bucket: "robot_test")
      |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
      |> filter(fn: (r) => r["_measurement"] == "mqtt_consumer")
      |> filter(fn: (r) => r["_field"] == "power" or r["_field"] == "long" or r["_field"] == "lat")


pivot = raw 
        |> pivot(
          rowKey:["_time"],
          columnKey: ["_measurement", "_field"],
          valueColumn: "_value"
        )

group = pivot
        |> group(columns: ["mqtt_consumer_lat", "mqtt_consumer_long"])
        |> last(column: "mqtt_consumer_power")
        |> group()



power_sum = group
            |> cumulativeSum(columns: ["mqtt_consumer_power"])
            |> yield(name: "total power")

1. First I perform a pivot to sort our data into three separate columns (lat, long, power).

2. Since per route the robot will be stopping to charge at unique locations we can use these values to group our data (time is of no use to us here). I would also advise adding another field that contains a unique identifier for the job/task the robot was doing or it could just be the total times it’s taken this route. You could use this as part of the group (this would stop us from grouping previous runs together).

3. Next as you suggested we can take the last known value from that location which should be the total amount of power taken from that area.

4. Lastly we group our groups back into one table. Essentially a table of the last values for each group. We then perform the cumulativesum on the power column. This will provide the total power of all areas with the last row of the table.

I also highly recommend checking out the geo package for flux. This will help you group your coordinates even when they fluctuate.

I hope this helps, please come back to me if I have missed the point

Thanks,
Jay

[Jens]

Hello Jay,
and - wow, thank you for your elaborate answer. I really need to get into Flux, it seems to be much more than just a query language.
Currently I use InfluxQL mostly from Grafana, and this is my first problem: how do I get Flux support installed? I use Grafana 7.0.3, do I need to upgrade?

Thank you,
Jens

[Jay_Clifford]

Hi Jens,
No problem at all. We like an interesting use case here at Influx so this is really took my fancy . So yes for your combination you would need to update to Grafana 7.1. Your Influx version is fine but Flux query needs to be enabled. To do this follow these instructions.

Can I suggest another route before we get to this point however? Since we are still unsure whether the Flux query solves your issue it seems silly to go to all this effort to upgrade. If you download the latest InfluxDB OSS or create an InfluxDB cloud account (the free tier will be absolutely fine as we are only experimenting with our data). Then load a sample of your data and run some tests through the Data Explorer. This acts as a great tool for learning and experimenting with Flux.

[Jens]

Hello Jay,
I took the challenge and “updated” to Grafana 7.1.5 today in the hope that the upgrade will be smooth (it was) and that I can use Flux now (I can’t).

I set “flux-enabled = true” in the influxdb.conf as described in the handbook, but then Influxdb doesn’t start up any more and requests to port 8086 return HTTP error 502 (bad gateway).

Sep 30 21:16:53 raspberrypi systemd[1]: Starting InfluxDB is an open-source, distributed, time series database…
Sep 30 21:16:53 raspberrypi influxd-systemd-start.sh[2046]: Merging with configuration at: /etc/influxdb/influxdb.conf
Sep 30 21:16:53 raspberrypi influxd-systemd-start.sh[2046]: ts=2021-09-30T19:16:53.787061Z lvl=info msg=“InfluxDB starting” log_id=0Wv22~bl000 version=1.8.9 branch=1.8 commit=d9b56321d579
Sep 30 21:16:53 raspberrypi influxd-systemd-start.sh[2046]: ts=2021-09-30T19:16:53.787133Z lvl=info msg=“Go runtime” log_id=0Wv22~bl000 version=go1.13.8 maxprocs=4
Sep 30 21:16:53 raspberrypi influxd-systemd-start.sh[2046]: Merging with configuration at: /etc/influxdb/influxdb.conf
Sep 30 21:17:04 raspberrypi influxd-systemd-start.sh[2046]: Failed to reach influxdb http endpoint at http://localhost:8086/health
Sep 30 21:17:04 raspberrypi systemd[1]: influxdb.service: Control process exited, code=exited, status=1/FAILURE
Sep 30 21:17:04 raspberrypi systemd[1]: influxdb.service: Failed with result ‘exit-code’.
Sep 30 21:17:04 raspberrypi systemd[1]: Failed to start InfluxDB is an open-source, distributed, time series database.
Sep 30 21:17:04 raspberrypi systemd[1]: influxdb.service: Service RestartSec=100ms expired, scheduling restart.
Sep 30 21:17:04 raspberrypi systemd[1]: influxdb.service: Scheduled restart job, restart counter is at 2.
Sep 30 21:17:04 raspberrypi systemd[1]: Stopped InfluxDB is an open-source, distributed, time series database.

Googling a little didn’t turn up anything useful. Do you have an idea what is wrong here?

Thanks!
Jens

[Jay_Clifford]

Hi Jens,
Ah sorry to hear this. Can you send me a copy of your influxdb.conf

[Jens]

[meta]
dir = “/var/lib/influxdb3/meta”
[data]
dir = “/var/lib/influxdb3/data”
wal-dir = “/var/lib/influxdb3/wal”
wal-fsync-delay = “10s”
index-version = “tsi1”
query-log-enabled = false
max-concurrent-compactions = 1
[coordinator]
write-timeout = “20s”
[retention]
[shard-precreation]
[monitor]
store-enabled = false
[http]
auth-enabled = true
flux-enabled = true
log-enabled = false

[Jay_Clifford]

Hi Jens,
How large is your InfluxDB instance? This might be the cause and solution to the issue:

Influxdb 1.8.7 will not start

I had the same issue just yesterday. It seems they changed something in the startup script in version v1.8.9, the same one we had to +x on version 1.8.7. Turns out my DB is too big and it takes longer to start than the 10 seconds influxdb expects to take and so the start script aborts the process Workaround: Open /usr/lib/influxdb/scripts/influxd-systemd-start.sh either comment out line 34 (exit 1) – < this is a bad idea in the long run but will allow you to test if it works or set the slee…

[Jens]

Actually, changing “sleep 1” to “sleep 5” helped do the trick as well. Thank you!
Now I can configure Flux connections to InfluxDB and these are successful.
However, no Flux query - even the simplest example queries - return any results in Grafana’s Data Explorer. There is an error if I stop influxdb, so I know the connection works. But nothing is returned.

What Flux query could I use for testing that definitively works on ALL databases regardless of structure and content, and returns someting non-null that I can verify?

[Jay_Clifford]

Hi Jens,
I am ever so sorry for not getting back to you sooner. I missed the notification for your last message. Have you checked in your Grafana instance that you are using Flux? Please see below when creating Data Source:

[Jens]

Yes, Flux is enabled and I can submit Flux queries. There is actually a log entry in the influx request log (which, strangely, contains SQL-like code… but what do I know).

But I never get any results. The result set is always empty.

[Jay_Clifford]

@Anaisdg do you have any ideas what might be going on here?

[Anaisdg]

You can try

from(bucket:"mybucket")
|> range(start: 0)
|> limit(n:1) 

If there’s any data in your bucket at all that should work.

[Anaisdg]

@Jens
If Flux is connected and you can verify that you have data in InfluxDB with cURL or the InfluxDB API, then I’m afraid this might be a question for

Grafana Labs Community Forums

[Jens]

Yes, Flux is enabled and I can submit Flux queries.
Correction: Flux now works and actually returns entries. Yay! Maybe it was a syntax issue.
Thanks for your support!