A City-Level Look At 6 Years Of American Television

Kalev's latest Forbes piece features a city/landmark-level map visualizing the every location on earth mentioned at least 5 or more times on American television news shows monitored by the Internet Archive over the last 6 years.

Creating this map required just a single line of code in Google BigQuery and just 4.8 seconds to complete and just a few more seconds to import directly into CartoDB for the final interactive clickable map.

select location, lat, long, cnt, numshows from (
SELECT MAX(location) location, lat, long, COUNT(*) as cnt, count(distinct(DocumentIdentifier)) as numshows
FROM (
select DocumentIdentifier, REGEXP_EXTRACT(SPLIT(V2Locations,';'),r'^[2-5]#(.*?)#') as location, ROUND(FLOAT(REGEXP_EXTRACT(SPLIT(V2Locations,';'),r'^[2-5]#.*?#.*?#.*?#.*?#(.*?)#.*?#')),3) as lat, ROUND(FLOAT(REGEXP_EXTRACT(SPLIT(V2Locations,';'),r'^[2-5]#.*?#.*?#.*?#.*?#.*?#(.*?)#')),3) AS long
FROM [gdelt-bq:gdeltv2.iatvgkg] 
where DATE>200900000000000
)
where lat is not null and long is not null and location is not null and abs(lat) < 80 and (abs(lat) > 0 or abs(long) > 0)
group by lat,long
) where cnt >= 5
ORDER BY cnt DESC

Click on the map below to launch the interactive zoomable version.

2016-television-as-data-city-level-map