This project is a culture analysis about the relationship between the supporters of Brack Obama and the readers of items concerning with him. It utilize the data from Seattle Pulic Libray (SPL) to visualize the loaning activity of Obama-related items, and compare it with Obama's Job approval trends.
The main form of the visualization is a 2d matrix. Each cell of it represents the checkout count of Obama-related item within one month by the cell's color, which varies from dark blue to light blue as checkout count increase.
The additonal part is a job approval curve during Obama's term. With horizonal time axis and vertical approval percentage axis, every time people point at a cell, the value of job approval in that cell's month will show up on the curve. That values are mapped to the same color range with the checkout counts, and these two colors are drawn together on left-top coner for comparision to see if people's interests in Obama go with their satisfaction with Obama's job.
Query 1 Checkout times for book item
SELECT DISTINCT
DATE_FORMAT(checkOut, '%Y-%m') AS checkOutMonth,
COUNT(checkOut) AS CheckoutCount,
SUBSTRING(itemType, 3, 4) AS type
FROM
subject , title , _rawXmlDataCheckIns
WHERE
subject.bibNumber = title.bibNumber
AND _rawXmlDataCheckIns.bibNumber = title.bibNumber
AND (subject.subject LIKE '%Obama%'
OR title.title LIKE '%Obama%')
AND SUBSTRING(itemType, 3, 4) = 'bk'
GROUP BY checkOutMonth
Query 2 Checkout times for DVD item
SELECT DISTINCT
DATE_FORMAT(checkOut, '%Y-%m') AS checkOutMonth,
COUNT(checkOut) AS CheckoutCount,
SUBSTRING(itemType, 3, 4) AS type
FROM
subject , title , _rawXmlDataCheckIns
WHERE
subject.bibNumber = title.bibNumber
AND _rawXmlDataCheckIns.bibNumber = title.bibNumber
AND (subject.subject LIKE '%Obama%'
OR title.title LIKE '%Obama%')
AND SUBSTRING(itemType, 3, 4) = 'dvd'
GROUP BY checkOutMonth
Query 3 Checkout times for CD item
SELECT DISTINCT
DATE_FORMAT(checkOut, '%Y-%m') AS checkOutMonth,
COUNT(checkOut) AS CheckoutCount,
SUBSTRING(itemType, 3, 4) AS type
FROM
subject , title , _rawXmlDataCheckIns
WHERE
subject.bibNumber = title.bibNumber
AND _rawXmlDataCheckIns.bibNumber = title.bibNumber
AND (subject.subject LIKE '%Obama%'
OR title.title LIKE '%Obama%')
AND SUBSTRING(itemType, 3, 4) = 'cd'
GROUP BY checkOutMonth
This sketch shows the different elements for constructing the final visualization.
This rough sketch shows how I arrange the 2d matrix and the job approval curve. It also indicates the use of grid system design, which turn out to be the final layout.
I used grid system to build my lay out.
Lower down the transparency of each cell. Only when it is pointed at, its transprancy will go up to make a indication of "chosen". The margin around the layout is added, too.
Without showing the curve: user can point at any cell in the 3*84 2d matrix to see detailed information on top-left corner of checkout count of specific type of items within specific month.
With the curve showing: user can point at any cell in the 3*84 2d matrix to see detailed information on top-left corner of checkout count of specific type of items within specific month, and also the job approval information for comparison. If the colors look similar overall, it means the trends of job approval approximately matches the trends of checkout times.