Proj 2 - 3D Visualization

Professor George Legrady
glegrady
Posts: 203
Joined: Wed Sep 22, 2010 12:26 pm

Proj 2 - 3D Visualization

Post by glegrady » Wed Jan 19, 2022 12:31 pm

Proj 2 - 3D Visualization

The 3D visualization project consists in visualizing downloaded MySQL multi-dimensional data within a 3D space using the Java-based Processing language: https://processing.org/
----------------------------------------------------
SCHEDULE
1.18 Visual Language Overview, Intro to Processing
1.20 PeasyCam | 3D Processing demo
1.25 3D labeling and log() for data
1.27 Introduce assignment, InfoGraph demo (to introduce camera perspective) and student Concept Development
2.01 In Class: Individual meetings to discuss concepts - Elings Hall, 2nf Flr, rm 2611
2.03 Concept presentations to group
2.07 Post work-in-progress concept description and code in student forum
2.08 2 short demos: multiple color in curveVertext | JSON - informal lab work - individual meetings
2.10 3D Project Completion Presentation
2.13 Latest Post of 3D Project for mid-term grade
-----------------------------------------------------
PROCESSING LIBRARIES
PeasyCam: is the Processing library that allows for the 3D spatialization and mouse interaction: http://mrfeinberg.com/peasycam/
Control P5: http://www.sojamo.de/libraries/controlP5/ to add buttons if needed
Color Sampler: http://tristen.ca/hcl-picker/#/hlc/6/1/A7E57C/2C4321

Some Processing functions for 3D:
P3D: https://processing.org/tutorials/p3d/
Translate, pushmatrix, popmatrix functions are introduced. Information about push, pop and translation can be found at: https://www.processing.org/tutorials/transform2d/
-----------------------------------------------------
LESSONS LEARNED
. How to import multi-column csv data into java-based Processing
. How to work with basics of visual language (form, space, color, lines, labeling, etc.)
. How to visualize data in 3D space
. Acquire skills in design
-----------------------------------------------------
The MySQL Query
. Ask an interesting question to query in the Seattle Library database
. Download as csv file a query that returns minimum 5 columns to have data for the following:
X - for the Horizontal location
Y - for the Vertical location
Z - for the Depth location
C - for the Color value
S - for any other value such as the Scale of the data
-----------------------------------------------------
Some previous 3D Projects to review https://vislab.mat.ucsb.edu/

Library geographic distribution. Data in 3D box with lines and curves
http://vislab.mat.ucsb.edu/2020/p2/Evge ... nyNoi.html, Evgeny Noi: "Slow Readers"
--
Irregular distribution of 641 Cookbooks Info Data
https://vislab.mat.ucsb.edu/2019/p2/Cha ... index.html
--
Prediction Analysis. Data that prioritizes curves
http://vislab.mat.ucsb.edu/2020/p2/Guan ... index.html, Guanyu Chen: "True vs Prediction"
--
Occult and Long Tube
http://vislab.mat.ucsb.edu/2020/p2/Erin_Woo/index.html, Erin Woo: "Trends in Parasychology & the Occult at SPL"
--
Correlating library data with external temperature
http://vislab.mat.ucsb.edu/2017/p2/MertToka/index.html, Mert Toka: "Word Temperatures"
--
Uses word2vec: https://www.tensorflow.org/tutorials/text/word2vec
http://vislab.mat.ucsb.edu/2017/p2/Hann ... index.html, Hannah Wolfe: "Lost & Forgotten Books"
----------------------------------------------------
EVALUATION
Your project will be reviewed according to these criteria. You can redo the project throughout the length of the course.

What are the conditions for a good grade?:
1) An interesting MySQL query
2) A working, interactive visualization in 3D in the java-based Processing environment
3) Data to determine the shape of the visual form: Let the metadata values determine where and how the data is to be organized within the 3D space, rather than to have a predetermined form
4) Be innovative - you can begin with an existing project but you must significantly change it so that it becomes something new
5) Visual Design: Consider space, the function of color, color relationships, clean fonts (Arial, Helvetica, Futura, etc.) Review examples at the course website: https://www.mat.ucsb.edu/~g.legrady/aca ... ences.html

CONTENT INNOVATION: your query question and outcomes. How original, engaging, unusual, your query, or your approach to the query may be, and how interesting the data may be. The data has to be multivariate, and granular (meaning a lot of data) so that we can see patterns forming in the data.

DESIGN: The design can build on our demos but hopefully go beyond. Areas of exploration are in how you use space, form, colors, data organization, timing, interaction, coherence, direction, etc.

COMPUTATION: The third evaluation is the computational component. First of all, the code needs to work. Special consideration will be for unusual, elegant expression, utilizing functions, algorithms, etc that you can introduce to the class.
------------------------------------------------------
Label your Documents
Please make sure to label your documents like csv files by the name of your project, or your name so we can identify where they come from

This is a lot to cover in the short time we have. Take one step at a time! Let us know if you have any questions.
George Legrady
legrady@mat.ucsb.edu

zilongliu
Posts: 3
Joined: Fri Jan 07, 2022 12:32 pm

Re: Proj 2 - 3D Visualization

Post by zilongliu » Tue Feb 08, 2022 2:43 pm

Concept: I was interested in how place names co-occurred in geography-related items in Seattle Public Library. So first of all, I retrieved all the items (along with their titles) and use spaCy (an open source Python package for natural language processing) to carry out toponym recognition on their subjects.

Query:

Code: Select all

SELECT 
    spl_2016.deweyClass.bibNumber,
    spl_2016.deweyClass.deweyClass,
    spl_2016.title.title,
    GROUP_CONCAT(spl_2016.subject.subject) AS 'subject',
    spl_2016.itemType.itemType
FROM
    spl_2016.deweyClass
        INNER JOIN
    spl_2016.title ON spl_2016.deweyClass.bibNumber = spl_2016.title.bibNumber
        INNER JOIN
    spl_2016.subject ON spl_2016.deweyClass.bibNumber = spl_2016.subject.bibNumber
        INNER JOIN
    spl_2016.itemToBib ON spl_2016.itemToBib.bibNumber = spl_2016.subject.bibNumber
        INNER JOIN
    spl_2016.itemType ON spl_2016.itemToBib.itemNumber = spl_2016.itemType.itemNumber
WHERE
    (spl_2016.deweyClass.deweyClass >= 900)
        && (spl_2016.deweyClass.deweyClass < 1000)
GROUP BY spl_2016.deweyClass.bibNumber , spl_2016.deweyClass.deweyClass , spl_2016.title.title , spl_2016.itemType.itemType
Toponym Recognition:

Code: Select all

import spacy
import pandas as pd
import numpy as np

## the function to extract toponyms
def extract_toponym(text):
    toponym_list = []
    nlp_result = nlp(str(text))
    for result in nlp_result.ents:
        if (result.label_ in [u"GPE", u"FACILITY", u"LOC", u"FAC", u"LOCATION"]):
            toponym_list.append(result.text)
    return '\t'.join(np.unique(np.array(toponym_list)))
            
## load the pre-trained English pipeline
nlp = spacy.load("en_core_web_lg")

## read query result
df_spl = pd.read_csv('3d_query_result_2016.csv')
## df_spl = df_spl.head(5000)

## extract toponyms from both subject
df_spl['toponyms_subject'] = df_spl['subject'].apply(lambda text:extract_toponym(text))

## output dataframe to csv
df_spl.to_csv('3d_query_result_2016_ner.csv')
Preliminary Results:
The points (i.e., toponyms) have different sizes, indicating different numbers of items associated with different toponyms. Hover over a toponym will show the name of it and associated items.
1.png
Attachments
placeGraphMain.zip
(8.73 MiB) Downloaded 59 times
Last edited by zilongliu on Sat Feb 12, 2022 11:17 pm, edited 2 times in total.

yifei_liu
Posts: 3
Joined: Fri Jan 07, 2022 12:31 pm

Re: Proj 2 - 3D Visualization

Post by yifei_liu » Tue Feb 08, 2022 2:53 pm

Concept
In my previous project, I obtained the results that the releases of HP films do not have much impact on the book alone but affecting the related book positively. In this project, my main goal is to visualize this result in 3D mode like a twist candy. Thus, the everyday checkout numbers of Harry Potter series (HP-1 to Hp-7) and five related books (Cchild, FBWTF, FBCG, Quid, and TBB) from 2006/01/02 to 2022/02/05 were collected by the query below.

Query

Code: Select all

SELECT 
    DATE(cout) AS date,
    #title,
    COUNT(bibNumber) AS Counts,
    SUM(CASE
        WHEN itemType = 'jcbk' THEN 1
        ELSE 0
    END) AS juvbook,
    SUM(CASE
        WHEN itemType = 'acbk' THEN 1
        ELSE 0
    END) AS adultbook,
    SUM(CASE
        WHEN
            itemType = 'jccas' OR itemType = 'jccd'
                OR itemType = 'jccdrom'
                OR itemType = 'jcvhs'
        THEN
            1
        ELSE 0
    END) AS juvmedia,
    SUM(CASE
        WHEN
            itemType = 'accd' OR itemType = 'acdvd'
                OR itemType = 'acvhs'
        THEN
            1
        ELSE 0
    END) AS adultmedia
FROM
    spl_2016.outraw
WHERE
    title LIKE '%Harry Potter%'
        AND title LIKE '%sorcerers%'
GROUP BY date #, title
ORDER BY date

Preliminary sketches
I was inspired by the space-time cube, in each layer of which the x axis and y axis are space and z axis is time. The points of checkout number of 12 books will distributed like a clock on each layer. Then I feel like rather than layer of time, a helix can connect the end of each year to the first of the following year as time is continuous. Thus, the points of check number of 12 books per day will show like a twist candy.
Sketch.jpg

Process
I set the helix as the baseline of date, and it seems to be confused to set 12 helix timelines, so 12 books start at the same starting point.
Preliminary result 1.PNG
Preliminary result 2.PNG
To show the size of checkout number, points were replaced by ellipses, while size and transparency represented the level of checkout number. Interactive controlP5 buttons were added to control the present of each book.
Preliminary result 3.PNG
Preliminary result 4.PNG
To accelerate the interaction, ellipses were replaced by line, while stroke weight represented the level of checkout number. Menu was narrowed and move to bottom left corner, and button background and front ground color were modified as well. Interactive rollover was added.
Preliminary result 5.png
Attachments
Project 2_Harry Potter_Yifei Liu V3.zip
(45.77 KiB) Downloaded 64 times
Project 2_Harry Potter_Yifei Liu V2.zip
(46.08 KiB) Downloaded 60 times
Last edited by yifei_liu on Tue Feb 15, 2022 7:59 pm, edited 5 times in total.

sdinulescu
Posts: 3
Joined: Fri Jan 07, 2022 12:30 pm

Re: Proj 2 - 3D Visualization

Post by sdinulescu » Tue Feb 08, 2022 4:24 pm

CONCEPT:

I want to visualize the check-in rate over time of each unique item title in the database that has a title which contains mention of either "embodied memory" or "generational memory". I see movement through time and space as a trail of personal history or memory of experience, and want this data visualization to parallel this theme. The popularity of books containing these terms, as indicated by their check-in frequency over time, is indicative of a memory trace through history of the desirability of these specific item entries in the database.

-------------------------------------------------------------------------------------------------------------

SQL CODE:

1st query code attempt -->

Code: Select all

SELECT title, itemtype, date(cin), count(*)  
from spl_2016.inraw  
where (title LIKE '%embody%' AND '%memory%')  
or (title LIKE '%generation%' AND '%memory%') 
group by itemtype, title, date(cin) order by count(*) desc LIMIT 100
Unfortunately, this returns 0 rows, so I need to do some more concept design on my SQL query. I have been having some issues with getting my queries to execute, due to my inexperience with nested SQL queries.

2nd query code attempt -->

Code: Select all

SELECT DISTINCT title, count(*) 
FROM spl_2016.inraw 
WHERE LOCATE('memory', title) > 0 
GROUP BY title;
This returns something! Not sure why my like keyword wasn't working above. Now, I will try compounding the keywords "embodied" OR "generational" AND "memory" ->

Code: Select all

SELECT DISTINCT title, count(*) 
FROM spl_2016.inraw 
WHERE LOCATE('memory' AND ('embodied' OR 'generational'), title) > 0
GROUP BY title;
This returns exactly what I need. Now, to organize it into a table where I can get check-out count per month over several years.

Code: Select all

SELECT title,
COUNT(IF(month(cin) = '01', 1, NULL)) '01',
COUNT(IF(month(cin) = '02', 1, NULL)) '02',
COUNT(IF(month(cin) = '03', 1, NULL)) '03',
COUNT(IF(month(cin) = '04', 1, NULL)) '04',
COUNT(IF(month(cin) = '05', 1, NULL)) '05',
COUNT(IF(month(cin) = '06', 1, NULL)) '06',
COUNT(IF(month(cin) = '07', 1, NULL)) '07',
COUNT(IF(month(cin) = '08', 1, NULL)) '08',
COUNT(IF(month(cin) = '09', 1, NULL)) '09',
COUNT(IF(month(cin) = '10', 1, NULL)) '10',
COUNT(IF(month(cin) = '11', 1, NULL)) '11',
COUNT(IF(month(cin) = '12', 1, NULL)) '12'
FROM spl_2016.inraw  
WHERE LOCATE('memory' AND ('embodied' OR 'generational'), title) > 0 AND year(cin) = '2010'
GROUP BY title;
This gives me all check-out counts by month in 2010. I will export csv files for each year, 2010 - 2021, and read them in to processing. But, there must be a better way.

After reaching out to Yixuan, she showed me a better way to structure the query that I wanted. This led me to write the following query:

Code: Select all

SELECT title, year(cin), month(cin), count(cin)
FROM spl_2016.inraw 
WHERE title like '%memory%' AND 
(title like '%embod%' OR title like '%generation%' or title like '%body%' or title like '%familial%')
GROUP BY title, year(cin), month(cin);
This query returned only 10 total entries over time, so I expanded my query to the following:

Code: Select all

SELECT title, year(cin), month(cin), count(cin)
FROM spl_2016.inraw 
WHERE (title like '%memory%' OR title like '%memories%') AND 
(title like '%embod%' OR title like '%generation%' or 
 title like '%body%' OR title like '%famil%' or 
 title like '%cognit%' OR title like '%autonom%')
GROUP BY title, year(cin), month(cin);
-------------------------------------------------------------------------------------------------------------

PROCESSING CODE:

Assignment_2: Currently, I have a processing program which animates check-in count through time using some randomly generated data in order to get something on screen for me to work with. Each entry at the moment is visualized as a sphere with random color, where its Y position is driven by randomly generated check-in count values (this will be replaced with mySQL query results). The points are interpolated through space as each day goes by (there is delay in the program to indicate the end of the day). The program will eventually have sliders that control the program's incrementation rate (i.e. how fast each day is "visualized") and amount of data shown.

Assignment2_V2: Color is mapped to x position, beginning to implement control p5 library and visualize history of the "agents". Now, I need to work on more cohesive visuals. Agent history needs to be able to be visually distinguishable from current agent position, while also being identifiable.

Assignment2_V3: Working on data distribution and visualization.

Assignment2_V4: Starting to bring in mySQL query data. TODO -- set positions based on new excel file format

Stejara_3D_V5: movement and position of agents on-screen directly driven by mySQL data query. TODO -- push the visualization further, integrate system controls.

Stejara_3D_V6: some color integrated, agent history is debugged. As program runs through the time periods, the layers begin to build a cohesive shape that resembles topography, indicative of the embodied memory trails of agent movement. I don't like how the color looks, as I am moving through the full hue spectrum based on agent index. I would like to find a better color palette to use that can be relational to the agent's count performance over time (maybe an average?).

Stejara_3D_V7: I didn't like the feel of the color, so I reverted back to a black/white color scheme. I will introduce color as a hover-over feature when mousing over the data points for more info. I also connected the agent "histories" (shaded spheres) with lines (where stroke weight = count # at that time period) in order to make the animation look like a loom that is weaving a pattern from data, which ties in to my theme of memory.

Stejara_3D_V8: I have improved the system's functionality, which includes sliders for
1. system rate (i.e. speed of spheres) and
2. the entry index (i.e. which agent) to highlight in red (text and position through time).
I have also displayed the entry titles on-screen, with axis labels for ease in reading.

Stejara_3D_V9: FINAL VERSION that integrates feedback from critique, in addition to improving upon run-time efficiency and visuals.
-> Spheres were changed to ellipses, which rotate towards the viewer in reference to the peasycam camera angle (so that I don’t completely lose the depth in certain views).
-> New font, saved and loaded from data folder
-> Adjusted coloration mappings, utilizing transparency and mapping based on average count of an entry through time.
-> Added some text under controlP5 sliders
Attachments
Screen Shot 2022-02-13 at 1.28.02 PM.png
Screen Shot 2022-02-13 at 1.29.27 PM 1.png
Screen Shot 2022-02-13 at 1.26.42 PM.png
Stejara_3D_V9.zip
(78.25 KiB) Downloaded 52 times
Stejara_3D_V8.zip
(14.91 MiB) Downloaded 53 times
Screen Shot 2022-02-09 at 11.11.41 PM.png
Stejara_3D_V7.zip
(10.74 KiB) Downloaded 61 times
Screen Shot 2022-02-09 at 3.44.46 PM.png
Screen Shot 2022-02-09 at 12.28.39 PM.png
Stejara_3D_V5.zip
(10.44 KiB) Downloaded 64 times
Screen Shot 2022-02-09 at 12.03.25 PM.png
Screen Shot 2022-02-09 at 11.49.00 AM.png
Assignment2_V4.zip
(5.07 KiB) Downloaded 54 times
Assignment2_V3.zip
(8.71 KiB) Downloaded 60 times
Assignment2_V2.zip
(2.52 KiB) Downloaded 60 times
Assignment_2.zip
(2.48 KiB) Downloaded 54 times
Last edited by sdinulescu on Thu Feb 17, 2022 5:27 pm, edited 20 times in total.

jiaxinwu
Posts: 3
Joined: Fri Jan 07, 2022 12:21 pm

Re: Proj 2 - 3D Visualization

Post by jiaxinwu » Tue Feb 08, 2022 8:41 pm

3D visualization of programming languages

Realized in Processing 4.0b4

Author: Jiaxin Wu

February 8, 2022

Class: M259, George Legrady

Idea:
I visualized the trend of different programming languages in Project 1. In this project, I picked the 5 most popular programming languages. I want to visualize the relationship between the checkouts and their popularity.

Query:

Code: Select all

SELECT 
    bibNumber,
    cout,
    cin,
    title,
    deweyClass,
    DATEDIFF(cin, cout) AS diff,
    ((MONTH(cout) - 1) * 30 + DAY(cout)) AS outDay,
    ((MONTH(cin) - 1) * 30 + DAY(cin)) AS inDay,
    YEAR(cout) AS outYear,
    YEAR(cin) AS inYear,
    (CASE
        WHEN (LOWER(title) LIKE '%python%') THEN 'Python'
        WHEN (LOWER(title) LIKE '%java %') THEN 'Java'
        WHEN (LOWER(title) LIKE '%javascript%') THEN 'JavaScript'
        WHEN (LOWER(title) LIKE '%sql%') THEN 'SQL'
        WHEN (LOWER(title) LIKE '% c %') THEN 'C'
    END) AS langType
FROM
    spl_2016.inraw
WHERE
    (LOWER(title) LIKE '%python%'
        OR LOWER(title) LIKE '%java%'
        OR LOWER(title) LIKE '%javascript%'
        OR LOWER(title) LIKE '% c %'
        OR LOWER(title) LIKE '%sql%')
        AND (deweyClass = ''
        OR (deweyClass > 0 AND deweyClass < 100))
        AND YEAR(cout) > 2000
        AND YEAR(cin) > 2000
In this query, I count how long each book was kept using the cin and cout parameters. I process the datetime to get the outDay, inDay, outYear, inYear parameters to help visualize. The programming language of each checkout was marked.

Details
I design the visualization as a column. Each layer of the column represents a year. The radius and the height of each layer are fixed parameters.
But the coordinate of each point is decided by the parameters outYear, outDay, inYear, and inDay. OutYear and inYear decide the z coordinate, which is related to the layer this point is on; OutDay and inDay can be used to calculate theta, which can further calculate the x, y coordinates on the circle.
We can get a line by linking the cout and cin point of each checkout. And the weight of each line is decided by how long the book was kept.
We also calculate the distance of the mouse and each line. If the distance is smaller than a certain threshold, the line will be colored red and the name of the book will be shown.
Since each checkout is related to a certain kind of programming language. We can add toggles to decide whether this kind of programming language is allowed to be shown. We add five toggles here. Toggles can help us visualize the difference in the popularity of each programming language.
We also add sliders to modify the fixed parameters H and R. It can help with the visualization.
As is shown in the visualization results, Python and C are far more popular, while Java and JavaScript are not that popular.

/* I slightly modified the font to make it more clear. */
Attachments
3D_visualization_programming_languages-Jiaxin.zip
(4.73 MiB) Downloaded 56 times
Last edited by jiaxinwu on Wed Feb 09, 2022 5:24 pm, edited 3 times in total.

zijianwan
Posts: 3
Joined: Fri Jan 07, 2022 12:32 pm

Re: Proj 2 - 3D Visualization

Post by zijianwan » Tue Feb 08, 2022 9:24 pm

Concept
I'm interested in how often books about learning magic are checked out and how long they have been kept (read). So, I ran the following query to obtain relevant book checkout records.
Query

Code: Select all

SELECT 
    *
FROM
    spl_2016.inraw
WHERE
    (title LIKE '%magic%'
        AND (title LIKE '%learn%'
        OR title LIKE '%beginners%'
        OR title LIKE '%tricks%'))
      AND YEAR(cout) BETWEEN 2006 AND 2021;
The records were obtained as a CSV file and preprocessed with Python (codes attached below, "dataPreprocessing.pdf").
Then, I visualized the records in Processing. Each checkout/checkin record is visualized as a trajectory from the date the book was checked out to the date it was checked in. The coordinates of the points that consist of a trajectory are computed as follows. The z coordinate represents the distance from the plane of that circle. Since the distance between two adjacent year circles is fixed (which corresponds to 365 days), the z coordinate can be calculated according to the days that the book has been kept by the reader. The x and y coordinates are computed based on a polar coordinate system as shown in the figure below. Note that both the angle phi and the length l are determined by the month and day. In this way, the visualization form (which is like flowers) is actually determined by the data instead of manually designed.
Polar_Coordinate_System.png
Preliminary results
Attached.
Wan_Proj2_PreliminaryResults1.png
Wan_Proj2_PreliminaryResults2.png
Final results
The radius of each circle is determined by the total number of checkouts of that year.
Wan_Proj2_Results3.png
Wan_Proj2_Results4.png
Findings
During the study period (2012-2021), 2013 and 2014 have the most annual checkouts of books about learning magic. Interesting, however, in those two years, those magic books have not been kept by readers for long (i.e., they were returned in a short time). This indicates that even though many people were interested in magic, they did not have enough patience or interest to learn magic. So, they got tired of the books and returned them after a short time. 2016 and 2017 are two years that have the most records with magic books that have been kept for a long time, even though those two years do not have the most checkouts. This represents those people who actually devote themselves to learning magic.
Attachments
Wan_3DVis_20220212.zip
(265.39 KiB) Downloaded 38 times
dataPreprocessing.pdf
(35.65 KiB) Downloaded 36 times
Last edited by zijianwan on Sat Feb 12, 2022 11:39 pm, edited 6 times in total.

ziyu_zhang309
Posts: 3
Joined: Fri Jan 07, 2022 12:34 pm

Re: Proj 2 - 3D Visualization

Post by ziyu_zhang309 » Tue Feb 08, 2022 11:34 pm

Concept
I have always been interested in Culture Exposure from various counties, especially my country, China. Therefore, through the analysis of the data in Seattle Public Library, I want to find out the Chinese cultural impact on the people in the United States from 2006 till today. With the continuous development of culture and technology in China, the main content and symbols of Chinese culture exposure have also kept pace with times and made corresponding changes. Setting books as the object of analysis, I first collected statistics on the types of books people borrowed the most. Taking 2006-2010\2011-2015\2016-2020 as the time boundary, I planned to analyze the trend of Chinese cultural exposure through changes in the number of times people borrowed related books in three time periods. The project 2 is the extension of my project 1, which uses "lantern" as the main visual symbol to create 3d visualization.

Query

Code: Select all

SELECT
● title, bibNumber, COUNT(bibNumber) AS counts, itemtype
● FROM
● spl_2016.outraw
● WHERE
● (itemtype like '%bk')
● AND (title LIKE '%Chinese%'
● OR title LIKE '%China%')
● AND(Year(cout) >= 2006 and Year(cout) <= 2010)
● GROUP BY bibNumber , itemtype , title
● ORDER BY counts DESC
●
● SELECT
● title,
● bibNumber,
● COUNT(bibNumber) AS counts,
● SUM(CASE
● WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 1) THEN 1
● ELSE 0
● END) AS '2006-1',
● SUM(CASE
● WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 2) THEN 1
● ELSE 0
● END) AS '2006-2',
● SUM(CASE
● WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 3) THEN 1
● ELSE 0
● END) AS '2006-3',
● SUM(CASE
● WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 4) THEN 1
● ELSE 0
● END) AS '2006-4',
● SUM(CASE
● WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 5) THEN 1
● ELSE 0
● END) AS '2006-5',
● SUM(CASE
● WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 6) THEN 1
● ELSE 0
● END) AS '2006-6',
● SUM(CASE
● WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 7) THEN 1
● ELSE 0
● END) AS '2006-7',
● SUM(CASE
● WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 8) THEN 1
● ELSE 0
● END) AS '2006-8',
● SUM(CASE
● WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 9) THEN 1
● ELSE 0
● END) AS '2006-9',
● SUM(CASE
● WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 10) THEN 1
● ELSE 0
● END) AS '2006-10',
● SUM(CASE
● WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 11) THEN 1
● ELSE 0
● END) AS '2006-11',
● SUM(CASE
● WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 12) THEN 1
● ELSE 0
● END) AS '2006-12',
● SUM(CASE
● WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 1) THEN 1
● ELSE 0
● END) AS '2007-1',
● SUM(CASE
● WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 2) THEN 1
● ELSE 0
● END) AS '2007-2',
● SUM(CASE
● WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 3) THEN 1
● ELSE 0
● END) AS '2007-3',
● SUM(CASE
● WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 4) THEN 1
● ELSE 0
● END) AS '2007-4',
● SUM(CASE
● WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 5) THEN 1
● ELSE 0
● END) AS '2007-5',
● SUM(CASE
● WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 6) THEN 1
● ELSE 0
● END) AS '2007-6',
● SUM(CASE
● WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 7) THEN 1
● ELSE 0
● END) AS '2007-7',
● SUM(CASE
● WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 8) THEN 1
● ELSE 0
● END) AS '2007-8',
● SUM(CASE
● WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 9) THEN 1
● ELSE 0
● END) AS '2007-9',
● SUM(CASE
● WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 10) THEN 1
● ELSE 0
● END) AS '2007-10',
● SUM(CASE
● WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 11) THEN 1
● ELSE 0
● END) AS '2007-11',
● SUM(CASE
● WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 12) THEN 1
● ELSE 0
● END) AS '2007-12',
● SUM(CASE
● WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 1) THEN 1
● ELSE 0
● END) AS '2008-1',
● SUM(CASE
● WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 2) THEN 1
● ELSE 0
● END) AS '2008-2',
● SUM(CASE
● WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 3) THEN 1
● ELSE 0
● END) AS '2008-3',
● SUM(CASE
● WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 4) THEN 1
● ELSE 0
● END) AS '2008-4',
● SUM(CASE
● WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 5) THEN 1
● ELSE 0
● END) AS '2008-5',
● SUM(CASE
● WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 6) THEN 1
● ELSE 0
● END) AS '2008-6',
● SUM(CASE
● WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 7) THEN 1
● ELSE 0
● END) AS '2008-7',
● SUM(CASE
● WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 8) THEN 1
● ELSE 0
● END) AS '2008-8',
● SUM(CASE
● WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 9) THEN 1
● ELSE 0
● END) AS '2008-9',
● SUM(CASE
● WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 10) THEN 1
● ELSE 0
● END) AS '2008-10',
● SUM(CASE
● WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 11) THEN 1
● ELSE 0
● END) AS '2008-11',
● SUM(CASE
● WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 12) THEN 1
● ELSE 0
● END) AS '2008-12',
Renewed results
1.1.PNG
1.2.PNG
1.3.PNG
Details
The wave lines in the visualization represent the trend of the check-out times of Chinese culture related books. Different combination of colors represent different time zones. And I renewed some controls and functions which can allow viewers to set different values range and position of the data.
Attachments
ZiyuZhang_Project2_1.zip
(9.91 KiB) Downloaded 35 times
Last edited by ziyu_zhang309 on Fri Feb 11, 2022 2:14 pm, edited 3 times in total.

senyuan
Posts: 3
Joined: Tue Jan 11, 2022 10:44 am

Re: Proj 2 - 3D Visualization

Post by senyuan » Wed Feb 09, 2022 4:56 pm

Russell Liu
MAT259
Project 2 Work-in-progress


Concept:
My project 2 is focused on visualizing the correlation between Tesla's Stock Price (from 2011 to 2021) and Checkouts from Seatle Public Library in 3D, realizing in Processing. By doing so, I would like to find out if people are borrowing more/less related books as Tesla's Stock Price increases/decreases in a time period. I only fetched the stock data started in 2011 because it wasn't a public company via IPO on NASDAQ until 2010 June, and thus fetching checkouts data starting in 2011.

Query 1:

Code: Select all

select *
from spl_2016.outraw
where (
LOWER(title) like '% Elon Musk %'
or LOWER(title) like '% Tesla %'
or LOWER(title) like '% Panasonic %'
or LOWER(title) like '% Lithium Iron Phosphate %'
or LOWER(title) like '% Battery %'
or LOWER(title) like '% stock %'
or LOWER(title) like '% stock market %'
or LOWER(title) like '% investment %'
)AND YEAR(cout) BETWEEN 2010 AND 2021
Query 2:

Code: Select all

SELECT YEAR
 ( cout ),
 title,
 COUNT( 1 )  'checkouts'
FROM
 spl_2016.outraw 
WHERE
 ( INSTR( title, 'stock market' )> 0 OR INSTR( title, 'Tesla' )> 0 OR INSTR( title, 'Elon Musk' )> 0 OR 
 INSTR( title, 'investment' )> 0 OR INSTR( title, 'Battery' )> 0 OR INSTR( title, 'stock' )> 0) 
 AND YEAR ( cout ) BETWEEN 2010 
 AND 2021 
GROUP BY
 YEAR ( cout ),
 title
 
Preliminary sketches
Screen Shot 2022-02-09 at 4.16.47 PM.png
Screen Shot 2022-02-09 at 4.16.52 PM.png
Preliminary process
Sketch.png

Process and Explanation
The X, Y, and Z-axis represent Year, Tesla Stock Price, and Checkouts respectively. I plotted each book, represented as a small sphere; and they are different in size and color. As the checkouts go up, the size of the small sphere will increase as well. The few they were borrowed, the smaller the sphere. Each Year in the X-axis represents a color and I have listed it on the left. I made a small box, which functions like a button that could rotate the box in a convenient way. The box has two motions, one is moving, and the other one is static. Once you click the button, the sphere will be moving or become static if do it again. Besides, you could search one specific book which includes some keywords related to Tesla and Elon Musk, and you will see all the related small spheres shown in the visualization, and thus you could move your mouse to the sphere to check the information such as the stock price on that day, number of checkouts, book title, and Year. If you would like to see the general situation in one certain year, you could just click the year on the X-axis and it will only show the small spheres in this year and blur the others.

Final Result
Screen Shot 2022-02-12 at 9.43.48 PM.png
Screen Shot 2022-02-12 at 9.43.53 PM.png
Screen Shot 2022-02-12 at 9.44.02 PM.png
Screen Shot 2022-02-12 at 9.44.07 PM.png
Screen Shot 2022-02-12 at 9.44.18 PM.png
Screen Shot 2022-02-12 at 9.44.26 PM.png
Screen Shot 2022-02-12 at 9.44.43 PM.png
Explanation/Analysis
Each sphere represents a book title. Through my 3D visualization, people could clearly see that people were borrowing what books and how many checkouts were at different stock prices. The time in the information part represents the books were checked out how many times during that month and it is based on the last stock price during that month. By rotating the box, the trends of the moving particles actually represent the Tesla stock price trends if paying attention. For the moving particles, it is moving with boundaries between the whole number because there are no decimal points for checkouts (Such as 2 times per month but it's impossible for a certain book to be checked out 2.3 times.) I optimized the data after what I fetched from MySQL. It is unavoidable that there is a data vacuum in some checkout range and I hope the moving particles may solve part of the visualization issue. All of the work is mainly developed by Processing, Anaconda, and MySQL. Thank you Professor George for inspiring me with the Tesla idea and all the help from TAs!
Attachments
sketch_220207i.zip
(1.85 MiB) Downloaded 35 times
Last edited by senyuan on Sat Feb 12, 2022 9:59 pm, edited 2 times in total.

siming
Posts: 3
Joined: Fri Jan 07, 2022 12:29 pm

Re: Proj 2 - 3D Visualization

Post by siming » Wed Feb 09, 2022 6:27 pm

Siming, Su
MAT259
Project 2

Title: Data River within 3D and 4D dimension
Concept
As a continuing exploration of my first project, I have found that the reason why people spend less time on art class is that the library puts all albums and music in Dewey class from 700 to 799. Thus, obviously listening to music takes less time. This project is to visualize the top 5 selling items in the art class, and how the check-in and check-out dates are structured in both 3d and 4d by making an amination of the movement from check-out date to check-in date. It looks like a river in a cube and tesseract, so I call it Data River.

I use two SQL queries to get the data
1st query to get the top-selling item's call number

Code: Select all

SELECT callNumber, count(*) FROM spl_2016.inraw
WHERE DeweyClass >= 700 AND DeweyClass <= 799 AND year(cout) >= 2015 AND year(cout) <= 2020
GROUP BY callNumber
ORDER BY count(*) DESC
LIMIT 10
2nd query to get the check-in check-out information

Code: Select all

SELECT 
year(cin) as cin_year, 
month(cin) as cin_month, 
day(cin) as cin_day, 
year(cout) as cout_year, 
month(cout) as cout_month,
day(cout) as cout_day,
callNumber
FROM spl_2016.inraw
WHERE (callNumber = "CD 782.42166 N86" 
OR callNumber = "CDJ 782.42083 K541"
OR callNumber = "791.45028 N661N 2016"
OR callNumber = "797.12309 B8126B 2013" 
OR callNumber = "CD 782.42166 Ad32T")
AND year(cout) <= 2020 
AND year(cout) >= 2015
The following pictures are some chosen results:
tess_vis_3.png
tess_vis_2.png
tess_vis_1.png
I put a lot of effort into the interactive part, so it is much more fun to just play the outcome in processing!
Attachments
two_cubes.zip
(75.26 KiB) Downloaded 36 times

lijuan
Posts: 3
Joined: Fri Jan 07, 2022 12:25 pm

Re: Proj 2 - 3D Visualization

Post by lijuan » Thu Feb 10, 2022 11:56 am

Title: 3D Visualization of the Trend of Reading Interests on Asian Countries
Lijuan Cheng
02.10.2022
Concept
In the last decade, the population diversity of the Seattle metropolitan area keeps growing. Especially more and more Asian people move to Seattle due to the increasing job opportunities in tech companies. Therefore, the project 2 is to investigate the trend of reading interests about different Asian countries, which can help us to understand the correlation between reading behavior and population composition.

Firstly, I chose China, India, Japan, Korea and Vietnam as five research countries. And then I designed two SQL queries to search the total check out counts of these five countries and the check out details (including related country, bibNumber, title, date and counts) from 2006 to 2021. After that, I made use of Processing to visualize the multi dimensional data in 3D format.

SQL Query 1 - Total Counts of Five Aisan Countries

Code: Select all

SELECT 
	Year(cout) AS Years,
    COUNT(IF(Lower(title) LIKE '%china%' or Lower(title) like '%chinese%' or callNumber like '%chinese%',
        1, NULL)) AS 'China',
    COUNT(IF(lower(title) LIKE '%india%' or lower(title) like '%indian%',
        1,
        NULL)) AS 'India',
	COUNT(IF(lower(title) LIKE '%japan%' or lower(title) like '%japanese%' or callNumber like '%japanese%',
        1,
        NULL)) AS 'Japan',
	COUNT(IF(lower(title) LIKE '%korea%' or lower(title) like '%korean%' or callNumber like '%korean%',
        1,
        NULL)) AS 'Korea',
	COUNT(IF(lower(title) LIKE '%vietnam%' or lower(title) like '%vietnamese%' or callNumber like '%vietnamese%',
        1,
        NULL)) AS 'Vietnam'
FROM
    spl_2016.outraw 
WHERE
	YEAR(cout) < 2022
GROUP BY Years
ORDER BY Years
SQL Query 2 - Check Out Details of Five Asian Countries

Code: Select all

SELECT 
    subject AS Countries,
    spl_2016.title.bibNumber AS BibNumbers,
    DATE(checkOut) AS Dates,
    title AS Titles,
    COUNT(title) AS Counts
FROM
    spl_2016.transactions,
    spl_2016.subject,
    spl_2016.title
WHERE
    spl_2016.transactions.bibNumber = spl_2016.subject.bibNumber
        AND spl_2016.transactions.bibNumber = spl_2016.title.bibNumber
        AND (subject = 'china' OR subject = 'india'
        OR subject = 'japan'
        OR subject = 'korea'
        OR subject = 'vietnam')
GROUP BY Countries, BibNumbers, Dates, Titles
ORDER BY Dates
3D Sketch - different years will be the Z axis

3D results 1
Firstly, I drew five circles to represent the five countries every year. Then I drew lots of points to represent every book related to these countries. And the results show that China and Japan ranked the first and second place, but the total counts are very close. Then India and Korea ranked at the third and the fourth place, and these two countries are very close to each other as well. The total counts of Vietnam is always the smallest.

3D results 2
As you can see, besides the five circles, the specific book points also construct five book circles bigger than the original five country circles. Because I calculate the coordinates of every book point based on the book country attribute and the radius of the corresponding country circle. These book circles also have the same features with the country circles.

To be continued
Next Step, I will continue to add more features in project 2, such as the interactive features and control buttons. Meanwhile, I will think about how to make the circles and points offer more useful information.

02.17.2022 Updated final version
In the final version, I modified some features, like making the book points show on the corresponding country circle, and drawing the line to show the count of one book and when the mouse move over the book point, the title, date and count of this book will show up. Meanwhile, I also added a few features, such as: adding the months information, designing five controlP5 buttons to show the different books of five countries respectively, drawing the curve lines to connect the book vertices which the titles equal the country name, and adding some interactions with keyboard (pressing the first character of every country to show the books information related to this country, and then pressing 1 to show the curve lines).
Attachments
Screen Shot 2022-02-17 at 9.54.17 AM.png
Screen Shot 2022-02-17 at 9.52.32 AM.png
Screen Shot 2022-02-17 at 9.50.05 AM.png
Screen Shot 2022-02-17 at 9.49.20 AM.png
Screen Shot 2022-02-17 at 9.48.57 AM.png
Lijuan_Project2_finalVersion.zip
(143.96 KiB) Downloaded 26 times
Lijuan_Project2_3D.zip
(139.6 KiB) Downloaded 42 times
Screen Shot 2022-02-10 at 10.03.10 AM.png
Screen Shot 2022-02-10 at 10.02.53 AM.png
Screen Shot 2022-02-10 at 11.45.42 AM.png
Last edited by lijuan on Thu Feb 17, 2022 11:00 am, edited 4 times in total.

Post Reply