I am having some trouble with the the_geom column and the the_geom_webmercator column in CartoDB. I have some SQL (which I modified from http://bl.ocks.org/andrewxhill/8695515) in which the the_geom_webmercator column is changed, but the the_geom column is not changed. This means that the changes to latitude and longitude which are visible in the map are not visible in the data, which is a problem. Below is the SQL:

with q AS (
  SELECT cartodb_id, the_geom_webmercator
  FROM untitled_table_7
  WHERE name= 'Rome'),
             m AS (
               SELECT count(*) n,
               array_agg(cartodb_id) id_list, the_geom_webmercator
               FROM q
               GROUP BY the_geom_webmercator),
             f AS (
               SELECT  n, generate_series(1, array_length(id_list,1)) p, unnest(id_list) cartodb_id, the_geom_webmercator
               FROM m),
              a AS (
             SELECT ST_SetSRID(
                 ST_X(f.the_geom_webmercator) + f.p * 180 * cos(f.p * radians(301*ceil(f.n/20.0))/f.n), ST_Y(f.the_geom_webmercator) + f.p*180 * sin(f.p * radians(301*ceil(f.n/20.0))/f.n)),3857) the_geom_webmercator, f.cartodb_id
FROM f, untitled_table_7 t)
UPDATE untitled_table_7
SET the_geom_webmercator = a.the_geom_webmercator
WHERE untitled_table_7.cartodb_id = a.cartodb_id

How can I adapt this so that the changes in the the_geom_webmercator column are shown in the the_geom column?


You could transform the_geom_webmercator, which is in Web Mercator (SRID = 3857) to the projection of the_geom, which is WGS84 (SRID = 4326). All you have to do is replace the row that has SET the_geom_webmercator = a.the_geom_webmercator with this:

SET the_geom = ST_Transform(a.the_geom_webmercator,4326)

Once the_geom is set, it will trigger the_geom_webmercator to be projected from that.

Read more about projections in CartoDB in this tutorial.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.