Map of the Week: Safest and riskiest areas of New York subway system by the New York Daily News


Explore NYC subway crime data with CartoDB! Sarah Ryley from NY Daily News reveals trends, rates, and more in this interactive map.

This post may describe functionality for an old version of CARTO. Find out about the latest and cloud-native version here.
Map of the Week: Safest and riskiest areas of New York subway system by the New York Daily News

Welcome Sarah Ryley to our Map of the Week series. Sarah is the data projects editor at the New York Daily News  which has the second-largest newspaper website in the United States. Crime reporting is the bread and butter of the Daily News  so Sarah has been focused on making crime data interactive and accessible to the publication's millions of readers.


The Daily News' transit reporter Pete Donohue recently received a Freedom of Information Law request with police data on all felonies and misdemeanors over a roughly five-year period  from July 1  2008 to Aug. 4  2013. The data included the time  location  category and crime classification of 48 749 incidents. Sarah developed an interactive web package that allow readers to explore this data in several different ways -by station  crime type  hour  day  month and quarter- while also uncovering trends such as the hours when the most crimes occur and the crowded train line that is the source of half the system's groping complaints. She also calculated a station-by-station crime rate by merging the dataset with the Metropolitan Transportation Authority's (MTA) ridership statistics. The CartoDB map displays both the rate and total of various crime categories by station.

Prepping the data

The raw data  which can be downloaded here  needed a lot of work before I could load it into CartoDB. First  I removed all records after June 30  2013 so we would have a clean five-year period.

The train lines in the New York City Police Department  data are based on an old map  possibly this one from 1998  when an S train stopped at Lexington Avenue and Roosevelt Island  and the Q ran along the B/D line in Manhattan. So I did a "change alias" on many line and station dimensions to match them up with the current system. I did this manually because many of the routes half-matched the current ones and figuring out the rest took some research.



The locations are by post  not station. A post is roughly equivalent to a platform  so a multi-platform station like Union Square has multiple posts  and in some cases an additional post if there is a district office located in the station. I did these groupings based on how the MTA does them for the purpose of calculating ridership.

Then there are 123 crime classifications. I grouped these dimensions based on New York State Penal Law. Then I was ready to do a lot of the more granular queries for the story and other two interactives. For the map  I made a separate set of broader groupings -violent crimes  property crimes  drug crimes  weapon possession  and misdemeanor sex crimes-. I then queried the number of records for each broader group by station  and exported the results into a spreadsheet.

I then joined the exported data with annual ridership figures and station entrance geocodes from the MTA's website.

Calculating the rate

Once I had all of this in one spreadsheet  I could begin to calculate a rate. I consulted several academics on the best methodology  and finally decided on a calculation per 100 000 trips.

A few things to consider:

CartoDB time!

Finally  I had a table to import into CartoDB  with a column for each of the rate and total categories. I had an ambitious vision for this map given my very novice status as a coder  so here I need to extend my bottomless thanks to Michael Keller from Al Jazeera America and Matt Clark from Newsday for their help in making this happen.


map of crime on nyc subway

I wanted a mode change to switch between the total and rate  and buttons to switch between crime categories. Here is a link to a full tutorial on how to make buttons that toggle between map views  which would not include a mode change.

To solve the double button issue  Michael made LayerActions into an object that under each of the six crime categories has a key called "rate" and another called "total." The variable "mode" tells it which function to use.

var LayerActions = {
 crime: {
   total: function() {
       sql: "SELECT * FROM mtacartodb_1"
       cartocss: "#mtacartodb_1{marker-fill-opacity:0.9;marker-line-color:#960916;marker-line-width:1;marker-line-opacity:1;marker-placement:point;marker-multi-policy:largest;marker-type:ellipse;marker-fill:#eb1024;marker-allow-overlap:true;marker-clip:false;}#mtacartodb_1 [ crime_total <= 1810] { marker-width: 40.6;} … #mtacartodb_1 [ crime_total <= 5] { marker-width: 2.1;}"
     return true;
   rate: function() {
       sql: "SELECT * FROM mtacartodb_1"
       cartocss: "#mtacartodb_1{marker-fill-opacity:0.9;marker-line-color:#960916;marker-line-width:1;marker-line-opacity:1;marker-placement:point;marker-multi-policy:largest;marker-type:ellipse;marker-fill:#eb1024;marker-allow-overlap:true;marker-clip:false;}#mtacartodb_1 [ crime_rate <= 27.4] {   marker-width: 50;} … #mtacartodb_1 [ crime_rate <= 0.0] { marker-width: 0;}
     return true;

This sets the active filter (all crime) and mode (total)  and allows you to click on a different mode and make that the current mode:


var map;
var active_filter = 'crime';
var mode = 'total';
function init() {
 $('#mode-change input').on('change'  function(){
   var radio_btn_value = $(this).val();</p>
##_INIT_REPLACE_ME_PRE_## <pre><code>// If the mode you clicked on is not the current mode  then make it the current mode and referesh the map
if (mode != radio_btn_value) {
 mode = radio_btn_value;
 console.log(active_filter  mode)
</code></pre> ##END_REPLACE_ME_PRE_##
##_END_REPLACE_ME_PRE_## <pre></p>
<p>This sets the query for the first layer (from the crime_total column):</p>
<p>##_INIT_REPLACE_ME_PRE_## <pre>
cartodb.createLayer(map  layerUrl)
.on('done'  function(layer) {
 var subLayerOptions = {
   sql: "SELECT * FROM mtacartodb_1"
   cartocss: "#mtacartodb_1{marker-fill-opacity:0.9;marker-line-color:#960916;marker-line-width:1;marker-line-opacity:1;marker-placement:point;marker-multi-policy:largest;marker-type:ellipse;marker-fill:#eb1024;marker-allow-overlap:true;marker-clip:false;}#mtacartodb_1 [ crime_total &lt;= 1810] { marker-width: 40.6;} … #mtacartodb_1 [ crime_total &lt;= 5] { marker-width: 2.1;}"
##_END_REPLACE_ME_PRE_## <pre></p>
<p>And this is the function for the buttons and the mode  which is written so that once it finds the right function group  it will pick the correct function and execute for that mode:</p>
<p>##_INIT_REPLACE_ME_PRE_## <pre>
$('.button').click(function() {
 console.log($(this).attr('id')  mode);
 active_filter = $(this).attr('id');
##_END_REPLACE_ME_PRE_## <pre></p>
<p>This assigns IDs and labels for the buttons and mode:</p>
<p>{% highlight html %}
<body onload="init()">
<div id='map'></div>
<p><div id='menu'>
<div id="mode-change">
     <label><input type="radio" name="mode"  value="total" checked/> <strong>Total incidents</strong></label>
     <label><input type="radio" name="mode" value="rate"/><strong>Rate per 100 000 trips</strong></label>
##_INIT_REPLACE_ME_PRE_## <pre><code>&lt;a href="#violent" id="violent" class="button violent"&gt;VIOLENT CRIME&lt;/a&gt;
&lt;a href="#sex" id="sex" class="button sex"&gt;MISD. SEX CRIMES&lt;/a&gt;
&lt;a href="#crime" id="crime" class="button crimet selected"&gt;ALL CRIME&lt;/a&gt;
&lt;a href="#weapons" id="weapons" class="button weapons"&gt;WEAPON POSSESSION&lt;/a&gt;
&lt;a href="#drug" id="drug" class="button drug"&gt;DRUG CRIMES&lt;/a&gt;
&lt;a href="#property" id="property" class="button property"&gt;PROPERTY CRIMES&lt;/a&gt;
</code></pre> ##END_REPLACE_ME_PRE_##
<p>##_END_REPLACE_ME_PRE_## <pre></p>
<p>And here is the code that creates the style for the buttons and mode. (This is without all the adjustments made by our front-end architect  <strong>Wissam Abayad</strong>. All of his code can be found in the project's <a href="">GitHub</a> repository).</p>
<p>##_INIT_REPLACE_ME_PRE_## <pre></p>
<h2>menu { position: absolute; top: 5px; right: 10px; width: 400px; height:60px; background: transparent; z-index:10; }</h2>
<h2>menu a {</h2>
<p>margin: 15px 10px 0 0;
 float: right;
 vertical-align: baseline;
 width: 70px;
 padding: 10px;
 text-align: center;
 font: bold 11px "Helvetica" Arial;
 line-height: normal;
 color: #555;
 border-radius: 4px;
 border: 1px solid #777777;
 background: #ffffff;
 text-decoration: none;
 cursor: pointer;
<h2>menu a.selected </h2>
<h2>menu a:hover {</h2>
<p>color: #F84F40;
<p>text-align: right;
 color: #fff;
##_END_REPLACE_ME_PRE_## <pre></p>
<p>I also wanted a <strong>fixed infobox</strong> because it had too much information to float in the page. This script sets the position at the top right:</p>
<p>##_INIT_REPLACE_ME_PRE_## <pre>
var infobox = new cdb.geo.ui.InfoBox({
 width: 255
 position: 'top|right'
 layer: sublayer
 template: '<p> </p>'
##_END_REPLACE_ME_PRE_## <pre></p>
<p>And this style sets it 175 px from the top:</p>
<p>##_INIT_REPLACE_ME_PRE_## <pre>
div.cartodb-infobox {
 display: none;
 top: 175px !important;
 font: 13px "Helvetica";
##_END_REPLACE_ME_PRE_## <pre></p>
<p>I also needed to <strong>disable the scroll wheel</strong> so the reader doesn't get "stuck" when trying to scroll down the story:</p>
<p>##_INIT_REPLACE_ME_PRE_## <pre>
##_END_REPLACE_ME_PRE_## <pre></p>
<p><strong>A few other notes:</strong> I set a consistent scale range in the marker widths across all six categories  rather than the default CartoDB setting that changes the scale range based on the range of numbers in each category. I felt this was important because otherwise  the viewer could erroneously perceive a station as having a higher number of sex crimes than violent crimes  for example  because the scales are set differently. If the marker width increments are the same  than the viewer can also see greater than or less than across categories. Michael also made the point that the markers should vary in size based on the area  not the marker width (diameter)  so I created a formula that would come up with a marker width based on an area proportionate to the increase or decrease in rate or total.</p>
<p>You can signup for free to learn how to create maps like this in <a href=""></a>.</p>