Opened 13 months ago

Closed 13 months ago

Last modified 13 months ago

#6565 closed defect (fixed)

CartoDB: Issue with GDAL 2.1 and the_geom_webmercator column

Reported by: Even Rouault Owned by: warmerdam
Priority: normal Milestone:
Component: OGR_SF Version: 2.1.0
Severity: normal Keywords: cartodb
Cc:

Description

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.

Change History (4)

comment:1 Changed 13 months ago by Even Rouault

Resolution: fixed
Status: newclosed

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)

comment:2 Changed 13 months ago by 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)

comment:3 Changed 13 months ago by Even Rouault

In 34477:

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

comment:4 Changed 13 months ago by Even Rouault

In 34478:

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

Note: See TracTickets for help on using tickets.