Much has been written about the increasing wealth gap in Seattle and the increasing number of homeless people in Seattle. Seattle is just one city of many in the US where the wealth gap is widening and the number of homeless people is increasing; this is a nation-wide trend, and Seattle is an interesting example where a relatively recently developed dominant industry could be the explanation for the wealth gap, thus perhaps putting the wealth gap and homelessness as correlated factors. Where this project comes in is the intersection of these economic trends along with the national defunding of libraries.I am extremely defensive of the library as a critical “third space,” neither the home nor the workplace, as third spaces increasingly are disappearing or becoming privately-funded ventures. This project, interestingly enough, began as an inquiry into whether a data type of ‘renewal’ could be reasonably extracted from the data in the SPL database. This turns out to be fairly true:

SELECT title, itemtype, bibNumber, COUNT(*) as renewals
FROM spl_2016.inraw
WHERE TIMESTAMPDIFF(MINUTE, cout, cin)<=1
GROUP BY title, itemtype, bibNumber
ORDER BY renewals desc
LIMIT 1000

The result of this query showed interesting results, however. The most “renewed” items, going off of my definition of the same item being checked in and out within a minute, were by far non-book items such as headphones and laptops. As such, what came to mind immediately was how this shows indeed the “third space” characteristic of the public library. It is a place that is neither home nor work. But it is also a place that might substitute for home or work. This is the abstracting work that data can sometimes do. It removes the space, or instrumentalizes information. Thus, I wanted to use data to re-inscribe the space. I am curious to see how these sorts of “non-book”, “non-CD,” etc. items have changed over time as the wealth gap and homelessness has increased in Seattle, perhaps indicating the increasing importance of the public library as a “third space” or otherwise public institution. Below is the query that I ultimately landed on:

SELECT
CASE WHEN itemtype IN ('alaptop' , 'areq', 'aceq') THEN 'Laptops, Headphones, or Hot Spots' WHEN itemtype LIKE 'a%' AND itemtype NOT IN ('alaptop' , 'areq', 'aceq') THEN 'Adult checkouts' WHEN itemtype LIKE 'j%' THEN 'Children checkouts' ELSE 'Other' END AS item_types,
CASE WHEN HOUR(cout) < 12 THEN 'Morning' WHEN HOUR(cout) >= 12 AND HOUR(cout) < 16 THEN 'Afternoon' WHEN HOUR(cout) >= 16 THEN 'Evening' END AS checkout_time,
YEAR(cout) AS year,
COUNT(*) AS number_of_checkouts,
ROUND(AVG(TIMESTAMPDIFF(HOUR, cout, cin)), 1) AS average_checkout_time_hours
FROM spl_2016.inraw
WHERE DAY(cout) = DAY(cin) AND TIMESTAMPDIFF(DAY, cout, cin) < 1
GROUP BY year , item_types , checkout_time
ORDER BY year ASC , CASE WHEN item_types = 'Laptops, Headphones, or Hot Spots' THEN 1 WHEN item_types = 'Adult Checkouts' THEN 2 WHEN item_types = 'Children Checkouts' THEN 3 WHEN item_types = 'Other' THEN 4 END ASC , CASE
WHEN checkout_time = 'Morning' THEN 1 WHEN checkout_time = 'Afternoon' THEN 2 WHEN checkout_time = 'Evening' THEN 3 END ASC

In the above query one can see that I have filtered the items I am looking at for those items that were checked out and checked back in on the same day. I then separated these items by check out time, either in the morning, the afternoon, or the evening. I then separated different types of items that were checked out into four different categories: Laptops, Headphones, or Hot Spots; Adult Checkouts; Children Checkouts; and Other. I also looked at how long these different kinds of items were checked out, on average.

The results of this query were interesting. I originally was only interested in the Laptops, Headphones, or Hot Spots item types, because they show a different kind of function of the library, but I decided to include other item types as well because I didn’t want to just limit the inquiry to this relatively narrow category. Generally speaking, however, the checkout times of these items were much longer than the items outside of this category (which included books, CDs, DVDs, and other items). I think this shows that it was well worth separating out these items from the others; the significant difference in checkout times shows exactly how these items are used differently than others. In any case, I think this query gives a different kind of picture of the library. Instead of looking at what was checked in and out of the library, kind of not considering the library as an actual space, this query gives a glimpse of how, and when, people are using the library in the space itself.

All work is developed within Processing
Source Code + Data