0

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?

0

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
  • In fact you can do it in one step with the CDB_LatLng(lat,lon) function instead of chaining the create and SRID assignment :-) – Jorge Sanz Jul 14 '16 at 8:05
  • I always forget about CARTO functions! :-P – ramiroaznar Jul 14 '16 at 8:19

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.