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?

score 2 · Accepted Answer · answered Apr 25 '18 at 18:00

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

this is sql from builder working map + I added line "ST_Transform(ST_Buffer(the_geom,0.001), 3857) as the_geom_webmercat" . Renaming columns to "longitude" "latitude" did not update table column "the_geom" values. — Juri K, Apr 25 '18 at 18:09
Renaming does not invoke the geocoding process at all, that only happens at import time. I would run the `UPDATE` as jaak said for best performance, otherwise you are not leveraging any indexing. With small number of points it's OK though. — Jorge Sanz, Apr 26 '18 at 08:53
p.s. Why there is `ST_Transform(ST_Buffer(the_geom,0.001), 3857)`? This draws 0.001 degree (buffers/distances in degrees by itself are generally bad) buffer circle around the point, converting technically Point geometry to Polygon; and the_geom_webmercator column has still points, so there is inconsistency. Why do you want the buffer anyway? Usually you just want to convert projection, then `ST_Transform(the_geom, 3857)` would be enough. Or if you somehow really need buffer zones, then in global scale you should convert geometry to geography first, and then it creates buffer in meters. — JaakL, Apr 26 '18 at 09:16
If your data is properly loaded and geocoded (with UPDATE or automatically during import), then your SQL for the map should be just simple `SELECT * FROM allreports` — JaakL, Apr 26 '18 at 09:18

Creating map by CARTO from table with zero the_geom parameter

1 Answers1