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 :)