Public
Edited
Mar 13, 2024
Paused
1 fork
Importers
5 stars
Also listed in…
Hello
addTable(censusDb, "vehicle")
censusDb
result = {
// Add the vehicle table first.
await addTable(censusDb, "vehicle");
// Then select the parts of the tables we need.
return censusDb.sql([
`SELECT vehicle.code,vehicle.category,vehicle.value
FROM vehicle,geo
WHERE vehicle.geography='MSOA' AND vehicle.code=geo.code AND geo.region='E12000007'`
]);
}
data = {
const accumulate = (tbl, key, storedCat, cat, val) => {
const pair = tbl.get(key);
if (cat === storedCat) {
return tbl.set(key, pair ? [pair[0] + val, val] : [val, val]);
} else {
return tbl.set(key, pair ? [pair[0] + val, pair[1]] : [val, 0]);
}
};
return result.reduce(
(tbl, row) => accumulate(tbl, row.code, 0, row.category, row.value),
new Map()
);
}
percentNoCars = Object.fromEntries(
[...data].map(([code, [total, noCar]]) => [code, (100 * noCar) / total])
)
{
// Just download the MSOA boundaries that match the codes in the percentNoCars object (i.e. London)
const msoas = await downloadBoundaries("msoas", Object.keys(percentNoCars));
// Scale percentages to range between pale yellow (0%) and dark brown (100%)
const colour = (code) => {
return d3.scaleSequential(d3.interpolateYlOrBr).domain([0, 100])(
percentNoCars[code]
);
};
// Draw the map. Coordinates are in OSGB, so no need to reproject other than flip the y-axis
return new Renderer(600, 500)
.setProjection(d3.geoIdentity().reflectY(true))
.strokeWidth(0.2)
.geoShape(msoas, "properties.code", colour)
.render();
}
result2 = {
// Add the vehicle table first.
await addTable(censusDb, "vehicle");
// Then perform the query
return censusDb.sql([
`WITH totals AS
(SELECT vehicle.code,sum(vehicle.value)::FLOAT AS allHouseholds
FROM vehicle,geo
WHERE vehicle.geography='MSOA' AND vehicle.code=geo.code AND geo.region='E12000007'
GROUP BY vehicle.code)
SELECT totals.code,100*vehicle.value/totals.allHouseholds AS percNoCar FROM totals,vehicle
WHERE totals.code = vehicle.code AND vehicle.category=0`
]);
}
percentNoCars2 = Object.fromEntries(
[...result2].map((obj) => [obj.code, obj.percNoCar])
)
{
// Just download the MSOA boundaries that match the codes in the percentNoCars object (i.e. London)
const msoas = await downloadBoundaries(
"msoasHex",
Object.keys(percentNoCars2)
);
// Scale percentages to range between pale yellow (0%) and dark brown (100%)
const colour = (code) => {
return d3.scaleSequential(d3.interpolateYlOrBr).domain([0, 100])(
percentNoCars[code]
);
};
// Draw the map. Coordinates are in OSGB, so no need to reproject other than flip the y-axis
return new Renderer(640, 390)
.setProjection(d3.geoIdentity().reflectY(true))
.strokeWidth(0.2)
.geoShape(msoas, "properties.code", colour)
.render();
}
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.
