[postgis-users] Convert x, y into geometry and update two geometries

[Nicolas Ribot]

Hi,
Shouldn't you declare an BEFORE trigger, not an AFTER one ?

I will quote a recent post from Mickael (Automated area calculating,
feb 4th 2008):

"An AFTER trigger runs after the insert or update has completed so
it's too late to modify the row except by issuing an UPDATE statement
within the trigger function, which would result in infinite recursion
if the UPDATE fired the trigger again.  AFTER triggers are suitable
for propogating the row to other tables when you need to be certain
of seeing the final value.  If you want to modify the row being
inserted or updated then you need to use a BEFORE trigger."

HTH
Nico

On 05/02/2008, Broun Uganda <tekuganda at hotmail.com> wrote:
>
> Dear all,
> I have x,y coordinates in CSV file which i copy to postgres database using:
>
>
> COPY links (id,from_node,to_node,....) FROM 'path/to/file' DELIMITER ',' CSV
> HEADER;
> COPY nodes (id,point_x,point_y...) FROM 'path/to/file' DELIMITER ',' CSV
> HEADER;
> I have then written a trigger that would generate the_geom from number 2
> (pointx,point_y);
> I then use the id of Number 2 to generate line geometries for Number 1,
> where from_node and to_node are the beginning and end points of the line.
> I use the following triggers:
> BEGIN
> IF NEW.the_geom is null
> THEN
> INSERT INTO NODES  (the_geom)
> (SELECT SetSRID(MakePoint(new.point_x, new.point_y), -1)FROM nodes) ;
> ELSE (for circumstances where i have the_geom and not the point_x and
> point_y)
> NEW.point_x = X(NEW.the_geom);
> NEW.point_y = Y(NEW.the_geom);
>
> END IF;
> RETURN NEW;
> END
>
> CREATE TRIGGER calc_point_insert
>   AFTER INSERT
>   ON nodes
>   FOR EACH ROW
>   EXECUTE PROCEDURE calc_point();
> CREATE TRIGGER calc_point_update
>    AFTERUPDATE
>    ON nodes
>    FOR EACH ROW
>    EXECUTE PROCEDURE calc_point();
> This runs with no end and if i remove the bold NEW  i get an error the_geom
> doesn't exist.
> On the Number 1 Side I have this that is for joining points, which i thought
> should work but doesnt work.
>
> CREATE OR REPLACE FUNCTION update_links()
>   RETURNS "trigger" AS
> $BODY$
> BEGIN
>
> UPDATE links SET the_geom = MakeLine(NEW.the_geom,ends.the_geom)
>         FROM nodes As ends
>         WHERE NEW.id = links.from_node and ends.id = links.to_node;
>
> UPDATE links SET the_geom = MakeLine(starts.the_geom,NEW.the_geom)
>         FROM nodes As starts
>         WHERE starts.id = links.from_node and NEW.id =links.to_node;
>
>
> RETURN NEW;
> END$BODY$
> CREATE TRIGGER update_links_insert
>   AFTER INSERT
>   ON nodes
>   FOR EACH ROW
>   EXECUTE PROCEDURE update_links();
> Similarly for update.
>
> Please help me with these triggers
> Thank you
>
> Broun Uganda
>
>
> ________________________________
> Express yourself instantly with MSN Messenger! MSN Messenger
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>