## 2.14 Proj 2: 2D Bivariate Spatial Map Concept & Sketch

glegrady
Posts: 160
Joined: Wed Sep 22, 2010 12:26 pm

### 2.14 Proj 2: 2D Bivariate Spatial Map Concept & Sketch

Post here your 2D bivariate spatial visualization concept and sketch. The project should map 2 or more SQL fields (such as itemtype, date, collcode, etc.) data into a 2D space map. The project should have some basic interactivity such as controlling when labels or other information can be visible or not.
George Legrady
legrady@mat.ucsb.edu

ansh2201
Posts: 7
Joined: Tue Jan 17, 2012 4:08 pm

### Re: 2.16 Proj 2: 2D Bivariate Spatial Map

The idea behind this visualization is to find out patterns in check-out and check-in for items belonging to various dewey categories. Each dewey category has a color coding to it. Each point on the bivariate graph corresponds to time of the day and day of the year.

For each library item, a line is drawn from (x1,y1) to (x2,y2) such that (x1,y1) is the datetime the item was checked out and (x2,y2) is the datetime the item was check back in.

This kind of visualization can be used to figure out various kinds of information like
1. Which dewey category has a lot of short term activity (checkout duration or the line on the graph is short)?
2. The check in behavior corresponding to various dewey categories.
etc.

To make it look less cluttered (all transactions in a year could lead to a very cluttered graph), I plan to filter out specific titles such as the keyword used is neutral to all dewey categories. I will start this visualization with 2 year data (2010,2011) and then try to extend it to the whole dataset.

Basic interactivity that could be added to this graph could be -
1. Mouse pointer can determine the datetime at that point
2. Keys - 1,2,3..0 can be used to control each dewey category (to hide and display data)
Attachments
Project 1 Doodle - Ankit

anisbharon
Posts: 10
Joined: Tue Jan 17, 2012 4:41 pm

### Re: 2.16 Proj 2: 2D Bivariate Spatial Map

Quantity (sum since 2005 to 2011) over time (month) visualization of 3 queries.
Rollover to show monthly transactions, keyboard input to switch between queries.

Q1: The Catcher in The Rye, Holden Coulfield

Q2: Isaac Asimov, Aum Shinrikyo

Q3: The Secret Agent, Joseph Conrad, Unabomber

Sequel Pro Query : select month(cout) as month, SUM(CASE WHEN ((title like "%The Secret Agent%" AND title like "%Joseph Conrad%") OR title like "%Unabomber%") THEN 1 ELSE 0 END) as Conrad, SUM(CASE WHEN (title like "%the catcher in the rye%" OR title like "%Holden Caulfield%") THEN 1 ELSE 0 END) as Catcher, SUM(CASE WHEN (title like "%Isaac Asimov%" OR title like "%Aum Shinrikyo%") THEN 1 ELSE 0 END) as Asimov, count(*) from inraw where year(cout)>2004 group by month order by month;
Last edited by anisbharon on Tue Feb 14, 2012 9:52 am, edited 1 time in total.

rjduranjr
Posts: 14
Joined: Tue Jan 17, 2012 4:40 pm

### Re: 2.16 Proj 2: 2D Bivariate Spatial Map

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
Last edited by rjduranjr on Tue Feb 14, 2012 10:36 am, edited 4 times in total.

hanyoonjung
Posts: 9
Joined: Tue Jan 17, 2012 4:40 pm

### Re: 2.16 Proj 2: 2D Bivariate Spatial Map

Project 2: 2D Bivariate Spatial Visualization
By Yoon Chung Han

Seattle is a city rich in a music history supporting a thriving industry that is renowned nationally and globally. In this project, I will explore how much Seattle people love a classical music and how many items related to the best 13 classical musicians they checked out from 2005 to 2010 at SPL.
Here's a list of the best 13 Classical musician

- Handel
- Beethoven
- Tchaikovsky
- Chopin
- Mozart
- Schoenberg
- Haydn
- Stravinsky
- Schumann
- Brahms
- Schubert
- Bach
- Debussy

In a visualization graph, left side will show the visualization of data of checked out items each year and months. On the right side, there will be a list of 13 musicians vertically, and each musician's data can be explored by clicking a small checkbox. Each musician will be determined by different colors as a line, and users can select which musician they want to see or compare with other musicians. X axis in the graph will represent time(months and years), and Y axis will represent the number of checked-out items. In the X, Y cross position, there will be a small square with a given color based on musician’s name, and the square will be connected together with a light line. There will be a smooth animation that delivers a feeling of music equalizer.

Text query(years, beethoven):
select count(*), year(ckinDateTime),
SUM(CASE WHEN year(ckinDateTime) = '2005' THEN 1 ELSE 0 END) as yr2005,
SUM(CASE WHEN year(ckinDateTime) = '2006' THEN 1 ELSE 0 END) as yr2006,
SUM(CASE WHEN year(ckinDateTime) = '2007' THEN 1 ELSE 0 END) as yr2007,
SUM(CASE WHEN year(ckinDateTime) = '2008' THEN 1 ELSE 0 END) as yr2008,
SUM(CASE WHEN year(ckinDateTime) = '2009' THEN 1 ELSE 0 END) as yr2009,
SUM(CASE WHEN year(ckinDateTime) = '2010' THEN 1 ELSE 0 END) as yr2010
from transactionsallID where deweyClass is not null and title like "%beethoven%" group by year(ckinDateTime) order by year(ckinDateTime) DESC;

Result:
Count year
1245 2010 0 0 0 0 0 1245
1573 2009 0 0 0 0 1573 0
1149 2008 0 0 0 1149 0 0
929 2007 0 0 929 0 0 0
1039 2006 0 1039 0 0 0 0
658 2005 658 0 0 0 0 0
Attachments

muhammadhafiz
Posts: 4
Joined: Tue Jan 17, 2012 4:39 pm

### Re: 2.16 Proj 2: 2D Bivariate Spatial Map

Name: Muhammad Hafiz Wan Rosli
Course: MAT 259: Visualizing information - Project 2
Instructor: George Legrady
TA: Joshua Dickinson
Attachments
dataVis120214.pdf
presentation 120214
(4.13 MiB) Downloaded 300 times

davidgordonartist
Posts: 7
Joined: Tue Jan 17, 2012 4:42 pm

### Re: 2.16 Proj 2: 2D Bivariate Spatial Map

The idea of my project is to continue exploring the financial crisis of 2008 by comparing the transactions with titles involving financial topics with the price of the S&P index and US unemployment rate.

Search Terms:
Money
Real Estate
Invest(ing)
Debt
Stock(s)

Sketch:
Query:
select week(cout) as week,count(*) from inraw where title like '%invest%' and deweyClass != 'null' and year(cout) >= '2006' group by week order by week;

dallasmercer
Posts: 7
Joined: Tue Jan 17, 2012 4:43 pm

### Re: 2.14 Proj 2: 2D Bivariate Spatial Map Concept & Sketch

The concept of my 2nd Processing assignment is to find correlations and data facts of real-world events (of any kind) and see if the SPL data can visually show us these correlations (if any) by relating the subject matter to the titles of items checked out by people there.
I chose the popular & real-world event of pregnancy. I was curious if there was a certain time of the year where people seemed to have more pregnancies than any other months. I was also curious if the data from the SPL would be in any way related to this question and if it might help me find some kind of visual aid/answer to this question.
I want to search from the years 2005-2011 and do a count search for any item that has "pregnancy, pregnant, etc" in the title. I would then like this data count to be ordered by month and then year, then grouped by the year and then the month.
The visualization I would like to create along with this data will be graph/color-intensity like and will have 7 columns (years 2005-2011) and 12 rows (months of the year). I would like the "cells" with the most data counts to be darker than the others so we can easily see a difference between the neighboring cells and know that more "pregnancy items" were checked out then. I'm still not sure what colors I'd like to use in my Processing visual but I'll work on that soon. My sketch attached to this document is just done with a pencil so you can at least get the general idea.
The attachment Asst.3_sketch.jpg is no longer available
Attachments