A while back, I posted about some experimentation I did with Leaflet and CartoDB in the wake of FOSS4G in Denver. I recently had the chance to go back and update that sample with some spatial queries. At the time of the original post, CartoDB was still in beta and spatial queries didn’t seem to work, despite the fact that the back-end was driven by PostGIS.
Since then, of course, CartoDB has gone production, as has PostGIS 2.0 (up to 2.0.1 now) so I decided to update the sample a little. This time, I added a floodway layer that flows through Leonardtown, Maryland. With a button click, you can identify the buildings that intersect the floodway. I am again using the CartoDB SQL API and GeoJSON to accomplish this. The API call that accomplishes this is:
If you pull that apart, you’ll see a relatively straightforward use of a PostGIS spatial operator in there:
[sourcecode language=”sql”]
—
SELECT leonardtown_bldgs.* FROM leonardtown_bldgs,floodway WHERE ST_Intersects(floodway.the_geom,leonardtown_bldgs.the_geom)
—
[/sourcecode]
The Javascript function that executes the query is here:
[sourcecode language=”javascript”]
function getFloodBldgs(){
var bldgLayer = new L.GeoJSON();
bldgLayer.on(‘featureparse’, function(e) {
e.layer.setStyle({ color: ‘#FFFFB2’, weight: 1, fill: true, fillColor: ‘#FFFFB2’, fillOpacity: 0.9 });
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/v2/sql?q=SELECT%20leonardtown_bldgs.*%20FROM%20leonardtown_bldgs,floodway%20WHERE%20" +
"ST_Intersects(floodway.the_geom,leonardtown_bldgs.the_geom)&format=geojson&callback=?",
function(geojson) {
$.each(geojson.features, function(i, feature) {
bldgLayer.addGeoJSON(feature);
})
});
map.addLayer(bldgLayer);
}
[/sourcecode]
The CartoDB documentation has greatly improved since my last post, including some good developer examples. A little bit of comfort with PostGIS-esque spatial SQL goes a long way with CartoDB. It’s good to see how the platform is evolving.
