Geomap with flux

InfluxDB 2
1

Grfana V10.0
InFluxDB 2.7

I need to store the following data per site - “Site name” “battery” “latitude” “level” “longitude”.

So I have a bucket flood with the following structure. (Cardinality possibly high)

I need to display the last points for each site on a Grafana GEO map. Use the query

from(bucket: "flood")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["tag1"] == "Kwezimbovu" or r["tag1"] == "Landulwazi" or r["tag1"] == "Mabedlana")
  |> filter(fn: (r) => r["_field"] == "Site" or r["_field"] == "battery" or r["_field"] == "latitude" or r["_field"] == "level" or r["_field"] == "longitude")
  |> last()
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> drop(columns: ["_start", "_stop", "_measurement"])

This obviously don’t deliver the correct result as it only show the last recorded in the DB.

I were advise to maybe change the structure of my DB, to something like.

image

I need advise on the best structure for the DB and the query.

Thank you