Music Map Mashup

When Sade sang in 'Smooth Operator' -- "Coast to coast  LA to Chicago" -- she obviously hadn't consulted a map.

For starters  L.A. - Los Angeles (not to be confused by the abbreviation of Louisiana  a southern U.S. state) is on the coast but what about Chicago? Only if you count the coast of Lake Michigan.

After my careful scrutiny of Sade's entire discography  I began to wonder about how I've been using geography to engage with music. In recent conversation with a friend  I came up with a not too accurate theory on the origins of American Jazz. It is surprisingly global and geographically spatial. My colleague Javier Arce from CartoDB is one step ahead of me with his awesome [Spotimap].

Javier cataloged on his Spotimap  7 681 songs and 212 cities across the globe using the songs listed in the Wikipedia article "List of songs about cities."

To create this map Javier extracted a list of the cities with their respective countries and created a table. Then he geocoded that table to get the position of each city on the map.

Next  he extracted all the song information in the main article using regular expressions and infinite amounts of patience. It generated a CSV file that he imported into his CartoDB account. Javier ended up having a table that contained the name of the song  the author  and the city.

He repeated the same process for many of the cities listed in the article that have their own page. For example  Berlin. This was a little tedious because each list has a slightly different formatting. Javier had to modify the regular expression or fix some mistakes by hand and in some cases used Open Refine to spot and correct problems with the data.

After that process was finished  Javier got two tables. One for the songs and one for the cities:

Finally  Javier used the CartoDB JavaScript library cartodb.js  the Spotify API and their Play Button Widget to create the map.

The styling is pretty simple. There are just two layers. One for the cities  indicated by a green  beamed eighth note icon and another one with the countries (which is a Choropleth map created with the CartoDB wizard).

And the geospatial queries are very straightforward too. Since both the 'songs table' and the 'cities table' use the notation for the name of the city  Javier used that as a key to join both tables:

##_INIT_REPLACE_ME_PRE_##
SELECT
 cities.*
FROM
 spotimap_cities cities  
 spotimap_songs songs
WHERE
 songs.city = cities.city AND
 songs.available IS NOT false
##_END_REPLACE_ME_PRE_##

You can find all the information related to the map  the sources  and the code on his GitHub account: https://github.com/javierarce/spotimap.