This seems like a dumb question, but I want to create (or add to) test_table2 a buffer feature from test_table1 point layer.
INSERT INTO test_table2 (SELECT ST_Buffer(the_geom_webmercator, 1000) AS the_geom_webmercator, cartodb_id FROM test_table1)
In this example, test_table2 already exists and shares the same columns as test_table1
In the CartoDB query window online in the Data View/Map View, this statement works:
SELECT ST_Buffer(the_geom_webmercator,500) AS the_geom_webmercator, cartodb_id FROM test_table1
So I basically need to INSERT this into a new or already created table.
So I got the following statement to work:
INSERT INTO testcreate (the_geom) VALUES (ST_Buffer(CDB_LatLng(40.7127, -74.0059),0.01));
I don't think this really solved my problem, unless I can get ST_Intersects, and ST_Transform (as I need this in a projected system - the result of this is unprojected) to fit inside the VALUES() function.
Advice provided from @schwanksta on twitter:
schwanksta: @nygeog Hm, maybe misunderstanding, but can't you UPDATE t1 SET buffer = (SELECT ST_Buffer(t2.geom,100) FROM t2 WHERE t1.id=t2.id)
@nygeog t1 would be the table you're trying to stuff the value into, t2 would be the source pt layer. sorry, reversed your naming scheme!
@nygeog You'd need to create a column with AddGeometryColumn first
UPDATE t2 SET buffer = (SELECT ST_Buffer(t1.the_geom,100) FROM t1 WHERE t1.cartodb_id=t2.cartodb_id)
This didn't seem to work for me, I could be translating to meet my data wrong (I did create two tables t1 and t2, but getting urllib2.HTTPError: HTTP Error 400: Bad Request from this.