How to Use Torque Outside of a Full CartoDB Setup

TorqueTalk.md

What is Torque?

Torque is an open-source JavaScript library from CartoDB for time series map visualization.

How are Torque Visualizations Created?

• The simplest way to create a Torque time-lapse animation is to upload the data to a CartoDB account, then select the Torque option from the visualization wizard. Here's an example of a visualization in CartoDB of L.A. rapid buses.

• The Torque JavaScript library can also be used to define a Torque map layer that can be added to a Leaflet or Google Maps base layer. The layer is defined with the user name and public table of a CartoDB account to use, and some special CartoCSS that defines aspects of the animation. The CartoDB Torque tutorial page contains an embedded map with a Torque animation.

• A third option for using Torque, which we will explore today, is to use the library in conjunction with two other CartoDB JavaScript libraries to connect to data that is not uploaded to a CartoDB account, but is rather in our own PostGIS-enabled PostgreSQL database.

How Does Torque Work?

Torque is part of the suite of projects that make up the CartoDB web service.

It's designed to work with the other projects, particularly the Windshaft tiler and SQL API service.

Okay, How Does CartoDB Work?

CartoDB has several open-source components:

• The web site itself, which manages user-name references by giving each user a subdomain that is the user name.

• Each user gets their own PostgreSQL role and database with the PostGIS extensions installed. There are a number of triggers installed, as well as extra functions from this project.

• The database gets queried via their JavaScript SQL API. The SQL API authenticates with the database by getting the user account name from the subdomain, using an API key, and finding current user credentials in Redis storage.

• Map tiles are served via the JavaScript Windshaft library. Windshaft works quite well on its own outside of a full CartoDB installation.

So How Do I Use Torque Without the Other Stuff?

Torque only really needs the Windshaft tiler and SQL API to work. Torque and Windshaft can be used without modification.

Once the SQL API is set up to operate without user credentials from Redis or user and database names from the subdomain, we're ready to go!

Here's a fork of the SQL API that has the authentication parts removed.

Instead, the database user, password, and database name are configured directly in the configuration file. For permissions, we can rely on using a database role that has read-only access to just the tables with Torque data.

How Do I Set Up the Database Table?

Four columns are needed:

• A unique identifier (used for aggregation functions)

• A timestamp

• A PostGIS Point column, named geom, in EPSG 4326. (The SQL API has a hard-coded reference to the geometry column for the table. It was the_geom, but I renamed it in my fork.)

• A PostGIS Point column, named the_geom_webmercator, in EPSG 3857. (The Torque library has this column name hard coded for use in querying Windshaft for tiles.) I've found it necessary to create this column by using the CartoDB function CDB_TransformToWebmercator rather than simply using ST_Transform.

• There are a number of CartoDB functions in this project that Torque and the SQL API will expect to be present. CDB_QueryStatements.sql and CDB_QueryTables.sql are definitely needed, as well as CDB_TransformToWebmercator for the table creation.

Okay, cool! Show me some examples!

• Bicycle trips logged using CyclePhilly: here and here

• SEPTA real-time bus locations

• Boston real-time bus locations -- this one takes a minute to warm up

• The CartoDB GitHub account has a page with interactive controls where you can change the CartoCSS and the animation settings.

@flibbertigibbet this was very useful many thanks. I managed to use this fork and get the SQL API reading my own PostGIS database, however, when I request format=GeoJSON I do not get valid GeoJSON back from the API.

Any ideas why this might be? Thanks