CARTO: Issue with GDAL 2.1.2 and the_geom_webmercator column

[smongon]

From Stephanie Mongon

scenario to reproduce

Use CSV that already contains a the_geom column with WKT.

Try to import the CSV with ogr2ogr and Carto driver. :

ogr2ogr --config CARTO_API_KEY myapikey -nln "from_csv" -t_srs "EPSG:4326" -f Carto "Carto:myaccountname" california_cities_points3.csv

expected behavior

A the_geom_webmercator (srid: 3857) column is created and populated based on the_geom (srid: 4326).

actual behavior

No the_geom_webmercator column is created.

additional information

Similar issue with shapefiles was fixed: ​https://trac.osgeo.org/gdal/ticket/6565

So this works in GDAL 2.1.2 for .shp:

ogr2ogr --config CARTO_API_KEY myapikey -nln "from_shp" -t_srs "EPSG:4326" -f Carto "Carto:myaccountname" california_cities_points3.shp

Table creation & population test performed via CARTO SQL API (outside of driver) does create a the_geom_webmercator column for CSVs:

1. CREATE TABLE
2. Cartodbfy it ​https://github.com/CartoDB/cartodb/wiki/creating-tables-though-the-SQL-API
3. INSERT CSV's WKT values into created table's the_geom column

[Even Rouault]

By default, the CSV driver will not recognize this CSV file as a spatial one, and thus the the_geom column will be dealt as a non geometric column. Hence the absence of a the_geom_webmercator column.

The wished behaviour can be gotten by adding the following options to the ogr2ogr command line:

-oo GEOM_POSSIBLE_NAMES=the_geom -oo KEEP_GEOM_COLUMNS=NO