PostGIS ST_Buffer with attribute preserve

Question

I'm playing with PostGIS a little bit but I'm stuck on, I think simple thing - how to create buffer from points with attribute inheritence?
Simple st_buffer code:

CREATE TABLE buffer AS
SELECT ST_Buffer(geom,1) AS geom
FROM ne_110m_populated_places;

Original point layer (ne_110m_populated_places) have 100 columns, result has only geom column.
I'm trying to put rest of attribute table from source to target layer but I'm stuck.
I can easly select columns with st_intersect:

SELECT ne_110m_populated_places.*, buffer.*
FROM ne_110m_populated_places 
INNER JOIN buffer ON ST_Intersects(ne_110m_populated_places.geom,buffer.geom);

But can't connect both to do the task.

geozelot · Accepted Answer

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.

DPSSpatial · Answer

I may be wrong but this is just a simple SQL issue - you can select multiple columns from the places table...

You're trying:

CREATE TABLE buffer AS
SELECT ST_Buffer(geom,1) AS geom
FROM ne_110m_populated_places;

But you can also select everything from the places table - adding an alias to the table illustrates this best:

SELECT
p.place_name
, p.place_id
, p.whatever
, p.geom
, ST_Buffer(p.geom,1) AS geom_buff
FROM ne_110m_populated_places as p;

As stated, there may be ways to avoid using the buffer using ST_DWithin, unless you want to keep that buffer geometry - the STDwithin cuts out the middle man aka. the geom_buff and does the work internally, which is great if you need it.

PostGIS ST_Buffer with attribute preserve

2 Answers

Add your own answers!

Ask a Question