orange_european_artist_travel_view (view)

>10,000 rows count all

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: level2_main_occ, pantheon_1, number_wiki_editions, birth_country_guess

Next page

CREATE VIEW orange_european_artist_travel_view AS
SELECT
  wikidata_code,
  -- short DuckDuckGo display URL (compact)
  CASE
    WHEN length(COALESCE(ddg_search_url, '')) > 80 THEN substr(COALESCE(ddg_search_url, ''), 1, 77) || '...'
    ELSE COALESCE(ddg_search_url, 'https://duckduckgo.com/?q=' || replace(COALESCE(name,''), ' ', '+'))
  END AS ddg_search_url_short,
  name,
  level3_main_occ,
  -- rounded distance (1 decimal)
  ROUND(born_died_distance_km, 1) AS distance_km,
  -- core place/year columns (left)
  birth_place_readable AS birth_place,
  death_place_readable AS death_place,
  CASE WHEN length(COALESCE(birth, '')) >= 4 THEN CAST(substr(birth, 1, 4) AS INTEGER) END AS birth_year,
  CASE WHEN length(COALESCE(death, '')) >= 4 THEN CAST(substr(death, 1, 4) AS INTEGER) END AS death_year,
  -- region
  COALESCE(all_geography_groups, un_region) AS region,
  -- other (middle) columns you still want accessible
  level1_main_occ AS occupation_group,
  level2_main_occ,
  pantheon_1,
  bpla1,
  bplo1,
  dpla1,
  dplo1,
  birth_place_readable_raw,
  death_place_readable_raw,
  -- ----- push these all the way to the right -----
  number_wiki_editions,
  wiki_readers_2015_2018,
  non_missing_score,
  list_wikipedia_editions,
  birth_place_lat,
  birth_place_lon,
  death_place_lat,
  death_place_lon,
  -- birth_country_guess (heuristic based on comma in readable place)
  CASE
    WHEN birth_place_readable IS NOT NULL AND instr(birth_place_readable, ',') > 0
    THEN trim(substr(birth_place_readable, instr(birth_place_readable, ',') + 1))
    ELSE NULL
  END AS birth_country_guess,
  -- full DuckDuckGo search URL (last/rightmost column)
  COALESCE(ddg_search_url, 'https://duckduckgo.com/?q=' || replace(COALESCE(name, ''), ' ', '+')) AS ddg_search_url
FROM orange_places
WHERE
  (COALESCE(all_geography_groups, '') LIKE '%Europe%' OR COALESCE(un_region, '') LIKE '%Europe%')
  AND born_died_distance_km IS NOT NULL
  AND born_died_distance_km >= 0
  AND (
    level1_main_occ = 'Culture'
    OR level2_main_occ LIKE '%artist%'
    OR level3_main_occ LIKE '%artist%'
  );