The avatar for @mjbo
Max Bo
Software Engineer @ Canva, ex-Google | music, color theory, Australian democracy, situated software & generative art
Public
auspol
Edited
Mar 20, 2023
2
auspol
Better Intersections - Sydney traffic light timings
Polling booth voronois
Australian House of Representatives 2022 federal electoral division cartogramsHow many QMS advertising panels in Sydney are near a Telstra public telephone? - an analysisInformal voting in the 2019/2022 Australian federal elections2019/2022 Australian federal election House of Representatives polling booth swings
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
addTooltips(
Plot.plot({
color: {
...makePartyScale(twoCandidatePreferred2022),
legend: true,
label: "Party"
},
projection: {
type: "mercator",
domain: {
type: "MultiPoint",
coordinates: [
// Brisbane bounding box
[152.965818, -27.511633],
[153.10495, -27.436701]
]
}
},
caption:
"Arrow angle indicates swing. Arrow size is swing mangitude multiplied by the total of people who voted at the booth (a big arrow is a lot of 'converted' voters. Booth voronoi is coloured by the 2CP victory magnitude.",
marks: [
Plot.graticule(),
Plot.sphere(),
// TODO(mjbo) Much nicer tilemap
Plot.geo(divisionGeojson2022, { strokeOpacity: 0.5 }),
Plot.vector(twoCandidatePreferred2022, {
x: "Longitude",
y: "Latitude",
// if Swing is 100 point right, if Swing is 0, point up, if Swing is -100 point left
rotate: (d) => (d.Swing / 100) * 90,
// arrow length should match the magnitude of the swing * the size of the booth
length: (d) => Math.abs(d.Swing) * d.OrdinaryVotes,
stroke: (d) => d.PartyAb,
title: getTooltip
}),
Plot.voronoi(twoCandidatePreferred2022, {
x: "Longitude",
y: "Latitude",
fill: (d) => d.PartyAb,
title: getTooltip,
fillOpacity: (d) => d.OrdinaryVotes
})
// TODO(mjbo) Why aren't these centering themselves in the centroid?
// Plot.text(
// divisionGeojson2022.features,
// Plot.centroid({
// text: (d) => d.properties.electorateName,
// fill: "currentColor",
// stroke: "white"
// })
// )
]
})
)
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
db = DuckDBClient.of({
HouseDopByPP2022,
GeneralPollingPlaces2022: FileAttachment("GeneralPollingPlaces-2022@1.csv")
})
Insert cell
Insert cell
db
SELECT * FROM HouseDopByPP2022
WHERE
DivisionNm = 'Griffith'
AND PPNm = 'West End'
AND (CalculationType = 'Preference Count' OR CalculationType = 'Transfer Count')
AND Surname = 'BUTLER'
Insert cell
Insert cell
db
SELECT
PPNm, Surname, ANY_VALUE(PartyAb) as PartyAb,
SUM(CalculationValue::INT) :: INT as votes
FROM HouseDopByPP2022
WHERE
DivisionNm = 'Griffith'
AND PPNm = 'West End'
AND (CalculationType = 'Preference Count' OR CalculationType = 'Transfer Count')
GROUP BY (PPNm, Surname)
Insert cell
Insert cell
db
SELECT
DivisionId, ANY_VALUE(DivisionNm) as DivisionNm,
PPId, ANY_VALUE(PPNm) as PPNm,
CandidateId, ANY_VALUE(Surname) as Surname, ANY_VALUE(PartyAb) as PartyAb,
SUM(CalculationValue::INT) :: INT as votes
FROM HouseDopByPP2022
WHERE
CalculationType = 'Preference Count'
OR CalculationType = 'Transfer Count'
GROUP BY (DivisionId, PPId, CandidateId)
Insert cell
Insert cell
db
SELECT DivisionId, MAX(CountNum) :: INT as MaxCountNum
FROM HouseDopByPP2022
-- TODO(mjbo) Fix Parquet file as CSV headers have polluted the combined table
WHERE CountNum != 'CountNum'
GROUP BY (DivisionId)
Insert cell
db
-- ???(mjbo) I had to do this because I couldn't write a nested aggregation
-- `FILTER (WHERE CountNum < MAX(CountNum) - 1)` which seems a little silly.
-- Am I holding this wrong?
WITH max_rounds_per_division AS (
SELECT DivisionId, MAX(CountNum) :: INT as MaxCountNum
FROM HouseDopByPP2022
-- TODO(mjbo) Fix Parquet file as CSV headers have polluted the combined table
WHERE CountNum != 'CountNum'
GROUP BY (DivisionId)
)

SELECT
HouseDopByPP2022.DivisionId, ANY_VALUE(DivisionNm) as DivisionNm,
PPId, ANY_VALUE(PPNm) as PPNm,
CandidateId, ANY_VALUE(Surname) as Surname, ANY_VALUE(PartyAb) as PartyAb,
SUM(CalculationValue::INT) FILTER (WHERE CountNum < MaxCountNum - 1) :: INT as votes
FROM HouseDopByPP2022, max_rounds_per_division
WHERE
HouseDopByPP2022.DivisionId = max_rounds_per_division.DivisionId
AND (CalculationType = 'Preference Count'
OR CalculationType = 'Transfer Count'
)
GROUP BY (HouseDopByPP2022.DivisionId, PPId, CandidateId)
Insert cell
Insert cell

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.
Learn more