Sunday, 18 November 2012

How to minify GeoJSON files?

You can't do web mapping these days without knowing your GeoJSON. It's the vector format of choice among popular mapping libraries like Leaflet, D3.js and Polymaps. Size matters on the web, especially if you want to distribute complex geometries, like the world's countries. The challenge is even bigger if you want to target mobile users - or support web browsers with poor vector handling (IE < 9). This blog post will show you how to minify your GeoJSON files before sending them over the wire.

The first thing you should do is to generalize your vectors so they don't contain more detail than you need. In a previous blog post, I was able to remove 90% of the coordinates without loosing to much detail for map scale I wanted to use. This will of course have a great effect on the file size.

Today, I'm going to use country borders from the Natural Earth dataset. These datasets are already generalized for different scales (1:10m, 1:50m, and 1:110 million), so I'll use them as they are. The 1:110m (small scale) and 1:50m (medium scale) shapefiles will cover the needs for the thematic world maps I plan to make:

The 110m and 50m country polygons shown in QGIS.

Let's open the datasets in QGIS. If you look at the attribute table you'll see that each dataset contains 63 attributes, which makes them very versatile. For your web maps, you probably need just a few of the attributes, and you should remove the ones you don't need. I'm keeping the country name and the ISO 3166-1 country codes (alpha-2, alpha-3, and numeric), which can be used to link country geometries to statistical data. 

Only keep the attributes you need.

Next, we can convert the shapefiles to GeoJSON with ogr2ogr:

ogr2ogr -f "GeoJSON" -lco COORDINATE_PRECISION=1 ne_110m_admin_0_countries.json ne_110m_admin_0_countries.shp

ogr2ogr -f "GeoJSON" -lco COORDINATE_PRECISION=2 ne_50m_admin_0_countries.json ne_50m_admin_0_countries.shp

The important thing is that I'm only keeping one decimal (coordinate precision) for the 110m dataset, and two decimals for the 50m dataset, which is sufficient for my map scales. This will reduce the size of the GeoJSON files by more than half. The size of the 110m GeoJSON is now 207 kB and the 50m version is 1,897 kB. But we can do better.

The files contains a lot of whitespace, which is waste of space. I planned to use Sublime Text to remove the whitespace, but it were not able to handle the 50m GeoJSON file, so I switched to Notepad++. I used these regular expressions:

Find: "([^a-z.]) "
Replace: "$1"
This will remove all whitespace which is not succeeding a letter or a dot, which are present in country names.

Find: "\n,"
Replace: ","
Remove line breaks (keeping some for readability).

Find: "\.0([,\]])"
Replace: "$1"
Remove trailing zeros.

This will reduce the file size of the 110m GeoJSON from 207 to 156 kB, without loosing any data quality. More than 400k of whitespace characters was removed from the 50m GeoJSON file, reducing the file size from 1,897 to 1,481 kB.

If your web server is supporting gzipping on-the-fly, the 110m GeoJSON will end up being 45 kB and the 50m version will be 430 kB. Not bad!

And if this is too much work, you can always download the final GeoJSON files on

NB! Mike Bostock’s TopoJSON would allow us to compress the GeoJSON even more, while preserving topology (shared borders between countries) - but we would need to use a map client supporting the format. Looks promising!

Sunday, 4 November 2012

Mapping New Zealand - a summary

I've had a fantastic two months study trip to New Zealand. Unfortunately, I had to go back to Norway this week to fill up my bank account - just when the summer was arriving in New Zealand. I'm going to miss the beautiful country with its great people.  

New Zealand is the perfect country to map, as an isolated country surrounded by a vast ocean, and because of all the free data available. I hope my Mapping New Zealand blog series has been useful for others as well:

It's a lot of exciting things happening on the New Zealand mapping scene, and the Kiwis are very welcoming people. I want to thank all the nice people I met on my journey, who was very willing to share their knowledge and experiences:

I've been a real map nerd in New Zealand, but I had lots of time to explore the country too. Here are a few of my photos: 


New Leaflet plugin to handle multiple TileMill layers

My setup for the population density map of New Zealand made it easy to create new choropleth maps with New Zealand census data. This blog post explains how you can use Leaflet to switch between multiple interactive layers created with TileMill.

I wanted to create a map of the social geography of New Zealand, using the Index of Deprivation from the Department of Public Health, University of Otago. I downloaded a Excel sheet containing data for the 2006 census area units, which I also used for my population density map. I simply added the data to the same SQLite database, and created the map using the same techniques described in two previous blog posts (1, 2).

The Index of Deprivation is constructed from nine Census 2006 variables, and provides a summary deprivation score from 1 to 10. A score of 1 is allocated to the least deprived 10 percent of areas, and 10 is allocated to the most deprived 10 percent of areas. You'll find more information about the index in the Atlas of Socioeconomic Deprivation in New Zealand.

Wax allows you to easily add an interactive TileMill map to Leaflet or other mapping libraries. Adding more than one interactive layer is not that straightforward, so I wrote a Leaflet plugin:

This plugin allows you to switch between various layers (interactive or not) and it will automatically load and display map legends, and remove the elements when they're no longer needed. It's easy to use the plugin:

Include a wax property when you create a tile layer, containing a TileJSON object or an URL to a TileJSON file.  

var population = L.tileLayer('tiles/nz-popden/{z}/{x}/{y}.png', {
  attribution: 'Statistics New Zealand',
  wax: 'tiles/nz-popden.tilejson'

After you've created the map you simply add:

That's it! :-)

You can try to switch between the various basemaps using the layers control below:

Fullscreen map

Creating map labels with TileMill

It's one obvious thing missing from my maps of New Zealand: map labels. This blog post will show you how to create a transparant layer with map labels with TileMill, which can be added as an map overlay in Leaflet or other mapping libraries.

To create map labels, you need a point dataset containg at least a position and the label text. As we'll see later, information about type of place, importance etc. will also be useful. For my New Zealand maps, I'm using a dataset from LINZ Data Service: NZ Geographic Names (Topo, 1:500k)

I started by using QGIS to convert the shapefile into SQLite database (by right-clicking on the layer name in QGIS and select “Save as…”). This allows us run SQL queries against the data in TileMill. The dataset includes three attributes or columns, - name, size and a code describing the type of place:

I'm opening the SQLite file in TileMill:

I'm using this SQL query to load the data from the SQLite database:  

SELECT * FROM nz_labels ORDER BY size DESC

This will sort the labels after size, in descending order. I'm assuming that larger size means more important labels, and this will instruct TileMill to start with the most important labels (there is not enough space to show all labels on all zoom levels). Remember also to include the SRS projection string for the data source (NZTM2000):

+proj=tmerc +lat_0=0 +lon_0=173 +k=0.9996 +x_0=1600000 +y_0=10000000 +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +units=m +no_defs

CartoCSS includes a provides several ways to style map labels or text, and I haven't got the time to explore all the possibilities. This is the my CartoCSS for zoom level 5-7:   

I start by defining the fonts I'm going to use. It's a convention in cartography to label water features (lakes, sea, rivers) in serif italic faces, but small serif fonts on screen can be hard to read.. New high resolution screens, like Apple's Retina display, will probably make serif fonts more popular for web maps. You can use TypeBrewer to test various font combinations.  

I'm starting with the places marked as "METR" (probably metropolitan areas, although using this term in New Zealand sounds a bit strange...). I'm aligning the labels (text-horizontal-alignment) to place them over the ocean, so they're not obscuring the map. A halo is added around the labels (text-halo-fill / text-halo-radius) to make the text visible on top of various basemaps.

Auckland, Wellington, Christchurch and Dunedin are the only labels shown on zoom level 5 (in the Web Mercator projection). I'm then adding towns on zoom level 6, and populated places on zoom level 7.

Labels shown on zoom level 6.

The minimum distance between each label (text-min-distance) is 30 pixels to avoid label collisions. This means that many towns or populated places will not be drawn because it's not enough space. More labels will be revealed as you zoom in. Although I sorted the features after the size attribute, it's probably not enough to make a good selection of labels for each zoom level. I'm sorry if I left out your town! :-)     

I'm continuing like this until zoom level 12, gradually adding more labels for features like suburbs, lakes, mountains etc.

The map labels were exported as a separate map using the MBTiles format, and added to my Leaflet map as a map overlay. This is the result:

Fullscreen map

Exploring the MapBox stack: MBTiles, TileJSON, UTFGrids and Wax

In my last blog post, we created a population density map of New Zealand using QGIS, SQLite and TileMill. Today, we’re going to publish this map to the web using various MapBox inventions. I'll also show you how to publish an interactive TileMill map on your own web server using some PHP and JavaScript wizardry. 

I love MapBox. The team behind this platform has created a series of new specifications, allowing us to create fast, good looking and interactive maps. The downside is the limited support for other map projections than Web Mercator.

TileMill allows you to add legends and tooltips to your maps. I’ve added a legend to my population density map with a HTML snippet describing the map and the color scale.

The tooltip shows when the user hovers over or clicks on the map. It allows us to show dynamic content - additional data, images, charts - for each map feature. I want to show the name, total population, area and population density for each feature:

The data fields for the layer are wrapped in curly Mustache tags. These tags will be replaced by data when you interact with the map. You can use the full Mustache template language.

The easy way to publish this map is to upload it to MapBox Hosting, and use the embed code provided. If you want to publish your map on your own web server, this is an alternative route:

To export an interactive map from TileMill, you need to use the MBTiles format. This is an innovative SQLite-based format specification capable of storing millions of map tiles in a single file. The format is also supported by various 3rd-party applications, and I'm sure we'll see a greater adoption in the future.

Within the MBTiles file, the map legend, the tooltip template and information about map extent, zoom levels etc. is stored in a format named TileJSON. This is also an open specification, providing a consistent way of describing a map, making it easier to load and display a map the way it’s meant to be seen.  The TileJSON for my map looks like this:

If you add interactivity to your map (tooltips), your MBTiles file will also include the most impressing part of the MapBox specifications: UTFgrids. This JSON-format allows us to add thousands of interactive points or polygons through interactivity data grids, and it will even work in older browsers with limited support for vector data. 

So how do we turn our MBTiles file into an interactive map? Previously, I've used MBUtil to extract the contents from MBTiles into a directory structure. But by doing this, we loose the benefits of the MBTiles format, like storing a map in a single file and dealing with redundant images. What we need is a script on our web server that will extract content from our MBTiles file on demand. I decided to try a PHP script from infostreams (this is probably not the most scaleable solution). The script supports the full MBTiles specification, including TileJSON and UTFGrids. Installation is simple: just put the .php file and the .htaccess file in the same directory as your .mbtiles files. The .htaccess file includes a rule that rewrites requested URLs on the fly, so the map data is available un URLs like:
So when we have our backend sorted, how can we recreate our interactive map with Leaflet or other JavaScript mapping libraries? This is way the MapBox team created Wax, which is a client implementation of the MBTiles interaction specification. You just include the wax script together with your mapping library of choice, and then you can add interactivity with a few lines of code:

I've also done some extra JavaScript coding to allow switching between various interactive map layers. I'll save that for a later blog post.

The Leaflet map looks like this (there seems to be an issue with the latest Wax distribution and Google Chrome):

Fullscreen map

Friday, 26 October 2012

Mapping the population density of New Zealand with QGIS, SQLite and TileMill

There are over 4.4 million people living in New Zealand, but they’re not evenly distributed across the country. On my travels, I’ve been to some very remote areas like Doubtful Sound in Fjordland with a permanent population of one! Where do the New Zealanders live? Let’s create a population density map of New Zealand.

Doubtful Sound

According to Lonely Planet, 63% of New Zealanders live on the North Island, 20% on the South Island, 10% in Australia, 5% in the rest of the world, and 2% are travelling! The area of New Zealand is 268,021 km2 (Norway has 385,252 km2), which means there are about 16.5 New Zealanders per km2. Norwegians have a bit more space, we're only 15.5 persons per km2.


I want to map the population density as a choropleth map, using darker colors for higher densities. But which units or geographical areas should I use? The regions (territorial authorities) I mapped in my last blog post are too big to show the population density of the country. Instead, I decided to use area units, which are non-administrative areas that are in between meshblocks (the smallest geographic unit for which statistical data is collected by Statistics New Zealand) and territorial authorities. In urban areas, area units normally contain a population of 3,000–5,000.

The census is the official count of how many people and dwellings there are in New Zealand. It takes a snapshot of the people in New Zealand and the places where we live. The last census was in 2006. There was supposed to be a new census in 2011, but it could not be completed due the national state of emergency after the Christchurch earthquake. A new census is scheduled for 2013.

I therefore decided to download the area units used for 2006 census. These can be found on I used QGIS to removed ocean, lakes, inlets and tidal bay areas where no or very few people live – which gives me the shape of New Zealand the people recognize.

The yellow area shows the extent of the original dataset, while the red color shows the area units after removing osean, lakes, inlets and tidal bay areas.

I’ve also used the field calculator in QGIS to calculate the size (in square kilometers) of each area unit polygon.

The dataset is in the New Zealand Transverse Mercator 2000 (NZTM2000) projection, which is using a metric coordinate system. The formula to calculate the area in km2 is $area / 1,000,000. There are one million square meters in a square kilometer.

The largest area unit is Fjordland with 8287 km2. The smallest area unit is
Motuopae Island in Tauranga, with less than 0.03 km2.

We can save the dataset as a SQLite database by right-clicking on the layer name in QGIS and select “Save as…”.

Using SQLite will allow us to add the population data to the same file.

Now that we have the area units, we need some population data covering the same geographical areas. Luckily, getting statistical data in New Zealand is as easy as getting geospatial data. I used the beta release of NZ.Stat to download estimated population data for 2006-2011.

It was not possible to download the dataset as a CSV file when I tried, so I downloaded the Excel version. I then created a CSV file with this format (first line is the column names):

500202;Karikari Peninsula-Maungataniwha;4350;4320;4300;4320;4370;4350
500203;Taipa Bay-Mangonui;1610;1590;1570;1560;1550;1540
500206;North Cape;520;500;490;480;460;450

I used SQLite Database Browser to import the population dataset to the file containing the area units (File -> Import -> Table from CSV file).

Finally, we can use TileMill to create population density map. You can create a layer from a SQLite database like this:

I'm using this SQL query to join the area units and the population data:

SELECT OGC_FID, au_no, au_name, geometry, area, pop2011, (pop2011/area) AS popdens FROM nz_area_units_2006_census AS area JOIN population ON au_no = no

The query is also calculating the population per km2 (pop2011/area), which will be our mapping variable. Also note that you need to specify the SRS projection string for the dataset (NZTM2000):

+proj=tmerc +lat_0=0 +lon_0=173 +k=0.9996 +x_0=1600000 +y_0=10000000 +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +units=m +no_defs

I've used a 9-class yellow-orange-brown color scheme from ColorBrewer to represent increasing population density.

This is my CartoCSS:

The map looks like this:

Recommended reading: Working with TileMill and SQLite (MapBox)

I'm going to create an interactive version with a map legend in my next blog post. Stay tuned!

Monday, 22 October 2012

Mapping the regions of New Zealand with MapShaper and Leaflet

There are 16 regions of New Zealand, and during my trip I’ll be visiting 15 of them (I’m saving the Taranaki region for my next visit). This blog post shows how you can download and simplify region boundaries, and add them to a map as an interactive layer.

The region is the top tier of local government in New Zealand. Eleven are governed by an elected regional council, while five are governed by territorial authorities (the second tier of local government) which also perform the functions of a regional council. The current regions came about in 1989, when they replaced more than 700 boards which had been formed in the preceding century.

The geographic extents of the regions are largely based on drainage basins, following drainage divides such as the Southern Alps. 

The dataset I’m using is from Statistics New Zealand, the national statistical office. has made life easier by collecting geographic data from Statistics New Zealand on their portal. I’ve downloaded a polygon dataset from the last census in New Zealand (2006).

The dataset includes more than 60,000 coordinates used to draw the boundaries for each region. As I’m going to display the vector data directly in the web browser, I should simplify the dataset by reducing the number of coordinates while preserving the shape of the regions. Leaflet has built-in support for vector simplification (to make the map more responsive), but you shouldn’t rely on this alone as the data still has to be loaded and processed by Leaflet.
You can do vector simplification with QGIS (Vector -> Geometry Tools -> Simplify geometries), but there is a better tool, MapShaper (about), which gives you more options and preserves topology (shared borders). I loaded the shapefile into MapShaper and set the simplification level to 50% using the Special Visvalingam method. This will reduce the number of coordinates by 90%, but you still have to zoom in very far to see the difference.

Next, I’m converting the simplified shapefile to GeoJSON with ogr2ogr:

ogr2ogr -t_srs EPSG:4326 -f "GeoJSON" -lco COORDINATE_PRECISION=3 nz-regional-councils-simplified.json nz-regional-councils-simplified.shp

By restricting the number of coordinates (COORDINATE_PRECISION=3) to the map scale or number of zoom levels, you can further reduce the size of the dataset.

I’m assigning the GeoJSON object to a JavaScript variable (regions), and this is the code required to create a Leaflet layer with custom style, region highlighting and popups:

The map looks like this:

Fullscreen map

Saturday, 20 October 2012

Mapping New Zealand: Creating a road map

I’ve travelled around New Zealand by bus, car, boat, and by foot. In a previous blog post, I created a “Where I’ve Been Map” using markers for each of the places I’ve stayed. Let’s add some lines showing the route between the markers.

I've destroyed my National Geographic map while travelling around the New Zealand. How can I add the roads travelled to my digital map?

Travelling around New Zealand can be quite an experience. This is a well known sight.

I’ll concentrate on the roads in this post, and deal with boat and foot tracks later. As I didn’t carry a GPS when travelling by bus or car, I had to find the road lines from a different source. I’m using road data from LINZ for my map, but I’m sure you can achieve the same with OpenStreetMap data.

First, I created an empty shapefile in QGIS for my road data. Then I marked and copied the roads I’ve travelled from the LINZ shapefile, and pasted the road segments into my own shapefile. I also had to cut road lines to remove parts where I’ve not been. Lastly, I merged the road segments together. QGIS is a great tool for tasks like this.

Editing road lines in QGIS.

I used ogr2ogr to convert the shapefile into GeoJSON, which is the vector format supported by Leaflet:

ogr2ogr -t_srs EPSG:4326 -f "GeoJSON" -lco COORDINATE_PRECISION=3 nz-tour-road.json nz-tour-road.shp

I then assigned the the GeoJSON object to a variable:

var roads = {
  "type": "FeatureCollection",
  "features": [...]

To create a GeoJSON layer with custom styles in Leaflet only requires a few lines of code:

var roads = L.geoJson(roads, {
  style: {
    color: '#333',
    weight: 1.5,
    opacity: 1

As I wanted to combine the road lines with the place markers I decided to use the Leaflet LayerGroup:

This is my new “Where I’ve Been” map:

Fullscreen map

Kingston road along Lake Wakatipu.

Towards Glenorchy and the Southern Alps.

Mapping New Zealand: Clustering DOC Huts with Leaflet

In New Zealand, long distance walking or hiking for at least one overnight stay is known as tramping. There is a great network of over 950 huts throughout New Zealand operated by the Department of Conservation (DOC). I’ve just stayed in four of the huts while tramping the Abel Tasman Coast Track, and you need a lifetime to reach them all. It’s much quicker to map them.

DOC huts in the Abel Tasman National Park.

Awaroa Hut
I was fortunate to visit the geospatial unit at the DOC office in Wellington. They have a lot of ineresting conservation projects going on, and it seemed to be a great place to work in New Zealand, especially when you can combine digital work with practical field work.

You can download DOC's geospatial data for free on their data portal. Unfortunately, the datasets are not available as shapefiles, so it might require some wizardry to extract the data from KML. Luckily, the Koordinates guys have done this already, and you can download the shapefile from their website. The version I’m using was updated 10 September 2012.

The dataset contains 967 huts, and adding them all to my Leaflet map will make it too cluttered to be useful. Instead, I wanted to try the great animated marker clustering plugin, created by Dave Leaver here in New Zealand.

I start off by converting the shapefile to GeoJSON with ogr2ogr:

ogr2ogr -t_srs EPSG:4326 -f "GeoJSON" -lco COORDINATE_PRECISION=4 nz-doc-huts.json doc-huts.shp

I could use the GeoJSON directly, but I converted it to a more compact format to save some bandwith:

var data = [
  [-43.3767,170.5685,"Scone Hut",1],
  [-42.9494,171.7047,"Sudden Valley Biv",0],
  [-41.5237,172.5604,"Granity Pass Hut",1],
  [-44.9261,168.2144,"Steele Creek Hut",0],
  [-44.6221,168.4481,"Earnslaw Hut",0],
  [-45.3852,167.6192,"Luxmore Hut",4],


The last number corresponds to the hut categories:

0. Basic Hut/Bivvy
1. Standard Hut
2. Serviced Hut
3. Serviced-Alpine Hut
4. Great Walk Hut

This is the code you need to create clustered huts with custom markers:

The JavaScript map function, which converts one array into another, is not supported in Internet Explorer 8 and lower. If you need to support prehistoric browsers, use a for-loop instead. The icons I’ve used (which could have been more descriptive) are from Map Icons Collection.

The interactive map looks like this:

Fullscreen map

I especially like the animated feature of this clustering plugin (try zooming in and out), as it makes it more obvious for the map user what a cluster really is.

Whariwharangi Hut, built as a farmhouse in about 1896.

Monday, 8 October 2012

Mapping New Zealand: Where are the hot and cold springs?

New Zealand has a large number of cold and hot springs, where water flows to the surface of the earth from underground. Where can you find these springs? This blog post will show you can visualise a spring point dataset on a Leaflet map. 

You can find spring point dataset at LINZ Data Service, which contains 147 significant springs either by size or location. I downloaded the dataset as a shapefile, and converted it to GeoJSON using ogr2ogr:

ogr2ogr -f "GeoJSON" -lco COORDINATE_PRECISION=4 nz-spring-points.json nz-spring-points-topo-150.shp 

The resulting GeoJSON FeatureCollection can be assigned to a JavaScript variable:

var springs = {"type":"FeatureCollection","features":[{...

... and visualised with Leaflet's GeoJSON layer and L.circleMarker:

Here is the result:

Fullscreen map

Te Waikoropupū Springs in Golden Bay, discharging 14,000 litres of water per second, - the largest freshwater springs in New Zealand.

The horizontal visibility of the water in the springs has been measured at an average of 63 metres, second only to sub-glacial water in the Antarctic.

How to control your Leaflet map with URL parameters

I needed a way to control the appearance for my Leaflet map of New Zealand, especially when embedding a map with an iframe. Leaflet have no native support for URL parameters for, like the Permalink control for OpenLayers (there is a plugin + Leaflet-hash). This blog post will show you how you can add support for URL parameters for map position (latitude/longitude), zoom level and active layers.

First, I'm defining my layers in an JavaScript object:

var layers = {
  'Seafloor': L.tileLayer('tiles/nz-seafloor/{z}/{x}/{y}.png'),
  'Topographic': L.tileLayer('tiles/nz-topo/{z}/{x}/{y}.png'),
  'Tour': L.cartoDB(' * FROM new_zealand_tour')

You can use the same object to populate the Leaflet layers control. Next, I'm extracting the URL parameters with a regular expression (from Papermashup):

var params = {};
window.location.href.replace(/[?&]+([^=&]+)=([^&]*)/gi, function(m, key, value) {
  params[key] = value;

All URL parameters are now stored as key/value pairs in the params object. We need to create an array of active map layers:

if (params.layers) {
  var activeLayers = params.layers.split(',').map(function(item) {    
    return layers[item];

The array map function will create a new array with a reference to the visible layers on the map. Please note that the map function is not supported in Internet Explorer < 9 (you can easily rewrite the function with a for loop to support this browser).

We can now create a map with our URL parameters:

var map = new L.Map('map', {
  center: [ || -41.2728, params.lng || 173.2996],
  minZoom: 4,
  maxZoom: 12,  
  zoom: params.zoom || 6,
  layers: activeLayers || [layers.Seafloor, layers.Topographic]

I'm using the Logical OR (||) operator to add default values. Lastly, I'm adding my map layers to the layers control, allowing users to toggle the visibility:

L.control.layers({}, layers).addTo(map);

This is the full example:

We can now control the map med URL parameters like (the code in this example is changed to support base layers and overlays):

Easy peasy!

Creating a "Where I've Been" map with Leaflet and CartoDB

I wanted to create a "Where I've Been" map of my trip around New Zealand. The map should be easy to update through a simple web interface. Previously, I've used Google Fusion Tables for tasks like this. Now, I want to try CartoDB, which is Google Fusion Tables on steroids.

I used the CartoDB free plan to create a simple table containg the name and location of the places I've been:

You can easily query your data with the HTTP-based SQL API: * FROM nz_tour

This request will return all the data in the default CartoDB JSON-format. Add format=geojson, if you want the data in the GeoJSON format supported by Leaflet: * FROM nz_tour&format=geojson

The SQL API also supports JSONP, allowing you to query your data directly from JavaScript in the web browser. Just add a callback parameter: * FROM nz_tour&format=geojson&callback=myFunction

Leaflet don't have native support for JSONP or CartoDB, but there are a few plugins around (1, 2). It's also very easy to extend Leaflet with the functionality you want, so I decided to create a new CartoDB layer class by extending the L.GeoJSON class:

This class rewrites the initialize function and adds a JSONP function (you can also use a library like jQuery). This is how you can create a new layer with this class:

L.cartoDB(' * FROM nz_tour')

This will create a layer with the default Leaflet markers:

If you want custom markers and popups, all the functionality available for the GeoJSON layer can also be used for the CartoDB layer:

The interactive map looks like this:

Fullscreen map

You can add a marker to the map by adding a new row to your CartoDB table. Dead simple!