Project 2
The goal of this project is to utilize bivariate spatial maps to visualize data answering the question: How “Green” is Seattle?
The inspiration for this question came from a genuine interest to understand how knowledgeable the general library going population of Seattle is when it comes to topics of sustainability, pollution, health and wellness, conservation, social issues, economic issues, and politics in relation to eachother. I feel that this is an important question to address because the general public needs to have an understanding of what’s happening locally and globally today in all these areas.
Through the library data I expect to uncover trends showing how much people are reading about green solutions, conservation, science & technology, politics, social, economic, and ecological topics. Initially I expect to analyze broad categories of books and plot the information against time, which will be broken down into years, months, and days. Further analyzation will allow me to uncover trends such as types of books read within specific categories and when the most checkouts occur during a given day, month and year.
Technically the project will involve several bivariate maps and will be grouped to illustrate a clear representation of the data and what it means. Each bivariate map will in itself be interactive and connect to the overall navigation of the visualization.
In the sketch you can see that different levels of shading or color will be used to designate categories of books and most read items per month. The other plots will show checkouts per day vs days of the week, total checkouts and most checkouts vs month and year, and total checkouts per day vs month of the year over an entire year. There will be simple navigation for switching between years and months from 2006 to 2011.
Sketch
Queries
These are the queries used for collecting information. See additional txt files below for lists of keywords.
keyword = keywords used to search for in titles and subjects
category-keyword = keywords used to classify items into categories based on title and subject
ignore-keywords = keywords to ignore in titles and subjects
QUERY1
SELECT title, subj, count(*), count(distinct barcode) as distinctItems, AVG(TIMESTAMPDIFF(DAY,cout,cin)) as avgDaysOut, year(cout) as year, month(cout) as month, floor(deweyClass/10)*10 as dewey, itemtype,
IF(title like '%category-keyword%' or..., '1', '0') as cat1,
IF(title like '%category-keyword%' or…, '1', '0') as cat2,
IF(title like '%category-keyword%' or…, '1', '0') as cat3,
IF(title like '%category-keyword%' or…, '1', '0') as cat4,
IF(title like '%category-keyword%' or…, '1', '0') as cat5,
IF(title like '%category-keyword%' or…, '1', '0') as cat6,
IF(title like '%category-keyword%' or…, '1', '0') as cat7
FROM inraw
WHERE (year(cout) = '2011' AND month(cout) > 0 AND month(cout) < 13)
AND ((title like '%keyword%' or...) AND NOT (title like '%ignore-keyword%' or...)
AND (subj like '%keyword%' or...) AND NOT (subj like '%ignore-keyword%' or...))
AND (itemtype = 'acbk' or itemtype = 'arbk' or itemtype = 'acper' or itemtype = 'arper' or itemtype = 'arnp')
GROUP BY month, title
ORDER BY year DESC, month ASC, count(*) DESC;
QUERY2
SELECT year(cout), month(cout), day(cout), count(*)
FROM inraw
WHERE year(cout) = '2011' AND month(cout) >= '01' AND month(cout) <=12
AND ((title like '%keyword%' or...) AND NOT (title like '%ignore-keyword%' or...)
AND (subj like '%keyword%' or...) AND NOT (subj like '%ignore-keyword%' or...))
AND (itemtype = 'acbk' or itemtype = 'arbk' or itemtype = 'acper' or itemtype = 'arper' or itemtype = 'arnp')
GROUP BY date(cout)
ORDER BY date(cout) ASC;
Reference
http://rjduran.net/MAT/259/project2/p2_queries.txt
http://rjduran.net/MAT/259/project2/p2_keywords.txt
http://rjduran.net/MAT/259/project2/p2_ ... ywords.txt
http://rjduran.net/MAT/259/project2/p2_ ... ywords.txt
Full Queries & Resulting Data
Query 1:
http://rjduran.net/MAT/259/project2/2011data.txt
Query 2:
http://rjduran.net/MAT/259/project2/che ... 11data.txt
Result (In Progress)
http://rjduran.net/MAT/259/project2/gre ... 1-0210.png
RJ