Schema for orange_places_final_for_prod
CREATE TABLE "orange_places" (
"wikidata_code" TEXT,
"birth" TEXT,
"death" TEXT,
"updated_death_date" TEXT,
"approx_birth" TEXT,
"approx_death" TEXT,
"birth_min" TEXT,
"birth_max" TEXT,
"death_min" TEXT,
"death_max" TEXT,
"gender" TEXT,
"level1_main_occ" TEXT,
"name" TEXT,
"un_subregion" TEXT,
"birth_estimation" TEXT,
"death_estimation" TEXT,
"bigperiod_birth_graph_b" TEXT,
"bigperiod_death_graph_b" TEXT,
"curid" TEXT,
"level2_main_occ" TEXT,
"freq_main_occ" TEXT,
"freq_second_occ" TEXT,
"level2_second_occ" TEXT,
"level3_main_occ" TEXT,
"bigperiod_birth" TEXT,
"bigperiod_death" TEXT,
"wiki_readers_2015_2018" TEXT,
"non_missing_score" TEXT,
"total_count_words_b" TEXT,
"number_wiki_editions" TEXT,
"total_noccur_links_b" TEXT,
"sum_visib_ln_5criteria" TEXT,
"ranking_visib_5criteria" TEXT,
"all_geography_groups" TEXT,
"string_citizenship_raw_d" TEXT,
"citizenship_1_b" TEXT,
"citizenship_2_b" TEXT,
"list_areas_of_rattach" TEXT,
"area1_of_rattachment" TEXT,
"area2_of_rattachment" TEXT,
"list_wikipedia_editions" TEXT,
"un_region" TEXT,
"group_wikipedia_editions" TEXT,
"bplo1" TEXT,
"dplo1" TEXT,
"bpla1" TEXT,
"dpla1" TEXT,
"pantheon_1" TEXT,
"level3_all_occ" TEXT,
"birth_place_readable" TEXT,
"birth_place_lat" TEXT,
"birth_place_lon" TEXT,
"birth_place_source" TEXT,
"birth_place_choice_reason" TEXT,
"death_place_readable" TEXT,
"death_place_lat" TEXT,
"death_place_lon" TEXT,
"death_place_source" TEXT,
"death_place_choice_reason" TEXT
, "birth_place_readable_raw" TEXT, "death_place_readable_raw" TEXT, born_died_distance_km REAL, ddg_search_url TEXT);
CREATE INDEX "idx_orange_places_qid" ON "orange_places"("qid");
CREATE INDEX "idx_orange_places_birth_place_match_quality" ON "orange_places"("birth_place_match_quality");
CREATE INDEX "idx_orange_places_death_place_match_quality" ON "orange_places"("death_place_match_quality");
CREATE INDEX idx_orange_wikidata_code ON orange_places(wikidata_code);
CREATE INDEX idx_orange_birth_coords ON orange_places(birth_place_lat, birth_place_lon);
CREATE INDEX idx_orange_death_coords ON orange_places(death_place_lat, death_place_lon);
CREATE VIRTUAL TABLE orange_places_fts USING fts5(name, birth_place_readable, death_place_readable, content='orange_places');
CREATE TABLE 'orange_places_fts_data'(id INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE 'orange_places_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
CREATE TABLE 'orange_places_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
CREATE TABLE 'orange_places_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID;
CREATE VIEW orange_people_view AS
SELECT
wikidata_code,
name,
gender,
-- normalized birth/death years
CASE WHEN birth_min IS NULL THEN NULL
WHEN typeof(birth_min) = 'text' AND length(birth_min) >= 4 AND substr(birth_min,5,1) IN ('-','T')
THEN CAST(substr(birth_min,1,4) AS INTEGER)
ELSE CAST(birth_min AS INTEGER)
END AS birth_year_min,
CASE WHEN birth_max IS NULL THEN NULL
WHEN typeof(birth_max) = 'text' AND length(birth_max) >= 4 AND substr(birth_max,5,1) IN ('-','T')
THEN CAST(substr(birth_max,1,4) AS INTEGER)
ELSE CAST(birth_max AS INTEGER)
END AS birth_year_max,
CASE WHEN death_min IS NULL THEN NULL
WHEN typeof(death_min) = 'text' AND length(death_min) >= 4 AND substr(death_min,5,1) IN ('-','T')
THEN CAST(substr(death_min,1,4) AS INTEGER)
ELSE CAST(death_min AS INTEGER)
END AS death_year_min,
CASE WHEN death_max IS NULL THEN NULL
WHEN typeof(death_max) = 'text' AND length(death_max) >= 4 AND substr(death_max,5,1) IN ('-','T')
THEN CAST(substr(death_max,1,4) AS INTEGER)
ELSE CAST(death_max AS INTEGER)
END AS death_year_max,
-- places & contextual fields
birth_place_readable AS birth_place,
death_place_readable AS death_place,
-- nationality / country fields
string_citizenship_raw_d AS country_raw,
citizenship_1_b,
citizenship_2_b,
-- occupation / periods
level1_main_occ AS occupation,
level2_main_occ,
level3_main_occ,
bigperiod_birth,
bigperiod_death,
-- identifier
curid,
-- (kept before coords/sources) any helper or other fields you want earlier can go here
-- coordinates and provenance (moved towards the right)
birth_place_lat,
birth_place_lon,
birth_place_source,
death_place_lat,
death_place_lon,
death_place_source,
-- choice reasons moved to the far right
birth_place_choice_reason,
death_place_choice_reason,
-- DuckDuckGo helper columns moved to the absolute right
replace(replace(coalesce(name,''), '&', 'and'), ' ', '+') AS ddg_query,
'https://duckduckgo.com/?q=' || replace(replace(coalesce(name,''), '&', 'and'), ' ', '+') AS ddg_search_url
FROM orange_places;
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%'
)