31:T46e,md`As you can see, this table maps column names from the \`survey\` table to the human questions the participants were asked. As such it can act as documentation. ### Survey The survey table is an anonymized table which has as rows the individual answers to these questions. ## About Aircloak Aircloak is an anonymization engine that sits between a database and you. It guarantees that no identifying information leaves the database. It achieves this by: 1. Dropping any row that would identify a single user. Think of this as hiding in a crowd - if there are always a number of other people around you, you can't be identified. 2. Adding some random noise to the answers. As such, the answers are only sort-of correct. However, Aircloak can report how much noise it has added (see the chart above which includes error bars to indicate the noise). 3. By outright rejecting queries for information that is too sensitive. You can read more about this in [our docs](https://covid-db.aircloak.com/docs/). ## Using this notebook This notebook exposes a few functions. I recommend you start with the \`query(statement)\` function: `32:T54b,{ let container = html`

`; // Give the container dimensions. yield container; let map = new mapboxgl.Map({ container, style: 'mapbox://styles/gampleman/ck8ijmps00qk11iqxgbtzpgq4' // scrollZoom: false }); map.on('load', () => { map.on('click', 'postaldistrict', e => { console.log(e.features); }); setTimeout(() => { mapData.forEach(({ postal_code, value, count }) => { if (postal_code && value != null && count != null) { console.log({ id: postal_code.split("").reduce((a, b) => { a = (a << 5) - a + b.charCodeAt(0); return a & a; }, 0), source: 'composite', sourceLayer: 'postaldistrict' }); map.setFeatureState( { id: postal_code.split("").reduce((a, b) => { a = (a << 5) - a + b.charCodeAt(0); return a & a; }, 0), source: 'composite', sourceLayer: 'postaldistrict' }, { value, count } ); } }); }, 1000); }); await Promises.delay(10); map.resize(); // Be careful to clean up the map's resources using \`map.remove()\` whenever // this cell is re-evaluated. invalidation.then(() => map.remove()); }33:T8c4,query = async function*(statement) { let view = html`

`; view.value = null; yield view; for await (let result of queryLowLevel(statement)) { if (result.completed) { if (result.query_state === 'error') { throw result.error; } else { const res = result.rows.flatMap(({ row, occurences }) => Array.from(new Array(occurences), () => Object.fromEntries(row.map((r, i) => [result.columns[i], r])) ) ); yield* table( result.rows.flatMap(({ row, occurences, unreliable }) => Array.from(new Array(occurences), () => ({ unreliable, ...Object.fromEntries(row.map((r, i) => [result.columns[i], r])) })) ), { limit: 100, enableFilter: false, columns: result.columns.map(key => { const decimalNumber = res.reduce((memo, d) => { if (typeof d[key] === 'number') { let frac = (d[key] || '').toString().split('.')[1]; if (frac) return Math.max(memo, frac.length); } return memo; }, 0); return { key, name: key, type: typeof res[0][key], accessor: row => ({ value: row[key], unreliable: row.unreliable }), render: ({ value, unreliable }) => { let result = value; if (decimalNumber && typeof value === 'number') { result = value.toFixed(decimalNumber); } if (value == null || value == undefined) { result = `NULL`; } if (unreliable) { return `${result}`; } return result; } }; }) } ); } } else { yield loadingBar(result.query_state); } } }34:Ta17,loadingBar = state => { const pendingStates = [ "created", "started", "parsing", "compiling", "awaiting_data", "ingesting_data", "processing", "post_processing" ]; const state2class = itemState => { const curr = pendingStates.indexOf(itemState); const act = pendingStates.indexOf(state); if (curr < act) { return "done"; } else if (curr === act) { return "active"; } return ""; }; return html`

Created

Started

Parsing

Compiling

Awaiting data

Ingesting data

Processing

Post-processing

`; }35:[] 2f:{"title":"Aircloak COVID data","cells":[{"id":0,"value":"md`# Aircloak COVID data\n\n[**Aircloak**](https://aircloak.com) provides an anonymization engine that behaves like a Postgres database, with the caveat that it guarantees that it will only provide fully anonymized data as a response to queries.\n\nThis allows us to ingest **highly sensitive medical datasets** for you to play with - all without worrying about privacy.\n\nThis notebook provides and documents everything you need to query this dataset. You can start your own exploration by including the following in your notebook:\n\n~~~javascript\nimport {query} from \"@aircloak/aircloak-covid-data\"\n~~~\n\nThis will give you access via SQL to the data that Aircloak aggregates. We will keep updating this data as we go along!\n\n## The Data\n\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":47,"value":"viewof simpleBar = vl\n .data(chartData)\n .width(width - 140)\n .height(240)\n .encode(\n vl\n .x()\n .fieldO(\"how_bad_feel\")\n .axis({\n domain: false,\n labelAngle: 0,\n ticks: false,\n title: 'How bad did you, or do you, feel?'\n })\n )\n .layer(\n vl.markBar().encode(vl.y().fieldQ(\"count\")),\n vl\n .markErrorbar()\n .encode(vl.y().fieldQ(\"count\"), vl.yError().field(\"count_noise\"))\n )\n .render()","pinned":false,"hidden":false,"mode":"ojs"},{"id":221,"value":"md`\nThe data currently comes from [COV-CLEAR](https://cov-clear.com), which has an online survey showing various symptoms. We hope to enlist more organizations to release their data through Aircloak, but this is a work in progress. If you have any data you would like to release, but are prohibited due to compliance reasons, please [get in touch](mailto:covid@aircloak.com)!\n\nThe data has two tables: The \\`questions\\` table and the \\`survey\\` table.\n\n### Questions\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":231,"value":"query(`SELECT * FROM questions`)","pinned":true,"hidden":false,"mode":"ojs"},{"id":235,"value":"$31","pinned":false,"hidden":false,"mode":"ojs"},{"id":10,"value":"viewof data = query(\n `SELECT how_bad_feel, count(*), count_noise(*) FROM survey WHERE how_bad_feel IS NOT NULL GROUP BY 1;`\n)","pinned":true,"hidden":false,"mode":"ojs"},{"id":242,"value":"md`As you can see, this will render the answer with a nice table for easy debugging. (Note also the button to download the result as CSV). Using the viewof operator, you later also get access to the raw data, which you can use for visualization:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":119,"value":"data","pinned":true,"hidden":false,"mode":"ojs"},{"id":245,"value":"md`There is also a simpler function, \\`simpleQuery(statement)\\`, which will not attempt to render the data, but merely returns it as a promise:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":135,"value":"chartData = simpleQuery(\n `SELECT how_bad_feel, count(*), count_noise(*) FROM survey WHERE how_bad_feel IS NOT NULL GROUP BY 1;`\n)","pinned":true,"hidden":false,"mode":"ojs"},{"id":267,"value":"md`## Example\n\nOK. We've gotten most of the basics, let's do something cool with it.\n\nFirst, let's fetch how anxious people are feeling, grouped by Post Code District.:\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":279,"value":"viewof mapData = query(\n `SELECT postal_code, avg(how_anxious) as value, count(*) FROM survey GROUP BY postal_code;`\n)","pinned":true,"hidden":false,"mode":"ojs"},{"id":297,"value":"md`Next, let's show it on a map. In the following map red is anxious, green is not anxios. The height of the polygon represents how many respondents were in that district.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":272,"value":"$32","pinned":false,"hidden":false,"mode":"ojs"},{"id":249,"value":"md`### Pro-tips\n1. Import other Observable notebooks to combine this data with prebuilt visualizations!\n2. If you have your own Aircloak instance, but would like to query it with a notebook, you can:\n - Set up a limited machine user, and get a token for it.\n - Setup CORS headers for the Air instance\n - Import this notebook like this:\n ~~~javascript\n import {query} with {\n \"SFMy...\" as token, \n \"https://my-instance.com\" as endpoint, \n \"my-datasource\" as dataSource\n } from \"@aircloak/aircloak-covid-data\";\n ~~~\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":57,"value":"md`## Appendix\n\nHere you can see the implementations of these functions:\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":5,"value":"token = \"SFMyNTY.g3QAAAACZAAEZGF0YW0AAAAkY2IzMjUwYzQtMzM0MS00Y2Q4LWIyOTktNTJjOTU0MmMxMmY5ZAAGc2lnbmVkbgYA8lgfMXEB.lN9mg0mX3K-BtnmDs1V6e0rYYCA5tHmAuGehzd6298I\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":12,"value":"endpoint = \"https://covid-db.aircloak.com\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":130,"value":"simpleQuery = async statement => {\n var result;\n for await (result of query(statement)) {\n }\n return result.value;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":37,"value":"$33","pinned":false,"hidden":false,"mode":"ojs"},{"id":8,"value":"queryLowLevel = async function*(statement) {\n const req1 = await fetch(`${endpoint + '/api/queries'}`, {\n method: \"POST\",\n headers: {\n 'auth-token': token,\n 'Content-Type': 'application/json'\n },\n body: JSON.stringify({\n query: {\n statement,\n data_source_name: dataSource\n }\n })\n });\n const result = await req1.json();\n yield { completed: false, query_status: 'pending' };\n const poll = async function*() {\n const req = await fetch(`${endpoint}/api/queries/${result.query_id}`, {\n method: \"GET\",\n headers: {\n 'auth-token': token,\n 'Content-Type': 'application/json'\n }\n });\n const res = await req.json();\n if (res.query.completed) {\n yield res.query;\n } else {\n yield res.query;\n await Promises.delay(1000);\n yield* poll();\n }\n };\n yield* poll();\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":186,"value":"$34","pinned":false,"hidden":false,"mode":"ojs"},{"id":66,"value":"dataSource = 'cov_clear'","pinned":true,"hidden":false,"mode":"ojs"},{"id":52,"value":"import { vl } from \"@vega/vega-lite-api\"","pinned":true,"hidden":false,"mode":"ojs"},{"id":138,"value":"import { table } from \"@gampleman/table\"","pinned":true,"hidden":false,"mode":"ojs"},{"id":263,"value":"mapboxgl = {\n const gl = await require(\"mapbox-gl@1.9\");\n if (!gl.accessToken) {\n gl.accessToken =\n \"pk.eyJ1IjoiZ2FtcGxlbWFuIiwiYSI6ImNpaDZieDFkMjBhNGp1amx4bXJyc3p3YTIifQ.UGhclQinv28O-CJDScQe_w\";\n const href = await require.resolve(\"mapbox-gl@1.9/dist/mapbox-gl.css\");\n document.head.appendChild(html``);\n }\n return gl;\n}","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W35","stdlib":"1","titling":"auto"}