Where's the SQL and what parquet files are getting queried for the Interactive Explorer?

[ekansa]

I want to investigate the SQL used in the Interactive Explorer to see how results are generated. I need to know where in the code base I should look, and I need Claude to make some simple plain English explanations about how the SQL works. Also clearly describe which parquet files get queried, and provide the locations for where those parquet files are stored.

[rdhyee]

Hi Eric — here's the full picture, and there's a docs PR in flight (#264) that adds reproducible provenance.

Where the code is: all Explorer logic lives in explorer.qmd. It runs entirely in your browser via DuckDB-WASM — there's no server processing queries. URL/file definitions are around lines 755–781; the SQL is inline throughout.

The parquet files it queries — all hosted at https://data.isamples.org/ (a Cloudflare Worker fronting an R2 bucket), all read via HTTP range requests so only the needed bytes download:

File	Purpose
…_h3_summary_res4 / res6 / res8.parquet	Pre-aggregated hex-cluster counts for the zoomed-out globe (continental → neighborhood). Carry only dominant_source.
…_samples_map_lite.parquet (~60 MB, 6M rows)	Individual point dots, the samples table, and the heatmap. Queried with a lat/lng bounding box for your viewport.
…_sample_facets_v2.parquet (~63 MB)	One row/sample with material/context/object_type URIs — drives facet filtering + full-text search.
…_facet_summaries.parquet (~2 KB) + …_facet_cross_filter.parquet (~6 KB)	Pre-baked facet counts for fast checkbox numbers.
current/wide.parquet (~292 MB)	Full record — only touched when you click a single sample (one-row range fetch).
vocab_labels.parquet (~58 KB)	Maps vocabulary URIs → human-readable labels.

How the SQL turns a view into results, in plain terms:

• Zoomed out → reads the small H3 summary files and paints colored hex dots (counts + dominant source per hex).
• Zoom in past ~120 km → switches to individual points, querying samples_map_lite with latitude BETWEEN … AND longitude BETWEEN … for your viewport (capped at ~5,000 dots).
• Type a search or click a facet → scans sample_facets_v2, materializes the matching pids into a temporary in-browser table, and every other query narrows itself with … pid IN (SELECT pid FROM search_pids).
• Click one sample → a single-row fetch from the big wide file, joined to vocab_labels for readable names (this is the popup).

Everything derives from the frozen Zenodo export (the iSamples Central API has been offline since Aug 2025).

Deeper docs: PR #264 adds DATA_PROVENANCE.md (the full build-pipeline DAG) and scripts/build_frontend_derived.py (the previously-unscripted build for the derived files). Together with the existing SERIALIZATIONS.md (per-file schema + example queries) that should give you everything to trace any number end-to-end. Happy to walk through any specific query.

---

— 🤖 rbotyee (RY's bot). RY skimmed this; the answer is the bot's. Ping @rdhyee if it's off.