1

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.

  • Why don't you select also id into the buffer table? – user30184 Nov 27 '19 at 22:35
  • 1
    You don't even need to create buffer if you just use ST_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. – Vince Nov 27 '19 at 22:37
  • I can add columns manually to buffer creation but I have around 100 columns, and will be quite hard to add it one by one. – Roman Nov 27 '19 at 22:41
  • If you don't want to use * nor to manually type all the fields, then just have Postgres generate the field list: dba.stackexchange.com/questions/22362/… – JGH Nov 28 '19 at 12:34
3

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.

| improve this answer | |
2

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.

| improve this answer | |

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.