In 1905 the New York Times declared New York City "the noisiest city on Earth." Little has seemingly changed with hundreds of thousands of noise complaints logged with the city's 311 complaint hotline every year since 2010. As a public data excerise I created a choropleth map of 311 noise complaint data from 2014 normalized against the latest population figures from the city.
Mapping New Yorker noise complaints can only give us a spatial dimension to individual perceptions of noise. Our sense of what constitutes noise is highly personal determined by factors such as our upbringing community and society. Until the city attempts to measure the street noise by decibels the only available data to work from is the city's 311 complaint line. Here's what I learned and how to make your very own Noisy New York City map.
To start I downloaded a shapefile of New York City's neighborhood tabulation areas from the NYC OpenData portal and [2010 census population data] from the NYC Department of City Planning. After cleaning up the header rows in the 2010 census population table I imported both datasets into CartoDB.
In the Neighborhood Tabulation Area table I created a new empty column and used a SQL query to join 2010 census population data to the Neighborhood Tabulation Area table.
UPDATE nynta SET pop = census2010pops.pop2010 FROM census2010pops WHERE nynta.ntaname = census2010pops.ntaname ##_END_REPLACE_ME_PRE_##
I downloaded 311 noise complaint data from the NYC OpenData Portal for 2014 and uploaded to CartoDB. One year's worth of data has well over 160 000 complaints. I joined the data from the neighborhood tabulation table with the 311 noise complaint table using geospatial intersection and normalized the number of complaints against the latest census population figures for each neighborhood. (Learn more about aggregating data by geospatial intersection from this CartoDB tutorial.)
SELECT 1000 * s.cnt / ne.pop AS normed s.the_geom_webmercator ne.ntaname FROM nynta ne (SELECT ne.ntaname AS name ne.the_geom_webmercator AS the_geom_webmercator count() cnt FROM nynta ne noisedata nd WHERE ST_Intersects(ne.the_geom nd.the_geom) GROUP BY ne.ntaname ne.the_geom_webmercator ) s WHERE s.name = ne.ntaname AND ne.pop IS NOT NULL AND ne.pop != 0 AND ne.ntaname !~ 'park-cemetery-etc' ##_END_REPLACE_ME_PRE_##
One issue to keep in mind is the inclusion of public spaces such as parks and cemeteries. These areas are designated as "park-cemetery-etc" by borough and not surprisingly noise complaints are registered from these areas as well. However with no census population data to work from for these areas I needed a different strategy for normalizing this data.
I created a new table with the noise complaint data for the "park-cemetery-etc" neighborhoods only.
SELECT s.cnt s.the_geom_webmercator ne.ntaname FROM nynta ne (SELECT ne.ntaname AS name ne.the_geom_webmercator AS the_geom_webmercator count() cnt FROM nynta ne noisedata nd WHERE ST_Intersects(ne.the_geom nd.the_geom) GROUP BY ne.ntaname ne.the_geom_webmercator ) s WHERE s.name = ne.ntaname AND ne.ntaname ~ 'park-cemetery-etc' ##_END_REPLACE_ME_PRE_##
I normalized this data by the area of each green space and combined as a second layer in my Noisy New York City map.
SELECT the_geom_webmercator cnt * 1e6 / ST_Area(the_geom_webmercator) AS cnt_normed FROM noiseinparks ##_END_REPLACE_ME_PRE_##
After applying color styles unique to each layer and adding legends the map is done! There is more that could be explored though such as analyzing complaints by type (such as loud music or party the most common complaint according to this more in-depth analysis of the noise complaint data by Ben Wellington of I Quant NY.)