[GIS] CartoDB Convert from the_geom to google’s lat long

cartoleafletweb-mercator

I have a CartoDB table that when I query it via the rest api I get something like this:

{
  "name": "sas",
  "street": "la plata 2390",
  "cartodb_id": 1,
  "created_at": "2013-06-26T00:46:21.412Z",
  "updated_at": "2013-06-26T00:58:26.799Z",
  "the_geom": "0101000020E6100000FDA4DAA7E32D4DC0E3FC4D28445041C0",
  "the_geom_webmercator": "0101000020110F0000064377E72DC858C1BB2873CAD2614FC1",
  "cartodb_georef_status": true
},

I want to conver the the_geom_webmercator to a lat, long pair of numbers, to use with leaflet like this:

L.marker([-34.617917, -58.433257]).addTo(map)
  .bindPopup('A pretty CSS3 popup. <br> Easily customizable.')
  .openPopup();

Which would be the best way to achieve it?

Create a view or query on CartoDB? (I tried with ST_Transform, but I don't know what parameter to use)

Use a specific leaflet option to pass the webmercator value?

Use some js library to do the conversion?

— EDIT

I've found the st_asText(the_geom) that returns me something like: POINT(-58.35851 -34.62708)

and also st_x(xxx) and st_y(xxx) functions which returns each coordinate.

so I create a the_geom_lat and the_geom_long columns and executed the following query

update test set
the_geom_lat = st_y(the_geom),
the_geom_long = st_x(the_geom)

I still would like to know if there's any js function that could achieve the same, so I wouldn't have to create a cartoDB view

Best Answer

you could use CartoDB SQL API to get that data

http://USER.cartodb.com/api/v1/sql?q=select st_y(the_geom) as lat, st_x(the_geom) as lon from TABLE

for example, using jQuery ajax:

$.get("http://USER.cartodb.com/api/v1/sql?q=select st_y(the_geom) as lat, st_x(the_geom) as lon from TABLE", function(data) {
  for(var i = 0; i < data.rows.length; ++i) {
     var row = data.rows[i];
     console.log("point", row.lat, row.lon);
  }
});
Related Question