Nine D3 chart types, a map and a cloud

Charts

Each chart is a page of its own, addressed by URL, drawing from the same pipeline as the table it came from. A chart page takes the view it was opened from — the source, joins, filters, calculated fields and time range — so the chart and the table behind it always agree.

The chart pages

Every page takes the pipeline parameters described under Common parameters.
Route	Draws	Its own parameters
`/graph` `/line`	Zoomable line chart with brush-to-zoom, reference lines for the minimum, average and maximum, a trend line (least squares, with its slope and R²), and a multi-series legend.	`x`, `y`, `series`, `span`, `agg`
`/bar`	Bar chart, horizontal or vertical, sorted by value or label, with a top-N tail merged into one bar.	`cat`, `val`, `orient=h`, `sort`, `max`
`/pie`	A pie or donut; clicking a slice or legend entry opens the rows behind it.	`cat`, `val`, `donut=1`
`/scatter`	Scatter plot, with bubbles sized by a numeric column and coloured by a category.	`x`, `y`, `series`, `size`, `scheme`
`/heatmap`	Grid with two categorical axes; each cell is a sum or a row count, in a named colour scheme.	`x`, `y`, `val`, `scheme`, `show_val=1`
`/stacked`	Stacked bars: one bar per category, split by a series column.	`cat`, `series`, `val`, `scheme`
`/combo`	Bars and a line on one pair of axes, each with its own measure column; a scheme gives the bars its first colour and the line its second.	`cat`, `bar_val`, `line_val`, `scheme`
`/treemap`	Tiles sized by a measure, grouped by a label column, with a top-N tail merged. Tiles can instead be shaded by a second measure — size for sales, colour for margin — with a legend for the scale.	`group`, `size`, `topn`, `scheme`, `color`, `color_agg` (default avg), `color_scheme`
`/histogram`	Histogram with server-side binning, so the counts are the data's counts rather than a client's sample.	`col`, `bins` (1–200, default 10), `scheme` (bar colour)
`/wordcloud`	Word cloud of a text column. Each row contributes its words once, so a word's size is the number of rows using it, not the times one row repeats it.	`col`, `topn`, `minlen`, `stop`
`/map`	OpenStreetMap map with clustered markers, an optional heat layer, county outlines, bubbles and colour rules.	see Maps
`/pivot`	Crosstab of rows against columns with one or more measures, subtotals and a grand total.	see Pivot
`/stats`	One row per column: count, nulls, distinct values, first, last and, for numeric columns, min, max, range, mean, median, a chosen percentile and the sample standard deviation.	`l`, `f`, `p`
`/insights`	Automatic observations: outliers by the Tukey fences, period-over-period deltas, top movers and seasonality. No model and no network call.	`l`, `f`

Common parameters

Every data page and chart takes the same selection of the pipeline:

l=<spec>: The left source: table:name from the data directory, path:/absolute/file.csv, a remote path /../host/dir/file.csv, url:, redis:tablename|redis://host:port, or mariadb:server/table.
j=<spec>|<leftkey>|<rightkey>: A left join, repeatable and chainable.
c=name:expression: A calculated field, added as a virtual column before any aggregation.
f=<col>:<op>:<value>: A filter, repeatable, applied after joins and before the chart aggregates.
span=1h&tcol=at: Bucket the dimension into time buckets before drawing.
range=last_30_days&rcol=date: A window resolved when the request arrives, so a link keeps its meaning tomorrow. See relative time ranges.
cs=, by=, order=: A cross-row plan: a running total, rank, z-score and so on, written beside each row. See cross-row columns.
back=, back2=: Where the breadcrumb goes. Only a same-site path or an http(s):// URL is followed.

Maps

GET /map plots the filtered view with HTML::OSM and Leaflet. A row's position comes from a place column that is geocoded (loc=, one lookup per distinct value, in the local data first and then on OpenStreetMap's Nominatim, remembered in the model store and shared by every dataset that names it) or from coordinate columns (lat= and lon=). Rows sharing a position become one marker. The map opens on every marker, unless zoom= says otherwise, and has a scale bar. A map coloured by series=, color= or size= draws its key on the map, listing only the colours of the markers in view, so it changes as the map is zoomed and moved. When some rows cannot be plotted, the note that says so counts them by reason, links to all of them in the table, and opens onto a list of their places: how many rows each one stands for, why it was not plotted, and a link to those rows.

Places that are not in the cache yet are looked up after the page is shown. The map draws what the cache knows, and a progress bar names each place as it is looked up, such as Looking up 5 of 10: 10 Downing St, London. Each answer is saved at once, and the map is drawn again when the last place is in. Cancel stops the lookups and draws the map with the places found so far. The rest are listed as not looked up yet, with a link to look them up. geocode=wait looks every place up before the page is sent, which is also what a client that is not a browser gets, and geocode=cached looks nothing up.

loc=<col>: A column of places to geocode. Repeat it for a place written across columns: loc=Address&loc=Country looks up each row's address and country joined with a comma, leaving out an empty cell. The map panel's Followed by field does the same.
loc_text=<text>: Text added to the end of every place before it is looked up, after a comma: a column of county names with loc_text=Virginia, USA looks up Accomack, Virginia, USA. A row with no place is still not looked up. The map panel's Add to each place field.
popup=<col>: A column shown in the marker popup; repeatable. By default the popup shows every column but the place. A marker stands for every row at its place, and its popup lists them: a value they all share once, the rest as a small table. Past ten rows (map_popup_rows) the table scrolls no further; the rest are counted, with a link to them in the table.
max=N, zoom=N: How many markers to draw (default 2,000) and the initial zoom; by default the map fits its markers.
tiles=name: A named tile source from the tile_sources configuration key, for a tile server on your own network.
heat=1, weight=<col>: A heat layer over the markers, optionally weighted by a numeric column.
bounds=county: The outline of the county each marker is in, with a checkbox on the map to hide them; the map panel's Show county boundaries. The outlines come from OpenStreetMap, which is sent each marker's position rounded to about 100 metres, and are remembered, so a later map asks again only for a point outside every outline it has. Those not known yet are looked up after the page is shown, with the same progress bar as places. A county is the division of a country below its states: a US county or independent city, an English county, a German Landkreis, a French département; a district in India and Bangladesh, a prefecture in China, a province in Spain, a county in Kenya. Australia, Brazil and Japan have nothing at that level, so they have no outlines. map_bounds_ranks in the configuration sets it for other countries. With heat=1 the outlines are coloured by what is in each, green for the least and red for the most: the weight column added up over the rows inside, or the rows counted. Each step up the colours is the same multiple, so totals of about the same size share a colour; a lone outline stays grey. Each outline's popup gives its total, and a key in the corner of the map (with HTML::OSM 0.16) lists each colour with the totals it covers; it hides with the outlines. When the markers are coloured (color=) the outlines take their colours instead; with HTML::OSM 0.17 each coloured outline is drawn just inside its own edge, so two neighbours of different colours show side by side along their border, and a county whose markers differ gets a line of each colour, the commonest outermost.
Under a map with outlines: When there are fewer outlines than markers, a list under the map says why: each outline holding more than one marker, with its places, and the markers in no outline, each with a link to its rows in the table. Places looked up by name can land in the wrong outline when a name is ambiguous, and show up here. While some places still have no outline, the note above the map links to their rows and to looking the outlines up again.
shade=<col>, with bounds=county: Colour shade: fills each outline in a shade of one colour, darker for a larger total of that column over the rows inside it, as a census map is drawn. shade_color= (a colour name or #rrggbb) chooses the colour, orange by default. A key on the map lists the shades with the totals each covers (as filled boxes with HTML::OSM 0.18), and a click anywhere in an outline gives its total. When the markers are coloured and no shade_color= is given, each outline is shaded in its markers' colour, stronger the further its total is into that colour's rule: under color_rule=lt:20000:green the smallest county is the greenest. In the map panel it is Colour shade, under Show county boundaries.
size=<col>: Markers become circles with a radius from a numeric column.
color=<col> with color_rule=op:value:colour: Colour markers by thresholds, tried in order, first match wins; the operators are gt, ge, lt, le, eq and ne. Unmatched values take color_default=, shown in the legend as "Other".
series=<col> with scheme=: Colour by category from a named palette instead of by rules.

Markers are clustered, always, so a dense map draws a count at low zoom and splits it as it is zoomed in. The Nominatim search box is switched off on every map page, so nothing typed on a map is sent anywhere. An unknown tiles=, an unknown scheme=, an unknown column, color= together with series=, or a transposed latitude and longitude pair is a 400 rather than a silently different map.

Resizing and exporting

Every chart can be resized by dragging its bottom-right grip, or from the keyboard: the grip takes focus, the arrow keys resize the chart, Shift takes bigger steps, Home and End go to the narrowest and widest it can be, and the size is announced as it changes.

Each chart page carries Copy link and Export SVG, Export PNG and Export PDF. The PDF is a one-page document the size of the chart, holding a high-resolution picture, so it needs no PDF library on the server. Saved files carry "Database::BI" at the bottom right, and use the light colours even when the reader has chosen the dark theme.

A chart is drawn in the browser, so what an export contains can only be seen in a browser. That is also why a chart on a canvas can be filtered again: it is a live snippet rather than a picture.

Time buckets

The aggregating routes bucket the dimension they already take, so a daily series is a request rather than a calculated field:

GET /bar?l=table:sales&cat=at&val=amount&span=1h
GET /graph?l=table:sales&x=at&y=amount&span=1h&agg=sum&series=region
GET /pivot?l=table:sales&rows=at&m=amount:sum:total&span=1mon

span= takes a count and s, m, h, d, w, mon, q or y. Labels are ISO at the bucket's own resolution, so they sort chronologically as strings. A value that cannot be read as a time lands in one visible "(unparseable)" bucket rather than disappearing.