id summary reporter owner description type status priority milestone component version severity resolution keywords cc 6565 CartoDB: Issue with GDAL 2.1 and the_geom_webmercator column Even Rouault warmerdam "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: {{{#!sql SELECT cdb_cartodbfytable('california_cities_points3') }}} then it inserts the data: {{{#!sql 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: {{{#!sql 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: {{{#!sql 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. " defect closed normal OGR_SF 2.1.0 normal fixed cartodb