CartoDB: Issue with GDAL 2.1 and the_geom_webmercator column

[Even Rouault]

From Rafa de la Torre

scenario to reproduce

Try to import a shapefile with ogr2ogr and CartoDB driver:

ogr2ogr --config CARTODB_API_KEY myapikey -nln "is_geomweb_there_3" -t_srs "EPSG:4326" -f CartoDB "CartoDB:stephaniemongon" california_cities_points3.shp

expected behavior

The table is imported correctly with the_geom (srid: 4326) and the_geom_webmercator (srid: 3857) in sync.

actual behavior

Both columns exists but the_geom_webmercator is left blank

aditional information

It works with 2.0.0. The problem was introduced in the version 2.1.0. I think the problem was introduced by r30283

traces and root cause

Below some traces capturing the traffic generated towards the SQL API:

• With ogr2ogr from gdal at r29639 OK:

first it cartodbfies:

SELECT cdb_cartodbfytable('california_cities_points3')

then it inserts the data:

BEGIN;
INSERT INTO "california_cities_points3" ("longitude", "latitude", "name_0", "namelsad", "the_geom", "cartodb_id") VALUES (-117.40921400000, 34.58276900000, 'California', 'Adelanto', '0101000020E61000002EC4EA8F305A5DC0B20FB22C984A4140', nextval('california_cities_points3_cartodb_id_seq'));
INSERT INTO "california_cities_points3" ("longitude", "latitude", "name_0", "namelsad", "the_geom", "cartodb_id") VALUES (-118.76167500000, 34.15333900000, 'California', 'Agoura Hills', '0101000020E610000092CB7F48BFB05DC0C619C39CA0134140', nextval('california_cities_points3_cartodb_id_seq'));
INSERT INTO "california_cities_points3" ("longitude", "latitude", "name_0", "namelsad", "the_geom", "cartodb_id") VALUES (-122.24163600000, 37.76520600000, 'California', 'Alameda', '0101000020E61000005262D7F6768F5EC0FA592C45F2E14240', nextval('california_cities_points3_cartodb_id_seq'));
INSERT INTO "california_cities_points3" ("longitude", "latitude", "name_0", "namelsad", "the_geom", "cartodb_id") VALUES (-122.29774700000, 37.88686900000, 'California', 'Albany', '0101000020E6100000DADE6E490E935EC0096B63EC84F14240', nextval('california_cities_points3_cartodb_id_seq'));

-- ...

COMMIT

so that in previous versions the cartodbfy'ation process happens before, and it creates a trigger that keeps the_geom and the_geom_webmercator in sync.

• With ogr2ogr from gdal at r33718 WRONG:

BEGIN;
INSERT INTO "california_cities_points3" ("longitude", "latitude", "name_0", "namelsad", "the_geom")
VALUES (-117.40921400000, 34.58276900000, 'California', 'Adelanto', '0101000020E61000002EC4EA8F305A5DC0B20FB22C984A4140'),
(-118.76167500000, 34.15333900000, 'California', 'Agoura Hills', '0101000020E610000092CB7F48BFB05DC0C619C39CA0134140'),
(-122.24163600000, 37.76520600000, 'California', 'Alameda', '0101000020E61000005262D7F6768F5EC0FA592C45F2E14240'),
(-122.29774700000, 37.88686900000, 'California', 'Albany', '0101000020E6100000DADE6E490E935EC0096B63EC84F14240'),
(-118.12701400000, 34.09528600000, 'California', 'Alhambra', '0101000020E6100000960854FF20885DC022E2E654320C4140'),

-- ...

COMMIT

then:

SELECT cdb_cartodbfytable('california_cities_points3')

Since the cartodbfy'ation occurs after inserting data, and it finds the columns the_geom and the_geom_webmercator with their correct srid's, it just assumes the table is ok and no further processing is done.

possible fixes

I see two possible alternatives:

(a) the cartodbfy process can happen before, or

(b) the gdal driver populates the column the_geom_webmercator as well, or

(c) the gdal driver does not create the column the_geom_webmercator leaving that responsibility to the cartodbfy process.

(a) is the previous behavior. I understand the reasons behind the change (mostly performance). (b) incurs in more bandwidth usage and maybe is just pointless. My preferred option would be (c).

I'm open to discussion here.

[Even Rouault]

In 34475:

CartoDB: fix CartoDB'fication() by removing manual creation of the_geom_webmercator mercator, and also attach created sequence to table.cartodb_id (fixes #6565)

[Even Rouault]

In 34476:

CartoDB: fix CartoDB'fication() by removing manual creation of the_geom_webmercator mercator, and also attach created sequence to table.cartodb_id (fixes #6565)

[Even Rouault]

In 34477:

ogr_cartodb.py: test that the_geom_webmercator is correctly populated (fixes #6565)

[Even Rouault]

In 34478:

ogr_cartodb.py: test that the_geom_webmercator is correctly populated (fixes #6565)