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_MakePoint( 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 FROM a 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?
أكثر...
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_MakePoint( 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 FROM a 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?
أكثر...