Publish your own geocoded web services with CartoDB


This is the third part of a series of articles that shows how to use several really nice tools to play with open data.

In the first article we saw how to take our comma-separated list of google contacts, and mangle them using OpenRefine.

In the second part we used OpenRefine to access exteral web services like Google Maps and OpenStreetMap to geocode the addresses of our contacts.

Now that we have our dataset ready, we are going to upload it to CartoDB to expose it as a web service.

Introducing CartoDB

cartodb homepage

Even though in this small example we will just use it to expose a rest web service, CartoDB is a full-fledged platform for creating maps and developing applications. Among other features it lets you import your data, style your maps, work with many basemap providers, merge datasets, perform complex spatial queries, visualize your data layers, publish and share your maps and visualizations, and many more features.

CartoDB uses a PostGIS enabled PostreSql database to store your data. That means you have the full power of sql statements and spatial extensions at your disposal. More over, it exposes all your data through a neat JSON rest web service, that allows you to execute all those sql commands.

It also provides you with a free account that lets you create up to 5 tables with 5 MB of storage space. Actually, it’s not really a lot of space, but the good news is that CartoDB is free software, and here you have the github repo with the instructions to install your own CartoDB server.

So, enough talk and get started. Head over to and click on Sign In, Create an account, and then just complete the form.

You’ll be received with a welcome page asking you to drop your dataset file to create a table. They really worked hard to make it dead simple!

drop files

Here you have the table view of your data

table view

As you can see there’s a the_geom column that CartoDB has added by itself, with null values. This is the column that CartoDB uses to display your records on the map. We have to configure which columns to use to fill that data.

select columns to geo

Click on georeference and then choose the osm_lat and osm_lon columns, click Georeference and then on Map view, and Voilá!, all your contacts are there on the map!

your contacts

CartoDB makes it dead easy to share this maps, just click on this link.

If you click on the sql icon on the right, you can manually enter sql queries, like this

enter sql query

select *
from my_contacts
where lower(name) like '%mic%'

You can use joins, where conditions, aggregated queries (group by), and even issue insert and updates commands.

You can also customize the appearance of your map, choose a different basemap, pick the fields to show in the infowindow, customize the css, apply filters, easily share and embed your maps, etc. Too many options to be covered in this article.

Geocoding your data with CartoDB

In the previous article we have already geocoded our data with Google Maps and Nominatim (the one powering OpenStreetMap) APIs, but CartoDB also has an option to geocode your data. Just click on georeference, and this time select I have one or more columns with the address. Notice how CartoDB lets compose the address field and also add more info to it.

geocode with cartodb

To continue with our experiment we’ll create two new columns to store the longitude and latitude calculated by CartoDB.

Open the SQL panel and enter this two commands:

alter table my_contacts
add column cartodb_lat text;

alter table my_contacts
add column cartodb_lon text;

Now, to extract the coordinates from a point you have several options, try this query:

from my_contacts

So to fill our cartodb_lat and cartodb_lon columns we’ll just issue:

update my_contacts set
cartodb_lat = Y(the_geom),
cartodb_lon = X(the_geom)

Now you can see how convenient and powerful is to have access to all the sql commands supported by PostgreSQL and PostGIS.

Exposing your data as a web service

Now we have to expose it all as a web rest web service in order to use it from an application.

Phew, that sounds difficult! right? … NO… The good news is that we don’t have to do anything at all.

as a web service

What? Is that all?

Yes. Don’t believe me? Give it a try here: * from my_contacts

The pattern to access the api is very simple:

http://{account}{SQL statement}&api_key={Your API key}

devel is just the account name I chose for this exercise. If you are using the free offering, all your tables are public, so they are automatically accessible via the web service. More over, if you are just querying them, there’s no need for the api_key parameter.

If you want to issue updates or inserts from the web service API, you have to click on your profile and choose Your API keys option. There you’ll see an example of read and write operations.

Example write: INTO table_name (the_geom, observation) VALUES (ST_GeomFromText(’POINT(-71.2 42.5)’, 4326),'rare bird spotted')&api_key=your_api_key

Example private table read: * FROM private_table&api_key=your_api_key

Remember that if you make this key public, anybody with a minimal knowledge will be able to erase all your data in every table in your account. So this rules out any modification you might be thinking to do from the client side (a pure JavaScript application, for example)

Our application will let us filter our contacts by name or address. So for our purposes we will just need to issue queries like this:

select * from my_contacts 
  lower(name) like '%mic%' or 
  lower(address) like '%mic%' * from my_contacts where lower(name) like ‘%mic%’ or lower(address) like ‘%mic%’

(Notice that we have to URL encode the query)

Next steps

This time we saw how easy is to upload your geocoded datasets to CartoDB and expose it as a web service. We also saw how to perform SQL statements against our tables to further customize our data.

In the next article, we will see how to consume this information from a JavaScript application using the Leaflet library

Tagged with: , , ,
Posted in geo, open data, tutorial
One comment on “Publish your own geocoded web services with CartoDB

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: