Dynamic Dashboards - Creating Variables

We can use variables in Grafana to manipulate time-ranges that are used as inputs to MobilityDB queries. We’ll create a drop-down type variable called FromTime that will be used as an input for the time period within which a query returns results.

  1. In the dashboard window, click Dashboard settings icon; the gear symbol, on the top-slightly-right of the window.

    Figure 2.17. Dashboard settings gear box

    Dashboard settings gear box

  2. Click on the Variables in the next window on the top-left side of the screen.

    Figure 2.18. Selecting Variables in dashboard settings

    Selecting Variables in dashboard settings

  3. You’ll see a screen that explains the variables in Grafana and also points to the Templates and variables documentation. Click on the Add variable button.

  4. In General

    • Name → FromTime

    • Type → Custom

  5. In Custom options we will manually add all the time ranges with 1 hour increment. e.g. 2018-01-04 00:00:00, 2018-01-04 01:00:00 … 2018-01-04 23:00:00

  6. You get a screen like below. Towards the bottom there is also a Preview of values that shows what the drop-down options will look like for the variable you created. In this case, we are creating the timestamps in the same format that MobilityDB will accept.

    Figure 2.19. Creating user-defined list of custom variables

    Creating user-defined list of custom variables

  7. We can create another variable called ToTime with values shifted 1 hour. So the starting value would be 2018-01-04 01:00:00 and the final value will be 2018-01-05 00:00:00.

Now we can modify some queries by including the newly created variables which will return results from a specific time window. We have now provided a user with the ability to dynamically modify visualization queries and slice through time.

Dynamic Query: Number of Boats Moving Through a Given Area in a Certain Time Period

In the query code we just need to make slight changes for it to take time values from the variables. In the original query, shown below:

SELECT P.port_name, 
      sum( numSequences( atGeometry( S.Trip, P.port_geom))) AS trips_intersect_with_port,
      p.lat,
      p.lng
FROM ports AS P, Ships AS S
WHERE intersects(S.Trip, P.port_geom)
GROUP BY P.port_name, P.lat, P.lng

We just need to modify the trips_intersect_with_port parameter in the SELECT statement to look like:

sum
(numSequences(atGeometry( atPeriod(S.Trip, period '[$FromTime, $ToTime)'), P.port_geom)))
    AS trips_intersect_with_port

Essentially we just wrapped S.Trip with atPeriod() and passed our custom period range. The full query with this modification is below:

-- Table with bounding boxes over regions of interest
WITH ports(port_name, port_geom, lat, lng)
       AS (SELECT p.port_name, p.port_geom, lat, lng
           FROM
             (VALUES ('Rodby',
                    ST_MakeEnvelope(651135, 6058230, 651422, 6058548, 25832)::geometry,
                    54.53, 11.06),
                   ('Puttgarden',
                    ST_MakeEnvelope(644339, 6042108, 644896, 6042487, 25832)::geometry,
                    54.64, 11.36)) AS p(port_name, port_geom, lat, lng))

SELECT P.port_name,
       sum(numSequences(atGeometry(atPeriod(S.Trip, period '[$FromTime, $ToTime)'),
                                   P.port_geom))) AS trips_intersect_with_port,
       p.lat,
       p.lng
FROM ports AS P,
     Ships AS S
WHERE intersects(S.Trip, P.port_geom)
GROUP BY P.port_name, P.lat, P.lng

We can select the start time, FromTime2018-01-04 02:00:00 & ToTime2018-01-04 06:00:00 . As we can see below, the port Rodby has less activity during this period and that’s why it is green now. But overall Rodby has more activity so when we look at the entire days data it is colored red.

Figure 2.20. Visualization of geometry intersection using dynamic variables

Visualization of geometry intersection using dynamic variables

Global Variables

Grafana also has some built-in variables (global variables) that can be used to accomplish the same thing we did with custom variables. We can use the global variables ${__from:date} and ${__to:date} instead of the $FromTime and $ToTime we created. The time range can then be modified with the time range options in the top right of the dashboard.

Figure 2.21. Assigning time range using global variables

Assigning time range using global variables

Note: It is important to be aware of the timezone used for the underlying data relative to the queries for global variables. Time zones can be adjusted at the bottom of the time range selection, Change time settings. For this example, we change the time zone to UTC to match our dataset.