carto api with javascript using online carto db. Argument of WHERE must be type boolean, not type geometry

Question

I am using cartodb api to fetch the geojson from the cartodb data set using this code

var map = L.map('map',{ center: [-35.1393961, 149.8530818], zoom: 13});
var alpha=map.getBounds();

var lat0=alpha.getNorthEast().lat;


var long0=map.getBounds().getNorthEast().lng;

var lat1=map.getBounds().getSouthWest().lat;
var long1=map.getBounds().getSouthWest().lng;


// Add Tile Layer basemap
L.tileLayer('https://api.mapbox.com/styles/v1/mapbox/streets-v10/tiles/256/{z}/{x}/{y}?access_token=pk.eyJ1IjoiaGFzaGlyaWx5YXMiLCJhIjoiY2l4NzdzMmhjMDB0dDJ0bXN0NW5jc3cwayJ9.UNDyJQP92EC0ToFilX28lA', {
maxZoom: 18,
id: 'your.mapbox.project.id',
accessToken: 'your.mapbox.public.access.token'}).addTo(map);


// Global Variables
// Will go here
var coffeeShopLocations = null;

// Database Queries
// Get all coffee cafes from dataset
var sqlQuery = "SELECT * FROM zearch_nsw_19042016 WHERE the_geom && ST_MakeEnvelope("+long1+", "+lat1+", "+long0+", "+lat0+", 4326);";
function showAll(){
  $.getJSON("https://{username}.carto.com/api/v2/sql/?q="+sqlQuery+"&api_key={key}&format=geojson&callback=?", function(data) {


  console.log(data);



       });
};



// Run showAll function automatically when document loads
$( document ).ready(function() {
  showAll();
});

While executing the query on the cartodb it is returning correct result but here at first it was showing invalid geom object but when I outputed the geojson it is showing this error

argument of WHERE must be type boolean, not type geometry

What is wrong? The query works fine when implemented on the cartodb or postgis database. But when used with cartodb api it generates the error on geojson

Answer 1

I see you are using && as the PostGIS "overlaps" spatial binary operator.

Make sure that both sides of that operation are either a geometry or a geography. One possibility is that the_geom is a PostGIS geography and that the result of ST_MakeEnvelope() is a geometry.

Also pay attention to the order of the operands in the ST_MakeEnvelope(xmin, ymin, xmax, ymax, srid) call, as it expects xmin to be less than xmax (idem for y).

Answer 2

If you use a WHERE as you have it structured, the statement expects a Boolean return....

Instead, I think you want something more like this example:

SELECT * FROM {table_name} WHERE the_geom && ST_SetSRID(ST_MakeBox2D(ST_Point(-73.9980, 40.726), ST_Point(-73.995, 40.723)), 4326)

This is from the section "Query Records in a Bounding Box":

https://carto.com/docs/tips-and-tricks/advanced-analysis

Answer 3

So i found out the solution. First of all the Where statement in postgis/ cartodb requires it to be boolean. The cartodb online was showing the result because sometimes the ui doesnt show the error and skirts around it to show the result according to cartodb help support.

Second of all i used this query which was suggested by the carto support personal to get the required result from the cartodb via carto sql api.

SELECT * from zearch_nsw_19042016 WHERE ST_Intersects(the_geom, ST_MakeEnvelope("+long1+", "+lat1+", "+long0+", "+lat0+", 4326)) 

It worked like a charm.

Thanks all for helping