Worldmap panel with MySQL database

Hello folks,

I am using Grafana with a MySQL data source and I’d like to use the Worldmap panel. Unfortunately, the documentation of the panel doesn’t cover MySQL at all. It emphasizes how to use the various other data sources but never really explains what kind of data the panel expects to receive, so I can’t figure out what query to issue.

I tried using a query that returns a two-column table, with a timestamp in the first column and a two-character country code in the second column but it doesn’t seem to work. Do I need time stamps at all? Do I need to count the number of rows on which each country appears or will the panel do it? Maybe I have to sort or group the countries somehow? Could somebody give me an example of what kind of data the panel expects?

if you select format as Table then you should only have to return contrycode and count, then in options tab I think you can map the columns to use

So I should be using table instead of time series? OK, I’ll give that a try, although I think I remember seeing here somebody saying that when MySQL is used as a data source, Worldmap will work with time series but he wasn’t certain about table data.

I just tested with a MySQL query in the Time Series format and it worked fine for me.

With this table:

CREATE TABLE `worldmap` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `country_code` varchar(3) NOT NULL,
  `value` varchar(45) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

and with data like this:

INSERT INTO `testdata`.`worldmap`
(`country_code`,
`value`,
`timestamp`)
VALUES
('IE',
3.0,
now());

and this query (although it is probably better to do a GROUP BY and sum here rather than using Grafana’s aggregation):

SELECT
  UNIX_TIMESTAMP(`timestamp`) as time_sec,
  `value` as value,
  country_code as metric
FROM worldmap
WHERE $__timeFilter(`timestamp`)
ORDER BY `timestamp` ASC

2 Likes

Is there a way to use longitude and latitude values ?

  • Allan

Sort of. If your data source supports geohashes. You can choose a high level of precision to exact points:

Hi,

I am trying to connect Worldmap panel/plugin with MySQL.

Could I use MySQL table with float numbers for longitude and latitude data?

My (unfortunately non-working) trial dashboard:
http://zodiac.hostetin.org:3000/d/EAefnYDik/world?orgId=2&from=1530536872593&to=1530709672593

Source MySQL table is here:
http://zodiac.hostetin.org/adminer/adminer-4.2.4.php?username=sosak_usr&db=sosak&select=view_geoTeploty_wgs

where Xwgs and Ywgs represent location fields.

What are right settings in Grafana to bring Worldmap into operation?

Value column must have numeric datatype, column: value type: string value: 3
After I change the datatype to int ,the error disappear.
grafana:5.2.4:
grin:

I also have the same question, it looks like change “Map Data Options”-> “Location Data” to “table”, and change “Field Mapping”->“Table Query Format” to “coordinates”, then it receives latitude and longitude.
I tried, but not work…

Hi Daniellee,

Can you give same example on “Sql Server”, in my scenario it is not working.

SELECT
UNIX_TIMESTAMP(‘timestamp’) as time_sec,
CountryNumber as value,
ISOAlpha2Code as metric
FROM country
WHERE $__timeFilter(‘timestamp’)
ORDER BY ‘timestamp’ ASC

Thanks,
G Surendranath

 CREATE TABLE `worldmap_latlng` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lat` FLOAT NOT NULL,
  `lng` FLOAT NOT NULL,
  `name` VARCHAR(20) NOT NULL,
  `value` FLOAT NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
INSERT INTO `testdb`.`worldmap_latlng`
(`lat`,
 `lng`,
 `name`,
`value`,
`timestamp`)
VALUES
(50.72213159,
 12.45680043,
'RZ5',
1.0,
now());

Worldmap Panel -> Metrics:

SELECT
  UNIX_TIMESTAMP(`timestamp`) as time_sec,
  `value` as value,
  `lat` as latitude,
  `lng` as longitude,
  `name` as name
FROM worldmap_latlng
WHERE $__timeFilter(`timestamp`)
ORDER BY `timestamp` ASC

Worldmap Panel -> Worldmap:

  • Table Query Format = coordinates
1 Like

that not work @marekre, i try your test but not result

Hi, do you have select “Format as: → Table” at Metrics settings?

Here my current configuration:

Tested with Grafana 5.4.4 / 6.2.4 and WorldMap 0.2.0. Which Version do you use?

1 Like

my version of grafana is 5.4.2, i tested now with the version 6.4.2 and not work

image

@marekre

that work :slight_smile:

do you test with geohash ? , we win one clumn

P1

Time series is wrong, should be “Format as: Table”

Can you please try it?

yes that work now, thanks

do you test with geohash ? , we win one clumn

1 Like

oh, great :slight_smile:
No, I have not tried that with geohash yet… it’s a good idea, seems to be easier to handle :slight_smile:

@marekre I used the above example to successfully display the point, but when I switch to my own table, it also shows a point but js gives an error.

for Postgresql it work?