How to Build an Interactive Choropleth Map with Carto

Lately I’ve been working with a dataset of New York real estate listings  from the Kaggle RentHop competition and decided to make a choropleth map in order to better understand listing distribution and rental prices by neighborhood. Also – let’s face it – it’s pretty to look at.

There are plenty of ways to go about building a choropleth map that I looked at, but Carto seemed like the best path to subscription-free and interactive mapping tools. That turned out to be true, since I went from possessing a raw dataset of lat/long and zero knowledge of Carto to the map above in the course of an afternoon.

1. Get a dataset of lat/long. If you have locations but no lat/long, there are plenty of geocoding services and APIs that can convert your data.
2. Find a geometry file to partition the map. For the above visualization, I found a file here that contains all of the NYC neighborhoods. Each neighborhood item in the file has a set of lat/long points that draws its boundary.
3. Set up a Carto account and take a quick tutorial of the map and dataset interfaces. I won’t lie: as of March 2017 (I’m told this is a new UI) the map interface could use some UX improvement. It’s very clean, but my feeling is that some of the core functionality got buried a little too deep in order to make it that way. I had a pretty confusing time getting it to surface basic analyses and heard a lot of my thoughts echoed the very next day by a new-to-Carto user while I helped him build a choropleth map at a hackathon.
4. Load both datasets to Carto and enter the dataset with your lat/long coordinates. Carto should automatically create the_geom data based off of the lat/long and geometry files.
5. Now we need to associate each lat/long point with a region from the geometry file. Open the SQL editor and run the following:
   1. SELECT l.*,g.region_var FROM {YourLatLongFile} as l, {YourGeometryFile} as g WHERE ST_Within(l.the_geom,g.the_geom)
6. Click “Create Map” to go to the map interface. From your dataset select Analysis -> Add Analysis -> Join from Second Layer
7. One source is your file with lat/long (which thanks to our SQL query now contains a field that associates each row with a region from the geometry file) and the other source is the geometry file. Join on the region_var column in your lat/long file and whatever the corresponding column is in the geometry file. At the bottom, you want to keep the region column from your geometry file, and that’s all you need for a simple choropleth map.
8. Go to the Style tab, select “Fill” and select “By Value” and choose your variable. It should have a default of cartodb_id along with your region variable. By selecting cartodb_id you should now have a choropleth map where each region is color-coded with the count of rows in cartodb_id. That’s it!
9. The interactive tools are a little more intuitive, so you can go ahead and create hover and click states. For variables that I’ve included like average price, you can go back into the database editor and create a new table with summary statistics.