This visualization will answer the question that what's the situation of borrowing health category books from the Seattle library under the COVID-19 epidemic. To archive this goal, several datasets need to be collected. The first dataset is dewy health category books check-out information from 2020 to 2021. I will use the MYSQL query to collect this dataset from the Seattle library (The MYSQL code will be attached). The second dataset is covid-19 confirmed cases in this time period (which is collected from the USAFacts:https://usafacts.org/visualizations/cor ... washington).
In the visualization process, as a geographer, I will choose the city of Seattle shape as the container to store these data. Each slide of it will display the check-out books' information at on single time spot. And the color will be represented by the situation of the COVID-19, which I will use the USAFacts data to demonstrate.
SQL:
Code: Select all
//summary of the monthly data of all checkout books
SELECT YEAR(cout) AS Years, MONTH(cout) AS Months SUM(CASE WHEN deweyClass != '' THEN 1 ELSE 0 END) AS Dewey_Data
FROM spl_2016.outraw WHERE YEAR(cout) = '2020' and itemtype='acbk'GROUP BY
YEAR(cout) , MONTH(cout);
// Query the dataset and find all the check-out the health category acbooks in 2020
Select id,itemNumber,date(cout),deweyClass from spl_2016.inraw
where deweyClass != ''
and itemtype='acbk'
and year(cout) = 2020 and
month(cout) >= 1 and month(cout) <= 12
order by date(cout);
// Query the dataset and find all the check-out the health category acbooks in 2020
Select id,itemNumber,date(cout),deweyClass from spl_2016.inraw
where deweyClass >609 AND deweyClass<620
and itemtype='acbk'
and year(cout) = 2020 and
month(cout) >= 1 and month(cout) <= 12
order by date(cout);
//summary of the monthly data of covid
SELECT MONTH(cout) AS Months, SUM(CASE WHEN title!="" THEN 1 ELSE 0 END) AS Dewey_Data
FROM spl_2016.outraw WHERE title regexp'covid|epidemic|pandemic' and
YEAR(cout) = '2020' and itemtype='acbk'GROUP BY
MONTH(cout);
// Query the dataset and find all the check-out acbooks that relates to "COVID" "pandemic" "epidemic" in 2020
Select id,itemNumber,date(cout),deweyClass from spl_2016.inraw
where title regexp'covid|epidemic|pandemic'
and itemtype='acbk'
and year(cout) = 2020 and
month(cout) >= 1 and month(cout) <= 12
order by date(cout);