Creating map by CARTO from table with zero the_geom parameter

Question

I have added new dataset to carto Builder using .csv file. But there is null the_geom column. So when i create map using reactjs and carto.js via:

this.cartoClient = new carto.Client({ apiKey: 'key', username: 'user' });
<Map center={center} zoom={zoom} ref={node => { this.nativeMap = node && node.leafletElement }}>
          <Basemap attribution="" url={CARTO_BASEMAP} />

          <Layer
            source={airbnb.source}
            style={this.state.layerStyle}
            client={this.cartoClient}
            hidden={this.state.hidelayers}
          />
        </Map>

and using airbnb.source

SELECT
  cartodb_id,  field_1,field_8, field_7, field_6, field_2, field_4, field_3, field_5,
  ST_SetSRID(    ST_MakePoint(
      field_4,
      field_3
    ),
    4326
  ) AS the_geom,
  ST_Transform(ST_Buffer(the_geom,0.001), 3857) as the_geom_webmercator
FROM (SELECT * FROM allreports) AS _camshaft_georeference_long_lat_analysis

Carto.js does not mark points on my map, so i get it clear. How should i workaround the_geom,the_geom_webmercator to get the map with points or the problem is somewhere else?

Answer 1

Does same SQL work in Builder? From where did you get it? Without seeing actual data one can only speculate what can be done, also your field names are not very helpful here. Or is it auto-generated SQL from Builder? I'm not sure if it is good idea to hack around that one, even if it works now, then these internal structures can change anytime. In general there are following scenarios how you get data to map:

• Importer tries some heuristics from table structure to add it to map. For example if you have column names latitude and longitude, then the table will automatically geocoded, i.e. geom fields are filled and the table just works. Or if there is column city with English city names, then there is good chance also that it will be on map. So the easiest way is to use these field names, if you already have it in your data.
• You can persistently geocode table using Carto SQL API, with something like UPDATE {tablename} SET the_geom = cdb_geocode_namedplace_point({city_column}, {province_column}, {country_column}) in the case of named places, or if you have wgs lat/lon fields then UPDATE {tablename} SET the_geom = st_setsrid(st_point({lon_column}, {lat_column}),4326). This way the the_geom will be filled. To make it sure I would also do UPDATE {tablename} SET the_geom_webercator = st_transform(the_geom, 3857). Of course you can use here st_buffer etc.
• you can do also live geocoding query as you seem to try now, just be sure that there is unique cartodb_id, proper the_geom (in wgs84) and the_geom_webmercator (in epsg3857 projection, just as you have). This makes more sense if your data is somehow dynamically updated, otherwise I would do one-time UPDATE to the table.
• use Builder geocoding analysis. This creates another 'virtual' live dataset with geocoded data, and this can be used in map view or further analyses. I'm afraid this cannot be done/shown in carto.js maps, this is within Builder only.

p.s. you can find more carto-related posts in https://gis.stackexchange.com/questions/tagged/carto