NYC, the City That Never Sleeps

Summary

This post may describe functionality for an old version of CARTO. Find out about the latest and cloud-native version here.
NYC, the City That Never Sleeps

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.

##_INIT_REPLACE_ME_PRE_##

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.)

##_INIT_REPLACE_ME_PRE_##

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.

##_INIT_REPLACE_ME_PRE_##

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.

##_INIT_REPLACE_ME_PRE_##

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.)

Happy mapping!