U.S. Specialty Coffee Roaster Atlas

tool call · select

SELECT
  url,
  title,
  query,
  UNNEST(
    SEM_EXTRACT_ALL(
      content,
      'a United States specialty-coffee roasting business or roasting facility; exclude cafes and retailers that do not roast their own coffee',
      roaster := 'business or roastery name',
      city := 'city of the roasting headquarters or roasting facility',
      state := 'US state or District of Columbia, two-letter abbreviation',
      street_address := 'street address of the roasting headquarters or facility',
      postal_code := 'US ZIP code',
      founded := 'founding date or founding year of the roasting business, as stated',
      founders := 'founder or founders, as stated',
      specialty_focus := 'important coffee attributes: sourcing, roast style, signature specialty, processing focus, or mission',
      certifications := 'certifications or verified sustainability programs',
      ownership_notes := 'stated ownership identity such as worker-owned, family-owned, woman-owned, Black-owned, Indigenous-owned, veteran-owned, or LGBTQ-owned',
      has_cafe := 'whether the roaster operates a cafe or tasting room, true or false if stated',
      online_sales := 'whether consumers can order coffee online, true or false if stated',
      wholesale := 'whether wholesale coffee is offered, true or false if stated',
      website := 'official business website URL'
    ),
    recursive := true
  )
FROM WEB_SEARCH(
  'Alabama specialty coffee roasters directory list',
  'Alaska artisan coffee roasting companies',
  'Arizona third wave coffee roasters guide',
  'Arkansas specialty coffee roaster directory',
  'California specialty coffee roasters directory cities',
  'Colorado independent coffee roasting companies list',
  'Connecticut specialty coffee roasters guide',
  'Delaware artisan coffee roasters list',
  'USA specialty coffee roaster directory Alabama Alaska Arizona Arkansas California Colorado Connecticut Delaware'
)
WHERE SEM_MATCH(content, 'identifies at least one business that roasts specialty coffee in one of the queried US states')

{
 "show_preview": false
}

tool call · select

SELECT
  url,
  title,
  query,
  UNNEST(
    SEM_EXTRACT_ALL(
      content,
      'a United States specialty-coffee roasting business or roasting facility; exclude cafes and retailers that do not roast their own coffee',
      roaster := 'business or roastery name',
      city := 'city of the roasting headquarters or roasting facility',
      state := 'US state or District of Columbia, two-letter abbreviation',
      street_address := 'street address of the roasting headquarters or facility',
      postal_code := 'US ZIP code',
      founded := 'founding date or founding year of the roasting business, as stated',
      founders := 'founder or founders, as stated',
      specialty_focus := 'important coffee attributes: sourcing, roast style, signature specialty, processing focus, or mission',
      certifications := 'certifications or verified sustainability programs',
      ownership_notes := 'stated ownership identity such as worker-owned, family-owned, woman-owned, Black-owned, Indigenous-owned, veteran-owned, or LGBTQ-owned',
      has_cafe := 'whether the roaster operates a cafe or tasting room, true or false if stated',
      online_sales := 'whether consumers can order coffee online, true or false if stated',
      wholesale := 'whether wholesale coffee is offered, true or false if stated',
      website := 'official business website URL'
    ),
    recursive := true
  )
FROM WEB_SEARCH(
  'District of Columbia DC specialty coffee roasters list',
  'Florida specialty coffee roasting companies directory',
  'Georgia artisan coffee roasters guide',
  'Hawaii specialty Kona coffee roasters directory',
  'Idaho independent specialty coffee roasters',
  'Illinois third wave coffee roasting companies list',
  'Indiana specialty coffee roaster directory',
  'Iowa artisan coffee roasters list',
  'USA specialty coffee roaster directory DC Florida Georgia Hawaii Idaho Illinois Indiana Iowa'
)
WHERE SEM_MATCH(content, 'identifies at least one business that roasts specialty coffee in one of the queried US states or District of Columbia')

{
 "show_preview": false
}

tool call · select

SELECT
  url,
  title,
  query,
  UNNEST(
    SEM_EXTRACT_ALL(
      content,
      'a United States specialty-coffee roasting business or roasting facility; exclude cafes and retailers that do not roast their own coffee',
      roaster := 'business or roastery name',
      city := 'city of the roasting headquarters or roasting facility',
      state := 'US state or District of Columbia, two-letter abbreviation',
      street_address := 'street address of the roasting headquarters or facility',
      postal_code := 'US ZIP code',
      founded := 'founding date or founding year of the roasting business, as stated',
      founders := 'founder or founders, as stated',
      specialty_focus := 'important coffee attributes: sourcing, roast style, signature specialty, processing focus, or mission',
      certifications := 'certifications or verified sustainability programs',
      ownership_notes := 'stated ownership identity such as worker-owned, family-owned, woman-owned, Black-owned, Indigenous-owned, veteran-owned, or LGBTQ-owned',
      has_cafe := 'whether the roaster operates a cafe or tasting room, true or false if stated',
      online_sales := 'whether consumers can order coffee online, true or false if stated',
      wholesale := 'whether wholesale coffee is offered, true or false if stated',
      website := 'official business website URL'
    ),
    recursive := true
  )
FROM WEB_SEARCH(
  'Kansas specialty coffee roasters directory list',
  'Kentucky artisan coffee roasting companies',
  'Louisiana third wave coffee roasters guide',
  'Maine specialty coffee roaster directory',
  'Maryland independent coffee roasting companies list',
  'Massachusetts specialty coffee roasters guide',
  'Michigan artisan coffee roasters list',
  'Minnesota specialty coffee roasting companies directory',
  'USA specialty coffee roaster directory Kansas Kentucky Louisiana Maine Maryland Massachusetts Michigan Minnesota'
)
WHERE SEM_MATCH(content, 'identifies at least one business that roasts specialty coffee in one of the queried US states')

{
 "show_preview": false
}

tool call · select

SELECT
  url,
  title,
  query,
  UNNEST(
    SEM_EXTRACT_ALL(
      content,
      'a United States specialty-coffee roasting business or roasting facility; exclude cafes and retailers that do not roast their own coffee',
      roaster := 'business or roastery name',
      city := 'city of the roasting headquarters or roasting facility',
      state := 'US state or District of Columbia, two-letter abbreviation',
      street_address := 'street address of the roasting headquarters or facility',
      postal_code := 'US ZIP code',
      founded := 'founding date or founding year of the roasting business, as stated',
      founders := 'founder or founders, as stated',
      specialty_focus := 'important coffee attributes: sourcing, roast style, signature specialty, processing focus, or mission',
      certifications := 'certifications or verified sustainability programs',
      ownership_notes := 'stated ownership identity such as worker-owned, family-owned, woman-owned, Black-owned, Indigenous-owned, veteran-owned, or LGBTQ-owned',
      has_cafe := 'whether the roaster operates a cafe or tasting room, true or false if stated',
      online_sales := 'whether consumers can order coffee online, true or false if stated',
      wholesale := 'whether wholesale coffee is offered, true or false if stated',
      website := 'official business website URL'
    ),
    recursive := true
  )
FROM WEB_SEARCH(
  'Mississippi specialty coffee roasters directory list',
  'Missouri artisan coffee roasting companies',
  'Montana third wave coffee roasters guide',
  'Nebraska specialty coffee roaster directory',
  'Nevada independent coffee roasting companies list',
  'New Hampshire specialty coffee roasters guide',
  'New Jersey artisan coffee roasters list',
  'New Mexico specialty coffee roasting companies directory',
  'USA specialty coffee roaster directory Mississippi Missouri Montana Nebraska Nevada New Hampshire New Jersey New Mexico'
)
WHERE SEM_MATCH(content, 'identifies at least one business that roasts specialty coffee in one of the queried US states')

{
 "show_preview": false
}

tool call · select

SELECT
  url,
  title,
  query,
  UNNEST(
    SEM_EXTRACT_ALL(
      content,
      'a United States specialty-coffee roasting business or roasting facility; exclude cafes and retailers that do not roast their own coffee',
      roaster := 'business or roastery name',
      city := 'city of the roasting headquarters or roasting facility',
      state := 'US state or District of Columbia, two-letter abbreviation',
      street_address := 'street address of the roasting headquarters or facility',
      postal_code := 'US ZIP code',
      founded := 'founding date or founding year of the roasting business, as stated',
      founders := 'founder or founders, as stated',
      specialty_focus := 'important coffee attributes: sourcing, roast style, signature specialty, processing focus, or mission',
      certifications := 'certifications or verified sustainability programs',
      ownership_notes := 'stated ownership identity such as worker-owned, family-owned, woman-owned, Black-owned, Indigenous-owned, veteran-owned, or LGBTQ-owned',
      has_cafe := 'whether the roaster operates a cafe or tasting room, true or false if stated',
      online_sales := 'whether consumers can order coffee online, true or false if stated',
      wholesale := 'whether wholesale coffee is offered, true or false if stated',
      website := 'official business website URL'
    ),
    recursive := true
  )
FROM WEB_SEARCH(
  'New York specialty coffee roasters statewide directory',
  'North Carolina artisan coffee roasting companies list',
  'North Dakota specialty coffee roaster directory',
  'Ohio third wave coffee roasters guide',
  'Oklahoma independent specialty coffee roasters',
  'Oregon specialty coffee roasting companies directory',
  'Pennsylvania artisan coffee roasters list',
  'Rhode Island specialty coffee roasters guide',
  'South Carolina specialty coffee roaster directory',
  'USA specialty coffee roaster directory New York North Carolina North Dakota Ohio Oklahoma Oregon Pennsylvania Rhode Island South Carolina'
)
WHERE SEM_MATCH(content, 'identifies at least one business that roasts specialty coffee in one of the queried US states')

{
 "show_preview": false
}

tool call · select

SELECT
  url,
  title,
  query,
  UNNEST(
    SEM_EXTRACT_ALL(
      content,
      'a United States specialty-coffee roasting business or roasting facility; exclude cafes and retailers that do not roast their own coffee',
      roaster := 'business or roastery name',
      city := 'city of the roasting headquarters or roasting facility',
      state := 'US state or District of Columbia, two-letter abbreviation',
      street_address := 'street address of the roasting headquarters or facility',
      postal_code := 'US ZIP code',
      founded := 'founding date or founding year of the roasting business, as stated',
      founders := 'founder or founders, as stated',
      specialty_focus := 'important coffee attributes: sourcing, roast style, signature specialty, processing focus, or mission',
      certifications := 'certifications or verified sustainability programs',
      ownership_notes := 'stated ownership identity such as worker-owned, family-owned, woman-owned, Black-owned, Indigenous-owned, veteran-owned, or LGBTQ-owned',
      has_cafe := 'whether the roaster operates a cafe or tasting room, true or false if stated',
      online_sales := 'whether consumers can order coffee online, true or false if stated',
      wholesale := 'whether wholesale coffee is offered, true or false if stated',
      website := 'official business website URL'
    ),
    recursive := true
  )
FROM WEB_SEARCH(
  'South Dakota specialty coffee roasters directory list',
  'Tennessee artisan coffee roasting companies',
  'Texas specialty coffee roasters statewide directory',
  'Utah third wave coffee roasters guide',
  'Vermont independent specialty coffee roasters',
  'Virginia specialty coffee roasting companies list',
  'Washington state artisan coffee roasters directory',
  'West Virginia specialty coffee roasters guide',
  'Wisconsin specialty coffee roaster directory',
  'Wyoming specialty coffee roasting companies list',
  'USA specialty coffee roaster directory South Dakota Tennessee Texas Utah Vermont Virginia Washington West Virginia Wisconsin Wyoming'
)
WHERE SEM_MATCH(content, 'identifies at least one business that roasts specialty coffee in one of the queried US states')

{
 "show_preview": false
}

tool call · select

WITH combined AS (
  SELECT * FROM r4dc0f634a71
  UNION ALL BY NAME
  SELECT * FROM rae679480621
  UNION ALL BY NAME
  SELECT * FROM r67eaf61c41b
  UNION ALL BY NAME
  SELECT * FROM rf4a4476e3e6
  UNION ALL BY NAME
  SELECT * FROM ra809134cd5c
  UNION ALL BY NAME
  SELECT * FROM r8b945084d87
),
valid AS (
  SELECT
    *,
    (CASE WHEN founded IS NOT NULL THEN 1 ELSE 0 END
      + CASE WHEN founders IS NOT NULL THEN 1 ELSE 0 END
      + CASE WHEN street_address IS NOT NULL THEN 1 ELSE 0 END
      + CASE WHEN postal_code IS NOT NULL THEN 1 ELSE 0 END
      + CASE WHEN specialty_focus IS NOT NULL THEN 1 ELSE 0 END
      + CASE WHEN certifications IS NOT NULL THEN 1 ELSE 0 END
      + CASE WHEN ownership_notes IS NOT NULL THEN 1 ELSE 0 END
      + CASE WHEN website IS NOT NULL THEN 1 ELSE 0 END
      + CASE WHEN has_cafe IS NOT NULL THEN 1 ELSE 0 END
      + CASE WHEN online_sales IS NOT NULL THEN 1 ELSE 0 END
      + CASE WHEN wholesale IS NOT NULL THEN 1 ELSE 0 END) AS completeness
  FROM combined
  WHERE roaster IS NOT NULL
    AND city IS NOT NULL
    AND UPPER(state) IN (
      'AL','AK','AZ','AR','CA','CO','CT','DE','FL','GA','HI','ID','IL','IN','IA','KS','KY','LA','ME','MD','MA','MI','MN','MS','MO','MT','NE','NV','NH','NJ','NM','NY','NC','ND','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VT','VA','WA','WV','WI','WY','DC'
    )
    AND length(trim(roaster)) >= 3
    AND lower(trim(roaster)) NOT IN ('coffee roaster','coffee roasters','specialty coffee','coffee shop','coffee shops','roaster','unknown')
)
SELECT
  SEM_NORM(roaster, 'canonical coffee-roasting business name; merge abbreviations and spelling variants, but keep distinct businesses separate') AS roaster,
  SEM_NORM(city, 'canonical US city name') AS city,
  UPPER(state) AS state,
  MODE(street_address) FILTER (WHERE street_address IS NOT NULL) AS street_address,
  MODE(postal_code) FILTER (WHERE postal_code IS NOT NULL) AS postal_code,
  MODE(founded) FILTER (WHERE founded IS NOT NULL) AS founded,
  MODE(founders) FILTER (WHERE founders IS NOT NULL) AS founders,
  ARG_MAX(specialty_focus, completeness) FILTER (WHERE specialty_focus IS NOT NULL) AS specialty_focus,
  MODE(certifications) FILTER (WHERE certifications IS NOT NULL) AS certifications,
  MODE(ownership_notes) FILTER (WHERE ownership_notes IS NOT NULL) AS ownership_notes,
  MODE(has_cafe) FILTER (WHERE has_cafe IS NOT NULL) AS has_cafe,
  MODE(online_sales) FILTER (WHERE online_sales IS NOT NULL) AS online_sales,
  MODE(wholesale) FILTER (WHERE wholesale IS NOT NULL) AS wholesale,
  MODE(website) FILTER (WHERE website IS NOT NULL) AS website,
  ARG_MAX(url, completeness) AS source_url,
  COUNT(DISTINCT url) AS source_mentions,
  MAX(completeness) AS attribute_completeness
FROM valid
GROUP BY
  SEM_NORM(roaster, 'canonical coffee-roasting business name; merge abbreviations and spelling variants, but keep distinct businesses separate'),
  SEM_NORM(city, 'canonical US city name'),
  UPPER(state)
ORDER BY
  state,
  city,
  roaster

{
 "show_preview": false
}

tool call · select

SELECT
  state,
  COUNT(*) AS roasters_found,
  COUNT(*) FILTER (WHERE founded IS NOT NULL) AS founding_date_known,
  COUNT(*) FILTER (WHERE founders IS NOT NULL) AS founders_known,
  COUNT(*) FILTER (WHERE street_address IS NOT NULL OR postal_code IS NOT NULL) AS address_known,
  COUNT(*) FILTER (WHERE specialty_focus IS NOT NULL) AS specialty_attributes_known,
  COUNT(*) FILTER (WHERE certifications IS NOT NULL) AS certifications_known,
  COUNT(*) FILTER (WHERE ownership_notes IS NOT NULL) AS ownership_attributes_known,
  COUNT(*) FILTER (WHERE has_cafe IS NOT NULL) AS cafe_status_known,
  COUNT(*) FILTER (WHERE online_sales IS NOT NULL) AS online_sales_status_known,
  COUNT(*) FILTER (WHERE wholesale IS NOT NULL) AS wholesale_status_known,
  COUNT(DISTINCT city) AS cities_represented,
  SUM(source_mentions) AS total_source_mentions,
  ARG_MAX(source_url, source_mentions) AS source_url
FROM r6c837f15f7d
GROUP BY state
ORDER BY roasters_found DESC,
  state

{
 "show_preview": false
}

tool call · generate_html_report

{"brief":"The user asked, verbatim: “show me a map of all specialty coffee roaster across the USA. show me their founding date and other important attributes as well in a table. do state-by-state queries, and then aggregate.”\n\nFinding: state-named web searches covering all 50 states plus Washington, DC produced 4,091 deduplicated specialty-coffee roaster/city/state records. These are all qualifying roasters found by this broad web census, not a guaranteed registry of every operating US roaster. California had the most records found (287), followed by New York (277), Texas (224), Washington (178), and Oregon (165). Every state plus DC is represented. A founding date/year was found for 989 records. Searches and source material were scanned on 2026-08-31. The six state batches yielded 10,491 unique search-result pages within batches (some pages may recur between batches), from which roughly 56,800 raw entity mentions were extracted before validation and deduplication.\n\nMake this a beautiful, polished national atlas. Lead with ONE large contiguous-USA choropleth map using state abbreviations and roasters_found from rced29f7aeff; include Alaska, Hawaii, and DC insets. Make the statewide volume differences obvious and label or call out the top five. The map is a distribution map, not exact storefront pins, because exact coordinates were not available consistently. Include a compact coverage/completeness panel using the supplied known-field counts, but do not independently total or infer missing values.\n\nrced29f7aeff is the state-level map and coverage dataset: one row per state/DC with roaster count, city count, known founding dates, known founders, address coverage, specialty attributes, certifications, ownership attributes, cafe/online/wholesale status coverage, source mentions, and a source URL. Use it for the lead choropleth and a ranked state table.\n\nr6c837f15f7d is the aggregated roaster dataset: one row per canonical roaster + city + state, 4,091 rows. Build a full searchable, sortable table grouped/filterable by state. Show roaster, city/state, founding date, founders, street/ZIP, specialty focus, certifications, ownership notes, cafe, online sales, wholesale, website, source mentions, and source URL. Link names to website where available and provide a source link. Preserve blank cells as unresolved rather than writing “varies” or “not stated.” Add filters for state and availability of founding date. If practical, offer CSV download/export from this table.\n\nMethodology/caveat: State-specific queries were issued for all states and DC, then extracted candidates were combined, limited to US state abbreviations with a named city and roaster, canonicalized by business name and city, and aggregated. Search pages ranged from official roaster sites and directories to local press and coffee guides. A row means the business was identified as roasting specialty coffee; it does not establish current operating status unless the source did. Chains or businesses associated with more than one city can appear once in each city. This is a broad, reproducible web census rather than a licensed or authoritative complete registry, and apparent state differences partly reflect web discoverability and directory coverage. Put this caveat near the map, not buried at the bottom.","result_set_ids":["rced29f7aeff","r6c837f15f7d"],"title":"U.S. Specialty Coffee Roaster Atlas","prev_report_token":null}