[postgis-users] Trigger Function for Updating Geom

Kevin Neufeld kneufeld at refractions.net
Wed Jan 23 08:31:32 PST 2008


Also, in your case, I would recommend using a RULE instead of a TRIGGER 
- it could be much faster.
http://www.postgresql.org/docs/8.2/static/rules-triggers.html
http://www.postgresql.org/docs/8.2/static/sql-createrule.html

A trigger is fired on every row affected. So in your case, if you update 
a row in your table, postgres inserts a new row into the table and 
deletes the old row flagging it as a dead tuple.  Then the trigger fires 
on the row and another insert and delete operation is performed.  That's 
2 inserts and 2 deletes - a rather expensive update operation.

On the other hand, a rule simply rewrites the query once at the 
beginning, leaving you with one update operation.

Following the docs, your rule might be something like:
CREATE OR REPLACE RULE make_geom_rule AS
  ON INSERT TO hyd
  DO INSTEAD
    INSERT INTO hyd (field1, ..., hydgeom) VALUES
      (NEW.field1, ..., SetSRID(ST_MakePoint(NEW.longitude, 
NEW.latitude), 8307));

-- Kevin

Obe, Regina wrote:
> It should be 
>
> NEW.hydgeom :=  
>
> You have an = there (which would evaluate to a boolean of some sort)
> instead of plpgsql assignment :=
>
> Also I think using makepoint is more efficient,shorter to write, and
> easier to read
>
> So would be
>
> CREATE or replace FUNCTION make_geom () RETURNS trigger as
> $BODY$
> BEGIN
> NEW.hydgeom := SetSRID(makepoint(NEW.longitude, NEW.latitude), 8307);
> RETURN NEW;
> END
> $BODY$
> LANGUAGE plpgsql;
>
> Hope that helps,
> Regina
>
>
>
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
> Willem Buitendyk
> Sent: Wednesday, January 23, 2008 4:20 AM
> To: rindahl at lrcwe.com; PostGIS Users Discussion
> Subject: Re: [postgis-users] Trigger Function for Updating Geom
>
> What you revised makes more sense;  however, the problem now is that the
>
> geom column does not update at all.  I modified the function to include
> RETURN NEW;
>
> CREATE or replace FUNCTION make_geom () RETURNS trigger as
> $BODY$
> BEGIN
> NEW.hydgeom = GeometryFromText('POINT (' || cast(NEW.longitude as 
> varchar(10)) || ' ' || cast(NEW.latitude as varchar(10)) || ')', 8307);
> RETURN NEW;
> END
> $BODY$
> LANGUAGE plpgsql;
>
> CREATE TRIGGER make_geom_trigger AFTER INSERT OR UPDATE ON hyd FOR EACH 
> ROW EXECUTE PROCEDURE make_geom();
>
>
> Any ideas?
>
> Cheers
>
>
>
>
> Bruce Rindahl wrote:
>   
>> Try:
>>
>> CREATE or replace FUNCTION make_geom () RETURNS trigger as
>> $BODY$
>> BEGIN
>> NEW.hydgeom = GeometryFromText('POINT (' || cast(NEW.longitude as 
>> varchar(10)) || ' ' || cast(NEW.latitude as varchar(10)) || ')',
>>     
> 8307);
>   
>> END
>> $BODY$
>> LANGUAGE plpgsql;
>>   
>> This will update each row as it is processed.  What you were doing was
>> trying to update EVERY row as EACH one was processed.
>>
>> Bruce Rindahl
>>
>>
>>
>>
>> -----Original Message-----
>> From: postgis-users-bounces at postgis.refractions.net
>> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
>>     
> Willem
>   
>> Buitendyk
>> Sent: Tuesday, January 22, 2008 2:17 PM
>> To: postgis-users at postgis.refractions.net
>> Subject: [postgis-users] Trigger Function for Updating Geom
>>
>> Hi there,
>>
>> I am trying to figure out how to go about creating a trigger that 
>> updates the geom on each insert.  Currently, I am using the following 
>> update statement on the table after bulk loading data in:
>>
>> update temp_hyd set hydgeom = GeometryFromText('POINT (' || 
>> cast(longitude as varchar(10)) || ' ' || cast(latitude as varchar(10))
>>     
>
>   
>> || ')', 8307);
>>
>> I admit to feeling a little lost as I am only now just starting to 
>> migrate GIS data into a server.  So using relational databases are a 
>> little new to me (and I don't think Access counts).  I am hoping that
>>     
> only
>   
>> updating those newly inserted records will be a whole lot faster then 
>> redoing the whole table.
>>
>> Here is what I have so far:
>>
>>
>> CREATE or replace FUNCTION make_geom () RETURNS trigger as
>> $BODY$
>> BEGIN
>> update hyd set hydgeom = GeometryFromText('POINT (' || cast(longitude
>>     
> as 
>   
>> varchar(10)) || ' ' || cast(latitude as varchar(10)) || ')', 8307);
>> END
>> $BODY$
>> LANGUAGE plpgsql;
>>
>> CREATE TRIGGER make_geom_trigger AFTER INSERT OR UPDATE ON hyd FOR
>>     
> EACH 
>   
>> ROW EXECUTE PROCEDURE make_geom();
>>
>> And of course it doesn't work.  I get the following after doing an 
>> insert statement:
>>
>> ERROR: stack depth limit exceeded
>> SQL state: 54001
>> Hint: Increase the configuration parameter "max_stack_depth", after 
>> ensuring the platform's stack depth limit is adequate.
>> Context: SQL statement "update hyd set hydgeom =
>>     
> GeometryFromText('POINT 
>   
>> (' || cast(longitude as varchar(10)) || ' ' || cast(latitude as 
>> varchar(10)) || ')', 8307)"
>>
>> Appreciate any help with this,
>>
>>
>> Willem
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>   
>>     
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> -----------------------------------------
> The substance of this message, including any attachments, may be
> confidential, legally privileged and/or exempt from disclosure
> pursuant to Massachusetts law. It is intended
> solely for the addressee. If you received this in error, please
> contact the sender and delete the material from any computer.
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>   



More information about the postgis-users mailing list