Grafana Geomap with InfluxdB

Grafana Geomap Panel
1

Hi all.
I am gathering geo and speed data which i want to visualize on geomap.

To retrieve the data i use the following query but i couldn’t figure out how geomap will show the data. Can someone help me to get the query and the settings in geomap to be correct?

from(bucket: "${measurements}")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r.name == "speed" or r.name == "latitude" or r.name == "longitude")
  |> keep(columns: ["_time", "_value", "name"])
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "mean")
2

Welcome @saschaluethi96

Please provide sample csv file from influxdb and post here?

speed,latitude,longitude

you can get this from influxdb port 8086 and putting this

from(bucket: "${measurements}")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r.name == "speed" or r.name == "latitude" or r.name == "longitude")
  |> keep(columns: ["_time", "_value", "name"])
1 Like
3

Hi @yosiasz

Thanks for the welcome.

I couldn’t directly get a CSV File from the query. Therefor here is the object which is shown in the grafana query inspector. I removed the irrelevant part of it. I hope this helps as well:

Object from:"1685941332777" to:"1685944932778" hideFromInspector:false response:Object results:Object A:Object frames:Array[3] 0:Object schema:Object refId:"A" meta:Object executedQueryString:"from(bucket: \"xy\") |> range(start: 2023-06-05T05:02:12.777Z, stop: 2023-06-05T06:02:12.778Z) |> filter(fn: (r) => r.name == \"speed\" or r.name == \"latitude\" or r.name == \"longitude\") |> keep(columns: [\"_time\", \"_value\", \"name\"])" fields:Array[2] 0:Object name:"Time" type:"time" typeInfo:Object frame:"time.Time" nullable:true 1:Object name:"Value" type:"number" typeInfo:Object frame:"float64" nullable:true labels:Object name:"latitude" data:Object values:Array[2] 0:Array[64] 0:1685943180000 1:1685943300000 2:1685943360000 3:1685943420000 4:1685943480000 5:1685943600000 6:1685943660000 7:1685943720000 8:1685943780000 9:1685943840000 10:1685943900000 11:1685943960000 12:1685944020000 13:1685944080000 14:1685944200000 15:1685944500000 16:1685943180000 17:1685943300000 18:1685943360000 19:1685943420000 20:1685943480000 21:1685943600000 22:1685943660000 23:1685943720000 24:1685943780000 25:1685943840000 26:1685943900000 27:1685943960000 28:1685944020000 29:1685944080000 30:1685944200000 31:1685944500000 32:1685943180000 33:1685943300000 34:1685943360000 35:1685943420000 36:1685943480000 37:1685943600000 38:1685943660000 39:1685943720000 40:1685943780000 41:1685943840000 42:1685943900000 43:1685943960000 44:1685944020000 45:1685944080000 46:1685944200000 47:1685944500000 48:1685943180000 49:1685943300000 50:1685943360000 51:1685943420000 52:1685943480000 53:1685943600000 54:1685943660000 55:1685943720000 56:1685943780000 57:1685943840000 58:1685943900000 59:1685943960000 60:1685944020000 61:1685944080000 62:1685944200000 63:1685944500000 1:Array[64] 0:3.4944297 1:3.4943751 2:3.4942698 3:3.4944466 4:3.4944088 5:3.4943645 6:3.4943484 7:3.4943346 8:3.4943018 9:3.4943025 10:3.4943525 11:3.4943168 12:3.4943748 13:3.4943742 14:3.4944326 15:3.4945489 16:3.49433889533333 17:3.49432552633333 18:3.49421865666667 19:3.4943790995 20:3.49433941766667 21:3.49432157500001 22:3.49428754516667 23:3.4942627215 24:3.4942476755 25:3.4942698565 26:3.494311448333335 27:3.49427119133333 28:3.4943466585 29:3.49431143833334 30:3.49441119816667 31:3.49447184016666 32:3.4942563 33:3.4942387 34:3.4941958 35:3.4942699 36:3.4942167 37:3.4942243 38:3.4942438 39:3.4942123 40:3.4942046 41:3.4942212 42:3.4942575 43:3.4942255 44:3.4943173 45:3.4942746 46:3.4943677 47:3.4943771 48:3.494338895351525 49:3.49432552635492 50:3.494218656669574 51:3.4943790995358 52:3.494339417711366 53:3.494321575019356 54:3.49428754517611 55:3.49426272152221 56:3.494247675510465 57:3.49426985650502 58:3.494311448342316 59:3.49427119133975 60:3.49434665850165 61:3.49431143834234 62:3.49441119817131 63:3.494471840192745 1:Object schema:Object refId:"A" fields:Array[2] 0:Object name:"Time" type:"time" typeInfo:Object frame:"time.Time" nullable:true 1:Object name:"Value" type:"number" typeInfo:Object frame:"float64" nullable:true labels:Object name:"longitude" data:Object values:Array[2] 0:Array[64] 0:1685943180000 1:1685943300000 2:1685943360000 3:1685943420000 4:1685943480000 5:1685943600000 6:1685943660000 7:1685943720000 8:1685943780000 9:1685943840000 10:1685943900000 11:1685943960000 12:1685944020000 13:1685944080000 14:1685944200000 15:1685944500000 16:1685943180000 17:1685943300000 18:1685943360000 19:1685943420000 20:1685943480000 21:1685943600000 22:1685943660000 23:1685943720000 24:1685943780000 25:1685943840000 26:1685943900000 27:1685943960000 28:1685944020000 29:1685944080000 30:1685944200000 31:1685944500000 32:1685943180000 33:1685943300000 34:1685943360000 35:1685943420000 36:1685943480000 37:1685943600000 38:1685943660000 39:1685943720000 40:1685943780000 41:1685943840000 42:1685943900000 43:1685943960000 44:1685944020000 45:1685944080000 46:1685944200000 47:1685944500000 48:1685943180000 49:1685943300000 50:1685943360000 51:1685943420000 52:1685943480000 53:1685943600000 54:1685943660000 55:1685943720000 56:1685943780000 57:1685943840000 58:1685943900000 59:1685943960000 60:1685944020000 61:1685944080000 62:1685944200000 63:1685944500000 1:Array[64] 0:70.7165807 1:70.7167882 2:70.7168466 3:70.7167551 4:70.7169024 5:70.716674 6:70.7166806 7:70.7167303 8:70.7167258 9:70.7167677 10:70.7167146 11:70.7167874 12:70.7166278 13:70.716703 14:70.7165411 15:70.7164389 16:70.716508089166666 17:70.716641178833333 18:70.716815524833335 19:70.716606914333333 20:70.716673643833333 21:70.716474395333334 22:70.716626059500001 23:70.716659331666666 24:70.716676097166665 25:70.716710630333331 26:70.716639615666667 27:70.716716295 28:70.716564802166666 29:70.716620019666667 30:70.716499951833333 31:70.716271238833333 32:70.7164295 33:70.7165542 34:70.7167545 35:70.7165176 36:70.7165543 37:70.7163436 38:70.7165347 39:70.7165755 40:70.7166214 41:70.7166678 42:70.7165812 43:70.7166284 44:70.716513 45:70.7165189 46:70.7164605 47:70.7161603 48:70.716508089239666 49:70.716641179099781 50:70.716815524856873 51:70.716606914696051 52:70.716673644498536 53:70.7164743958394 54:70.716626059567663 55:70.716659331856222 56:70.716676097221566 57:70.716710630366112 58:70.71663961575366 59:70.716716295086703 60:70.716564802209447 61:70.716620019815442 62:70.716499951864643 63:70.716271239202653 2:Object schema:Object refId:"A" fields:Array[2] 0:Object name:"Time" type:"time" typeInfo:Object frame:"time.Time" nullable:true 1:Object name:"Value" type:"number" typeInfo:Object frame:"float64" nullable:true labels:Object name:"speed" data:Object values:Array[2] 0:Array[64] 0:1685943180000 1:1685943300000 2:1685943360000 3:1685943420000 4:1685943480000 5:1685943600000 6:1685943660000 7:1685943720000 8:1685943780000 9:1685943840000 10:1685943900000 11:1685943960000 12:1685944020000 13:1685944080000 14:1685944200000 15:1685944500000 16:1685943180000 17:1685943300000 18:1685943360000 19:1685943420000 20:1685943480000 21:1685943600000 22:1685943660000 23:1685943720000 24:1685943780000 25:1685943840000 26:1685943900000 27:1685943960000 28:1685944020000 29:1685944080000 30:1685944200000 31:1685944500000 32:1685943180000 33:1685943300000 34:1685943360000 35:1685943420000 36:1685943480000 37:1685943600000 38:1685943660000 39:1685943720000 40:1685943780000 41:1685943840000 42:1685943900000 43:1685943960000 44:1685944020000 45:1685944080000 46:1685944200000 47:1685944500000 48:1685943180000 49:1685943300000 50:1685943360000 51:1685943420000 52:1685943480000 53:1685943600000 54:1685943660000 55:1685943720000 56:1685943780000 57:1685943840000 58:1685943900000 59:1685943960000 60:1685944020000 61:1685944080000 62:1685944200000 63:1685944500000 1:Array[64] 0:0.378 1:0.538 2:0.395 3:0.383 4:0.626 5:0.603 6:0.62 7:0.707 8:0.465 9:0.366 10:0.41 11:0.533 12:0.327 13:0.239 14:0.447 15:0.879 16:0.13587333333333335 17:0.1708083333333333 18:0.19778 19:0.11506333333333332 20:0.19266666666666665 21:0.25519833333333336 22:0.15576333333333334 23:0.2558516666666667 24:0.18219666666666667 25:0.13298166666666666 26:0.11037333333333334 27:0.29117333333333334 28:0.11853 29:0.09314 30:0.13998333333333332 31:0.3096883333333333 32:0.002 33:0.004 34:0.005 35:0.001 36:0.005 37:0.004 38:0.008 39:0.007 40:0.011 41:0.01 42:0.004 43:0.002 44:0.001 45:0.001 46:0.007 47:0.013 48:0.15758801773400583 49:0.19583170921312346 50:0.21629641698373092 51:0.13991595096104423 52:0.25924525710865637 53:0.29061608982757076 54:0.18556385244258466 55:0.28894515915654306 56:0.20986575868079735 57:0.15499466119837804 58:0.13018251290656002 59:0.3102132008796531 60:0.13759560312742555 61:0.10919369639925802 62:0.1601133660879066 63:0.3523956039264206 refId:"A"

4

Is this from grafana or from infkuxdb:8086 query panel

1 Like
5

If you view your query in Influx Data Explorer, there should be a CSV download button as shown in my example below:

image
image1641×807 80 KB

1 Like
6

This is from grafana

7

Unfortunately i’m currently unable to access the server on which the Influx DB is running on. Due to a new network infrastructure, I’ll have to figure out a way to be able to access it, to be able to provide you with these details.

1 Like
8

So finally here it is. Attached is the picture of the anomyized output of the query you asked for. Looking forward to your suggested query to get this thing running.

query
query535×853 13.3 KB

9

Thanks @saschaluethi96

Any possible way you can download that as .csv and post here?

2 Likes
10

Uploading of csv files unfortunately doesn’t work but i can add it as text here:

Column1;Column2;Column3;Column4;Column5;Column6
#group;false;false;false;false;true
#datatype;string;long;dateTime:RFC3339;double;string
#default;_result;;;;
;result;table;_time;_value;name
;;0;2023-05-28T12:00:00Z;52.4943235;latitude
;;0;2023-05-28T12:03:00Z;52.4943705;latitude
;;0;2023-05-28T12:04:00Z;52.4943952;latitude
;;0;2023-05-28T12:05:00Z;52.4943817;latitude
;;0;2023-05-28T12:06:00Z;52.4943929;latitude
;;0;2023-05-28T12:07:00Z;52.4943909;latitude
;;0;2023-05-28T12:08:00Z;52.4944052;latitude
;;0;2023-05-28T12:10:00Z;52.4944238;latitude
;;0;2023-05-28T12:11:00Z;52.4943554;latitude
;;0;2023-05-28T12:12:00Z;52.4942854;latitude
;;0;2023-05-28T12:16:00Z;52.4944302;latitude
;;0;2023-05-28T12:17:00Z;52.4944159;latitude
;;0;2023-05-28T12:18:00Z;52.4944353;latitude
;;0;2023-05-28T12:19:00Z;52.4943542;latitude
;;0;2023-05-28T12:20:00Z;52.494379;latitude
;;1;2023-05-28T12:00:00Z;0.7167917;longitude
;;1;2023-05-28T12:03:00Z;0.7166295;longitude
;;1;2023-05-28T12:04:00Z;0.7166379;longitude
;;1;2023-05-28T12:05:00Z;0.7165911;longitude
;;1;2023-05-28T12:06:00Z;0.7165963;longitude
;;1;2023-05-28T12:07:00Z;0.7165782;longitude
;;1;2023-05-28T12:08:00Z;0.7166274;longitude
;;1;2023-05-28T12:10:00Z;0.7165727;longitude
;;1;2023-05-28T12:11:00Z;0.7168433;longitude
;;1;2023-05-28T12:12:00Z;0.7169106;longitude
;;1;2023-05-28T12:16:00Z;0.7165527;longitude
;;1;2023-05-28T12:17:00Z;0.7165559;longitude
;;1;2023-05-28T12:18:00Z;0.7166495;longitude
;;1;2023-05-28T12:19:00Z;0.7166965;longitude
;;1;2023-05-28T12:20:00Z;0.716637;longitude
;;2;2023-05-28T12:00:00Z;0.305;speed
;;2;2023-05-28T12:03:00Z;0.351;speed
;;2;2023-05-28T12:04:00Z;0.509;speed
;;2;2023-05-28T12:05:00Z;0.57;speed
;;2;2023-05-28T12:06:00Z;0.373;speed
;;2;2023-05-28T12:07:00Z;0.349;speed
;;2;2023-05-28T12:08:00Z;0.599;speed
;;2;2023-05-28T12:10:00Z;1.556;speed
;;2;2023-05-28T12:11:00Z;1.126;speed
;;2;2023-05-28T12:12:00Z;0.644;speed
;;2;2023-05-28T12:16:00Z;0.27;speed
;;2;2023-05-28T12:17:00Z;0.651;speed
;;2;2023-05-28T12:18:00Z;0.891;speed
;;2;2023-05-28T12:19:00Z;0.912;speed
;;2;2023-05-28T12:20:00Z;0.327;speed

1 Like
12

Thanks for your answer.

Unfortunately i couldn’t get it running with this query. As soon as i add the filter for the tag1, i don’t get any data anymore, as my data has no tag1.
I also had to adapt the query filter from _field to name, because the _field doesn’t contain the names you filtered for.

Unfortunately it still doesn’t work.

I think the position data is not inserted correctly.

from(bucket: “${measurements}”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “geodata”)
|> filter(fn: (r) => r[“_field”] == “mean”)
|> filter(fn: (r) => r.name == “latitude” or r.name == “longitude” or r.name == “speed”)
|> pivot(rowKey:[“_time”], columnKey: [“name”], valueColumn: “_value”)
|> drop(columns: [“_start”, “_stop”, “_measurement”, “type”])

#group,false,false,false,true,true,true,true,false
#datatype,string,long,dateTime:RFC3339,string,string,string,string,double
#default,_result,
,result,table,_time,_field,manufacturer,position,unit,speed
,0,2023-05-29T06:38:00Z,mean,Example,None,m/s,0.1340616666666667
,0,2023-05-29T06:39:00Z,mean,Example,None,m/s,0.12490666666666667
,0,2023-05-29T06:40:00Z,mean,Example,None,m/s,0.15754666666666664
,0,2023-05-29T06:41:00Z,mean,Example,None,m/s,0.1601766666666667
,0,2023-05-29T06:58:00Z,mean,Example,None,m/s,0.13971
,0,2023-05-29T07:28:00Z,mean,Example,None,m/s,0.06371166666666667
,0,2023-05-29T07:29:00Z,mean,Example,None,m/s,0.10502
,0,2023-05-29T07:30:00Z,mean,Example,None,m/s,0.08490333333333332
,0,2023-05-29T07:36:00Z,mean,Example,None,m/s,0.19732833333333336
,0,2023-05-29T07:37:00Z,mean,Example,None,m/s,0.37923999999999997
,0,2023-05-29T08:28:00Z,mean,Example,None,m/s,0.10796000000000001
,0,2023-05-29T08:29:00Z,mean,Example,None,m/s,0.13311000000000003
,0,2023-05-29T08:30:00Z,mean,Example,None,m/s,0.12991
,0,2023-05-29T08:39:00Z,mean,Example,None,m/s,0.24276499999999998

13

That is just a query for my influxdb.
You never provided tag and measurement data so we had to guess those

You will need to plug in your own tag (if you have tag(s) ) and measurement.

Dont just copy pasta :spaghetti:

14

Hey yosiasz.

I got it running now with some modifications. Thanks for the support from all of you!

What felt strange for me, is that i had to drop some irrelevant columns in order to see the data. That’s what made me to need quite some time to get it running.

1 Like
15

Congrats @saschaluethi96

Remember that Flux queries are pipe-forward operators, so if you have (or had) a line that filtered out some result which you later needed, it would not be there. The order of the Flux functions usually matters a lot.

1 Like