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:
SELECT
cities.*
FROM
spotimap_cities cities,
spotimap_songs songs
WHERE
songs.city = cities.city AND
songs.available IS NOT false
You can find all the information related to the map, the sources, and the code on his GitHub account: https://github.com/javierarce/spotimap.
Credit card transaction insights allow decision makers to equip themselves with a deeper understanding of consumers and trends. In partnering with Mastercard, and with the ...
Location IntelligenceThe unprecedented growth in Americans aged 65 and over is driving demand in the senior housing subsector. The Census Bureau expects the U.S. senior population to increase b...
Location IntelligenceIt’s no secret that the retail and real estate industries have been disrupted over the past decade. Traditional Retailers are learning to co-exist with ecommerce giants and...
Location IntelligencePlease fill out the below form and we'll be in touch real soon.