2D Spatial Map
MAT 259, 2012
RJ Duran
Introduction
What are the top 10 checked out DVD's of 2011?
The goal of this project is to utilize a bi-variate spatial map to visualize data answering the question: How "Green" is Seattle?
Through the library data I tried to illustrate the reading habits of people based on the number of checkouts per hour per day within 7 categories: Conservation, Economic, Political, Social, Sustainability, Science, and Technology. By grouping titles into these categories I was able to make sense of the large amount of information.
Technically the project involved one query to search for titles within a set list of keywords and filter out the desirable titles from the undesirable. I then looked through each month displayed and counted the total books marked with a specific category for each hour of each day. While this approach produces some interesting results, there is still some error in the counting and data requested itself. Mainly, duplicate entries and marking items for more than one category.
The goal of this project is to utilize a bi-variate spatial map to visualize data answering the question: How "Green" is Seattle?
Through the library data I tried to illustrate the reading habits of people based on the number of checkouts per hour per day within 7 categories: Conservation, Economic, Political, Social, Sustainability, Science, and Technology. By grouping titles into these categories I was able to make sense of the large amount of information.
Technically the project involved one query to search for titles within a set list of keywords and filter out the desirable titles from the undesirable. I then looked through each month displayed and counted the total books marked with a specific category for each hour of each day. While this approach produces some interesting results, there is still some error in the counting and data requested itself. Mainly, duplicate entries and marking items for more than one category.
Background
and Sketches
and Sketches
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.
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.
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.
Query
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;
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;
Explanation
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
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
Result and
Analysis
Analysis
Code
Control
There is a simple amount of interaction to allow the user to navigate through the data for each month within 2011 shown in the bottom left. The mouse was used to provide basic hover functionality showing the total checkouts per day for a given hour. A secondary color palette was also implemented to show dark and light palettes. I used a simple grid to do the layout of elements. The code itself is a little crude since we had somewhat of a short time frame to implement a design but I tried to keep things organized.