Importing data to CartoDB using ogr2ogr

Question

First attempt, I want to create a table at CartoDB from another Postgis database:

snow:Docker-Postgis alasarr$ ogr2ogr --config CARTODB_API_KEY daf3960b41733ef71e03ea77019642761d547f5b -f CartoDB "CartoDB:alasarr" PG:"host=192.168.59.103 user=postgres dbname=eiel_huesca port=5433" "geometries_eiel.alumbrado_ok"

// output
CartoDB driver does not support data source creation.

Second attempt, I try that ogr2ogr create a table at CartoDB from a Shapefile:

snow:Docker-Postgis alasarr$ ogr2ogr --config CARTODB_API_KEY XXXX  -f CartoDB "CartoDB:alasarr" ~/dev/naturalearth/ne_110m_admin_0_countries/ne_110m_admin_0_countries.shp 

// output:
CartoDB driver does not support data source creation.

Third attempt:

• I import the shapefile at CartoDB.
• Truncate the table. Truncate table ne_110m_admin_0_countries;
• I try to import using ogr2ogr.

Here the command:

ogr2ogr --config CARTODB_API_KEY XXXX  -f CartoDB "CartoDB:alasarr" -append -t_srs EPSG:4326  ~/dev/naturalearth/ne_110m_admin_0_countries/ne_110m_admin_0_countries.shp

//output
ERROR 1: HTTP error code : 400
ERROR 1: Error returned by server : relation "ne_110m_admin_0_countries" already exists
ERROR 1: Terminating translation prematurely after failed
translation of layer ne_110m_admin_0_countries (use -skipfailures to skip errors)

I'm using Gdal 1.11.0 that in theory supports CartoDB format.

Any ideas? Should I try with a newer version of GDAL.

Answer 1

It was an issue related with Gdal version 1.11.0. Using Gdal 2.0.0 it works.

Postgis 2 CartoDB:

ogr2ogr -t_srs EPSG:4326 --config CARTODB_API_KEY XXXXX -f CartoDB "CartoDB:account" PG:"host=host user=postgres dbname=db port=port" "<tablenamesrc>" 

CartoDB to Postgis:

ogr2ogr --config CARTODB_API_KEY XXXXXX -f PostgreSQL PG:"host=host  user=postgres dbname=db port=5433"  "CartoDB:account tables=spain_cities_population" -nln <outputtablename> -lco GEOMETRY_NAME=the_geom