# Worldmap panel with MySQL database

**URL:** <https://community.grafana.com/t/worldmap-panel-with-mysql-database/1871>\
**Category:** MySQL\
**Tags:** mysql\
**Created:** [July 18, 2017, 9:00pm UTC](https://community.grafana.com/t/worldmap-panel-with-mysql-database/1871 "2017-07-18T21:00:28Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![vbontchev](https://avatars.discourse-cdn.com/v4/letter/v/e36b37/32.png) [@vbontchev](https://community.grafana.com/u/vbontchev)\
**Post date:** [July 18, 2017, 9:00pm UTC](https://community.grafana.com/t/worldmap-panel-with-mysql-database/1871/1 "2017-07-18T21:00:28Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![torkel](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/torkel/32/20_2.png) [@torkel](https://community.grafana.com/u/torkel)\
**Post date:** [July 19, 2017, 8:25am UTC](https://community.grafana.com/t/worldmap-panel-with-mysql-database/1871/2 "2017-07-19T08:25:45Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![vbontchev](https://avatars.discourse-cdn.com/v4/letter/v/e36b37/32.png) [@vbontchev](https://community.grafana.com/u/vbontchev)\
**Post date:** [July 19, 2017, 10:50am UTC](https://community.grafana.com/t/worldmap-panel-with-mysql-database/1871/3 "2017-07-19T10:50:33Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![daniellee](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/daniellee/32/41_2.png) [@daniellee](https://community.grafana.com/u/daniellee)\
**Post date:** [July 19, 2017, 11:58am UTC](https://community.grafana.com/t/worldmap-panel-with-mysql-database/1871/4 "2017-07-19T11:58:18Z")

</div>

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

With this table:

```auto
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:

```auto
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):

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

```

 ![](https://us1.discourse-cdn.com/grafana/original/2X/0/037cb80274993996b1245e8dcac69b0c21c6a744.jpg)

---

<div class="post-metadata">

**Author:** ![staticweb](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/staticweb/32/1625_2.png) [@staticweb](https://community.grafana.com/u/staticweb)\
**Post date:** [February 2, 2018, 12:28pm UTC](https://community.grafana.com/t/worldmap-panel-with-mysql-database/1871/5 "2018-02-02T12:28:38Z")

</div>

Is there a way to use longitude and latitude values ?

- Allan

---

<div class="post-metadata">

**Author:** ![daniellee](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/daniellee/32/41_2.png) [@daniellee](https://community.grafana.com/u/daniellee)\
**Post date:** [February 12, 2018, 10:28am UTC](https://community.grafana.com/t/worldmap-panel-with-mysql-database/1871/6 "2018-02-12T10:28:23Z")

</div>

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

> **[GitHub - grafana/worldmap-panel: Worldmap panel plugin for Grafana 3.0 that...](https://github.com/grafana/worldmap-panel#geohashes-as-the-data-source)**
>
> Worldmap panel plugin for Grafana 3.0 that can be overlaid with circles for data points. - GitHub - grafana/worldmap-panel: Worldmap panel plugin for Grafana 3.0 that can be overlaid with circles f...

---

<div class="post-metadata">

**Author:** ![radimmachu](https://avatars.discourse-cdn.com/v4/letter/r/87869e/32.png) [@radimmachu](https://community.grafana.com/u/radimmachu)\
**Post date:** [July 4, 2018, 1:23pm UTC](https://community.grafana.com/t/worldmap-panel-with-mysql-database/1871/7 "2018-07-04T13:23:44Z")

</div>

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](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](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?

---

<div class="post-metadata">

**Author:** ![lucaswang](https://avatars.discourse-cdn.com/v4/letter/l/ecc23a/32.png) [@lucaswang](https://community.grafana.com/u/lucaswang)\
**Post date:** [September 12, 2018, 2:29am UTC](https://community.grafana.com/t/worldmap-panel-with-mysql-database/1871/8 "2018-09-12T02:29:20Z")

</div>

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:

---

<div class="post-metadata">

**Author:** ![bgt567ujm](https://avatars.discourse-cdn.com/v4/letter/b/22d042/32.png) [@bgt567ujm](https://community.grafana.com/u/bgt567ujm)\
**Post date:** [January 16, 2019, 2:22am UTC](https://community.grafana.com/t/worldmap-panel-with-mysql-database/1871/9 "2019-01-16T02:22:17Z")

</div>

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…

---

<div class="post-metadata">

**Author:** ![surendranathgrafana](https://avatars.discourse-cdn.com/v4/letter/s/f05b48/32.png) [@surendranathgrafana](https://community.grafana.com/u/surendranathgrafana)\
**Post date:** [February 7, 2019, 10:25am UTC](https://community.grafana.com/t/worldmap-panel-with-mysql-database/1871/10 "2019-02-07T10:25:12Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![marekre](https://avatars.discourse-cdn.com/v4/letter/m/77aa72/32.png) [@marekre](https://community.grafana.com/u/marekre)\
**Post date:** [March 5, 2019, 1:40pm UTC](https://community.grafana.com/t/worldmap-panel-with-mysql-database/1871/11 "2019-03-05T13:40:45Z")

</div>

```auto
 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;

```

```auto
INSERT INTO `testdb`.`worldmap_latlng`
(`lat`,
 `lng`,
 `name`,
`value`,
`timestamp`)
VALUES
(50.72213159,
 12.45680043,
'RZ5',
1.0,
now());

```

Worldmap Panel -\> Metrics:

```auto
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

---

<div class="post-metadata">

**Author:** ![gaj](https://avatars.discourse-cdn.com/v4/letter/g/e36b37/32.png) [@gaj](https://community.grafana.com/u/gaj)\
**Post date:** [June 18, 2019, 3:31pm UTC](https://community.grafana.com/t/worldmap-panel-with-mysql-database/1871/12 "2019-06-18T15:31:53Z")

</div>

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

 ![image](https://us1.discourse-cdn.com/grafana/original/2X/a/a88a9b1d193197771f28eff7d9806aeae5741647.png)

---

<div class="post-metadata">

**Author:** ![marekre](https://avatars.discourse-cdn.com/v4/letter/m/77aa72/32.png) [@marekre](https://community.grafana.com/u/marekre)\
**Post date:** [June 19, 2019, 11:00am UTC](https://community.grafana.com/t/worldmap-panel-with-mysql-database/1871/13 "2019-06-19T11:00:49Z")

</div>

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

 ![G2](https://us1.discourse-cdn.com/grafana/original/2X/8/871e1f28290a380b42cee969d85e8d60e1a09831.png)

Here my current configuration:

 ![G3](https://us1.discourse-cdn.com/grafana/original/2X/7/75dc4f202f4744faa231197d63d11851a9752a73.png)

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

---

<div class="post-metadata">

**Author:** ![gaj](https://avatars.discourse-cdn.com/v4/letter/g/e36b37/32.png) [@gaj](https://community.grafana.com/u/gaj)\
**Post date:** [June 19, 2019, 12:06pm UTC](https://community.grafana.com/t/worldmap-panel-with-mysql-database/1871/14 "2019-06-19T12:06:49Z")

</div>

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

 ![image](https://us1.discourse-cdn.com/grafana/original/2X/9/91a28e86785f2f8066d65c7448312d626dbf8e89.png)

 ![image](https://us1.discourse-cdn.com/grafana/original/2X/b/be6ee3e68445ed5a7fd861e2f0674e1d89e4c2c0.png)

![image](https://us1.discourse-cdn.com/grafana/original/2X/f/f3ce3b63128c811e65b825651c27f2773145092c.png)

@marekre

---

<div class="post-metadata">

**Author:** ![gaj](https://avatars.discourse-cdn.com/v4/letter/g/e36b37/32.png) [@gaj](https://community.grafana.com/u/gaj)\
**Post date:** [June 19, 2019, 12:56pm UTC](https://community.grafana.com/t/worldmap-panel-with-mysql-database/1871/15 "2019-06-19T12:56:16Z")

</div>

that work 🙂

do you test with geohash ? , we win one clumn

---

<div class="post-metadata">

**Author:** ![marekre](https://avatars.discourse-cdn.com/v4/letter/m/77aa72/32.png) [@marekre](https://community.grafana.com/u/marekre)\
**Post date:** [June 19, 2019, 1:05pm UTC](https://community.grafana.com/t/worldmap-panel-with-mysql-database/1871/16 "2019-06-19T13:05:36Z")

</div>

![P1](https://us1.discourse-cdn.com/grafana/original/2X/e/e27b8b79d5a9bdb500dca18a011d1ad881e6e35b.png)

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

Can you please try it?

---

<div class="post-metadata">

**Author:** ![gaj](https://avatars.discourse-cdn.com/v4/letter/g/e36b37/32.png) [@gaj](https://community.grafana.com/u/gaj)\
**Post date:** [June 19, 2019, 1:16pm UTC](https://community.grafana.com/t/worldmap-panel-with-mysql-database/1871/17 "2019-06-19T13:16:59Z")

</div>

yes that work now, thanks

do you test with geohash ? , we win one clumn

---

<div class="post-metadata">

**Author:** ![marekre](https://avatars.discourse-cdn.com/v4/letter/m/77aa72/32.png) [@marekre](https://community.grafana.com/u/marekre)\
**Post date:** [June 19, 2019, 6:16pm UTC](https://community.grafana.com/t/worldmap-panel-with-mysql-database/1871/18 "2019-06-19T18:16:08Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![yuling565](https://avatars.discourse-cdn.com/v4/letter/y/59ef9b/32.png) [@yuling565](https://community.grafana.com/u/yuling565)\
**Post date:** [August 14, 2019, 7:22am UTC](https://community.grafana.com/t/worldmap-panel-with-mysql-database/1871/19 "2019-08-14T07:22:25Z")

</div>

![%E5%BE%AE%E4%BF%A1%E5%9B%BE%E7%89%87_20190814152118](https://us1.discourse-cdn.com/grafana/original/2X/e/e87bee5cc4f05a459b82dc99f5cb6a331f91d48c.png) @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.

---

<div class="post-metadata">

**Author:** ![kotvip](https://avatars.discourse-cdn.com/v4/letter/k/8797f3/32.png) [@kotvip](https://community.grafana.com/u/kotvip)\
**Post date:** [August 20, 2019, 9:34am UTC](https://community.grafana.com/t/worldmap-panel-with-mysql-database/1871/21 "2019-08-20T09:34:02Z")

</div>

for Postgresql it work?

[Next page](https://community.grafana.com/t/worldmap-panel-with-mysql-database/1871.md?page=2)
