About Chris: Solutions Engineer, Urbanist, Mapmaker, Open Data Junkie
@chris_whong
About Andy:
@mrephysics
###Objective
In this workshop, we will explore various ways of getting real-time or regularly-changing datasets into CartoDB for mapping. We will cover CartoDB's sync table functionality.
###Schedule
- 1:00-1:30 - Software Installation - Get your environment set up so we can write some code
- 1:30-2:30 - CartoDB Crash Course, Architecture, & Sync Tables -- Create your free CartoDB academy account. -- Use sync tables to create a map using NWS Data
- 2:00-2:30 - Try it out - Create a sync table dataset and a simple map
- 2:30-3:00 - Building API calls for CartoDB's SQL API
- 3:00-3:15 - Try it out - Construct a Simple API call to insert values into CartoDB one row at a time
- 3:15-4:00 - Real-time Bus Data - Scripting the push of data into CartoDB using the SQL API
- 4:00-5:00 - Try it out - Build a node script to push data into CartoDB every 30 seconds
###Software
- Chrome Web Browser
- JSONView Chrome Extension
- Sublime Text Text Editor
- Node.js (javascript scripting environment)
- Make sure you have a CartoDB Academy Account
###Architecture
CartoDB is essentially a PostGIS database in the cloud, with a tile-based map renderer. The APIs allow for dynamic map-building, data import/export, and data manipulation.
###Building a Basic Map
Let's build a simple map in CartoDB that combines static data with synced data. The static data will be the locations of large cities in the U.S.
####A quick look at geoJSON
geoJSON is a web-friendly vector data format. It's really easy to transfer as plain text via files or APIs, and stores points, lines or polygons. It also stores properties (a.k.a. attributes) for each vector geometry.
Go to geojson.io to create some geojson from scratch.
Download this geoJSON of world cities, which we'll use for our map. https://www.dropbox.com/s/1f19agwl5i1lvz9/popplaces.geojson?dl=0
####Severe Weather Alert Areas I've created a service that converts the NWS' ATOM Feed into GeoJSON that CartoDB can import. Check out https://polar-reef-4351.herokuapp.com/ to see the geoJSON feed. If you like, you can test it out at geojsonlint.com (A cool site that is useful for testing whether geojson is valid)
What you're seeing here is the output of a custom node script that converts the NWS' ATOM Feed into geoJSON (http://alerts.weather.gov/cap/us.php?x=1). In order to use CartoDB sync tables, the data must be publicly accessible on the web, and must be in a format that CartoDB can import automatically. The NWS XML feed is not in a valid geospatial format, so extra work was necessary to get it ready for import and sync.
####Try it out I will demonstrate building the following map. When I'm done, you can try it yourself
- Import City Data - Drag the city geojson file into your CartoDB Datasets list
- Import NWS Data as a Sync Table - Set it to refresh hourly
- Create a map with both layers, use the wizards to style it.
With a little SQL, we can tell the map to only show cities that are located inside a NWS alert area!
SELECT a.* FROM chriswhong.popplaces a, chriswhong.polar_reef_4351_herokuapp b WHERE ST_WITHIN(a.the_geom,b.the_geom)
##Using the SQL API to Push Data into CartoDB
First we'll use Postman to explore how inserting data using CartoDB SQL API works.
Create an empty dataset in CartoDB You will see that the empty dataset already contains two generic columns called 'name' and 'description'. We'll use the SQL API to insert data into these columns.
To insert data into the new table, we need to build out an API call. Here's the template for interacting with a table using the SQL API:
https://{account}.cartodb.com/api/v2/sql?q={SQL statement}&api_key={Your API key}
All we need to do is replace the placeholders in the template and we can run API calls right from our web browser. You'll need to copy your API key from your CartoDB Account. To add a new line to the table, use this SQL statement:
INSERT INTO mytable (name,description) VALUES ('Austin','Coolest City in Texas');
Combining that with the API call template gets us
https://chriswhong.cartodb.com/api/v2/sql?q=INSERT INTO mytable (name,description) VALUES ('Austin','Coolest City in Texas')&api_key={Your API key}
When we paste this in the browser's URL field, we're executing this SQL, and the server will respond letting us know how many rows were inserted:
Try it a few times, changing the values that you insert. This is a simple example, but we'll build on it in the next example and script the creation of new rows from a real-time data source.
###A custom Script for Real-time Data
Now that we know how the SQL API works, we can look into scripting the push of data into CartoDB from a real-time source.
The bus sytem in NYC makes real-time data available via an Open API, but the response is JSON, not geoJSON, so CartoDB can't import it without a little extra work. Here's an example api response for the B52 bus in Brooklyn:http://api.prod.obanyc.com/api/siri/vehicle-monitoring.json?&LineRef=MTA%20NYCT_B52&key=adf3b381-85b5-48b9-a049-32c335108f6e
Once again, I've written a proxy script that converts the output to geoJSON, and also grabs only a small portion of the data returned from the source API. Here's the same real-time data for the B52 bus, but coming from my proxy api: http://nycbusproxy.herokuapp.com/api/b52
Each point feature is a live vehicle location. Now we can use another script to pull data from this API every 15 seconds, and push a row to cartoDB for each vehicle!
Before we can run the script, we need to create a new table in CartoDB. Create a new table named 'bus' and create new columns with the following types: -line - text -direction - text -timestamp - date -bearing - number
Here's the script. https://github.com/chriswhong/bus2cartodb
Copy and paste the contents of script.js to your working directory.
From the command prompt or terminal, type npm install Mustache
and npm install request
to install dependencies.
Edit the script in Sublime text. Copy and paste your CartoDB API key from your cartoDB account, set your tablename and account name. Save your changes.
From the command prompt or terminal, type node script.js
. If all goes well, you should see some SQL queries appear in your terminal. Let's go back to the dataset and take a look.
###Querying and styling the Map
Now that we are getting fresh data every 15 seconds, we can build a map that makes use of it. Remember, we set the description
field to 'fresh' with each new insert, and set all of the old description
values to null. This means we can query to show only the latest data using SELECT * FROM bus WHERE description = 'fresh'
We can also add another dataset that has the line feature for the B52 bus' route. Here's the geojson file: https://www.dropbox.com/s/c8h6gygqy7fqrox/b52.geojson?dl=0
With some CartoCSS, we can use a custom marker, and even change its orientation based on the bearing
column we included in our data import! (A bearing of zero indicates the vehicle is facing due east. Bearing increments counterclockwise, so 90 would be due north)
#bus{
marker-file: url(http://com.cartodb.users-assets.production.s3.amazonaws.com/simpleicon/placeholder4.svg);
marker-fill-opacity: 0.9;
marker-line-color: #FFF;
marker-line-width: 1;
marker-line-opacity: 1;
marker-placement: point;
marker-type: ellipse;
marker-width: 18;
marker-fill: #136400;
marker-allow-overlap: true;
marker-transform: rotate(90-[bearing],0,0);
}
As long as your script continues to run, the SQL you have applied will always include only the latest vehicle locations, and the cartoCSS will make sure your markers are pointed in the right direction. Every time you reload the map, you should see the latest and greatest real-time bus information.