In the last decade, the population diversity of the Seattle metropolitan area keeps growing. Especially more and more Asian people move to Seattle due to the increasing job opportunities in tech companies. Therefore, the project 2 is to investigate the trend of reading interests about different Asian countries, which can help us to understand the correlation between reading behavior and population composition. Firstly, I chose China, India, Japan, Korea and Vietnam as five research countries. And then I designed two SQL queries to search the total check out counts of these five countries and the check out details (including related country, bibNumber, title, date and counts) from 2006 to 2021. After that, I made use of Processing to visualize the multi dimensional data in 3D format.

Query 1 - Total Counts of Five Aisan Countries
SELECT
Year(cout) AS Years,
COUNT(IF(Lower(title) LIKE '%china%' or Lower(title) like '%chinese%' or callNumber like '%chinese%', 1, NULL)) AS 'China',
COUNT(IF(lower(title) LIKE '%india%' or lower(title) like '%indian%', 1, NULL)) AS 'India',
COUNT(IF(lower(title) LIKE '%japan%' or lower(title) like '%japanese%' or callNumber like '%japanese%', 1, NULL)) AS 'Japan',
COUNT(IF(lower(title) LIKE '%korea%' or lower(title) like '%korean%' or callNumber like '%korean%', 1, NULL)) AS 'Korea',
COUNT(IF(lower(title) LIKE '%vietnam%' or lower(title) like '%vietnamese%' or callNumber like '%vietnamese%', 1, NULL)) AS 'Vietnam'
FROM
spl_2016.outraw
WHERE
YEAR(cout) < 2022
GROUP BY Years
ORDER BY Years

Query 2 - Check Out Details of Five Asian Countries
SELECT
subject AS Countries,
spl_2016.title.bibNumber AS BibNumbers,
DATE(checkOut) AS Dates,
title AS Titles,
COUNT(title) AS Counts
FROM
spl_2016.transactions,
spl_2016.subject,
spl_2016.title
WHERE
spl_2016.transactions.bibNumber = spl_2016.subject.bibNumber
AND spl_2016.transactions.bibNumber = spl_2016.title.bibNumber
AND (subject = 'china' OR subject = 'india'
OR subject = 'japan'
OR subject = 'korea'
OR subject = 'vietnam')
GROUP BY Countries, BibNumbers, Dates, Titles
ORDER BY Dates

I will mainly choose China, India, Japan, Korea and Vietnam as my research countries. So there will be five concentric circles in the 2D sketch. Every circle represents a country, the check-out counts of this country will decide the radius of the circle. The points on the circle edge will be the check-out records of every book (including date, title, and check-out counts). When moveing the mouse to the distance near the point, the details will be showed. In 3D Sketch different years will be the Z axis.

3D results 1
Firstly, I drew five circles to represent the five countries every year. Then I drew lots of points to represent every book related to these countries. And the results show that China and Japan ranked the first and second place, but the total counts are very close. Then India and Korea ranked at the third and the fourth place, and these two countries are very close to each other as well. The total counts of Vietnam is always the smallest.
3D results 2
As you can see, besides the five circles, the specific book points also construct five book circles bigger than the original five country circles. Because I calculate the coordinates of every book point based on the book country attribute and the radius of the corresponding country circle. These book circles also have the same features with the country circles.
To be continued
Next Step, I will continue to add more features in project 2, such as the interactive features and control buttons. Meanwhile, I will think about how to make the circles and points offer more useful information.

In the final version, I modified some features, like making the book points show on the corresponding country circle, and drawing the line to show the count of one book and when the mouse move over the book point, the title, date and count of this book will show up. Meanwhile, I also added a few features, such as: adding the months information, designing five controlP5 buttons to show the different books of five countries respectively, drawing the curve lines to connect the book vertices which the titles equal the country name, and adding some interactions with keyboard (pressing the first character of every country to show the books information related to this country, and then pressing 1 to show the curve lines).

All work is developed within Processing
Lijuan_Project2_finalVersion