Using CARTO For Creating Data Driven Web Pages

Topics Covered

Recently at Radish Lab I created an interactive resource guide for the Women's City Club of New York. The goal of the guide is

to help individuals and organizations access needed low- or no-cost social services, programs, and resources available throughout the five boroughs of New York City. Most entries include address, phone number and website, as well as the neighborhood and community board in which the agency is located.

WCCNY asked to have the guide exist in a searchable and filterable format, so jQuery DataTables seemed like a practical solution. Additionally because the data is geographic in nature (the majority of the data has detailed street address location information) this meant it could be mapped. As a result I decided to use CARTO to host and map the data.

What is CARTO?

Put simply CARTO (formerly known as CartoDB) is web platform that focuses on making it easy to map and analyze geospatial data. It is built on top of a suite of open source software for database management, GIS, and web-mapping. With CARTO you can drag and drop data into its dashboard and instantly start prototyping an interactive map data-vizualization or querying your data with SQL. They've built quite a large following in the GIS, web-development, and Cartography world, provide plenty of educational materials and tutorials to get you started, and have great customer service and support.

Scripting Your Data Wrangling Workflow

One of the great things about using CARTO is that it provides the full functionality of using PostgreSQL (also commonly referred to as Postgres), a very popular open-source relational database, and PostGIS, an extension for Postgres that allows for working with geospatial data --that is data that has a geometry data type. However, just because CARTO has the ability to host geospatial data doesn't mean you have to use it for that. You can use it like any plain old database and then use their tools to integrate your data with your webpage without having to set up a server running a database yourself!

One of the common issues of working with data is having to run similar tasks repeatedly on your data, such as updating, formatting, etc. This comes up quite frequently at Radish when we work with clients to create web-interactives that are data driven. Rather than using software such as Excel and having to do a lot of repeated clicking and macros running, we can write SQL code that can be executed each time we need to repeat a task. Then we can simply copy and paste the SQL code from a text editor into the SQL window in CARTO's dashboard, and run it. You can even do this with a whole series of commands.

A simple task might be to remove any trailing white space from a field that contains text:

-- remove trailing whitespace
UPDATE table_name SET field_name = regexp_replace(field_name, '\s+$', '', 'g');

Or perhaps you may want to create additional columns based on existing data in a table that will be used by your web app but not displayed to the user. One such example is to simplify data from a field, such as for boroughs:

-- add a column to store the borough code:
ALTER TABLE table_name ADD COLUMN borough TEXT;

-- add values to the borough column
UPDATE table_name SET borough = 'BK' WHERE addressline2 ILIKE '%brooklyn%';
UPDATE table_name SET borough = 'MN' WHERE addressline2 ILIKE '%new york%';
UPDATE table_name SET borough = 'BX' WHERE addressline2 ILIKE 'bronx%';
UPDATE table_name SET borough = 'QN' WHERE addressline2 ILIKE 'queens%';
UPDATE table_name SET borough = 'SI' WHERE addressline2 ILIKE 'staten island%';
UPDATE table_name SET borough = 'N/A' WHERE addressline2 IS NULL OR addressline2 ILIKE '%n/a%';
UPDATE table_name SET borough = 'N/A' WHERE borough IS NULL;

Then any time you need to update the data in CARTO you can just copy and paste your SQL code into the dashboard SQL window and re-run your workflow. As soon as you update your data in CARTO, the changes will be reflected on any webpage you have that is using that data.

Using the CARTO SQL API

On top of a intuitive and easy to use dashboard, CARTO provides a RESTful SQL API endpoint which allows you to pass SQL queries to your data tables and get back the data in JSON format. As long as your table is "public" in your account you do not need an API Key to perform SELECTqueries. You may also perform UPDATE, DELETE, and INSERT queries with an API Key generated from your CARTO account if you'd like. Just be sure to not include your API Key anywhere that is publically visible on your website!

At the time of this writing the CARTO SQL API endpoint is in the following format:

https://{account}.carto.com/api/v2/sql?q={SQL statement}&api_key={Your API key}

You would simply replace {account} with your CARTO account name and {SQL statement} with your SQL query, eg:

https://my_account_name.carto.com/api/v2/sql?q=SELECT * FROM my_table

The Response will be in JSON format and have the following structure:

{
"rows": [
{...},
{...},
{...},
{...},
{...},
{...},
{...},
{...},
{...},
{...},
...
],
"time": 0.01,
"fields": {...},
"total_rows": 1649
}

Each row has data stored in an object with the column name as the key and that column's respective row as the value:

{
"nameoforganization": "Community Parents, Inc.",
"neighborhood": "Bedford-Stuyvesant",
"addressline1": "90 Chauncey Street",
"addressline2": "Brooklyn, NY",
"zipcode": "11233",
"communityboardnumber": "3",
"phonenumber": "718-771-4002",
"alternativecontact": "",
"website": "http://www.headstartsbc.org",
"category1": "Pre-School & Childcare",
...
}

For the resource guide we are building for the Women's City Club I pass JSON data from the CARTO SQL API to the jQuery Data Table API to render a searchable and interactive HTML table:

// create the dataTable from the CARTO SQL API response
var dtable = $('#data-table').DataTable({
ajax : {
url : queryURIencoded,
type : 'GET',
dataSrc : 'rows'
},
order : [[0, "asc"]],
columns : dtableColumns
});

Where queryURIencoded is a variable containing the CARTO SQL API endpoint with the SQL query I've provided, URI encoded.

Using the CARTO JS Library

And why would they call it CARTO if you couldn't make a map with it? CARTO makes that easy with their javascript library so you can integrate your data hosted on CARTO with popular web-mapping API's such as Leaflet JS, Open Layers, and yes even Google Maps. You can read more about the library in their documentation and learn how to use it from their tutorials.

For the Resource Guide we are building this is how the data being displayed on the map changes, as well as the map's zoom level and extent, after the user applies filters through the UI:

[i]

.reverse(); bounds.push(L.latLng(latlng[0], latlng[1])); } map.fitBounds(L.latLngBounds(bounds)); }); }

When the user applies filters to the dataset, the Data Tables API sends an array of ids from the filtered rows to the the updateMap function. The ids are then joined using using a comma as a delimiter and inserted into an SQL WHERE clause. A second query is created that will grab the filtered feature's geographic extent using the PostGIS functions ST_AsGeoJSON, ST_SetSRID, and ST_Extent. The first query eventually is passed to the CARTO javascript library to update the features being displayed on the map. The second query is passed to the function getBounds which performs an AJAX request to the CARTO SQL API endpoint. The features returned from this query are then passed to Leaflet's map.fitBounds method which centers the map and changes the zoom level to fit the map to a specified geographic area.

As you can see, using CARTO as a database to interact with for your web interactive offers a whole lot of helpful functionality. You don't have to use it specifically for mapping purposes, but it sure doesn't hurt if you are working with geospatial data.