Grafana location map - split lat/lon string from geocoded_location

Configuration
1

Hi all,

the Home Assistant companion (Android) app sends the location of my phone to HA. This works perfectly, I can see a sensor.geocoded_location entity in HA, which shows latitude/longitude data. I send this data to an InfluxDB database, and try to plot it on a map with Grafana. The Worldmap plugin in grafana needs two separate fields with latitude and longitude, but in the InfluxDB database they are joined together in one string: [lat, lon]

Does anybody managed to get around this problem? How can I write a query that can split the string provided by the mobile app?

Thanks!

2

I can’t check if it works because I’ve been using the LTSS component for a while now and save my long term data to a TimescaleDB, but maybe a simple split function will help?:
https://v2.docs.influxdata.com/v2.0/reference/flux/stdlib/strings/split/

3

Split would be definitely one way to solve this issue. However, the query language (InfluxQL) doesn’t support this function. After some research I’ve found that it is part of another query language developed by the Influx team, called Flux. There is a beta plugin for grafana, I could install and use it as a new datasource. I’ll definitely keep this in mind, but I feel an easier solution here, I’m just not experienced enough to find it with my current tools :slight_smile:

4

Oh, I’m sorry, you’re absolutely right. The Docu I postet was for Flux and not for InfluxQL.
Maybe you can do it with some kind of Regex with InfluxQL?:

But as far as I can see, it is not really possible with InfluxQL, because there is no string processing intended. Wherever I could find something about this, it is recommended to generate separate field values pairs for the data.:

If you don’t have any old data that you still need, you might realy want to try LTSS. This makes data manipulation much easier in any case, since it basically uses a PostgresDB.

5

Hi,
i had the same problem and i tinkered a bit with 5-10 different examples until I found the right solution.

import "strings"
from(bucket: "homeassistant")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["friendly_name"] == "X11Pro Geocoded Location")
  |> filter(fn: (r) => r["_field"] == "location_str")
  |> keep(columns: ["_time", "_value", "_field"])
  |> map(fn: (r) => {
    clean = strings.replace(v: r._value, t: "[", u: "", i: 2)
    final = strings.replace(v: clean, t: "]", u: "", i: 2)
    parts = strings.split(v: final, t: ", ")
    return { time: r["_time"], lat: float(v: parts[0]), lon: float(v: parts[1])}
  })

thats that I used in grafana to be able to create a geo map with all my location from my little test trip through town today.

6

Does this flux query still work for you? When I run it in grafana, I get 0 series returned, when I run it in InfluxDB, I get the same table output I was receiving (not split lon/lat) prior to trying your map solution. I am new to InfluxDB & Grafana, I’ve been recording my location in InfluxDB for a while and ready to visualize my data. Thank you for the help!

7

yeah somehow they changed the nameing somethere

|> filter(fn: (r) => r[“friendly_name”] == “X11Pro Geocoded Location” or r[“friendly_name”] == “X11Pro Geocoded location”)