How can you use the new Covid-19 Geographic News Database to map the locations mentioned in Covid-19 news coverage? With a single SQL query in BigQuery you can bring a slice of the data directly into the incredible online mapping platform Carto for instant visualization! Explore the interactive map below, clicking on each location to get a list of relevant Covid-19 news articles published April 1-4 mentioning that location. NOTE that this is NOT an infection map, it is a map of locations mentioned in Covid-19 news coverage.
TECHNICAL DETAILS
Creating the map above took just a single SQL query, which is then trivially mapped in Carto.
select ARRAY_TO_STRING(ARRAY_AGG(Art ORDER BY cnt DESC LIMIT 5), '<BR><BR>') Articles, max(SharingImage) SharingImage, Lat Latitude, Lon Longitude, APPROX_TOP_COUNT(Loc, 1)[OFFSET(0)].value LocationName, Count(1) NumMentions from (
select CONCAT('<a href="', min(URL), '" target="blank">',max(Title),'</a>') Art, Lat, Lon, max(SharingImage) SharingImage, APPROX_TOP_COUNT(Location, 1)[OFFSET(0)].value Loc, count(1) cnt FROM `gdelt-bq.covid19.onlinenewsgeo` WHERE DATE(DateTime) >= "2020-04-01" group by LOWER(Title), Lat, Lon
) group by Lat, Lon
Then load the resulting CSV file into Carto and choose the following options for the Map Layer:
And set the following options for the Info Window:
Then you're ready to publish your live clickable map! That's all there is to it!