If I get this right, just add all columns to the new table and drop the old geom
column:
CREATE TABLE buffer AS
SELECT *,
ST_Buffer(geom, 1)::GEOMETRY(POLYGON, <SRID>) AS _geom
FROM ne_110m_populated_places
;
ALTER TABLE buffer
DROP COLUMN geom
;
ALTER TABLE buffer
RENAME _geom TO geom
;
CREATE INDEX ON buffer USING GIST(geom);
You can (almost) freely choose (and manipulate) the columns to copy in the SELECT
list, just as with your second query!?
Or, if you don't intend to keep the original geometries, update the table with
ALTER TABLE ne_110m_populated_places
ALTER COLUMN geom TYPE GEOMETRY(POLYGON, <SRID>)
USING ST_Buffer(geom, 1)
;
Note that nothing keeps you from adding a second geometry column. It's not best practice, though.
Alternatively, the relational way; create a table with the buffers and the original PRIMARY KEY
:
CREATE TABLE buffers AS
SELECT <pkey_col>,
ST_Buffer(geom, 1)::GEOMETRY(POLYGON, 4326) AS geom
FROM ne_110m_populated_places
;
ALTER TABLE buffer
ADD PRIMARY KEY (<pkey_col>)
;
ALTER TABLE buffer
ADD FOREIGN KEY (<pkey_col>)
REFERENCING ne_110m_populated_places (<pkey_col>)
ON UPDATE CASCADE ON DELETE CASCADE
;
CREATE INDEX ON buffer USING GIST(geom);
You can now JOIN
both tables USING <pkey_col>
and choose all wanted columns from ne_110m_populated_places
, plus the geom
from buffer
.
Run VACUUM ANALYZE buffer;
afterwards.
buffer
if you just useST_DWithin
on the query. This then becomes a self-join. Alternatively, you could just add the columns you want to preserve to the buffer creation.*
nor to manually type all the fields, then just have Postgres generate the field list: dba.stackexchange.com/questions/22362/…