Visualization

SpectX can visualize results as a, Line Chart, Bar Chart, or Map.

When selecting which data to map or chart, SpectX searches for a certain type of column(s) that can be visualized using the charts described below. Use the select command to create suitable set of columns for the desired type of chart.

SpectX only displays the chart and map buttons when the resultset matches the requirement of those outputs. For example unless the resultset has geographical data the map button will not be displayed.

Line Chart

To create a line chart click Chart > Line Chart. For a line chart to be created the resultset must contain:

  1. At least one monotonic sequence column (i.e containing monotonically nonincreasing or nondecreasing numeric values), which must appear before (left of) from unique categorical values column (if any). This column is used for values on the x-axis. When there is more than one monotonic sequence columns in the resultset then the leftmost column is used for values on the x-axis.
  2. at least one numeric column, which will be used for values on the y-axis. Columns containing non-numerical values are ignored.

The column aliases or names will be used as series labels. The name of the query script is used for the chart title.

Example 1. Let’s compute sin and cos over the angle from 0 to 1000 degrees. We can use DUAL generator to generate angle values. To demonstrate that non-numerical data is ignored let’s add a column with categorical values (country code strings):

1
2
3
4
5
6
7
dual(1000)          //generate 1000 rows of data
| select(
   sin(RADIANS(i))  //non-monotonic numeric column is used as data series with values on y-axis.
   ,cos(RADIANS(i)) //non-monotonic numeric column is used as data series with values on y-axis.
   ,angle:i         //monotonic sequence column is used as x-axis values.
   ,country:cc(IPADDR(i*100000000)) //categorical values are ignored
);

The lower diagram displays the full range of data. The grayed box marks the zoom-area which is then displayed in the upper diagram. The zoomed area can be changed by dragging or moving the grayed box. Hovering the mouse pointer over the upper diagram will display the values of data points. Clicking on data series labels in the lower-left corner will display/hide that series.

Graphs can be saved as image files by clicking the hamburger menu in the top left corner.

../../_images/line_chart.png

Bar Chart

To create a bar chart click Chart > Bar Chart. For a bar chart to be created the resultset must contain:

  1. At least one unique categorical values [1] column, which must appear before (left of) from monotonic sequence column (if any). This column is used for values on the x-axis. If there is more than one such column the leftmost will be used for values on the x-axis.
  2. At least one numeric column, which will be used for values on the y-axis. Columns containing non-numerical values are ignored.

Example 2. Let’s display counts of country codes of a set of generated IP-addresses. As categorical values column (country) appears before the monotonic sequence column (rowid) we will get a bar chart. To demonstrate that the categorical values column does not have to be the very first we add a numeric column of random values (foo):

1
2
3
4
5
6
7
8
9
dual(1000)
| select(IPADDR(i*100000000) as ipaddr)  //compute 1000 ip addresses
| select(CC(ipaddr) as country,count(*) as cnt).group(country) //compute unique country codes and counts
| select(
    foo:RANDOM()*100  //numeric columns can appear before categorical
   ,country           //unique categorical values column: country code strings
   ,ROWID()           //monotonically increasing numeric column
   ,cnt)              //numeric column: count of country codes
;
../../_images/bar_chart.png
[1]A set of unique non-numeric values (string, ipaddr, etc).

Map

The map chart is intended to display numerical information related to a geographical location. Hence the menu button Map is made visible only when resultset contains at least one column with GEOPOINT data. When there is more than one GEOPOINT column in the resultset then the leftmost is used.

A map can display only one series of numerical data.

Map computes grouped minimum, maximum, average, sum or count. To do so it looks for explicit column names in the resultset: min, max, avg, sum. If none of those is present then count is performed. If there are more than one of explicitly named columns in the result set, then the leftmost is used.

To display:

  1. minimum include in resultset a numerical column named min. For each value in GEOPOINT column the minimum of numeric values is computed and displayed.
  2. maximum include in resultset a numerical column named max. For each value in GEOPOINT column the maximum of numeric values is computed and displayed.
  3. average include in resultset a numerical column named avg. For each value in GEOPOINT column the average of numeric values is computed and displayed.
  4. sum include in resultset a numerical column named sum. For each value in GEOPOINT column the sum of numeric values is computed and displayed.
  5. count is computed directly on GEOPOINT column values when none of the explicit columns are present in resultset.
  6. arbitrary numeric value place it in the column named count (or sum). The values must be computed for unique GEOPOINT values (to prevent Map summing them).

The aggregation range is set by the zoom area.

Example 3. Displaying the count of requests from different geolocations. We’ll use the Apache access log from our examples dataset. We just need to add GEOPOINT column to the parsed data:

1
dual(offset:0xfffffffl, count:1000, increment:10000000) | select(time:t, GEO(ip));

The lower diagram shows the range of data: the row numbers of the resultset. The grayed box marks the range of data used for computing aggregated values displayed in the map above. You can change the range by stretching or moving the gray box.

You can also zoom in or out on the map by clicking ‘+’ or ‘-‘ icons. The polygon icon allows you to select an area, which produces a GEO_POINT_IN() function statement which can be copy-pasted to filter or WHERE statements.

Note that since our dataset is ordered by the time we can nicely let the map play the activity over time. Just set the zoom area and map mode (cluster or heatmap) according to your taste and click on play.

../../_images/map.png

Example 4. Displaying arbitrary values on the map. Let’s visualize the population of the 10 biggest cities in the world.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
@cities = VALUES([
   {city: 'Shanghai', lat: 31.252282D, lon: 121.472832D, population: 24256800}
  ,{city: 'Beijing', lat: 39.918204D, lon: 116.396002D, population: 21516000}
  ,{city: 'Delhi', lat: 28.672417D, lon: 77.052872D, population: 16787941}
  ,{city: 'Lagos', lat: 6.511167D, lon: 3.311421D, population: 16060303}
  ,{city: 'Tianjin', lat: 39.139482D, lon: 117.151161D, population: 15200000}
  ,{city: 'Karachi', lat: 24.888304D, lon: 67.000581D, population: 14910352}
  ,{city: 'Istanbul', lat: 41.009915D, lon: 28.972359D, population: 14160467}
  ,{city: 'Tokyo', lat: 35.726500D, lon: 139.729800D, population: 13513734}
  ,{city: 'Guangzhou', lat: 23.147794D, lon: 113.252625D, population: 13080500}
  ,{city: 'Mumbai', lat: 18.969996D, lon: 72.819438D, population: 12442373}
]);

@cities | select(location:GEO(lat,lon), population);
../../_images/map1.png