CartoDB + Leaflet = Easy

One of the biggest sources of buzz at FOSS4G was CartoDB. It is a hosted solution from Vizzuality that uses PostGIS to allow you to store your spatial data online. I got a beta account a couple of weeks ago but life (i.e. paying work) kept getting in the way but I finally got to play with it recently.

One of the things that intrigued me is that, similar to Google Fusion Tables, CartoDB exposes a SQL interface through a RESTful API (I’m still not sure if the term “API” applies to REST but it’s a convenient shorthand). Essentially, CartoDB exposes PostgreSQL SQL and the spatial SQL extensions of PostGIS. Once your data is loaded, you can query it and return the results as either CartoDB’s JSON syntax, KML or GeoJSON.

With this information, I set out to build a simple application to query property data and display the results on a map in a browser. In addition to CartoDB, I elected to use the Leaflet Javascript library to accomplish the mapping (although I also experimented with OpenLayers). Displaying and styling GeoJSON in Leaflet is very straightforward and this task gave me and excuse to get a little more comfortable with it.

For starters, I downloaded building footprints for St. Mary’s County, Maryland (freely available here). The footprints themselves are countywide so I also downloaded the boundary for Leonardtown, Maryland and used QGIS to clip out just those buildings that fell within the town boundary. Once my data was prepped, I uploaded it into CartoDB.

The CartoDB uploader is very easy to use.

Once the data is uploaded and made public, it is as easy as a URL to pull down the GeoJSON (or even KML). Click here:

http://geomusings.cartodb.com/api/v1/sql?q=SELECT%20*%20FROM%20leonardtown_bldgs&format=geojson

You’ll notice that some SQL is embedded in the URL. Finding the commercial properties in the data set is as simple as adding a WHERE clause:

http://geomusings.cartodb.com/api/v1/sql?q=SELECT%20*%20FROM%20leonardtown_bldgs%20WHERE%20structure_%20=%20’Commercial’&format=geojson

This became the basis of my sample application. I decided to do a simple property finder that allows a user to find buildings by property type and square footage. As I mentioned, it’s very easy to use GeoJSON with Leaflet. Here is the Javascript function that fetches the buildings from CartoDB and adds them to the map. It uses jQuery to do the actual fetch and then processes the result, adding the features into a Leaflet GeoJSON layer.

	function getBuildings()
	{
        var bldgLayer = new L.GeoJSON();
        //here we also parse the attributes for the popups on the map
        bldgLayer.on('featureparse', function(e) {
          e.layer.setStyle({ color:  '#BDBDBD', weight: 1, fill: true, fillColor: '#EF6548', fillOpacity: 0.85 });
		  var label = "";
		  if (e.properties && e.properties.address){
        		label += "<b>Address:</b>: " + e.properties.address + "<br/>";
    	  }
		  if (e.properties && e.properties.structure_){
        		label += "<b>Property Type:</b>: " + e.properties.structure_ + "<br/>";
    	  }
		  if (e.properties && e.properties.shape_area){
        		label += "<b>Square Footage:</b>: " + e.properties.shape_area + "<br/>";
    	  }
		  if (label != "")
		  {
		  	e.layer.bindPopup(label);
		  }
        });
	
        $.getJSON(
            "http://geomusings.cartodb.com/api/v1/sql?q=SELECT%20*%20FROM%20leonardtown_bldgs&format=geojson&callback=?",
            function(geojson) {
	        $.each(geojson.features, function(i, feature) {
              bldgLayer.addGeoJSON(feature);
            })
        });
		
        map.addLayer(bldgLayer);		
	}

If you look closely, you’ll notice the use of a proxy handler to get past the same origin restriction. This handler is written in .Net. I had been using a Python proxy but urllib2 was indicating header errors in the return from CartoDB so I fell back to this one. I’ll dig into that more later. (Thanks to Javier de la Torre for straightening me out with the use of a callback.) The above routine is basically the template for all the work in the app. Aside from the basemap tiles, the app loads the town boundary and the buildings from CartoDB. A similar funtion drives the query function, building and passing a WHERE clause to CartoDB. The image below shows the interface with query results in blue. The popups are performed by Leaflet.

Selected buildings are shown in blue with popups to display the property details

All told, this app took about 3 or so hours to build and deploy. This version doesn’t actually make use of any spatial SQL yet but I’ll add that soon. The live demo can be found here: http://demo.zekiah.com/propfinder/ (give the buildings a few seconds to load). It seems to work well in Firefox and Chrome and is functional, although clunky, in IE9. I haven’t tried Safari or earlier versions of IE.

CartoDB is still in beta but already seems solid and is definitely powerful. As a hosted solution, there are associated costs but that’s to be expected. I’ll keep playing with it but it’s already impressive.

Advertisements
This entry was posted in gis, open source, postgis, programming, web development and tagged , , , , . Bookmark the permalink.

15 Responses to CartoDB + Leaflet = Easy

  1. Sean Gillies says:

    Neat, Bill. I’m bookmarking this for when I get a chance to try it.

    A gnarly query string doesn’t rule out REST (http://google.com/?q=… for example) but CartoDB’s is simply a handy way to tunnel SQL over HTTP. No REST involved and no need for it to be involved in this case.

  2. Javi says:

    Good post. Have you tried cartob-python? https://github.com/javisantana/cartodb-python

  3. Terry says:

    Thanks, Bill. I’ve been waiting to hear a third-party review of CartoDB. For the record, the demo delivered limited functionality (pan and zoom, but no query) on my Android’s built-in browser.

    Fun stuff.

    • Bill Dollins says:

      I’m seeing the same thing on my Android browser. I guess I need to play with mobile behavior more.

    • Hi. I am Javier de la Torre, developer at CartoDB.

      So as Bill said, the security model around CartoDB is based on public/private tables. When you make a table public what you are essentially doing is giving read permissions to a “public” user. That means that anybody can fire requests to your table, but they will hit PostgreSQL security if they try to modify something. So yeah, SQL injection as you want, as soon as you try to write, it will fail. You can actually try manually by taking the URL bill is calling on the example and run it directly.

      Now. If you want to modify data via the API then you have to authorized using OAuth. Then of course you cant make direct calls like in this case from within the browser and it would have to be proxied trough your application code, and then, you are responsible to prevent SQL injections like in any other development platform.

      If you guys want invitations to the Private Beta drop us an email at wadus@cartodb.com and we will try to provide them quickly.

      • Bill Dollins says:

        Hi Javier,

        Thanks for dropping by to explain how it works. So far, so good. Having lots of fun with it!

        Bill

      • Terry says:

        Javier –

        I get what you’re saying, and it’s not news to me. My previous comment was not, in any way, a comment on CartoDB. I was simply telling Bill how his particular instance of it behaved on the browser built into my android device (which – by the way – is a browser whose suckiness puts it on a par with IE6).

  4. MerseyViking says:

    Very nice. I’d be interested to see how robust the system is to SQL injection attacks.

    • Bill Dollins says:

      I actually thought the same thing as I was doing this. I know the system doesn’t allow any actions that can change data without authentication but that’s a good question for the developers.

  5. Fascinating post, Bill. I’m still waiting to get into the CartoDB beta, so it’s good to see it in action.

    This is timely for me since I’ll be presenting on some work I’ve done pulling WKT out of PostGIS into an OpenLayers vector layer using PHP (jeers?) at the ILGISA conference next week. CartoDB certainly appears to provide a far more elegant solution for similar tasks than pg_connect and pg_query.

    • Bill Dollins says:

      No jeers. I’m hoping you’ll blog/post the results of your work so we can all take a look. It’ll be nice to see something from someone who is close to the needs as opposed to my haphazard guess at something. πŸ™‚

      • I really should write up something since a public-facing application was the result. I developed a redistricting application for our County Board districts that pulled demographics for user-drawn or user modified versions of the existing County Board districts. It was another instance of PostGIS’ blistering speed, even on aging hardware, never failing to amaze me.

        My message at ILGISA will be that you don’t necessarily need to stick a heavyish server application between your RDBMS and user interface to do web-based geoprocessing. It’ll be interesting to see how it resonates with the Esri-heavy crowd that typically attends. πŸ™‚

Comments are closed.