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