CARTO Assignment

Refer to the CARTO site for tutorials, instructions, documentation, their blog site which provides for many examples of CARTO.

This tutorial was originally developed by Joey Lee and is posted  on GitHub . Joey modified the City of Vancouver 311 call data for this tutorial and his code is to follow.

CARTO changed their (name and) software in June 2017 so the tutorial has been modified October 2017 to reflect changes in the display and design of the interactive map.


This is a quick teaser introducing CARTO (formally CartoDB), web maps, and where you might exploring interacting with and publishing your data on the web.

We will be exploring Vancouver’s 3-1-1 maintenance service phone calls and creating an animated map of the calls per day in January 2014.

 Create a New Map in CARTO


Acquire the Geotagged Data to Map


We will then get a whole slew of nice options – you can see that we can directly drag and drop data, upload from Google Drive, dropbox, etc. Pretty cool!

This what the data looks like: (open data, download the excel file for 2016 311 calls) you can see that address are not parsed and do not have a lot long, and there are about 100 unique case types that need grouping for mapping. Joey parsed and geocoded the addressed, and created a ‘cid’ of 0-5 grouping like calls. 

Lucky for us, our data is stored on Github and therefore we can link directly to it.


for a description of spatial file formats supported by CARTO, click on “see all formats’ in the window above.

In the input box, copy and paste this link below and click submit:

If you go to the link, you’ll see a file that is structured like this (here’s just a snippet):

    "type": "FeatureCollection",
    "crs": {
        "type": "name",
        "properties": {
            "name": "urn:ogc:def:crs:OGC:1.3:CRS84"

    "features": [{
                "type": "Feature",
                "id": 5,
                "properties": {
                    "X": 5,
                    "Year": 2014,
                    "Month": 1,
                    "Day": 1,
                    "Hour": 10,
                    "Minute": 15,
                    "Department": "PRB - Administration",
                    "Division": "General Park Board Information",
                    "Case_Type": "PRB_Park Ranger SR",
                    "Hundred_Block": "11##",
                    "Street_Name": "W CORDOVA ST",
                    "Local_Area": "Downtown",
                    "h_block": "1100",
                    "full_address": "1100 W CORDOVA ST, Vancouver, BC, Canada",
                    "lat": 49.288287599999997,
                    "lon": -123.11927900000001,
                    "cid": 0,
                    "lat_offset": 49.2886835848809,
                    "lon_offset": -123.118948454953,
                    "timestamp": "2014-1-1 10:15:00"
                "geometry": {
                    "type": "Point",
                    "coordinates": [-123.118948454952999, 49.2886835848809]
            }, {
                "type": "Feature",
                "id": 6,
                "properties": {
                    "X": 6,
                    "Year": 2014,
                    "Month": 1,
                    "Day": 1,
                    "Hour": 10,
                    "Minute": 17,
                    "Department": "ENG - Streets",
                    "Division": "Traffic and Electrical Operations",
                    "Case_Type": "Sign - Repair",
                    "Hundred_Block": "8600",
                    "Street_Name": " - 8699 GRANVILLE ST",
                    "Local_Area": "Marpole",
                    "h_block": "8600",
                    "full_address": "8600  - 8699 GRANVILLE ST, Vancouver, BC, Canada",
                    "lat": 49.208433100000001,
                    "lon": -123.14039510000001,
                    "cid": 0,
                    "lat_offset": 49.208598370229801,
                    "lon_offset": -123.14012958765601,
                    "timestamp": "2014-1-1 10:17:00"
                "geometry": {
                    "type": "Point",
                    "coordinates": [-123.140129587656006, 49.208598370229801]
            }, ...

This is a geojson file that contains the 3-1-1 calls and the coordinates. You can find out more about how this dataset was created and run the R code that produced this dataset here.

Categories for 311 complaint data

The 311 call data has many many categories for complaints, too many to put on a map, so we needed to group like complaints into fewer categories. Joey did this for us with some R code with the geojson file that the City created.

# Determine classes to group case types:

# graffiti and noise
graffiti_noise = c('Graffiti Removal - City Property','Graffiti Removal - External Organization','PUI Noise Complaint Case','PUI Noise General Inquiry Case')

# street surface & Maintenance
street_traffic_maint = c('Street Furniture Repair and Maintenance Request','Street Cleaning & Debris Pickup','Street Light - Out','Street Tree Work Request SR','Street Litter Can Request','Streets - General Issues','Street Light - Pole Repair','Street - Surface Water Flooding','Street - Repair','Street Light - New/Relocation','Street Light - Flat Glass Fixture Request','Street and Traffic Light - Utility Damage','Street Sign - New','Crosswalk Marking - New','Boulevard Maintenance Issues','Bicycle Route Map Request','Sidewalk - Repair','Pothole - Repair','Pavement Markings Request - New/Modify','Pavement Marking - Repair','Sewer Pipe Inquiries','Sewer Manhole Issues','Sewer General Inquiries','Sewer Design General Inquiries','Sewer Separation Inspection Cancellation Case','Sewer Utility Damage','Sewer Odour Complaints','Plumbing and Gas Inspection Cancellation Case','Snow Angel Program - Individual Volunteer','Snow & Ice Removal - City Property','Snow and Ice Removal - Sidewalk Bylaw Violation','Snow and Ice Removal - Sidewalk Bylaw Inquiry','Traffic & Pedestrian Signal - New','Traffic Calming Request','Traffic & Pedestrian Signal - Modify','Traffic Sign - Modify','Street and Traffic Light - Utility Damage','Traffic Sign - New','Traffic Count Request','Truck Violation','Residential Parking Requests','Parking Meter Requests','Abandoned Vehicle Request')

# garbage, Recycling & organics related
garbage_recycling_organics = c('Missed Garbage Pickup','Abandoned Garbage Pickup - City Property & Parks','Cart - Garbage','Illegal Dumping/Abandoned Garbage Pickup','Parks Litter Can or Cart Request','Recycling Bag Request','Missed Recycling Pickup','Missed Apartment Recycling Pickup','Recycling Box Request','Cart - Apartment Recycling','Apartment Recycling - Registration Request','Transfer Station & Recycling - General Inquiries','Blue Box and Leaf Removal Guide Mail-Out Request','Missed Yard Trimmings and Food Scraps Pickup','Cart - Green (Yard Trimmings and Food Scraps)','Green Bin Program - Feedback and General Inquiry','Collection Calendar Mail-Out Request')

# water related
water = c('Water Leaks/Breaks','Water Service Turn On/Off Request','Water Service Locate Request','Street - Surface Water Flooding','Water Work Site Complaint','Water Hydrant Issue','Water General Inquiry','Water General Work Request','Water Pressure or No Water Issue','Water Conservation Violation','Water Meter Issue','Water Damage To City Water System','Catch Basin Issues')

#animal and vegetation related
animal_vegetation = c('Dead Animal Pickup Case','Animal Control General Inquiry Case','Animal Complaint - Non-Emergency Case','Animal Cremation Case','Dead Skunk Pickup','Lost Pets Case','Holding Stray Case','Chafer Beetle Feedback','Vegetation Maintenance SR','Trees and Vegetation Encroachment - City Property','Horticulture Inquiry on Right-of-Way')

# other
other = c('Poster/Sign Removal Request','Sign - Repair','Curbside Sign - New','Curbside Sign - Modify','Banner Request','Fire Reinspection Request for Firehall','Fire Reinspection Request for Inspector','Citizen Feedback','Wheelchair Curb/Ramp Request','Wheelchair','PRB_Park Ranger SR','Building Plans Information Request','Building Inspection Cancellation Case','Licence Payment Request Case','Gone Out of Business Case','FPB_General Inquiry Case','PUI General Inquiry Case','Electrical Inspection Cancellation Case','Bridges & Structures - Repair','Secondary Suite Information Request','General Information Request SR','Election General Concerns','Occupancy Permit Information Request','Home Safety Check Request Case','Flag Request','Homelessness/Transient Issue')

and we know the categories based on:

# give class id numbers:
data$cid = 9999
for(i in 1:length(data$Case_Type)){
  if(data$Case_Type[i] %in% graffiti_noise){
    data$cid[i] = 1    
  }else if(data$Case_Type[i] %in% street_traffic_maint){
    data$cid[i] = 2   
  }else if(data$Case_Type[i] %in% garbage_recycling_organics){
    data$cid[i] = 3   
  }else if(data$Case_Type[i] %in% water){
    data$cid[i] = 4   
  }else if(data$Case_Type[i] %in% animal_vegetation){
    data$cid[i] = 5   
    data$cid[i] = 0   

Or – the categories that Joey created are as follows:

  • gaffitti & noise – 1
  • street, traffic, & maintenance – 2
  • garbage, recycling, & organics – 3
  • water related – 4
  • animal & vegetation related – 5
  • other – 0

Now you can click connect dataset:



And you’ll get a map of all 10,000+ points:

Designing the Interactive Map in CARTO

layers: Voyager is base map – both map data and labels;  calls1401_3 is the name of the geojson file.

Change the name of your map (like a mxd in ArcGIS) and change the name of your layer (to reflect data for end user)


The points reflect the 6 categories of aggregated complaint data the Joey developed. These categories have been assigned values, 0-5. Think about how the software is intrepreting the numbers that Joey has given nominal categories.

What are appropriate styles we can use to map this data?


  1. Select Style – select appropriate legend type for your nominal categories
  2. Creating your legend – enter in names that match number
  • gaffitti & noise – 1
  • street, traffic, & maintenance – 2
  • garbage, recycling, & organics – 3
  • water related – 4
  • animal & vegetation related – 5
  • other – 0


  1. select style
  2. select attributes you want to see in your popup.

Data – using SQL

Look at widget options to create charts for users. The cid widget gives us information on the number of complaint calls for each category.

cid means nothing to map users.

Toggle to the Data view and change the name of the cid column.

Toggle to data view (lower right of screen: files icon, google maps push pins)

CARTO support s or uses PostGreSQL and you are able to query the database. We can see complaints by hour with the widget, but we can also query the data for just evening calls, and save that query as a layer.

Select the SQL tab at the bottom left of this data window;  you will get a black screen, key in the following statement (in red below) after the SELECT line:

SELECT * FROM calls_1401_3 (or whatever you renamed your file to)

Go back to the map, and you will see that fewer dots are displayed and if you hover over them, they are all evening calls between 17:00 and 24:00 or midnight.

You can clear this selection (bottom of screen, click clear) and create other SQL statements, or reture to the original data set. When you go back to the main menu ((very top left arrow back) you will see either the results of your query to work with, a subset of your data, or you will be back to your original dataset.

Multiple layers – Using CSS

You can at any time Add more layers of data.

Go to the City of Vancouver open data site and add the Neighbourhood Layer. Visually, you can see what types of complaints fall in which neighbourhoods.

You can move the order of the layers, and display the evening point calls on top of the neighbourhoods.

It would be beneficial to add a transparency to the neighbourhood layer. You can make custom edits to the display of map elements with html and CSS (Cascading Style Sheets)

in your main menu, click on Local Area Boundaries, at the bottom of your window, click from Values to CSS.

Add the

polygon-opacity: 0.3;

(0 is transparent and 1 is opaque)

under the first line:

 layer {

so it looks like this:

Now you can see visually is there is any correlation between our evening calls and neighbourhoods.

 Assignment Part B

Part B Complaints 3-1-1 call interactive map 

  • Complete your Complaint Call Map using the tutorial as a guide, and using as many options as possible to enhance the interactive map for the map user. Post your CARTO interactive map on your blog site.
  • Compare and Contrast this interactive map using CARTO to the static infographic you made to visualize carbon emissions. In your assessment, discuss:
    • pros and cons regarding software you used, limitations to each, advantages of each
    • the user experience with each of the visuals
    • the readings by Aishe and Roth are relevant to this discussion:
    • Your discussion should be no longer than 600 words.

In summary – post on your blog site the Complaint Map with CARTO and your discussion.


I will not mark this – but to practice you could also create a new CARTO map of the City of Vancouver Crime data. The data is available by year.

  • What attributes does the data contain?
  • Are issues of privacy and confidentially addressed by the City with this dataset?
  • what formats are available for this data? Define each of the formats and explain the differences in these formats and what you need to consider before selecting a particular format?
  • what other datasets would be useful to visually overlay on the crime data to help see spatial coorelations etc.