Re: PROJ 2: 3D INTERACTION & CHANGE OVER TIME
Posted: Tue Feb 16, 2016 8:15 am
My project was an extension of my 2D project regarding frequently challenged titles. I used the same set of titles for this 3D visualization, however this time I wanted to explore things from a slightly different angle. I wanted to include some of the reasons behind the books being challenged, and I also wanted to include checkout times, dates, and durations for the titles.
I essentially used two queries for this. The first query was a modification of my original query in the first assignment that allowed me to gather unique bib numbers for each of the titles that I had in my list. The second query used the raw transactions table joined with the title table to pull bib numbers, titles, checkout date and time, checkin date and time, and a timestamp difference in hours between the two dates. I used the time stamp difference directly to compute the scale of a particle.
The processing code is attached, but the idea with the visualization was to create a particle for every checkout per title on the x axis, time on the y axis, and date on the z-axis. Initially, I had planned to have all books showing each of their particles, but the amount of particles was around 100k and was a little much for my system when I added the particle motion on top of it. So I restricted the x-axis to a single book which is filterable by a drop down menu control in the sketch. When the 'p' keyboard button is pressed, the particles move from their checkout date and time on the left to their check in date and time on the left. The particles themselves are rotating circles of lines, so when they move it generates a helix looking formation. The radius of the helix is directly proportional to the checkout time, so longer checkouts have larger circles.
Additionally, the colors of the particles themselves are randomly generated from the set of possible "challenged" categories that the book falls into. This is to give the user a sense of the reasons that particular book was placed on the list.
I essentially used two queries for this. The first query was a modification of my original query in the first assignment that allowed me to gather unique bib numbers for each of the titles that I had in my list. The second query used the raw transactions table joined with the title table to pull bib numbers, titles, checkout date and time, checkin date and time, and a timestamp difference in hours between the two dates. I used the time stamp difference directly to compute the scale of a particle.
Code: Select all
//Query 1
SELECT DISTINCT checkouts.bibNumber, title FROM spl3.`_rawXmlDataCheckOuts` AS checkouts
WHERE ((checkouts.title LIKE "%bad boy can be good%")
OR (checkouts.title LIKE "stolen life a memoir%")
OR (checkouts.title LIKE "starting with alice")
OR (checkouts.title LIKE "%tango makes three%")
OR (checkouts.title LIKE "%athletic shorts%")
OR (checkouts.title LIKE "bless%ultima%")
OR (checkouts.title LIKE "bone vol 1%")
OR (checkouts.title LIKE "brave new world")
OR (checkouts.title LIKE "adventures of captain underpants%")
OR (checkouts.title LIKE "crank")
OR (checkouts.title LIKE "drama")
OR (checkouts.title LIKE "fifty shades of grey")
OR (checkouts.title LIKE "flashcards%life%")
OR (checkouts.title LIKE "gossip girl")
OR (checkouts.title LIKE "golden compass")
OR (checkouts.title LIKE "i know why the caged bird%")
OR (checkouts.title LIKE "its perfectly normal%")
OR (checkouts.title LIKE "looking for alaska")
OR (checkouts.title LIKE "lush")
OR (checkouts.title LIKE "my moms having a baby%")
OR ( checkouts.title LIKE "my sisters keeper a novel")
OR ( checkouts.title LIKE "nickel and dimed on%")
OR ( checkouts.title LIKE "olives ocean")
OR ( checkouts.title LIKE "persepolis")
OR ( checkouts.title LIKE "revolutionary voices%")
OR ( checkouts.title LIKE "saga volume one")
OR ( checkouts.title LIKE "scary stories to tell%")
OR ( checkouts.title LIKE "absolutely true diary%")
OR (checkouts.title LIKE "adventures of huckleberry finn")
OR (checkouts.title LIKE "bluest eye")
OR (checkouts.title LIKE "catcher in the rye")
OR (checkouts.title LIKE "chocolate war")
OR (checkouts.title LIKE "color of earth")
OR (checkouts.title LIKE "color purple")
OR (checkouts.title LIKE "earth my butt%")
OR (checkouts.title LIKE "glass castle")
OR (checkouts.title LIKE "hunger games")
OR (checkouts.title LIKE "kite runner")
OR (checkouts.title LIKE "perks of being%")
OR (checkouts.title LIKE "thirteen reasons why%")
OR (checkouts.title LIKE "to kill a mockingbird")
OR (checkouts.title LIKE "ttyl%" AND callNumber LIKE "%myracle%")
OR (checkouts.title LIKE "twilight")
OR (checkouts.title LIKE "uncle bobby%")
OR (checkouts.title LIKE "what my mother does%"))
AND ((checkouts.checkout BETWEEN "2005-01-01" AND "2014-12-31")
AND (checkouts.`itemType` LIKE "%bk%"))
ORDER BY title, checkout
//Query 2
SELECT title.title, txs.checkOut, txs.checkIn, txs.bibNumber, TIMESTAMPDIFF(HOUR, checkOut, checkIn) FROM spl3.`x_rawTransactions` as txs INNER JOIN spl3.`title` as title ON txs.`bibNumber` = title.`bibNumber`
WHERE ((DATE(txs.`checkOut`) >= "2006-01-01")) AND (checkIn IS NOT NULL) AND (txs.checkIn <= "2014-12-31")
AND (txs.bibNumber IN ('2453513', '2481055', '2746344', '2946060', '1757322','1632693', '1285221', '789957', '2166836', '547742', '1585918', '1644533', '1710746', '1370906', '1669075', '2414922', '2622715', '2716010', '2298706', '636150', '1634827', '2335915', '2452772', '3036722', '2227295', '1352046', '2525744', '124499', '1364051', '1923085', '2356219', '1987343', '1632629', '1285181', '2432782', '1285187', '66182', '1285189', '2573890', '1275101', '1632697', '1896500', '2372793', '2266835', '2689055', '3003535', '2812375', '2806542', '2196807', '2794842', '2862544', '2338323', '2612334', '2176385', '1659144', '2367748', '2306851', '2108336', '2507346', '2611504', '2665213', '1680627', '51344', '1833250', '491437', '2713673', '3010908', '1400520', '2629285', '3043012', '2170183','2197365', '2078798', '2447827', '2286653', '2390676', '2417282', '2296278', '2229627', '2244560', '2041508', '2162719', '2811214', '2208430', '1898787', '2183402', '2001667', '2841844', '464739', '203806', '2689855', '2142232', '2724328', '2674415', '1287304', '1975241', '1612258', '2391663', '2658714', '2238563', '2294743', '2831674', '2307613', '2280207', '2024643', '2123403', '2135876', '2335250', '2318828', '2146035', '2357004', '488263', '2458643', '2551743', '2689016', '2689233', '2967782', '2382573', '2479926', '2078709', '2176401'))
ORDER BY txs.checkOut
Additionally, the colors of the particles themselves are randomly generated from the set of possible "challenged" categories that the book falls into. This is to give the user a sense of the reasons that particular book was placed on the list.