Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

Can u help me in creating a trigger and function so that every time i change the flag in table A the buffer distance should get changed and also its geometry field also , ie the polygon area?

I have two tables named A and B.

Table A has the fields GID (primary key), FLAG, Buffer_distance, and the geometry field. Table B has the fields GID and buffer_distance, and has only three records as shown below:

GID(PK)   Buffer_distance(Double Precision)
1     1000
2     2000
3     3000

Table A's structure:

gid(PK)  buffer_distance(Double Precision)  flag(Text)  the_geom( Geometry)
1           200              1          the_geom
2            100             2          the_geom
3            100             3          the_geom
4           500              3          the_geom
5           300              2          the_geom
6           899              1          the_geom

What I want is the value of flag in table A to be updated from table B, and as soon as I updated the value of flag in table A the buffer_distance in A should also get replaced along with the geometry.

If what you want is to update buffer_distance in Table A based on the flag value (ie, buffer_distance=2000 when flag=2), can anyone write me an SQL case statement that will accomplish this. along with this the_geom Filed the geometry should also change with the new buffer_distance. i used the following code. help me to correct the same

create or replace function update_point_buffer() returns trigger as
$$
 begin
 -- delete
IF (TG_OP = 'UPDATE') THEN
UPDATE tablea
    SET buffer_distance = (SELECT tableb.buffer_distance
                        FROM tableb
                       WHERE 
                       "tableb.gid" = "tablea.flag");

   END IF;
   return NEW;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER trg_upgradeBufferTrigger
        AFTER UPDATE ON tablea
        FOR EACH ROW
        EXECUTE PROCEDURE update_point_buffer();

i have used this query bit it has given this error ERROR: syntax error at or near "v_output" LINE 2: v_output integer;

create or replace function update_point_buffer(v_pointflag) returns number as---------------------Return buffer distance v_output number ; begin

SELECT buffer_distance into v_output FROM point_buffer WHERE gid = v_pointflag;

return v_output; END;

CREATE TRIGGER trg_upgradeBufferTrigger-----------------------------------Update table from B to A AFTER UPDATE ON point FOR EACH ROW

begin

update point_buffer set buffer_distance= (select buffer_distance from table B where gis= :new.gis)

where gis= :new.gis

end;

can any body correct it for use.

share|improve this question
    
possible duplicate of gis related question –  blah238 Nov 13 '12 at 4:49
    
UPDATE tableA SET buffer_distance = CASE WHEN flag = 1 THEN 1000 WHEN flag = 2 THEN 2000 WHEN flag = 3 THEN 3000 END; sir can u help me in creating a trigger and function so that every time i change the flag in table A the buffer distance should get changed and also its geometry field also , ie the polygon area . –  user12766 Nov 14 '12 at 14:55
    
Please edit your question instead of posting new information in the answers section. Also, you seem to have created a second account. Do you want me to merge them for you? Which one do you want to continue using? –  underdark Nov 18 '12 at 11:53
add comment

1 Answer 1

Something like this will update the buffers to the new values in Table A:

UPDATE tableA
SET buffer_distance =
    CASE
        WHEN flag = 1 THEN 1000
        WHEN flag = 2 THEN 2000
        WHEN flag = 3 THEN 3000
    END;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.