Create map of US state(South Carolina) and counties in SC with the help of Worldmap Panel, using SQL server datasource

sonyvl · June 10, 2019, 1:18pm

Hi There,

I want to create a map of South Carolina with counties in it. I have county name, latitude and longitude fields available within the data. I am using MSSQL Server data source.

Thanks in advance,
Sony.

mefraimsson · June 11, 2019, 3:11pm

What have you tried to far? Can you include a screenshot of your query and the panel options?

Think everything should be documented here, have a look at table data section: https://grafana.com/plugins/grafana-worldmap-panel

sonyvl · June 12, 2019, 6:01am

Hi @mefraimsson,

Thank you for the response. I have created a WorldMap panel and the data source attached to it. I am using MSSQL, and when I inspect the query I am getting all values in response.

But on the world map panel, I am getting an error ‘Cannot read property ‘length’ of undefined’, please find below screenshot.

Thanks,
Sony.

amotl · June 12, 2019, 8:54am

Dear Sony,

can you also share a screenshot of your panel settings?

With kind regards,
Andreas.

sonyvl · June 12, 2019, 10:05am

Hi Andreas,

Thank you for the response, not sure whether I have missed any settings, please find below screenshot.

Thanks,
Sony.

amotl · June 12, 2019, 10:16am

Dear Sony,

there might be a hiccup. On the first screenshot, it says “Table format”, while the second one tells us something about “Timeseries data”. Both are different kinds of beasts and determine how data is queried and how it is flowing through Grafana. You will have to learn that first.

We have been able to ramp up our remote-hands skills on that topic recently. You might also follow these conversations where at some point it might also click for you.

Especially, we would like to point out the Quick summary about using the Worldmap Panel with timeseries data coming from InfluxDB. This might also clarify some things for you.

Good luck getting into this world!

With kind regards,
Andreas.

mefraimsson · June 12, 2019, 2:34pm

Maybe wrong here, but given you’ve selected table format in query and you select latitude and longitude you probably want to change Location Data config to table and then below Table Query Format as coordinates.

Then input your column names in each config field where applicable.

With my suggested config I don’t think you’ll need to select a time column in query.

sonyvl · June 13, 2019, 5:09am

Could you please provide code samples for the same, I am new to Grafana.

Thanks,
Sony.

amotl · June 13, 2019, 5:22am

Dear Sony,

what @mefraimsson is talking about here is that you are probably on the right track with using table format as you are having location information in your datasource itself (as opposed to timeseries format, where location information is coming from an out-of-band JSON).

While I do like code as well, I believe screenshots are better in this specific context.

Metrics

Worldmap panel settings

With kind regards,
Andreas.

sonyvl · June 13, 2019, 5:31am

Hi Andreas,

Thank you for the quick response. Could you please send the sample query also, it will help me to achieve the goal. I am using MSSQL server 2016, FYI.

Thanks,
sony.

sonyvl · June 13, 2019, 5:40am

Hi Andreas,

Thank you very much for your great help!!.

Now I am able to load the map.

Thanks once again.

Sony.

amotl · June 13, 2019, 8:00am

Good to hear that you made it through.

Happy mapping!

All the best,
Andreas.

sonyvl · June 19, 2019, 9:02am

Could you please advice me to implement cross filtering in my dashboard. I am using MS SQL as data source.

Can I filter all charts/panels with mouse events for eg. click on a pie chart legend or click on one bar of my bar chart/ click and drag on bar chart etc.

Thanks in advance,
Sony.

amotl · June 19, 2019, 9:19am

Dear Sony,

as far as we know, all vanilla Grafana Panels will only allow for interaction to manipulate the time-axis by selecting a time range on the Graph Panel and also allow showing/hiding specific time-series by toggling the items in the legend.

We haven’t used any Panel Plugin yet which would allow for interactive filtering of arbitrary query constraints.

However, we dearly would like to be able to do just that and might dedicate some work into this in the future. For reference, we are tracking things like that with [1].

Please let us know if we got your question right and if this might be the appropriate answer. Otherwise, please feel free to get back again by rephrasing your question.

With kind regards,
Andreas.

[1] https://community.hiveeyes.org/t/wunschliste-fur-verbesserungen-im-grafana/1834/22

mefraimsson · June 20, 2019, 8:32am

We’re currently looking into more advanced drilldown links: https://github.com/grafana/grafana/issues/15130 for the graph panel to start with