PostGIS, CartoDB Bounding Box Trick

When pulling out CartoDB or PostGIS data into a web mapping environment like LeaftletJS or Google Maps, it is handy to know the bounding box of the data in question. The bounding box is a convenient way to ensure your data is fully visible to the user, no matter what platform they are using or how responsive the viewport is.

A quick way to extract the bounding box from a PostGIS data layer is:

SELECT st_astext(st_envelope(st_union(the_geom))) FROM << table >>

As noted in this blog post, it is possible to do lots of pure PostGISy things directly in the CartoDB UI, this trick above is one of them. Just add the above snippet into the ‘SQL’ tab, and you will be presented with the same result PGAdminIII or PSQL (the more typical PostGIS interfaces) would provide,  a string which looks like:

[[-122.899495685641,53.9576281860941],[-122.899495685641,53.9727081860345],[-122.859095858743,53.9727081860345],[-122.859095858743,53.9576281860941],[-122.899495685641,53.9576281860941]]

Now, note that this coordinate string is in the form:

[[x1,y1],[x2,y2][x3,y3],[x4,y4],[x1,y1]]

You will see that the first coordinate is repeated; this closes the bounding box. Also, the individual vertex coordinates are in the form of [x,y] NOT [lat,long].

You could do the mind-bending trick of figuring out the appropriate coordinate order to determine a Lat-Long bounding box. Or you could, let the computer figure it out. Below is a quick way to convert the above string into a Leaflet or Google Maps bounding box.

LeafletJS

var envelope = [[-122.899495685641,53.9576281860941],[-122.899495685641,53.9727081860345],[-122.859095858743,53.9727081860345],[-122.859095858743,53.9576281860941],[-122.899495685641,53.9576281860941]]
 
 var dataBounds = new L.latLngBounds(L.latLng(envelope[0][1], envelope[0][0]))
 for (i = 1; i < envelope.length; i++) {
     dataBounds.extend(L.latLng(enveolpe[i][1],envelope[i][0]))
 }

map.fitBounds(dataBounds)

 

Google Maps

var envelope = [[-122.899495685641,53.9576281860941],[-122.899495685641,53.9727081860345],[-122.859095858743,53.9727081860345],[-122.859095858743,53.9576281860941],[-122.899495685641,53.9576281860941]]
 
var dataBounds = new google.maps.LatLngBounds({lat:envelope[0][1], lng:envelope[0][0]})
for (i = 0; i < envelope.length; i++) {
    dataBounds.extend({lat:enveolpe[i][1],lng:envelope[i][0]})
}

map.fitBounds(dataBounds)

Isn’t it interesting how similar these code snippets are! Now, you could pump the dataBounds object out to a console.log() and then copy the calculated result back into your map code…

… alternatively you could leave the calculation in place, but it will be run at every rendering of the map code, obviously not optimal, but somewhat convenient.