The above embedded map shows the number of 2014 drug misdemeanors by NYPD precinct as a choropleth. The color of the labels indicate whether the number is a net decrease (in green) or increase (in red) since 2009.

Did statewide drug law reforms change NYPD policing of low-level drug crimes? Short answer/spoiler: probably not – but read on for a tutorial on how to make more maps.

This tutorial describes one way of showing multiple dimensions of data on a single map – in this case, the current absolute number of crimes (as we did in the previous tutorial) and the change in number of crimes. The approach we take to this is to go beyond the Map Layer Wizard and write our own CartoCSS conditional styles.

As in the previous tutorial, the crime data comes from this compilation of NYPD spreadsheets.

Table of contents

Overview of map iterations

There's a lot of code in this tutorial, much of which I don't go into full detail. So it's helpful to see the overall strategy in iteration.

2014 drug misdemeanors by NYPD precinct

Recall in the previous tutorial, we generated a choropleth map showing just the 2014 drug misdemeanors:

img

Unfortunately, such a visualization doesn't make use of the 15 years of precinct-level data that we have. And when evaluating crime data, we often care about the change in crime rates over time.

2009 drug misdemeanors by NYPD precinct

Here's what the map of 2009 drug misdemeanors look like:

image red-choropleth-of-2009.png

Change in drug misdemeanors, 2009 to 2014, by NYPD precinct

However, looking at two different maps is a very laborious way for users to determine change. So we use SQL to derive a dataset that has both 2014 and 2009 numbers, and then create a column that has the difference between both columns. The choropleth of that map can be seen below:

Not bad, except that with a single-color choropleth, negative change is represented by less color. In our real-world scenario, though, it seems that precincts in which drug misdemeanors should be a whole different category from precincts in which drug misdemeanors increased.

Change in drug misdemeanors, 2009 to 2014, by NYPD precinct, as a double-color choropleth

So we create a two-color choropleth, in which green and red are used to indicate negative and positive growth in drug misdemeanors, respectively, between 2009 and 2014:

However, now we've lost the context of absolute number of drug misdemeanors – after all, the implications of a historically high-crime precinct decreasing (or increasing) its drug misdemeanors count is different than they are for a precinct which doesn't have many drug misdemeanors, period.

Single-color choropleth for 2014 drug misdemanors by precinct, with colored labels to indicate net increase/decrease since 2009

In the final map we create, we attempt to depict absolute number of drug misdemeanors for 2014 as a white-to-black choropleth, and use the text labels to indicate whether that precinct has experienced a decrease or increase in drug misdemeanors since 2009. We can see that precincts with overall high numbers of drug misdemeanors also experienced large drops in reporting such crimes:

It's not pretty, because showing both dimensions is inherently difficult with a single map. But this is just an example of the many insights we can get from a single dataset.

Single-color choropleth as above, but showing 2009 drug misdemeanors

Note: It's probably more relevant to have the choropleth show the 2009 drug misdemeanors, as to show the precincts in which these crimes used to be more prevalent. Here's what that variation looks like:

Note that the right-side limit of the scale in 2009 is 4,700+, which is an indication that at least in one precinct, the number of drug misdemeanors has dropped drastically in 2014.

(note: this tutorial is a little unfinished in its explanation, but the code is all here)

The Rockefeller Drug Law Reform of 2009

I hypothesize that the number of reported drug misdemeanors in 2009 might be more than they are in 2014, given statewide reforms in deprioritizing the enforcement of low-level drug crimes.

Some background from the New York Civil Liberties Union:

On April 2, 2009, the State Legislature passed legislation that embraces two important principles of drug law reform: It includes an elimination of mandatory minimum sentences, and it includes a restoration of judges’ authority to send many drug offenders to treatment programs instead of jail.

The effect on NYPD policing

Did the change in state legislation affect the NYPD's policing of misdemeanor drug crimes? If we look at only recent news stories, we'll see that the current administration is making efforts to reduce arrests for low-level drug crimes.

Overall misdemeanor arrests fell from 2014 to 2013.

In November 2014, the NYPD announced that minor marijuana possession would no longer result in an arrest.

Via the New York Daily News:

As it is, misdemeanor marijuana arrests are down citywide through the first 10 months of the year: through Nov. 10, there have been 24,081 arrests for smoking weed or possessing pot in public view. That’s about 3% lower than the 24,838 arrests through the same point last year, police said.

But none of this really refers to the Rockefeller reforms…

Definition of misdemeanor drug crimes

From the Historical New York City Crime Data files, drug misdemeanors are defined as:

Total of all top charge Misdemeanor Dangerous Drugs arrests made by all arresting agencies. Contains Criminal Possession of Controlled Substance, Criminally Possessing a Hypodermic Instrument, Criminally using Drug Paraphernalia, Criminal Possession of Methamphetamine Manufacturing Material, Criminal Possession of Marijuana, Criminal Sale of Marijuana

The problem of map comparisons

It's trivial to create two different maps for two different years. But it's difficult to see the actual difference between both maps:

2014 drug misdemeanors map

From the previous tutorial:

img

2009 drug misdemeanors map

You can repeat the SQL in previous tutorial and modify it to filter for 2009 crimes to generate this map:

image red-choropleth-of-2009.png

If you look at both maps for a very, very long time, you might be able to pick out exactly which precincts have increased or decreased in the 5-year period. But wouldn't it be nicer to see the quantity of change on a single map?

Writing nested queries and one big query

Before we write the one big SQL query to get us the dataset for this map, let's think about them as individual queries.

Getting the misdemeanor drug charges from 2014

The simple SELECT to get all of the drug misdemeanors for 2014:

SELECT
  precinct,
  incident_count AS misd_drugs_2014
FROM nypd_crime_data
WHERE
    year = 2014 AND
    category = 'MISDEMEANOR DANGEROUS DRUGS';

Getting the misdemeanor drug charges from 2009

The simple SELECT to get all of the drug misdemeanors for 2009:

SELECT
  precinct,
  incident_count AS misd_drugs_2009
FROM nypd_crime_data
WHERE
    year = 2009 AND
    category = 'MISDEMEANOR DANGEROUS DRUGS';

Joining the two years of data and adding a comparison column

And now for the ugliness: joining the results of those two queries together to get misd_drugs_2009 and misd_drugs_2014 into a single table, plus another column that calculates the percentage of change between the two values:

SELECT
  t_2014.precinct,
  misd_drugs_2009,
  misd_drugs_2014,
  ROUND((misd_drugs_2014 - misd_drugs_2009) * 100.0 / misd_drugs_2009)
    AS delta_2009_2014
FROM (SELECT
        precinct,
        incident_count AS misd_drugs_2014
      FROM nypd_crime_data
      WHERE
        year = 2014 AND
        category = 'MISDEMEANOR DANGEROUS DRUGS')
      AS t_2014
INNER JOIN (SELECT
    precinct,
    incident_count AS misd_drugs_2009
  FROM nypd_crime_data
  WHERE
    year = 2009 AND
    category = 'MISDEMEANOR DANGEROUS DRUGS')
  AS t_2009
  ON t_2014.precinct = t_2009.precinct
ORDER BY delta_2009_2014;

Because I ordered by delta_2009_2014, we see the most dramatic drops at the top:

image table-of-2009-2014-delta.png

Before moving on, let's adjust the query to get the overall average change from 2009 to 2014. I calculate this by dividing the SUM of misd_drugs_2014 - misd_drugs_2009 by the SUM of misd_drugs_2009:

SELECT
  ROUND(SUM(misd_drugs_2014 - misd_drugs_2009) * 100.0 / SUM(misd_drugs_2009))
    AS avg_delta_2009_2014
FROM (SELECT
        precinct,
        incident_count AS misd_drugs_2014
      FROM nypd_crime_data
      WHERE
        year = 2014 AND
        category = 'MISDEMEANOR DANGEROUS DRUGS')
      AS t_2014
INNER JOIN (SELECT
    precinct,
    incident_count AS misd_drugs_2009
  FROM nypd_crime_data
  WHERE
    year = 2009 AND
    category = 'MISDEMEANOR DANGEROUS DRUGS')
  AS t_2009
  ON t_2014.precinct = t_2009.precinct

For number of drug misdemeanors, the average change from 2009 to 2014, citywide, appears to be about -34 percent.

Making a new geospatial dataset

Below I've joined the above query with nypd_precincts to be able to associate misd_drugs_2009, misd_drugs_2014, and delta_2009_2014 with their respective NYPD precinct shapefiles:

SELECT
  nypd_precincts.precinct,
  misd_drugs_2009,
  misd_drugs_2014,
  ROUND((misd_drugs_2014 - misd_drugs_2009) * 100.0 / misd_drugs_2009)
    AS delta_2009_2014,
  nypd_precincts.cartodb_id,
  nypd_precincts.the_geom_webmercator
FROM nypd_precincts
INNER JOIN (SELECT
        precinct,
        incident_count AS misd_drugs_2014
      FROM nypd_crime_data
      WHERE
        year = 2014 AND
        category = 'MISDEMEANOR DANGEROUS DRUGS')
      AS t_2014
  ON nypd_precincts.precinct = t_2014.precinct
INNER JOIN (SELECT
    precinct,
    incident_count AS misd_drugs_2009
  FROM nypd_crime_data
  WHERE
    year = 2009 AND
    category = 'MISDEMEANOR DANGEROUS DRUGS')
  AS t_2009
  ON nypd_precincts.precinct = t_2009.precinct
ORDER BY delta_2009_2014;

I created a new dataset from the above query and named it: nypd_precincts_delta_misd_drugs_2009_2014.

Then I've configured the Map Layer Wizard to create a Choropleth in which the darker shade of green, the more the drug misdemeanors increased in 2014 compared to 2009 (i.e. delta_2009_2014).

The map that results from this is here:

It's not terrible, but not ideal either. Having this single-color choropleth doesn't quite work in a situation in which we have negative change, which is in this scenario, something different than having less of an increase. In this kind of situation, we hypothesize that there is something fundamentally different between precincts that have increased in drug misdemeanors and those that have decreased – i.e. have a negative delta_2009_2014.

In this situation, it is better to use two colors, for negative and positive changes, respectively.

Learning CartoCSS and moving beyond the wizard

There's several ways to approach this problem, including writing a lot more SQL and learning about CartoDB's multiple layers option. However, for this tutorial, I've chosen to configure the CartoCSS code.

CartoCSS is a language for defining the visual style of the map. For those of you familiar with CSS as it pertains to normal webpage styling, CartoCSS has a similar syntax and concept. However, CartoCSS allows for some programmatic-like syntax, so that styles for shapes can be defined on a conditional basis.

This is pertinent to our situation, because this is what we fundamentally want to do (expressed as pseudocode): for precincts in which delta_2009_2014 is negative, make their fill color green. For precincts in which delta_2009_2014 has increased, make their fill color red.

Describing all of CartoCSS, or even the concept of CSS in general, is beyond the scope of this tutorial. However, see if you can figure out the pattern and structure by first examining the kind of code created by the Map Layer Wizard, and then tweaking it for yourself.

Accessing the CartoCSS panel

Click the icon that has the letters, CSS:

GIF: cartodb-cartocss-panel.gif

Here, we see a little bit beyond the wizard's curtain. When generating a Choropleth map, the wizard creates that CartoCSS code that describes exactly what the lines and polygons should look like.

Here's what it looks like in full:

/** choropleth visualization */

#nypd_precincts_delta_misd_drugs_2009_2014{
  polygon-fill: #EDF8FB;
  polygon-opacity: 0.8;
  line-color: #FFF;
  line-width: 0.5;
  line-opacity: 1;
}

#nypd_precincts_delta_misd_drugs_2009_2014 [ delta_2009_2014 <= 149] {
   polygon-fill: #006D2C;
}


#nypd_precincts_delta_misd_drugs_2009_2014 [ delta_2009_2014 <= 149] {
   polygon-fill: #006D2C;
}
#nypd_precincts_delta_misd_drugs_2009_2014 [ delta_2009_2014 <= 69] {
   polygon-fill: #2CA25F;
}
#nypd_precincts_delta_misd_drugs_2009_2014 [ delta_2009_2014 <= 36] {
   polygon-fill: #66C2A4;
}
#nypd_precincts_delta_misd_drugs_2009_2014 [ delta_2009_2014 <= -3] {
   polygon-fill: #B2E2E2;
}
#nypd_precincts_delta_misd_drugs_2009_2014 [ delta_2009_2014 <= -41] {
   polygon-fill: #EDF8FB;
}

Interactively editing the CartoCSS

Change one thing at a time:

Here's the first element

#nypd_precincts_delta_misd_drugs_2009_2014{
  polygon-fill: #EDF8FB;
  polygon-opacity: 0.8;
  line-color: #FFF;
  line-width: 0.5;
  line-opacity: 1;
}

Let's make that line-color attribute a shade of red. You can do this interactively simply by clicking the current value of line-color to bring up a color panel. Choose the red swatch. The map should automatically refresh, but if not, hit the Apply Style button to apply the change:

GIF: cartodb-cartocss-change-red-line.gif

And that's how easy it is to customize the default wizard styles. Play around with the different attributes and get a feel for how the syntax maps to the configuration you've seen in the Wizard.

Manually editing the CartoCSS

After clicking your way through the CartoCSS, try changing the values by typing them out manually. Because not only does it end up being a bit more precise, writing our own CartoCSS styles is the only way to create entirely new visual functionality that the Choropleth wizard doesn't provide by default.

Try making these changes:

There's multiple ways to achieve this effect. Give it a go.

/** choropleth visualization */

#nypd_precincts_delta_misd_drugs_2009_2014{
  polygon-fill: #EDF8FB;
  polygon-opacity: 0.8;
  line-color: #000;
  line-width: 0.5;
  line-opacity: 0.7;
}

#nypd_precincts_delta_misd_drugs_2009_2014 [ delta_2009_2014 > 30] {
   polygon-fill: #900;
}
#nypd_precincts_delta_misd_drugs_2009_2014 [ delta_2009_2014 <= 30] {
   polygon-fill: #C88;
}
#nypd_precincts_delta_misd_drugs_2009_2014 [ delta_2009_2014 <= 5] {
   polygon-fill: #FDD;
}

#nypd_precincts_delta_misd_drugs_2009_2014 [ delta_2009_2014 <= -5] {
   polygon-fill: #CFC;
}
#nypd_precincts_delta_misd_drugs_2009_2014 [ delta_2009_2014 <= -30] {
   polygon-fill: #8C8;
}
#nypd_precincts_delta_misd_drugs_2009_2014 [ delta_2009_2014 <= -60] {
   polygon-fill: #090;
}

#nypd_precincts_delta_misd_drugs_2009_2014::labels {
  text-name: [precinct];
  text-face-name: 'DejaVu Sans Book';
  text-size: 10;
  text-label-position-tolerance: 10;
  text-fill: #000;
  text-halo-fill: #FFFFFF;
  text-halo-radius: 2.5;
  text-dy: -10;
  text-allow-overlap: true;
  text-placement: point;
  text-placement-type: simple;
}

My result:

image red-green-choropleth-of-2009-2014-delta.png

Things to note:

Adding our own CartoCSS logic

Going back to the wizard will wipe out all of our manual CartoCSS changes. So copy-paste them into a file.

This time around, I make a choropleth, except I want to base it again on the raw counts of misd_drugs_2014, which I specify in the Column dropdown:

image grayscale-choropleth-2014-misdrugs-nypd.png

This map, at a glance, gives us a decent overview of where these crimes happen the most. However, we've lost the ability to see the change over time.

How can we get both insights?

By setting a visual attribute – other than the polygon-fill – to vary on the value of delta_2009_2014_misd_drugs.

But we can't do this through the wizard. But we can do it through the CartoCSS panel.

There are many approaches you can take to this. I've opted to copy all of the CartoCSS code from the red-green example, but change the instances of polygon-fill to line-color. And then I've tacked it onto the end of the file.

Here is all of my CartoCSS:

/** choropleth visualization */

#nypd_precincts_delta_misd_drugs_2009_2014{
  polygon-fill: #F7F7F7;
  polygon-opacity: 0.8;
  line-color: #f7f7f7;
  line-width: 2.0;
  line-opacity: 1;
}

#nypd_precincts_delta_misd_drugs_2009_2014::labels {
  text-name: [precinct];
  text-face-name: 'DejaVu Sans Book';
  text-size: 10;
  text-label-position-tolerance: 10;
  text-fill: #000;
  text-halo-fill: #FFFFFF;
  text-halo-radius: 2.5;
  text-dy: -10;
  text-allow-overlap: true;
  text-placement: point;
  text-placement-type: simple;
}

#nypd_precincts_delta_misd_drugs_2009_2014 [ misd_drugs_2014 <= 2594] {
   polygon-fill: #252525;
}
#nypd_precincts_delta_misd_drugs_2009_2014 [ misd_drugs_2014 <= 1292] {
   polygon-fill: #525252;
}
#nypd_precincts_delta_misd_drugs_2009_2014 [ misd_drugs_2014 <= 942] {
   polygon-fill: #737373;
}
#nypd_precincts_delta_misd_drugs_2009_2014 [ misd_drugs_2014 <= 707] {
   polygon-fill: #969696;
}
#nypd_precincts_delta_misd_drugs_2009_2014 [ misd_drugs_2014 <= 467] {
   polygon-fill: #BDBDBD;
}
#nypd_precincts_delta_misd_drugs_2009_2014 [ misd_drugs_2014 <= 318] {
   polygon-fill: #D9D9D9;
}
#nypd_precincts_delta_misd_drugs_2009_2014 [ misd_drugs_2014 <= 168] {
   polygon-fill: #F7F7F7;
}



#nypd_precincts_delta_misd_drugs_2009_2014 [ delta_2009_2014 > 30] {
   line-color: #900;
}
#nypd_precincts_delta_misd_drugs_2009_2014 [ delta_2009_2014 <= 30] {
   line-color: #C88;
}
#nypd_precincts_delta_misd_drugs_2009_2014 [ delta_2009_2014 <= 5] {
   line-color: #FDD;
}

#nypd_precincts_delta_misd_drugs_2009_2014 [ delta_2009_2014 <= -5] {
   line-color: #CFC;
}
#nypd_precincts_delta_misd_drugs_2009_2014 [ delta_2009_2014 <= -30] {
   line-color: #8C8;
}
#nypd_precincts_delta_misd_drugs_2009_2014 [ delta_2009_2014 <= -60] {
   line-color: #090;
}

Applying those styles gets us this multi-layered map:

image outlined-grayscale-choropleth-2014-misdrugs-nypd.png

It's not pretty, but it's not bad for a first pass at hand-coding data values. At a glance, though, we get a new insight: for the most part, the drop in enforcement of drug misdemeanors isn't restricted to precincts in which such busts were rare – precincts of all levels of drug activity experience a drop.

Some of the precincts where the drug busts have increased are in lower Manhattan, where the number of incidents is relatively low. However, there is one precinct in which drug busts are frequent and in which they've increased: Precinct 48, in the Bronx.

What's going on? While the apparent trend stands out, we still don't know for sure. It could be that that precinct is having more misdemeanors for reasons independent of changes in policing strategy. Or, the precinct commander has decided that it is necessary to maintain the same strictness in policing. Or maybe 2014 is a fluke, in which case, we have all the other years to look at.

Correlation, causation, etc.

Did the 2009 reforms change things? This kind of multi-year data comparison is not something a map can explain well. In fact, a much simpler SQL query – a group count by year – will give us better insights:

SELECT
  year,
  SUM(incident_count) AS total_incidents
FROM nypd_crime_data
WHERE
    category = 'MISDEMEANOR DANGEROUS DRUGS'
GROUP BY year
ORDER BY year;

image group-drug-misd-by-year.png

We can see that drug misdemeanors held steady through 2010, and peaked in 2011 with 80,000+ such reports. In 2012, the number dropped drastically to 67,000+, and has fallen each year since. So it doesn't seem likely that the Rockefeller Drug Laws were the main factor. However, there was something else about NYPD policy that changed drastically in 2012

More polished version of the map

Here I do a few more edits to the CartoCSS, so that the text labels reflect the change over time.

The map can be seen here.

And here's the CSS and custom HTML to generate it:

/** choropleth visualization */

#nypd_precincts_delta_misd_drugs_2009_2014{
  polygon-fill: #F7F7F7;
  polygon-opacity: 0.8;
  line-color: #000000;
  line-width: 1.0;
  line-opacity: 1;
}

#nypd_precincts_delta_misd_drugs_2009_2014 {
  text-name: [precinct];
  text-face-name: 'Lato Bold';
  text-size: 12;
  text-label-position-tolerance: 10;
  text-fill: #000;
  text-halo-fill: #FFFFFF;
  text-halo-radius: 4.5;
  text-dy: -0;
  text-allow-overlap: true;
  text-placement: point;
  text-placement-type: simple;
}

#nypd_precincts_delta_misd_drugs_2009_2014 [ misd_drugs_2014 <= 2594] {
   polygon-fill: #252525;
}
#nypd_precincts_delta_misd_drugs_2009_2014 [ misd_drugs_2014 <= 1292] {
   polygon-fill: #525252;
}
#nypd_precincts_delta_misd_drugs_2009_2014 [ misd_drugs_2014 <= 942] {
   polygon-fill: #737373;
}
#nypd_precincts_delta_misd_drugs_2009_2014 [ misd_drugs_2014 <= 707] {
   polygon-fill: #969696;
}
#nypd_precincts_delta_misd_drugs_2009_2014 [ misd_drugs_2014 <= 467] {
   polygon-fill: #BDBDBD;
}
#nypd_precincts_delta_misd_drugs_2009_2014 [ misd_drugs_2014 <= 318] {
   polygon-fill: #D9D9D9;
}
#nypd_precincts_delta_misd_drugs_2009_2014 [ misd_drugs_2014 <= 168] {
   polygon-fill: #F7F7F7;
}


#nypd_precincts_delta_misd_drugs_2009_2014 [ delta_2009_2014 > 30] {
   text-halo-fill: #900;
   text-fill: #FFF;
}
#nypd_precincts_delta_misd_drugs_2009_2014 [ delta_2009_2014 <= 30] {
   text-halo-fill: #C88;
   text-fill: #FFF;
}
#nypd_precincts_delta_misd_drugs_2009_2014 [ delta_2009_2014 < 5] {
   text-halo-fill: #FDD;
   text-fill: #000;
}

#nypd_precincts_delta_misd_drugs_2009_2014 [ delta_2009_2014 <= 2] {
   text-halo-fill: #FFFFFF;
   text-fill: #000;
}


#nypd_precincts_delta_misd_drugs_2009_2014 [ delta_2009_2014 <= -2] {
   text-halo-fill: #ddffdd;
   text-fill: #000;
}

#nypd_precincts_delta_misd_drugs_2009_2014 [ delta_2009_2014 <= -30] {
   text-halo-fill: #55bb55;
   text-fill: #FFF;
}

#nypd_precincts_delta_misd_drugs_2009_2014 [ delta_2009_2014 <= -60] {
   text-halo-fill: #090;
   text-fill: #FFF;
}

(yeah, this HTML is pretty ugly – not sure if there's a better way to do styling)

<div class="cartodb-tooltip-content-wrapper foo">
  <div class="cartodb-tooltip-content">
    <h4>Precinct {{precinct}}</h4>
    <table class="data-table" style="width: 100%;">
      <thead style="border-bottom: thin solid #999;">
        <tr>
           <th style="border-right: thin solid #999;">2009</th>
           <th style="border-right: thin solid #999;">2014</th>
           <th style=""></th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <td style="border-right: thin solid #999; text-align: right; padding-right: 2px;">{{misd_drugs_2009}}</td>
          <td style="border-right: thin solid #999; text-align: right; padding-right: 2px;">{{misd_drugs_2014}}</td>
          <td style="text-align: right;">{{delta_2009_2014}}%</td>
        </tr>
      </tbody>
    </table>  
  </div>
</div>