My idea for this project is visualizing the popularity of the top cities in the world. I check online data to get the top 100 cities and query the check out quantities of them in SPL from 2005 to 2013, and use the past data to predict the data in 2014 using statistic model GM(1,1). In the visualization, I draw rectangles as layers to represent years and use different colors to represent different cities according to which continent they belong to. Blue represents Europe, red represents America and yellow represents Asia. For cities in the same continent, I distinguish them by the checked out quantity. The more books checked out, the darker the color is.

select year(cout) as 'Year', sum(case when (title like '%Paris%') then 1 else 0 end) as 'Paris', sum(case when (title like '%London%') then 1 else 0 end) as 'London', sum(case when (title like '%Bangkok%') then 1 else 0 end) as 'Bangkok', sum(case when (title like '%Singapore%') then 1 else 0 end) as 'Singapore', sum(case when (title like '%New York%') then 1 else 0 end) as 'New York', sum(case when (title like '%Kuala Lumpur%') then 1 else 0 end) as 'Kuala Lumpur', sum(case when (title like '%Hong Kong%') then 1 else 0 end) as 'Hong Kong', sum(case when (title like '%Dubai%') then 1 else 0 end) as 'Dubai', sum(case when (title like '%Istanbul%') then 1 else 0 end) as 'Istanbul', sum(case when (title like '%Rome%') then 1 else 0 end) as 'Rome', sum(case when (title like '%Shanghai%') then 1 else 0 end) as 'Shanghai', sum(case when (title like '%Los Angeles%') then 1 else 0 end) as 'Los Angeles', sum(case when (title like '%Las Vegas%') then 1 else 0 end) as 'Las Vegas', sum(case when (title like '%Miami%') then 1 else 0 end) as 'Miami', sum(case when (title like '%Toronto%') then 1 else 0 end) as 'Toronto', sum(case when (title like '%Barcelona%') then 1 else 0 end) as 'Barcelona', sum(case when (title like '%Dublin%') then 1 else 0 end) as 'Dublin', sum(case when (title like '%Amsterdam%') then 1 else 0 end) as 'Amsterdam', sum(case when (title like '%Moscow%') then 1 else 0 end) as 'Moscow', sum(case when (title like '%Cairo%') then 1 else 0 end) as 'Cairo', sum(case when (title like '%Prague%') then 1 else 0 end) as 'Prague', sum(case when (title like '%Vienna%') then 1 else 0 end) as 'Vienna', sum(case when (title like '%Madrid%') then 1 else 0 end) as 'Madrid', sum(case when (title like '%San Francisco%') then 1 else 0 end) as 'San Francisco', sum(case when (title like '%Vancouver%') then 1 else 0 end) as 'Vancouver', sum(case when (title like '%Budapest%') then 1 else 0 end) as 'Budapest', sum(case when (title like '%Rio de Janeiro%') then 1 else 0 end) as 'Rio de Janeiro', sum(case when (title like '%Berlin%') then 1 else 0 end) as 'Berlin', sum(case when (title like '%Tokyo%') then 1 else 0 end) as 'Tokyo', sum(case when (title like '%Mexico City%') then 1 else 0 end) as 'Mexico City', sum(case when (title like '%Buenos Aires%') then 1 else 0 end) as 'Buenos Aires', sum(case when (title like '%St. Petersburg%') then 1 else 0 end) as 'St. Petersburg', sum(case when (title like '%Seoul%') then 1 else 0 end) as 'Seoul', sum(case when (title like '%Athens%') then 1 else 0 end) as 'Athens', sum(case when (title like '%Jerusalem%') then 1 else 0 end) as 'Jerusalem', sum(case when (title like '%Seattle%') then 1 else 0 end) as 'Seattle', sum(case when (title like '%Delhi%') then 1 else 0 end) as 'Delhi', sum(case when (title like '%Sydney%') then 1 else 0 end) as 'Sydney', sum(case when (title like '%Mumbai%') then 1 else 0 end) as 'Mumbai', sum(case when (title like '%Munich%') then 1 else 0 end) as 'Munich', sum(case when (title like '%Venice%') then 1 else 0 end) as 'Venice', sum(case when (title like '%Florence%') then 1 else 0 end) as 'Florence', sum(case when (title like '%Beijing%') then 1 else 0 end) as 'Beijing', sum(case when (title like '%Cape Town%') then 1 else 0 end) as 'Cape Town', sum(case when (title like '%Washington D.C.%') then 1 else 0 end) as 'Washington D.C.', sum(case when (title like '%Montreal%') then 1 else 0 end) as 'Montreal', sum(case when (title like '%Atlanta%') then 1 else 0 end) as 'Atlanta', sum(case when (title like '%Boston%') then 1 else 0 end) as 'Boston', sum(case when (title like '%Philadelphia%') then 1 else 0 end) as 'Philadelphia', sum(case when (title like '%Chicago%') then 1 else 0 end) as 'Chicago', sum(case when (title like '%San Diego%') then 1 else 0 end) as 'San Diego', sum(case when (title like '%Stockholm%') then 1 else 0 end) as 'Stockholm', sum(case when (title like '%Cancún%') then 1 else 0 end) as 'Cancún', sum(case when (title like '%Warsaw%') then 1 else 0 end) as 'Warsaw', sum(case when (title like '%Sharm el-Sheikh%') then 1 else 0 end) as 'Sharm el-Sheikh', sum(case when (title like '%Dallas%') then 1 else 0 end) as 'Dallas', sum(case when (title like '%Hồ Chí Minh%') then 1 else 0 end) as 'Hồ Chí Minh', sum(case when (title like '%Milan%') then 1 else 0 end) as 'Milan', sum(case when (title like '%Oslo%') then 1 else 0 end) as 'Oslo', sum(case when (title like '%Libson%') then 1 else 0 end) as 'Libson', sum(case when (title like '%Punta Cana%') then 1 else 0 end) as 'Punta Cana', sum(case when (title like '%Johannesburg%') then 1 else 0 end) as 'Johannesburg', sum(case when (title like '%Antalya%') then 1 else 0 end) as 'Antalya', sum(case when (title like '%Mecca%') then 1 else 0 end) as 'Mecca', sum(case when (title like '%Macau%') then 1 else 0 end) as 'Macau', sum(case when (title like '%Pattaya%') then 1 else 0 end) as 'Pattaya', sum(case when (title like '%Guangzhou%') then 1 else 0 end) as 'Guangzhou', sum(case when (title like '%Kiev%') then 1 else 0 end) as 'Kiev', sum(case when (title like '%Shenzhen%') then 1 else 0 end) as 'Shenzhen', sum(case when (title like '%Bucharest%') then 1 else 0 end) as 'Bucharest', sum(case when (title like '%Taipei%') then 1 else 0 end) as 'Taipei', sum(case when (title like '%Orlando%') then 1 else 0 end) as 'Orlando', sum(case when (title like '%Brussels%') then 1 else 0 end) as 'Brussels', sum(case when (title like '%Chennai%') then 1 else 0 end) as 'Chennai', sum(case when (title like '%Marrakesh%') then 1 else 0 end) as 'Marrakesh', sum(case when (title like '%Phuket%') then 1 else 0 end) as 'Phuket', sum(case when (title like '%Edirne%') then 1 else 0 end) as 'Edirne', sum(case when (title like '%Bali%') then 1 else 0 end) as 'Bali', sum(case when (title like '%Copenhagen%') then 1 else 0 end) as 'Copenhagen', sum(case when (title like '%São Paulo%') then 1 else 0 end) as 'São Paulo', sum(case when (title like '%Agra%') then 1 else 0 end) as 'Agra', sum(case when (title like '%Varna%') then 1 else 0 end) as 'Varna', sum(case when (title like '%Riyadh%') then 1 else 0 end) as 'Riyadh', sum(case when (title like '%Jakarta%') then 1 else 0 end) as 'Jakarta', sum(case when (title like '%Auckland%') then 1 else 0 end) as 'Auckland', sum(case when (title like '%Honolulu%') then 1 else 0 end) as 'Honolulu', sum(case when (title like '%Edinburgh%') then 1 else 0 end) as 'Edinburgh', sum(case when (title like '%Wellington%') then 1 else 0 end) as 'Wellington', sum(case when (title like '%New Orleans%') then 1 else 0 end) as 'New Orleans', sum(case when (title like '%Petra%') then 1 else 0 end) as 'Petra', sum(case when (title like '%Melbourne%') then 1 else 0 end) as 'Melbourne', sum(case when (title like '%Luxor%') then 1 else 0 end) as 'Luxor', sum(case when (title like '%Hà Nội%') then 1 else 0 end) as 'Hà Nội', sum(case when (title like '%Manila%') then 1 else 0 end) as 'Manila', sum(case when (title like '%Houston%') then 1 else 0 end) as 'Houston', sum(case when (title like '%Phnom Penh%') then 1 else 0 end) as 'Phnom Penh', sum(case when (title like '%Zürich%') then 1 else 0 end) as 'Zürich', sum(case when (title like '%Lima%') then 1 else 0 end) as 'Lima', sum(case when (title like '%Santiago%') then 1 else 0 end) as 'Santiago', sum(case when (title like '%Bogotá%') then 1 else 0 end) as 'Bogotá' from inraw where year(cout) >=2006 and year(cout) <= 2013 group by year(cout)

Every rectangle represents a year and the dots represent cities. It’s a little crowded to draw 100 cities on one rectangle so I draw the top 30 cities. These cities mainly belong to North America, Europe and Asia. For each continent, I use different colors to represent it according to how they are represented in The Olympic Rings. Here blue represents Europe, red represents America and yellow represents Asia. For different cities, the darkness of color represent the quantity of checked out books. We can see from the final results that cities in America occupies about half of the share and one interesting thing is that Seattle is at a high position. Since this is a Seattle local library, the checked out quantity of books may not completely reveal the popularity of cities in the world. On the other hand, it reflects Seattle people’s preferences to world top cities.

I used processing and PeasyCam.

Source Code