Hello guys,
I use an InfluxDB time series database and I’m trying to set up a World Map that displays the last location of some devices, but it shows every location recorded over time.
As you can see on the screenshots I set FORMAT AS to Table and in the World Map Settings I set it to Table and current.
PS: I also added the “last()” Selector to the longitude and latitude field
Thank you in advance
Best regards Tobias
I tried a lot in the past few weeks to get it work and noticed something interesting.
For testing purposes, I tried to get the last location from only one device but there is no point on the map showing up.
As mentioned before I use the “last()” selector in all fields but then multiple points from one device gets displayed. Now i used the reduce transformation with last(not null).
Does anyone has an idea why this is like it is and if I maybe forgot something?
If I try this and deactivate the transformation still multiple points on the map are getting displayed but I want only the last point to show on the map
Thanks for tagging me @dianapayton.
@tobias2001 Influxer here, though I’m just an InfluxQL beginner. One of my mantras when working on technical issues is, “try a lot of stuff”. In that vein, can you please share what you get when you use last() on just one of each of the columns (lat, long, error, and device)? You can also try last(*).
When sharing, please share both the query and a snippet of results as text, rather than an image. That makes it easier for me to share with my teammates for ideas.
In case you haven’t already studied the doc on last(), here you go: https://docs.influxdata.com/influxdb/v1.8/query_language/functions/#last
If you’re open to using Flux, it has a last() function that works well: https://docs.influxdata.com/influxdb/cloud/reference/flux/stdlib/built-in/transformations/selectors/last/
Thank you for your help and quick response
If I try last(lat)
SELECT last(“location_lat”) AS “latitude”, “location_lon” AS “longitude”, “_65226_0_0” AS “error”, “device_id” AS “ID” FROM “consumer” WHERE (“topic” =~ /^$device$/) AND $timeFilter GROUP BY time($__interval) fill(null)
or last(lon)
SELECT “location_lat” AS “latitude”, last(“location_lon”) AS “longitude”, “_65226_0_0” AS “error”, “device_id” AS “ID” FROM “consumer” WHERE (“topic” =~ /^$device$/) AND $timeFilter GROUP BY time($__interval) fill(null)
I get 11 points on the map.
With last() on each column I get 43 points.
So a little better I guess 
When I use last(*) nothing gets displayed probably because there are more columns than I used
SELECT last(*) FROM “consumer” WHERE (“topic” =~ /^$device$/) AND $timeFilter GROUP BY time($__interval) fill(null)
Tobias, thanks for iterating. To continue in that vein:
The snippet AND timeFilter seems odd to me. There’s no expression along the lines of AND timeFilter = something. What happens when you remove AND timeFilter?
Also, what happens when you remove the WHERE clause, when you remove the GROUP BY clause, and when you remove both WHERE and GROUP BY?
The bigger idea here is to start with a simpler query and work up from there.
Separate topic: regexes are slow at scale. So, if you can add a tag to remove the need for a regex, that will help if you have a lot of data.
I tried the things you wrote and multiple different combinations, but nothing worked. 
Im not sure if I get this error because the query is wrong, but maybe because my world map settings are not right.
Is it correct or am I overlooking something?
Hi Tobias,
A couple of ideas:
– If not, go into InfluxDB and make sure your query is correct (delivers expected results) before using that query in Grafana
– If yes, then I’m guessing the issue is due to the map settings. (Sorry, I’m not a Grafana expert so I can’t comment on the screenshots you shared).
I think i got a solution.
I changed the text edit mode an tried some things.
SELECT last(“location_lat”) AS “latitude”, last(“location_lon”) AS “longitude”, last(“location_alt”) AS “metric”, last(“device_id”) AS “name” FROM “consumer” WHERE (“topic” = ‘/devices/’) AND timeFilter GROUP BY time(__interval) fill(null)
With this query I get multiple points on the map as mentioned before.
If I delete everything after devices
SELECT last(“location_lat”) AS “latitude”, last(“location_lon”) AS “longitude”, last(“location_alt”) AS “metric”, last(“device_id”) AS “name” FROM “consumer” WHERE (“topic” = ‘/devices/’)
I get only the last location by time.
