Skip to content

Instantly share code, notes, and snippets.

@chriswhong
Last active October 26, 2015 22:11
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save chriswhong/f9a6f0b9c05d78f8b489 to your computer and use it in GitHub Desktop.

##Real-Time Data in CartoDB texasGIS

About Chris: Solutions Engineer, Urbanist, Mapmaker, Open Data Junkie

cwhong@cartodb.com

@chris_whong

About Andy:

eschbacher@cartodb.com

@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

###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.

architecture 001

###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.

geojson_io

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.

popplaces___cartodb_and_u_s__populated_places_-_google_search_and_microsoft_word

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)

popplaces___cartodb_1

##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 chriswhong___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: https___chriswhong_cartodb_com_api_v2_sql_q_insert_into_mytable__name_description__values___27austin_27__27coolest_city_in_texas_27__api_key_995e03a979957cc2cc0b2fced0aa735516cfcd0d

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);
}

untitled_map___cartodb

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment