This 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.

//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
                

My initial idea was to simply have some kind of particle representation. I spent time trying to figure out my axes and then ultimately had to scrap some of those ideas because my computer couldn't handle all the particle calculations.

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.



The picture above shows the base particle system that I modified. The original author implemented this two dimensionally. I tweaked various parameters until I liked it and also converted the particles to systems of three dimensions. The code for the original particle system is located here. The picture below shows the controls which I used mostly to setup camera views and to verify data was correct, and also to give some level of interaction with the visualization.

A couple of examples of the final output. I added a presentation mode so the sketch randomly loops through the books and chooses a viewing angle for presentation.

All work is developed within Processing
Source Code + Data