import { useState, useEffect, useRef } from "react"; import { useSQLQuery, useDiveState } from "@motherduck/react-sql-query"; import * as d3 from "d3"; import { LineChart, Line, XAxis, YAxis, CartesianGrid, Tooltip, ResponsiveContainer, ReferenceLine } from "recharts"; export const REQUIRED_DATABASES = [ { type: 'share', path: 'md:_share/tourism/7be9193b-7a2a-4907-8bf7-a0c0c8a0835a', alias: 'tourism' } ]; const N = (v) => (v != null ? Number(v) : 0); const MAX_RATIO = 2000; const ALL_YEARS = Array.from({ length: 28 }, (_, i) => 1995 + i); const REGION_COLORS = { "Africa": "#e18727", "Asia": "#0777b3", "Americas": "#2d7a00", "Europe": "#bd4e35", "Oceania": "#638CAD", }; const LINE_COLORS = ["#0777b3", "#bd4e35", "#2d7a00", "#e18727", "#638CAD", "#8b5cf6", "#ec4899", "#f59e0b"]; const DEFAULT_COUNTRIES = ["ISL", "HRV", "KHM", "ETH"]; function AnimatedMap({ allData, geoData, year, isLoading }) { const svgRef = useRef(null); const [tooltip, setTooltip] = useState(null); useEffect(() => { if (!geoData || isLoading || !allData.length || !svgRef.current) return; const yearData = allData.filter(r => N(r.year) === year); const byIso = {}; yearData.forEach(r => { byIso[r.iso3] = r; }); const colorScale = d3.scaleSequential(d3.interpolateRdYlGn).domain([MAX_RATIO, 0]).clamp(true); const svg = d3.select(svgRef.current); const isFirst = svg.select("g.countries").empty(); const width = svgRef.current.clientWidth || 720; const height = 340; svg.attr("viewBox", `0 0 ${width} ${height}`); const proj = d3.geoNaturalEarth1().scale(width / 6.3).translate([width / 2, height / 2 + 10]); const path = d3.geoPath().projection(proj); const features = geoData.features || []; if (isFirst) { const g = svg.append("g").attr("class", "countries"); g.selectAll("path").data(features).join("path") .attr("d", path).attr("stroke", "#fff").attr("stroke-width", 0.4).style("cursor", "pointer") .on("mousemove", function(event, feat) { const match = byIso[feat.properties?.iso_a3]; if (match) { const rect = svgRef.current.getBoundingClientRect(); setTooltip({ x: event.clientX - rect.left, y: event.clientY - rect.top, iso3: match.iso3 }); } else setTooltip(null); }) .on("mouseleave", () => setTooltip(null)); const zoom = d3.zoom().scaleExtent([1, 8]).translateExtent([[0, 0], [width, height]]) .on("zoom", e => { g.attr("transform", e.transform); g.selectAll("path").attr("stroke-width", 0.4 / e.transform.k); }); svg.call(zoom); svg.on("dblclick", () => svg.transition().duration(400).call(zoom.transform, d3.zoomIdentity)); } const paths = svg.select("g.countries").selectAll("path").data(features); (isFirst ? paths : paths.transition().duration(300)) .attr("fill", feat => { const match = byIso[feat.properties?.iso_a3]; return match ? colorScale(N(match.ratio)) : "#d4d4d4"; }); }, [geoData, allData, year, isLoading]); const tooltipRow = tooltip ? allData.find(r => r.iso3 === tooltip.iso3 && N(r.year) === year) : null; return (

{isLoading || !geoData ?

Loading map…

: } {tooltipRow && tooltip && (

{tooltipRow.country}

UNESCO sites: {N(tooltipRow.unesco_count)}

Visitors/site: {N(tooltipRow.ratio).toLocaleString()}K

{tooltipRow.region}

)}

); } function TrendsTab({ allData, isLoading }) { const [selected, setSelected] = useDiveState("selected", DEFAULT_COUNTRIES); const [search, setSearch] = useState(""); const countries = Array.from( allData.reduce((m, r) => { if (!m.has(r.iso3)) m.set(r.iso3, { iso3: r.iso3, country: r.country, region: r.region, unesco_count: N(r.unesco_count) }); return m; }, new Map()).values() ).sort((a, b) => a.country.localeCompare(b.country)); const filtered = search.trim() ? countries.filter(c => c.country.toLowerCase().includes(search.toLowerCase())) : countries; const regions = ["Africa", "Americas", "Asia", "Europe", "Oceania"]; const byRegion = regions.reduce((acc, r) => { acc[r] = filtered.filter(c => c.region === r); return acc; }, {}); const toggle = (iso3) => { const s = Array.isArray(selected) ? selected : DEFAULT_COUNTRIES; setSelected(s.includes(iso3) ? s.filter(x => x !== iso3) : [...s, iso3].slice(-8)); }; const sel = Array.isArray(selected) ? selected : DEFAULT_COUNTRIES; const chartData = ALL_YEARS.map(yr => { const row = { year: yr }; sel.forEach(iso3 => { const d = allData.find(r => r.iso3 === iso3 && N(r.year) === yr); row[iso3] = d ? N(d.ratio) : null; }); const yearRatios = allData.filter(r => N(r.year) === yr).map(r => N(r.ratio)).filter(v => v > 0).sort((a, b) => a - b); if (yearRatios.length) { const mid = Math.floor(yearRatios.length / 2); row._median = yearRatios.length % 2 !== 0 ? yearRatios[mid] : Math.round((yearRatios[mid - 1] + yearRatios[mid]) / 2); } return row; }); const CustomTooltip = ({ active, payload, label }) => { if (!active || !payload?.length) return null; return (

{label}

{payload.filter(p => p.value != null).sort((a, b) => b.value - a.value).map(p => (

{p.dataKey === "_median" ? "Median (all countries)" : countries.find(c => c.iso3 === p.dataKey)?.country}: {N(p.value).toLocaleString()}K

))}

); }; return (

Visitors per UNESCO site over time. Pick any countries to compare — up to 8 at once.

{isLoading ? (

) : ( `${v}K`} fontSize={11} tick={{ fill: "#6a6a6a" }} width={45} /> } /> {sel.map((iso3, i) => ( ))} )}

Median

{sel.map((iso3, i) => { const label = countries.find(c => c.iso3 === iso3)?.country || iso3; return (

{label}

); })}

setSearch(e.target.value)} style={{ width: "100%", padding: "7px 12px", border: "1px solid #ddd", borderRadius: 6, fontSize: 13, marginBottom: 8, boxSizing: "border-box", outline: "none" }} />

{regions.map(region => { const regionCountries = byRegion[region]; if (!regionCountries?.length) return null; return (

{region}

{regionCountries.map(c => { const active = sel.includes(c.iso3); const idx = sel.indexOf(c.iso3); return ( ); })}

); })}

); } function StatsTab({ allData, isLoading }) { const byYear = (yr) => allData.filter(r => N(r.year) === yr); const data2022 = byYear(2022).filter(r => N(r.arrivals_m) > 0).sort((a, b) => N(b.arrivals_m) - N(a.arrivals_m)); const rank1995 = {}; byYear(1995).filter(r => N(r.arrivals_m) > 0) .sort((a, b) => N(b.arrivals_m) - N(a.arrivals_m)) .forEach((r, i) => { rank1995[r.iso3] = i + 1; }); const rankShifters = data2022.slice(0, 15).map((r, i) => ({ ...r, rank2022: i + 1, rank1995: rank1995[r.iso3] || null, change: rank1995[r.iso3] ? rank1995[r.iso3] - (i + 1) : null })); const data1995 = byYear(1995).reduce((m, r) => { m[r.iso3] = r; return m; }, {}); const data2019 = byYear(2019).reduce((m, r) => { m[r.iso3] = r; return m; }, {}); const glowUp = Object.keys(data2019) .filter(iso3 => data1995[iso3] && N(data1995[iso3].ratio) > 10) .map(iso3 => ({ ...data2019[iso3], ratio_1995: N(data1995[iso3].ratio), ratio_2019: N(data2019[iso3].ratio), pct: Math.round((N(data2019[iso3].ratio) - N(data1995[iso3].ratio)) / N(data1995[iso3].ratio) * 100) })) .sort((a, b) => b.pct - a.pct) .slice(0, 8); const sleepers = byYear(2022).filter(r => N(r.unesco_count) >= 5).sort((a, b) => N(a.ratio) - N(b.ratio)).slice(0, 8); const mostVisited = byYear(2022).filter(r => N(r.arrivals_m) > 0).sort((a, b) => N(b.arrivals_m) - N(a.arrivals_m)).slice(0, 8); const RankBadge = ({ change }) => { if (change === null) return NEW; if (change > 0) return ▲{change}; if (change < 0) return ▼{Math.abs(change)}; return —; }; const Panel = ({ title, subtitle, children }) => (

{title}

{subtitle &&

{subtitle}

} {children}

); if (isLoading) return (

{[...Array(4)].map((_, i) =>

)}

); return (

{rankShifters.map(r => ( ))}

#	Country	2022	vs '95
{r.rank2022}	{r.country}	{N(r.arrivals_m).toFixed(1)}M

{glowUp.map(r => ( ))}

Country	1995	2019	Growth
{r.country}	{r.ratio_1995.toLocaleString()}K	{r.ratio_2019.toLocaleString()}K	+{r.pct.toLocaleString()}%

{sleepers.map(r => ( ))}

Country	Sites	Visitors	Per Site
{r.country}	{N(r.unesco_count)}	{N(r.arrivals_m).toFixed(1)}M	{N(r.ratio).toLocaleString()}K

{mostVisited.map((r, i) => ( ))}

#	Country	Arrivals	Sites
{i + 1}	{r.country}	{N(r.arrivals_m).toFixed(1)}M	{N(r.unesco_count)}

); } export default function HiddenGemsTimeline() { const [tab, setTab] = useDiveState("tab", "map"); const [year, setYear] = useDiveState("year", 2000); const [playing, setPlaying] = useState(false); const [geoData, setGeoData] = useState(null); const intervalRef = useRef(null); const { data: geoRows } = useSQLQuery(`SELECT iso3, name, geometry FROM "tourism"."main"."world_features"`); useEffect(() => { if (!geoRows || !Array.isArray(geoRows)) return; setGeoData({ type: "FeatureCollection", features: geoRows.map(r => ({ type: "Feature", properties: { iso_a3: r.iso3, name: r.name }, geometry: typeof r.geometry === "string" ? JSON.parse(r.geometry) : r.geometry }))}); }, [geoRows]); const { data: rawData, isLoading } = useSQLQuery(` WITH iso_map AS (SELECT iso2, iso3, region FROM "tourism"."main"."iso_countries"), site_counts AS ( SELECT TRIM("ISO Codes") as iso2, COUNT(*) as unesco_count FROM "tourism"."main"."unesco_sites" WHERE Transboundary = false AND "ISO Codes" NOT LIKE '%,%' GROUP BY TRIM("ISO Codes") ) SELECT a.Entity as country, a.Code as iso3, m.region, CAST(a.Year AS INTEGER) as year, CAST(s.unesco_count AS INTEGER) as unesco_count, ROUND(a."Arrivals of tourists from abroad" / s.unesco_count / 1000.0) as ratio, ROUND(a."Arrivals of tourists from abroad" / 1000000.0, 1) as arrivals_m FROM "tourism"."main"."arrivals" a JOIN iso_map m ON a.Code = m.iso3 JOIN site_counts s ON m.iso2 = s.iso2 WHERE a."Arrivals of tourists from abroad" IS NOT NULL AND s.unesco_count >= 2 AND a.Year BETWEEN 1995 AND 2022 ORDER BY a.Year, ratio ASC `); const allData = Array.isArray(rawData) ? rawData : []; useEffect(() => { if (playing) { intervalRef.current = setInterval(() => { setYear(y => { const n = (y || 2000) + 1; if (n > 2022) { setPlaying(false); return 2022; } return n; }); }, 600); } else clearInterval(intervalRef.current); return () => clearInterval(intervalRef.current); }, [playing]); const currentYear = year || 2000; const isCovid = currentYear === 2020 || currentYear === 2021; const tabs = [{ id: "map", label: "🗺 Map" }, { id: "trends", label: "📈 Trends" }, { id: "stats", label: "📊 Stats" }]; return (

Watch the World Get Discovered 🌍

Tourist arrivals per UNESCO site, 1995–2022. Green = hidden gem. Red = tourist trap.

{tabs.map(t => ( ))}

{tab === "map" && ( <>

{currentYear} {isCovid && COVID}

{ setPlaying(false); setYear(Number(e.target.value)); }} style={{ width: "100%", cursor: "pointer", accentColor: "#0777b3" }} />

{[1995, 2000, 2005, 2010, 2015, 2019, 2020, 2022].map(y => ( {y} ))}

Hidden gem → Tourist trap · Scroll to zoom · Drag to pan · Double-click to reset

Top Hidden Gems · {currentYear}

{isLoading ? (

{[...Array(6)].map((_, i) =>

)}

) : ( {["#", "Country", "Region", "Sites", "Visitors/Site"].map((h, i) => ( ))} {allData.filter(r => N(r.year) === currentYear).sort((a, b) => N(a.ratio) - N(b.ratio)).slice(0, 8).map((row, i) => ( ))}

= 3 ? "right" : "left", padding: "5px 8px", color: "#6a6a6a", fontWeight: 600 }}>{h}
{i + 1}	{row.country}	{row.region}	{N(row.unesco_count)}	{N(row.ratio).toLocaleString()}K

)}

)} {tab === "trends" && } {tab === "stats" && }

Sources: UN Tourism via Our World in Data · UNESCO World Heritage Centre · Excludes transboundary sites

); }

Hidden Gems: Watch the World Get Discovered

Comments