[postgis-users] Trigger Function for Updating Geom

[Bruce Rindahl]

And the trigger...


-- Trigger: well_update on wells

-- DROP TRIGGER well_update ON wells;

CREATE TRIGGER well_update
  BEFORE INSERT
  ON wells
  FOR EACH ROW
  EXECUTE PROCEDURE check_well_ids();


Bruce

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Bruce
Rindahl
Sent: Wednesday, January 23, 2008 10:17 AM
To: 'PostGIS Users Discussion'
Subject: RE: [postgis-users] Trigger Function for Updating Geom

Well here is my trigger function which does work:

-- Function: check_well_ids()

-- DROP FUNCTION check_well_ids();

CREATE OR REPLACE FUNCTION check_well_ids()
  RETURNS "trigger" AS
$BODY$
  DECLARE
  BEGIN

    if (NEW.utm_x > 0 and NEW.utm_y > 0) then
	NEW.the_geom = setSRID(MakePoint(NEW.utm_x,NEW.utm_y),26913);
	NEW.latdecdeg = y(transform(NEW.the_geom,4326));
	NEW.longdecdeg = x(transform(NEW.the_geom,4326));
    end if;

    RETURN NEW;
  END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION check_well_ids() OWNER TO postgres;

My updates give all attributes including utm_x and utm_y.  The trigger
function updates the_geom and computes the lat/long values on the fly and is
very fast (450,000 row table).

Bruce

-----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 10:10 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Trigger Function for Updating Geom

I was unaware of makepoint.  I agree it is easier to work with.  
However, same problem as before.  It doesn't seem to work with the 
function I created.  It will work no problem if I use an update 
statement.  Strange

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

_______________________________________________
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