I want to run in leaflet a query that works in carto Builder to connect points in a line: enter image description here

SELECT ST_MakeLine(the_geom_webmercator) as the_geom_webmercator, min(cartodb_id) as cartodb_id FROM my_table

i try to reproduce this in an html page : https://jsfiddle.net/2w4a3f1x/

var sqlQuery = "SELECT ST_MakeLine(the_geom_webmercator) as the_geom_webmercator, min(cartodb_id) as cartodb_id FROM my_table";

// Set CartoDB Username
var cartoDBUserName = "admin";

// Get CartoDB selection as GeoJSON and Add to Map
function showAll(){
  map.setView(new L.LatLng(40.8722012,14.3036309), 10);
  $.getJSON("https://spatial.query.city/user/"+cartoDBUserName+"/api/v2/sql?format=GeoJSON&q="+sqlQuery, function(data) {
    pointsincartodb = L.geoJson(data,{
      onEachFeature: function (feature, layer) {
       layer.bindPopup('<pre>'+JSON.stringify(feature.properties,null,' ').replace(/[\{\}"]/g,'')+'< /pre>');

But the link to geojson:

gives an error: {"error":["column \"the_geom\" does not exist"]} but i know that the_geom exist! as you can see here: https://spatial.query.city/user/admin/dataset/my_table


Firstly GeoJSON API expects that the geometry part of the GeoJSON data will come from the column called the_geom.

Secondly Leaflet expects the geometry to be in WGS84 coordinates, unless specifically specified to be different.

To address the above points you need to use the_geom column as input to ST_MakeLine (since it’s in WGS84), aliasing it to the_geom

Based on this change your query to

var sqlQuery = `SELECT ST_MakeLine(the_geom) as the_geom, 
  min(cartodb_id) as cartodb_id FROM my_table`;


  • Can you explain your answer? When explained, answer has much greater value for others with similar problems. – TomazicM May 10 '20 at 16:45
  • @TomazicM good point. Done :) – Shoaib Burq May 10 '20 at 16:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.