Screening in a state’s counties, with rurality

This is the same measure/release query the Home map’s data was built from — run here live, against the real, 29.6-million-row measure.observation table in the latest release, through DuckDB-WASM in your own browser. Scoped to one state rather than the whole country: measure.observation is one very large Parquet file, and a national-scope live read of it is exactly the pattern that fails intermittently in this DuckDB-WASM build (cancer-basemap#120). One state at a time stays well inside the sizes that read reliably.

BUNDLE_URL = "https://basemap.cancerdatasci.org/data/latest/cancerbasemap.duckdb"

// Standard USPS state FIPS codes — public, static, and needed for a
// selector before any connection to the release exists, so it's a plain array
// here rather than a live query. geo_id in the release is 'state:<fips>'.
US_STATES = [
  ["01","Alabama"],["02","Alaska"],["04","Arizona"],["05","Arkansas"],["06","California"],
  ["08","Colorado"],["09","Connecticut"],["10","Delaware"],["11","District of Columbia"],
  ["12","Florida"],["13","Georgia"],["15","Hawaii"],["16","Idaho"],["17","Illinois"],
  ["18","Indiana"],["19","Iowa"],["20","Kansas"],["21","Kentucky"],["22","Louisiana"],
  ["23","Maine"],["24","Maryland"],["25","Massachusetts"],["26","Michigan"],["27","Minnesota"],
  ["28","Mississippi"],["29","Missouri"],["30","Montana"],["31","Nebraska"],["32","Nevada"],
  ["33","New Hampshire"],["34","New Jersey"],["35","New Mexico"],["36","New York"],
  ["37","North Carolina"],["38","North Dakota"],["39","Ohio"],["40","Oklahoma"],["41","Oregon"],
  ["42","Pennsylvania"],["44","Rhode Island"],["45","South Carolina"],["46","South Dakota"],
  ["47","Tennessee"],["48","Texas"],["49","Utah"],["50","Vermont"],["51","Virginia"],
  ["53","Washington"],["54","West Virginia"],["55","Wisconsin"],["56","Wyoming"]
].map(([fips, name]) => ({ fips, name }))

// Incrementing this mutable is the ONLY thing that rebuilds dbState below.
// Selector changes on the page never touch it — they only re-run that
// page's own queryState, reusing the same connection.
mutable dbGeneration = 0

retryButton = {
  const btn = html`<button>Retry connection</button>`;
  btn.onclick = () => { mutable dbGeneration = dbGeneration + 1; };
  return btn;
}

// Loads a pinned duckdb-wasm build (Quarto's own OJS stdlib DuckDBClient is
// an older bundled client that cannot ATTACH a remote database file) and
// attaches the latest release as `cbm`, as the playground does. Depends only
// on dbGeneration, so it runs once per page load and again only on Retry.
dbState = {
  dbGeneration; // reactive dependency — Retry increments this to rebuild the instance
  const t0 = performance.now();
  try {
    const duckdb = await import(
      "https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm@1.33.1-dev57.0/dist/duckdb-browser.mjs"
    );
    const bundle = await duckdb.selectBundle(duckdb.getJsDelivrBundles());
    const tBundle = performance.now();
    const workerUrl = URL.createObjectURL(
      new Blob([`importScripts("${bundle.mainWorker}");`], { type: "text/javascript" })
    );
    const worker = new Worker(workerUrl);
    const db = new duckdb.AsyncDuckDB(new duckdb.ConsoleLogger(duckdb.LogLevel.WARNING), worker);
    await db.instantiate(bundle.mainModule, bundle.pthreadWorker);
    URL.revokeObjectURL(workerUrl);
    // Read byte ranges only; by default DuckDB-Wasm downloads each whole
    // Parquet file. All three flags are needed (see playground.qmd).
    await db.open({
      filesystem: { reliableHeadRequests: true, allowFullHTTPReads: false, forceFullHTTPReads: false }
    });
    const conn = await db.connect();
    const tInstantiate = performance.now();
    await conn.query(`ATTACH '${BUNDLE_URL}' AS cbm (READ_ONLY)`);
    const tAttach = performance.now();
    // Tear this instance down fully before the cell is ever replaced (a
    // future Retry, or navigating away) — never leave a stale worker + WASM
    // heap running alongside a new one.
    invalidation.then(async () => {
      try { await conn.close(); } catch {}
      try { await db.terminate(); } catch {}
    });
    return {
      status: "ready",
      conn,
      timings: {
        instantiate: tInstantiate - tBundle,
        attach: tAttach - tInstantiate,
        total: tAttach - t0
      }
    };
  } catch (error) {
    return { status: "error", error };
  }
}

function rowsFromArrow(table) {
  const fields = table.schema.fields.map((f) => f.name);
  return table.toArray().map((r) => {
    const o = {};
    for (const f of fields) {
      let v = r[f];
      if (typeof v === "bigint") v = Number(v);
      o[f] = v;
    }
    return o;
  });
}

// Shared status line: "connecting" / a query-failed or release-unreachable
// callout with Retry / a ready line with timings. Every recipe page calls
// this from its own display cell as renderStatus(dbState, queryState).
function renderStatus(dbState, queryState) {
  if (dbState.status === "error")
    return html`<p class="status-error"><strong>Release unreachable:</strong> ${dbState.error?.message ?? dbState.error}.
      Large-table reads can fail intermittently in a long-lived tab
      (<a href="https://github.com/seandavi/cancer-basemap/issues/120">cancer-basemap#120</a>) —
      try Retry, or a hard refresh if Retry doesn't help.</p><p>${retryButton}</p>`;
  if (dbState.status !== "ready")
    return html`<p class="recipe-status"><em>Connecting to the release and loading DuckDB-WASM…</em></p>`;
  if (queryState.status === "error")
    return html`<p class="status-error"><strong>Query failed:</strong> ${queryState.error?.message ?? queryState.error}</p><p>${retryButton}</p>`;
  if (queryState.status !== "ready")
    return html`<p class="recipe-status"><em>Running query…</em></p>`;
  return html`<p class="recipe-status">DuckDB-WASM ready in ${dbState.timings.total.toFixed(0)} ms
    (ATTACH ${dbState.timings.attach.toFixed(0)} ms).
    This query: ${queryState.ms.toFixed(0)} ms, ${queryState.rows?.length ?? 0} rows.</p>`;
}

MEASURES = [
  { measure_id: "PLACES:CANCER:age_adjusted", label: "Cancer (non-skin) or melanoma" },
  { measure_id: "PLACES:COLON_SCREEN:age_adjusted", label: "Colorectal cancer screening" },
  { measure_id: "PLACES:MAMMOUSE:age_adjusted", label: "Mammography use" },
  { measure_id: "PLACES:CSMOKING:age_adjusted", label: "Current cigarette smoking" },
  { measure_id: "PLACES:CHECKUP:age_adjusted", label: "Annual checkup" }
]
RELEASES = ["2025", "2024"]

viewof stateChoice = Inputs.select(US_STATES, {
  label: "State",
  format: (d) => d.name,
  value: US_STATES.find((s) => s.fips === "08")
})
viewof measureChoice = Inputs.select(MEASURES, { label: "Measure", format: (d) => d.label, value: MEASURES[0] })
viewof releaseChoice = Inputs.select(RELEASES, { label: "Release" })

sql = `SELECT u.name, o.value AS pct, o.lower, o.upper, o.value_status, r.value AS rucc_code
FROM cbm.measure.observation o
JOIN cbm.geography.unit u
  ON u.geo_id = o.geo_id AND u.vintage = 2026
LEFT JOIN cbm.measure.observation r
  ON r.geo_id = o.geo_id AND r.source = 'RUCC'
WHERE o.source = 'PLACES' AND o.measure_id = '${measureChoice.measure_id}'
  AND o.source_release = '${releaseChoice}' AND u.parent_geo_id = 'state:${stateChoice.fips}'
ORDER BY pct`

queryState = {
  sql; // reactive dependency
  if (dbState.status !== "ready") return dbState;
  const t0 = performance.now();
  try {
    const result = await dbState.conn.query(sql);
    return { status: "ready", rows: rowsFromArrow(result), ms: performance.now() - t0 };
  } catch (error) {
    return { status: "error", error };
  }
}

renderStatus(dbState, queryState)

queryState.status === "ready"
  ? Inputs.table(queryState.rows, { columns: ["name", "pct", "lower", "upper", "value_status", "rucc_code"] })
  : html`<p><em>Table will render once the query returns.</em></p>`

SQL run for this table

html`<pre><code>${sql}</code></pre>`