[GIS] Changing Buffer table geometry dynamically in PostGIS using plain SQL

postgispostgresqlsql

I have two tables in PostGIS, a point and a point_buffer table. The point table has a buffer_distance field, with a default value say 200. Now I want to change the buffer table geometry whenever I change the buffer_distance value in my point table. I can do this for a single row in my point_buffer table using the following:

UPDATE point_buffer
SET the_geom = (SELECT ST_Buffer(the_geom,500) FROM point WHERE gid = 1)
FROM point
WHERE point.gid = point_buffer.gid

But whenever I am trying to change the whole of point_buffer table (droping WHERE clause in my sub-query) I am getting error message:

'ERROR: more than one row returned by a subquery used as an
expression'.

My question is, can I change the entire point_buffer table in one go? I know one option is using a for lopp, with the upper bound being the count value of my point table and incrementing the point.gid value . But I want to do this in plain SQL.

Best Answer

You can use a view, but you can also use triggers to automatically update your buffer table when you modify the original point table. This is really useful if you don't want to regenerate the buffers every time you view your table, as buffer computation is a cpu-intensive task.

Here is a full code sample which implements it : a point table and a point_buffer table which is automatically updated based on the point table modifications.

You can test it with QGIS : open both tables, enter edit mode on the point table. Move a point or change the buffer_distance value, and every time you save, the buffer layer will be updated.

enjoy :)

drop table if exists point;
create table point (
    gid serial primary key
    , point_name varchar
    , buffer_distance double precision
    , the_geom geometry
);

drop table if exists point_buffer;
create table point_buffer (
    gid serial primary key
    , point_gid integer
    , the_geom geometry
);

select populate_geometry_columns();

insert into 
    point (point_name, buffer_distance, the_geom) 
select
    'point ' || n::varchar as point_name
    , random() * 100 + min_buf as buffer_distance
    , st_setsrid(st_point(random() * 10000 + x0, random() * 10000 + y0), 2154) as the_geom
from
        generate_series(1, 1000) as n
        , (values (10)) as foox(x0)
        , (values (10)) as fooy(y0)
        , (values (10)) as buf(min_buf);

-- insert values into point_buffer
insert into
    point_buffer (point_gid, the_geom)
select
    gid as point_gid
    , st_buffer(the_geom, buffer_distance)
from
    point;

-- update all point_buffer
update
    point_buffer as pb
set
    the_geom = st_buffer(p.the_geom, p.buffer_distance)
from
    point as p
where
    p.gid = pb.point_gid;

-- add trigger to automate insert / delete / update
create or replace function update_point_buffer() returns trigger as
$$
begin
    -- delete
    IF (TG_OP = 'DELETE') THEN
        delete from point_buffer as pb where point_gid = OLD.gid;
        return OLD;
    -- insert
    ELSIF (TG_OP = 'INSERT') THEN
        insert into
            point_buffer (point_gid, the_geom)
        select
            NEW.gid as point_gid
            , st_buffer(NEW.the_geom, NEW.buffer_distance);
        return NEW;
    -- update
    else
        update
            point_buffer as pb
        set
            the_geom = st_buffer(NEW.the_geom, NEW.buffer_distance)
        where
            pb.gid = NEW.gid;
        return NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_point_point_buffer ON point;
CREATE TRIGGER trg_point_point_buffer AFTER DELETE OR INSERT OR UPDATE ON point
    FOR EACH ROW EXECUTE PROCEDURE update_point_buffer();

/* use it */

-- insert
insert into 
    point (point_name, buffer_distance, the_geom)
select
    'added point to test trigger' as point_name
    , random() * 100 + min_buf as buffer_distance
    , st_setsrid(st_point(random() * 10000 + x0, random() * 10000 + y0), 2154) as the_geom
from
        (values (10)) as foox(x0)
        , (values (10)) as fooy(y0)
        , (values (10)) as buf(min_buf);

select
    st_astext(pb.the_geom)
    , *
from 
    point_buffer as pb
join
    point as p
on
    p.gid = pb.point_gid
where
    p.point_name = 'added point to test trigger';

-- update
update 
    point as p
set
    the_geom = st_setsrid(st_point(0, 0), 2154)
    , buffer_distance = 1
where
    p.point_name = 'added point to test trigger';

-- check point_buffer
select
    st_astext(pb.the_geom)
    , *
from 
    point_buffer as pb
join
    point as p
on
    p.gid = pb.point_gid
where
    p.point_name = 'added point to test trigger';

-- delete
delete from
    point as p
where
    p.point_name = 'added point to test trigger';

-- check point_buffer
select
    *
from
    point_buffer as pb
where
    point_gid = 1001;
Related Question