How to: get started with SQL in Carto and create filtered maps

map carto

Today I will be introducing my MA Data Journalism students to SQL (Structured Query Language), a language used widely in data journalism to query databases, datasets and APIs.

I’ll be partly using the mapping tool Carto as a way to get started with SQL, and thought I would share my tutorial here (especially as since its recent redesign the SQL tool is no longer easy to find).

So, here’s how you can get started using SQL in Carto — and where to find that pesky SQL option.

3 ways to use SQL in Carto

There are actually three ways to use SQL queries in Carto that I know of. These are:

I’ll just cover the first two.

Before you begin, download some crime data from Data.police.uk. I’ll be using this for my examples.

Applying SQL queries within a dataset in Carto

Here’s a brief summary of the steps when you want to query a dataset in Carto

  1. Open Carto
  2. Upload your dataset
  3. Open the dataset
  4. Toggle the switch at the bottom left from ‘Metadata’ to ‘SQL’
  5. Write your SQL query, and apply it
  6. Create a map from the query if you want

Start by logging in and go to the ‘datasets’ section (there’s a button in the upper right corner).

Click on New dataset.

Click on Browse and find the data.

Click Connect dataset to upload it.

After a moment you should be able to see the data. There might be some annoying empty columns but those can be ignored. Look out for columns that are in green (numbers) and those that aren’t (text). This will affect the sorts of queries you can make against those columns (you can only sum and average number columns, for example)

Now, while inside your data, look at the bottom of the page for a toggle switch marked ‘METADATA’ and ‘SQL’.

toggle switch

Click on that toggle switch to move it from METADATA to SQL. It should light up green and the bottom half of the screen should now show a dark area where you can type a SQL query:

toggle on

In fact, a SQL query will already be there in that box. It will look something like this:

SELECT * FROM table_2017_09_west_midlands_street_1

This means select all columns from the table with that name (this dataset).

You can now adapt this query to query your data in different ways. Notice that as you start typing a column name it will bring up a menu to make it easier for you to select the right name. Here is one example of a query which generates a pivot table of crime totals by type:

select crime_type, count(*) from table_2017_09_west_midlands_street_1 group by crime_type order by crime_type desc

That should give you an idea of what types there are. You can choose one of those to write a new query which filters the data to only show one type:

SELECT * FROM table_2017_09_west_midlands_street_1 WHERE crime_type = 'Anti-social behaviour'

Click CREATE MAP to see just those crimes shown.

Applying SQL queries within a Carto map

If you’ve already created a map and want to create a SQL query from within there, you just need to go into the data within the map.

First go into your Carto maps view and open the map. Once opened, the map will occupy the right two thirds of the screen, but the left third of the screen should contain a list of all the layers of that map (you may have only one).

Click on the layer containing the data you want to query.

That left area should now change to show ‘STYLE’ – one of 5 tabs including ‘DATA’, ‘ANALYSIS’, ‘POP-UP’ and ‘LEGEND’.

Click on the ‘DATA’ tab.

At the bottom of this left hand area you should now be able to see a toggle switch with VALUES and SQL. Click on that to toggle it to SQL.

toggle map view

Once again, when toggled it will turn green and that area of the screen will turn dark. A ready-made SQL query will also be in this window that you can start to adapt:

map view toggle on

Change that query to:

select * from table_2017_09_west_midlands_street_1 where crime_type = 'Anti-social behaviour'

And you will only see the crimes shown that match the query.

Click APPLY to apply it.

Now you can leave the data window and the query should still be applied. Note that when you are looking at your map again you should now see a ‘SQL’ box on the layer where you queried it.

sql badge

That’s it. You can always change your query by going back into the layer, and then into the DATA view. When you’re happy with it, publish.

Using SQL queries elsewhere

Carto is just a good place to start making SQL queries — there are dozens of other contexts where you might use them too. Here are some places to go next:

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s