Collection Relevance to Technology
MAT 259, 2016
David Aleman
Concept
For this project I continued to look into technology. Instead of looking at the total checkout rates per year on books I decided to look at the at the books The Seattle Public Library acquires per year. I wanted to see if the the library kept their collection relevant with books about new technology and media. Keywords like technology, programming, computer, engineering, coding, and computer were used to filter the data.
Query
SELECT DISTINCT
title.title,
(CASE
WHEN SUBSTRING(itemType.itemType, 3, 3) = 'bk' THEN 'book'
WHEN SUBSTRING(itemType.itemType, 3, 3) = 'cd' THEN 'audioBook'
WHEN SUBSTRING(itemType.itemType, 3, 3) = 'cdr' THEN 'CDMedia'
WHEN SUBSTRING(itemType.itemType, 3, 3) = 'vhs' THEN 'video'
WHEN SUBSTRING(itemType.itemType, 3, 3) = 'dvd' THEN 'video'
ELSE 'OTHERS'
END) AS type,
(CASE
WHEN spl.keyword.keyword = 'Technology' THEN 'Tech'
WHEN spl.keyword.keyword = 'Programming' THEN 'Programming'
WHEN spl.keyword.keyword = 'Engineering' THEN 'Engineering'
WHEN spl.keyword.keyword = 'Code' THEN 'Coding'
WHEN spl.keyword.keyword = 'Computer' THEN 'Computing'
ELSE 'OTHERS'
END) AS keyWord,
DATE(checkOut) AS Dates,
SUBSTRING(callNumber, - 4) AS PubYear,
COUNT(spl.transactions.bibNumber) AS Counts
FROM
spl.itemToBib, spl.title, spl.itemType, spl.keyword, spl.callNumber, spl.transactions
WHERE
spl.itemToBib.itemNumber = spl.itemType.itemNumber
AND spl.itemToBib.itemNumber = spl.transactions.itemNumber
AND spl.itemToBib.itemNumber = spl.callNumber.itemNumber
AND spl.itemToBib.bibNumber = spl.keyword.bibNumber
AND spl.itemToBib.bibNumber = spl.title.bibNumber
AND spl.itemToBib.bibNumber = spl.transactions.bibNumber
AND spl.itemType.itemNumber = spl.itemToBib.itemNumber
AND (spl.keyword.keyword = 'Technology'
OR spl.keyword.keyword = 'Programming'
OR spl.keyword.keyword = 'Engineering'
or spl.keyword.keyword = 'Code'
or spl.keyword.keyword = 'Computer')
and date(checkOut) >= '20060101'
and date(checkOut) <= '20151231'
AND CAST(SUBSTRING(callNumber, - 4) AS UNSIGNED) > 2005
AND CAST(SUBSTRING(callNumber, - 4) AS UNSIGNED) < 2016
GROUP BY spl.itemToBib.bibNumber
title.title,
(CASE
WHEN SUBSTRING(itemType.itemType, 3, 3) = 'bk' THEN 'book'
WHEN SUBSTRING(itemType.itemType, 3, 3) = 'cd' THEN 'audioBook'
WHEN SUBSTRING(itemType.itemType, 3, 3) = 'cdr' THEN 'CDMedia'
WHEN SUBSTRING(itemType.itemType, 3, 3) = 'vhs' THEN 'video'
WHEN SUBSTRING(itemType.itemType, 3, 3) = 'dvd' THEN 'video'
ELSE 'OTHERS'
END) AS type,
(CASE
WHEN spl.keyword.keyword = 'Technology' THEN 'Tech'
WHEN spl.keyword.keyword = 'Programming' THEN 'Programming'
WHEN spl.keyword.keyword = 'Engineering' THEN 'Engineering'
WHEN spl.keyword.keyword = 'Code' THEN 'Coding'
WHEN spl.keyword.keyword = 'Computer' THEN 'Computing'
ELSE 'OTHERS'
END) AS keyWord,
DATE(checkOut) AS Dates,
SUBSTRING(callNumber, - 4) AS PubYear,
COUNT(spl.transactions.bibNumber) AS Counts
FROM
spl.itemToBib, spl.title, spl.itemType, spl.keyword, spl.callNumber, spl.transactions
WHERE
spl.itemToBib.itemNumber = spl.itemType.itemNumber
AND spl.itemToBib.itemNumber = spl.transactions.itemNumber
AND spl.itemToBib.itemNumber = spl.callNumber.itemNumber
AND spl.itemToBib.bibNumber = spl.keyword.bibNumber
AND spl.itemToBib.bibNumber = spl.title.bibNumber
AND spl.itemToBib.bibNumber = spl.transactions.bibNumber
AND spl.itemType.itemNumber = spl.itemToBib.itemNumber
AND (spl.keyword.keyword = 'Technology'
OR spl.keyword.keyword = 'Programming'
OR spl.keyword.keyword = 'Engineering'
or spl.keyword.keyword = 'Code'
or spl.keyword.keyword = 'Computer')
and date(checkOut) >= '20060101'
and date(checkOut) <= '20151231'
AND CAST(SUBSTRING(callNumber, - 4) AS UNSIGNED) > 2005
AND CAST(SUBSTRING(callNumber, - 4) AS UNSIGNED) < 2016
GROUP BY spl.itemToBib.bibNumber
Preliminary sketches
When creating the early sketches I knew from the beginning that I wanted to stray away from the general boxed region. I decided to look into other shapes that I can use to display my data around.
Here is a future sketch I had planned to have bars or rectangles stand on the circle. I thought about filling the inside of the circle but that idea was later abandoned.
Here is the final sketch that made it to the end visual. However the Average per day parameter was not used after all.
Here is a future sketch I had planned to have bars or rectangles stand on the circle. I thought about filling the inside of the circle but that idea was later abandoned.
Here is the final sketch that made it to the end visual. However the Average per day parameter was not used after all.
Process
Here are early sketches of the visualization. The colors represent different item types (books, video, eBooks, and CD) and its size is the amount acquired for that book. The diameter of the circle represents the day of the month. The space between the circle represents the month and the circle itself is a year from 2006 to 2015.
Final result
Here is the final result for this project. The data is the same but the title is now fixed in space even if one rotates the camera. The keywords used are displayed also a key to help the viewer understand the data.
Code