Creating a Dashboard

With the dashboard configured, and a datasource added, we can now build different panels to visualize data in intuitive ways.

Speed of Individual Ships

Let’s visualize the speed of the ships using the previously built query. Here we will represent it as a statistic with a color gradient.

  1. Add a new panel

  2. Select DanishAIS as the data source

  3. In Format as, change Time series to Table and choose Edit SQL

  4. Here you can add your SQL queries. Let’s replace the existing query with the following SQL script:

    SELECT mmsi, ABS( twavg(SOG) * 1.852 - twavg( speed(Trip))* 3.6 ) AS SpeedDifference
    FROM Ships
    ORDER BY SpeedDifference DESC
    LIMIT 5;
  5. We can also quickly do some datatype transformations to help Grafana correctly interpret the incoming data. Next to the Query button, select Transform, add Convert field type and choose mmsi as String.

    Figure 2.2. Datatype transformations in Grafana

    Datatype transformations in Grafana

  6. We will modify some visualization options in the panel on the right.

    First, choose stat as the visualization

    Figure 2.3. Choosing visualization type

    Choosing visualization type

    Panel Options: Give the panel the title Incorrect AIS Boat Speed Reporting

    Value Options:

    • Show: All values

    • Fields: speeddifference

      Figure 2.4. Value options dialogue box

      Value options dialogue box

    Note: we can include a limit here instead of in our SQL query as well.

    Stat Styles:

    • Orientation: Horizontal

      Figure 2.5. Stat styles dialogue box

      Stat styles dialogue box

    Standard Options:

    • Unit: Velocity → meter/second (m/s). Note: you can scroll in the drop-down menu to see all options.

    • Color scheme: Green-Yellow-Red (by value)

    Figure 2.6. Standard options dialogue box

    Standard options dialogue box


    • remove the existing threshold by clicking the little trash can icon on the right. Adding a threshold will force the visualization to color the data a specific color if the threshold is met.

    Figure 2.7. Thresholds dialogue box

    Thresholds dialogue box

The final visualization will look like the screenshot below.

Figure 2.8. Individual ship speed statistics visualization

Individual ship speed statistics visualization

Routes Used Most Frequently Visualized with a Static Heat Map

We can visualize the routes used by ships with a heat map generated from individual GPS points of the ships. This approach is quite costly, so we will use TABLESAMPLE SYSTEM to specify an approximate percentage of the data to use. If the frequency of locations returned varies in different areas, a heatmap using individual datapoints could be misleading without further data pre-processing. An alternative approach could be to use the postGIS ST_AsGeoJSON to generate shapes in geoJSON format which can be used in Grafana’s World Map Panel plugin.

  1. Add a panel, select DanishAIS as the data source and Format As Table.

  2. Using Edit SQL, add the following SQL code:

    -- NOTE: TABLESAMPLE SYSTEM(40) returns ~40% of the data.
    FROM aisinputfiltered TABLESAMPLE SYSTEM (40)
  3. Change the visualization type to Geomap.

  4. On the map, zoom in to fit the data points into the frame and modify the following visualization options:

    Panel Options:

    • Title: Route Usage Frequency

    Map View:

    • Use current map setting (this will use the current zoom and positioning level as default)

    • Share View: enable (this will sync up the movement and zoom across multiple maps on the same dashboard)

    Figure 2.9. Setting initial view in map view dialogue box

    Setting initial view in map view dialogue box

    Data Layer:

    • Layer type: Heatmap

    • Location: Coords

    • Latitude field: latitude

    • Longitude field: longitude

    • Weight values: 0.1

    • Radius: 1

    • Blur: 5

    Figure 2.10. Setting up heat-map in data layer dialogue box

    Setting up heat-map in data layer dialogue box

    Standard Options:

    • Color scheme: Blue-Yellow-Red (by value).

    Figure 2.11. Choosing color scheme in standard options dialogue box

    Choosing color scheme in standard options dialogue box

The final visualization will look like the screenshot below.

Note: The number of datapoints rendered can be manipulated by changing the parameter of the TABLESAMPLE SYSTEM() call in the query.

Figure 2.12. Route usage frequency heat-map visualization

Route usage frequency heat-map visualization

Number of Boats Moving Through a Given Area

  1. Create a new panel, and set DanishAIS as the Source, Format as: Table.

  2. Select visualization as: Geomap

  3. Add this SQL in the SQL editor section

    -- 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
                 -- ST_MakeEnvelope creates geometry against which to check intersection
                 (VALUES ('Rodby',
                        ST_MakeEnvelope(651135, 6058230, 651422, 6058548, 25832)::geometry,
                        54.53, 11.06),
                        ST_MakeEnvelope(644339, 6042108, 644896, 6042487, 25832)::geometry,
                        54.64, 11.36)) AS p(port_name, port_geom, lat, lng))
    -- and p.lng will be used to place the port location on the visualization
    SELECT P.port_name,
           sum(numSequences(atGeometry(S.Trip, P.port_geom))) AS trips_intersect_with_port,
    FROM ports AS P,
         Ships AS S
    WHERE intersects(S.Trip, P.port_geom)
    GROUP BY P.port_name,, P.lng

    Note: You will see queries are build using the WITH statement (common table expressions - CTE). This helps to break the query down into parts, and also helps make it easier to understand by others.

  4. The options (visualization settings - on the right side of the screen) should be as follows:

    Data Layer

    • Layer type: → markers

    • Style Size: → Fixed and value: 20

    • Color: → trips_intersect_with_port (This will color points on the map based on this value)

    Standard options

    • Min → 88

    • Max → 97

    • Color scheme → Green-Yellow-Red (by value)

    Note: At the writing of this tutorial, the Geomap plugin is in beta and has some minor bugs with how colors are rendered based when the Min and Max values are auto calculated.

In the visualization below we can see port Rodby has a higher number of ships coming and going to it and that’s why it is colored red. This visualization can show relative activity of ships in regions and ports.

Figure 2.13. Frequency intersecting with geometric envelop visualization

Frequency intersecting with geometric envelop visualization

Boats in Proximity in a Given Time Range

Follow the similar steps to add a Geomap panel as before, we include the following SQL script:

-- 2 CTEs are help to make these queries user-friendly; TimeShips and TimeClosestShips.
  -- The TimeShips CTE returns the data for a time period from 1am to 6:30am
  TimeShips AS (
      atPeriod(S.Trip, period '[2018-01-04 01:00:00, 2018-01-04 06:30:00)' ) AS trip
      Ships S
  -- The TimeClosestShips CTE returns the time, location, and closest distance of the boats
  -- that are within 300m of each other. Note the use of dwithin in the WHERE clause
  -- improves performance by limiting the computation to only those ships that were within
  -- 300m.
  TimeClosestShips AS (
      S1.MMSI AS "boat1", S2.MMSI AS "boat_2",
      startValue( atMin(S1.trip <-> S2.trip)) AS closet_distance,
      startTimestamp( atMin(S1.trip <-> S2.trip)) AS time_at_closest_dist,
      S1.trip AS "b1_trip",
      S2.trip AS "b2_trip"
      TimeShips S1, TimeShips S2
      S1.MMSI > S2.MMSI AND
      dwithin(S1.Trip, S2.Trip, 300)
-- The final SELECT is used to project the time_at_closest_distance onto the sequence of
-- locations to return the lat and long of both ships.
SELECT  t.boat1, t.boat_2, t.closet_distance, t.time_at_closest_dist,
  ST_X( ST_Transform( valueAtTimestamp(b1_trip, time_at_closest_dist), 4326) ) AS b1_lng,
  ST_Y( ST_Transform( valueAtTimestamp(b1_trip, time_at_closest_dist), 4326) ) AS b1_lat,
  ST_X( ST_Transform( valueAtTimestamp(b2_trip, time_at_closest_dist), 4326) ) AS b2_lng,
  ST_Y( ST_Transform( valueAtTimestamp(b2_trip, time_at_closest_dist), 4326) ) AS b2_lat
FROM TimeClosestShips t;

To add the points to the map modify the following options:

Panel Options:

  • Title: Ships within 300m

Map View:

  • Share view: enabled

Data Layer:

  • Layer 1: rename to Boat1

  • Layer type: Heatmap

  • Location: Coords

  • Latitude field: b1_lat

  • Longitude field: b1_lng

  • Radius: 5

  • Blur: 15

Click on + Add layer to add another heat map layer to the data, this time using b2_lat and b2_long as the coordinates. We can also add a layer to show the precise locations with markers for both ships (using b1_lat, b1_lng, b2_lat and b2_long), setting each marker to a different color. For the Boat 1 and Boat 2 Locations, we use the following options:

Data Layer:

  • Value: 1

  • Color: select different color for each boat.

Figure 2.14. Multiple layers in data layers dialogue box

Multiple layers in data layers dialogue box

The final visualization looks like the below.

Figure 2.15. Visualization of ships within 300m using heat-map

Visualization of ships within 300m using heat-map

It’s helpful to include the tooltip for layers to allow users to see the data behind the visualization, which helps in interpretation and is a good way for subject-matter-experts to provide concrete feedback. Using the tooltip, we can quickly see that the same ship can be within 300m to multiple other ships in the same time frame (as seen in the screenshot below). This can result in a higher frequency of results in a heat map view than expected. SQL queries should be modified to ensure they are correctly interpreted.

Not surprisingly, we see there are lots of results for proximity within ports. We could avoid including results in ports by excluding all results that occur within envelopes defined by ST_MakeEnvelope, as seen in the previous queries.

Figure 2.16. Multiple results for the same ship at various times while in a port

Multiple results for the same ship at various times while in a port