CartoDB API and NodeJS

Carto DB Screenshot

Earthware were recently contacted by a client to help them build a NodeJS application which imported land registry INSPIRE land parcels into a CartoDB instance. Our solution was implemented using a combination of NodeJS, MongoDB (to hold the tracking data) and CartoDB to hold and serve the land parcel data.

This blog article will concentrate on how we used the CartoDB SQL API in NodeJS and how it can be used to create awesome applications.

Setup

Before you start you'll need your CartoDB account's API Key and Username. Your username will appear in the top right hand corner of your CartoDB dashboard and your API Key can be found by clicking the arrow next to your username and choosing Your API Keys. Also on this page is an example of using the SQL API, which we'll come to below.

You may also want to use an existing npm package, rather than writing your own or calling the api directly. We used Vizzuality's CartoDB's package. All of the below examples assume you're using this package.

Calling the SQL API

Now that you've got your api key and username and installed your chosen node package you can start calling the api.

Before sending your SQL you have to open a connection to the API:

var CartoDB = require('cartodb');

var client = new CartoDB({
	user: USERNAME,
	api_key: APIKEY
});

cartoDbClient.on("connect", function () {
	console.log("connected to cartoDB");
    
    // Call the SQL API here
});

What are we doing here? The first line simply creates a reference to the cartodb package and assigns the output of the package to the CartoDB variable, once we have a reference to the package we create a new instance of the CartoDB client class, passing through the username and api key that we retrieved before, and finally we open a connection to the API.

Once we have established a connection to the API we can simply execute our SQL against the API:

var sql = "INSERT INTO YOUR_TABLE (the_geom) VALUES (GEOMETRY)";

cartoDb.Query(sql, function (err, data) {
	
});

We'll be introducing some of the SQL concepts below but for a full reference CartoDB's own tips and tricks article is the place to head.

Importing Land parcels

Above is a simple, non-real world example of using the CartoDB SQL API, one real world example that Earthware implemented was to import INSPIRE land parcels, to do this we had to download the land parcels, extract the zip file that contains the said land parcels and then import the GML file that contains the land parcel meta and geometry data.

One of the trickiest aspects of this was going to be converting the GML gemoetry object to GEOJson to import it into CartoDB, we originally were going to write some code to handle this ourselves until we found the helpful ST_GeomFromGML POSTGIS function which converts a GML geometry object for importing into CartoDB.

For the SQL import, because we didn't know if records already existed to update or if we needed to insert new records we can use POSTGRES's UPSERT feature that can handle INSERTS as well as UPDATES:

WITH
n(id, geom) AS (
	VALUES
	(ID, GEOMETRY),
	(ID, GEOMETRY),
	(ID, GEOMETRY),
	(ID, GEOMETRY),
	(ID, GEOMETRY)        
),

upsert AS (
	UPDATE land_registry_cadastral_parcels o
	SET 
       	cartodb_id = n.id, 
        the_geom = n.geom
	FROM n 
	WHERE o.cartodb_id = n.id
	RETURNING o.cartodb_id
)
	
INSERT INTO TABLE_NAME (cartodb_id, the_geom)
SELECT n.id, n.geom FROM n
WHERE n.id NOT IN (SELECT id FROM upsert)

Gotchas

With the quantity of data being imported we came across a couple of gotchas in using the SQL API:

  • Due to the number of asynchronous requests that we were dispatching we had to override the maximum number of sockets allowed in NodeJS, to do this you have to set http.globalAgent.maxSockets at the top of your node script. we set it to 10000.

  • Also due to the number of requests being sent, and the size of them we had to change a couple of lines of code in the cartodb package to send the cartodb SQL as part of the request's body rather than as a URL parameter. To do this change line 171 of cartodb.js from using .query to using .send.

If you'd like to hear more about INSPIRE land parcels, importing GML, CartoDB or NodeJS then please get in touch!