How to save a a CartoDB buffer to its own data table/layer

Question

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

Update

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.

Update2

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

Tried this:

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.

• user30184: Most simple with postgresql.org/docs/9.3/static/sql-createtableas.html
• PolyGeo: To make this fit our focussed Q&A format better, would you be able to edit your Solution out into a self-answer, please? It is perfectly acceptable to do that.

Answer 1

From @user30184 comment's help:

Ran this statement:

CREATE TABLE test_tablebuffer AS 
SELECT ST_Buffer(the_geom_webmercator, 5000) AS the_geom_webmercator, cartodb_id FROM test_table1

Then this (to get it to show up in CartoDB UI):

select cdb_cartodbfytable('test_tablebuffer');