In cartodb, how to automatically update the_geom with new longitude and latitude?

Question

I am currently posting new table row using the cartodb sql api with the following commands: update {table_name} set geom = ST_Transform(ST_SetSRID(ST_MakePoint(latitude, longitude),4326) latitude and longitude are taken from numeric columns in my table. However the_geom is not updated, and instead an error is received.

I am aware I can use 'Georeference your dataset' within cartodb to update the the_geom...but I don't want to do this manually whenever I add new coordinates.

Is there a way to automatically update the_geom when I add new new longitude and latitude coordinates?

Answer 1

You do not need to use ST_Transform because you have already set your map projection with ST_SetSRID, so the SQL query would be like this:

UPDATE 
  new_dataset
SET 
  the_geom = ST_SetSRID(st_makepoint(longitude, latitude),4326)

But if you want to use ST_Transform, because you want to visualize your query in Web Mercator or other projection this is the way to do it:

SELECT
  ST_Transform(ST_SetSRID(st_makepoint(longitude, latitude),4326),3857) as the_geom_webmercator
FROM 
  new_dataset