Maps, Data, Viz, UX, Design
// SQL query that selects persons INJURED only by bicycle and only by motor vehicle
queryInjuries = sls`
WITH bicycle_fault_injuries AS (
SELECT SUM(${person_type_injured}) as value,
TO_TIMESTAMP(year || '-' || LPAD(month::text, 2, '0'), 'YYYY-MM') AS year_month
FROM ${table}
WHERE
${person_type_injured} > 0 AND
hasvehicle_bicycle
AND NOT hasvehicle_car
AND NOT hasvehicle_truck
AND NOT hasvehicle_SUV
AND NOT hasvehicle_motorcycle
AND NOT hasvehicle_scooter
AND NOT hasvehicle_other
AND NOT hasvehicle_busvan
GROUP BY year_month
),
motorvehicle_fault_injuries AS (
SELECT SUM(${person_type_injured}) as value,
TO_TIMESTAMP(year || '-' || LPAD(month::text, 2, '0'), 'YYYY-MM') AS year_month
FROM ${table}
WHERE
${person_type_injured} > 0
AND NOT hasvehicle_bicycle
AND NOT hasvehicle_scooter
AND (
hasvehicle_car OR
hasvehicle_truck OR
hasvehicle_SUV OR
hasvehicle_motorcycle OR
hasvehicle_other OR
hasvehicle_busvan
)
GROUP BY year_month
),
all_year_months as (
SELECT
GENERATE_SERIES(
'2011-08-01'::TIMESTAMP,
'2020-01-01'::TIMESTAMP,
'1 month'::INTERVAL
) AS year_month
)
SELECT
COALESCE(a.value, 0) as injured_by_bicycle,
COALESCE(b.value, 0) as injured_by_motorvehicle,
c.year_month as year_month
FROM
all_year_months c
LEFT JOIN bicycle_fault_injuries a
ON a.year_month = c.year_month
LEFT JOIN motorvehicle_fault_injuries b
ON b.year_month = c.year_month
`
// SQL query that selects persons KILLED only by bicycle and only by motor vehicle
queryFatalities = sls`
WITH bicycle_fault_fatalities AS (
SELECT SUM(${person_type_killed}) as value,
TO_TIMESTAMP(year || '-' || LPAD(month::text, 2, '0'), 'YYYY-MM') AS year_month
FROM ${table}
WHERE
${person_type_killed} > 0 AND
hasvehicle_bicycle
AND NOT hasvehicle_car
AND NOT hasvehicle_truck
AND NOT hasvehicle_SUV
AND NOT hasvehicle_motorcycle
AND NOT hasvehicle_scooter
AND NOT hasvehicle_other
AND NOT hasvehicle_busvan
GROUP BY year, month
ORDER BY year, month
),
motorvehicle_fault_fatalities AS (
SELECT SUM(${person_type_killed}) as value,
TO_TIMESTAMP(year || '-' || LPAD(month::text, 2, '0'), 'YYYY-MM') AS year_month
FROM ${table}
WHERE
${person_type_killed} > 0
AND NOT hasvehicle_bicycle
AND NOT hasvehicle_scooter
AND (
hasvehicle_car OR
hasvehicle_truck OR
hasvehicle_SUV OR
hasvehicle_motorcycle OR
hasvehicle_other OR
hasvehicle_busvan
)
GROUP BY year, month
ORDER BY year, month
),
all_year_months as (
SELECT
GENERATE_SERIES(
'2011-08-01'::TIMESTAMP,
'2020-01-01'::TIMESTAMP,
'1 month'::INTERVAL
) AS year_month
)
SELECT
COALESCE(a.value, 0) as killed_by_bicycle,
COALESCE(b.value, 0) as killed_by_motorvehicle,
c.year_month as year_month
FROM
all_year_months c
LEFT JOIN bicycle_fault_fatalities a
ON a.year_month = c.year_month
LEFT JOIN motorvehicle_fault_fatalities b
ON b.year_month = c.year_month
`
// code credit: https://observablehq.com/@d3/line-chart-with-tooltip
drawLineChart = data => {
const height = 500;
const margin = ({top: 20, right: 30, bottom: 30, left: 40})
const x = d3.scaleUtc()
.domain(d3.extent(data, d => d.date))
.range([margin.left, width - margin.right])
const y = d3.scaleLinear()
.domain([0, d3.max(data, d => d.value)]).nice()
.range([height - margin.bottom, margin.top])
const xAxis = g => g
.attr("transform", `translate(0,${height - margin.bottom})`)
.call(d3.axisBottom(x).ticks(width / 80).tickSizeOuter(0))
const yAxis = g => g
.attr("transform", `translate(${margin.left},0)`)
.call(d3.axisLeft(y))
.call(g => g.select(".domain").remove())
.call(g => g.select(".tick:last-of-type text").clone()
.attr("x", 3)
.attr("text-anchor", "start")
.attr("font-weight", "bold")
.text(data.y))
const line = d3.line()
.curve(d3.curveLinear)
.defined(d => d.value !== null)
.x(d => x(d.date))
.y(d => y(d.value))
const bisector = d3.bisector(d => d.date).left;
const bisect = mx => {
const date = x.invert(mx);
const index = bisector(data, date, 1);
const a = data[index - 1];
const b = data[index];
return date - a.date > b.date - date ? b : a;
}
const callout = (g, value) => {
if (!value) return g.style("display", "none");
g
.style("display", null)
.style("pointer-events", "none")
.style("font", "10px sans-serif");
const path = g.selectAll("path")
.data([null])
.join("path")
.attr("fill", "white")
.attr("stroke", "black");
const text = g.selectAll("text")
.data([null])
.join("text")
.call(text => text
.selectAll("tspan")
.data((value + "").split(/\n/))
.join("tspan")
.attr("x", 0)
.attr("y", (d, i) => `${i * 1.1}em`)
.style("font-weight", (_, i) => i ? null : "bold")
.text(d => d));
const {x, y, width: w, height: h} = text.node().getBBox();
text.attr("transform", `translate(${-w / 2},${15 - y})`);
path.attr("d", `M${-w / 2 - 10},5H-5l5,-5l5,5H${w / 2 + 10}v${h + 20}h-${w + 20}z`);
}
const svg = d3.select(DOM.svg(width, height))
.style("-webkit-tap-highlight-color", "transparent")
.style("overflow", "visible");
svg.append("g")
.call(xAxis);
svg.append("g")
.call(yAxis);
svg.selectAll("circle")
.data(data)
.join("circle")
.attr("cx", d => x(d.date))
.attr("cy", d => y(d.value))
.attr("r", 2.5)
.attr("fill", "steelblue")
svg.append("path")
.datum(data)
.attr("fill", "none")
.attr("stroke", "steelblue")
.attr("stroke-width", 1.5)
.attr("stroke-linejoin", "round")
.attr("stroke-linecap", "round")
.attr("d", line);
const tooltip = svg.append("g");
svg.on("touchmove mousemove", function() {
const {date, value} = bisect(d3.mouse(this)[0]);
tooltip
.attr("transform", `translate(${x(date)},${y(value)})`)
.call(callout, `${value.toLocaleString()}
${date.toLocaleString(undefined, {month: "long", year: "numeric"})}`);
});
svg.on("touchend mouseleave", () => tooltip.call(callout, null));
return svg.node();
}
Purpose-built for displays of data
Observable is your go-to platform for exploring data and creating expressive data visualizations. Use reactive JavaScript notebooks for prototyping and a collaborative canvas for visual data exploration and dashboard creation.
