A county’s boundary history

A state’s county-level geography across every Gazetteer vintage the release carries — run live. Connecticut (the default) is the clearest example: eight legacy counties through 2020, replaced by nine planning regions in 2022 (see Concepts › Boundary vintages).

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>`;
}

viewof stateChoice = Inputs.select(US_STATES, {
  label: "State",
  format: (d) => d.name,
  value: US_STATES.find((s) => s.fips === "09")
})

sql = `SELECT vintage, geo_id, name, round(aland_m2 / 1e6, 1) AS land_km2
FROM cbm.geography.unit
WHERE level = 'county' AND parent_geo_id = 'state:${stateChoice.fips}'
ORDER BY vintage, geo_id`

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: ["vintage", "geo_id", "name", "land_km2"] })
  : html`<p><em>Table will render once the query returns.</em></p>`

queryState.status === "ready"
  ? html`<p class="recipe-status">County count by vintage: ${
      Array.from(d3.rollup(queryState.rows, (v) => v.length, (d) => d.vintage))
        .sort((a, b) => a[0] - b[0])
        .map(([v, n]) => `${v}: ${n}`)
        .join(", ")
    }</p>`
  : ""

SQL run for this table

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